ACCESS FEED: RE: [MS_AccessPros] using query results for a default value

Wow. Thanks A.D. I believe this is what I'm after. I'm going to try it.

Liz Ravenwood
Programmer/Analyst
Super First Class Products
B/E Aerospace
O: 1.520.239.4808
www.beaerospace.com

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of A.D. Tejpal
Sent: Saturday, July 28, 2012 4:58 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] using query results for a default value

Generation Of Sequential Numbers
Using A Function As Default Value
(No Code In Form's Module)
========================

John, Liz,

We could consider an interesting way to use a function as default value of a bound control so as to generate a series of sequential numbers.

It is observed that if a built-in function like DMax() is directly used as default value, the default value as displayed, tends to lag behind by one step. This can be remedied by wrapping the said function within a user defined function, where a statement for form's Recalc action could be incorporated.

Sample function Fn_SetNextNumber() as given below, illustrates the approach suggested above. It accepts two arguments: (a) A pointer to the form object and (b) Name of bound control for which next number in series is required to be displayed (as default value).

For example let Rank be the name of bound control. All that is needed is the following expression against default value on data tab of control's property sheet (No need for any code in form's module):

=Fn_SetNextNumber([Form],"Rank")

My sample db named Form_CustomSeriesByDefaultProperty demonstrates the principle outlined above. It is in access 2000 file format and is available at Rogers Access Library. Link:
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=45

Apart from plain number series, the sample db also covers alpha-numeric series with the desired prefix.

Best wishes,
A.D. Tejpal
------------

' Sample code in general module
'============================
Function Fn_SetNextNumber( _
frm As Access.Form, _
ControlName As String) As Long
frm.Recalc
Fn_SetNextNumber = Nz(DMax( _
frm(ControlName).ControlSource, _
frm.RecordSource), 0) + 1
End Function
'============================

----- Original Message -----
From: John Viescas
To: MS_Access_Professionals@yahoogroups.com
Sent: Friday, July 27, 2012 23:17
Subject: RE: [MS_AccessPros] using query results for a default value

Liz-

Yes. The Before Insert event happens when the user types the first character on
the form.

http://msdn.microsoft.com/en-us/library/bb256707(v=office.12).aspx

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Liz Ravenwood
Sent: Friday, July 27, 2012 7:36 PM
To: 'MS_Access_Professionals@yahoogroups.com'
Subject: RE: [MS_AccessPros] using query results for a default value

John, can I then have it displayed as the user is typing in data respective to
that field?

Liz Ravenwood
Programmer/Analyst
Super First Class Products
B/E Aerospace
O: 1.520.239.4808
www.beaerospace.com

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Friday, July 27, 2012 9:31 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] using query results for a default value

Liz-

You cannot use that sort of expression in the Default Value property, but you
*can* look it up in the Before Insert event of the form and set it for the user.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Liz Ravenwood
Sent: Friday, July 27, 2012 6:25 PM
To: 'MS_Access_Professionals@yahoogroups.com'
Subject: RE: [MS_AccessPros] using query results for a default value

Crystal, there's no way to show the next number as the default is there?

I did something similar in another database that went behind the scenes and did
an insert to the subtable, but I don't want to do that again if I don't have to.

I do, however, want the user's to have that comfortable view of seeing the
LayerNum reflect back as they are typing the data in.

It's for FAA certification, so I think it's important that they see the actual
layer number showing back as they type.

Why can't I seem to use =nz(DMax("[LayerNum]","[TestSideLayers]","[ID] = " &
[me].[ID]),0)+1 as an expression for default value?

Liz Ravenwood
Programmer/Analyst
Super First Class Products
B/E Aerospace
O: 1.520.239.4808
www.beaerospace.com

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Crystal
Sent: Wednesday, July 25, 2012 5:00 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] using query results for a default value

Hi Liz,

create a field in the table:

Ordr, Integer or Long Integer, depending on how big it needs to be. Put a
control on the form for it. I usually put it first on a continuous form.

here is easiest way to number the records within the parent ID

'~~~~~~~~~~~~~~~~~ form BeforeInsert event (new records only) Private Sub
Form_BeforeInsert(Cancel As Integer) 'strive4peace
'fill out order according to position
Me.Ordr= Me.Recordset.RecordCount + 1 End Sub '~~~~~~~~~~~~~~~~~

When a record is deleted, there needs to be code to renumber everything so this
continues to work. This also assumes there are no records with Ordr filled.
Sometimes, if there is a chance than an existing record might not have Ordr
filled, I do this:

'~~~~~~~~~~~~~~~~~ form BeforeUpdate event (new and changed records) Private Sub
Form_BeforeUpdate(Cancel As Integer) 'strive4peace
If IsNull(Me.Ordr) Then
Me.Ordr = Me.Recordset.RecordCount _
+ IIf(Me.NewRecord, 1, 0)
End If
'~~~~~~~~~~~~~~~~~

if the Ordr field is for the entire table:

nz(DCount("*", "[tablename]"),0) +1

if you want to get the next number (perhaps numbering did not start with 1
and/or there are gaps):

nz(DMax("[fieldname]", "[tablename]"),0) +1

Warm Regards,
Crystal

*
(: have an awesome day :)
*

________________________________
From: Liz Ravenwood <liz_ravenwood@beaerospace.com>
To: "'MS_Access_Professionals@yahoogroups.com'"
<MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, July 25, 2012 5:29 PM
Subject: RE: [MS_AccessPros] using query results for a default value

Yes Crystal, you've got it. I am only thinking of the calculation because it is
the default value for the next record in the subform.

So I need to learn how order is used?

Liz Ravenwood

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Crystal
Sent: Wednesday, July 25, 2012 4:21 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] using query results for a default value

Hi Liz,

I am having trouble understanding what you are asking ...

Do you want a field called LayerNum to have its value sequentially created when
new records are added to the subform? You should store this information, not
calculate it. I often put a field called Ordr (ordEr is reserved) into tables
and have code to create its value automatically. Then users can rearrange
things by changing its value :)

Warm Regards,
Crystal

*
(: have an awesome day :)
*

________________________________
From: Liz Ravenwood

Pros, I have a subform with an FK of ID from the PK of main form ID.

I need the subform to autonumber not the ID, but something called a LayerNum but
only within the ID. That is, each ID of main record has multiple layers of sub.

So, I was thinking I could set the default value to a calculated field in a
query.

SELECT Max([LayerNum]+1) AS [Next]
FROM TestSideLayers
GROUP BY TestSideLayers.ID
HAVING (((TestSideLayers.ID)=[Forms]![DSMain]![ID]));

And then have the form field default be: =[NextLayer]![Next]

However, I get that nasty #Name? business or whatever it is and it isn't
working.

Liz Ravenwood

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.