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.
Tidak ada komentar:
Posting Komentar