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.
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar