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

Rabu, 25 Juli 2012

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]