Create Next Tag number based on last number in record

[Cheryl R]

I created my database before I really knew what I was doing so I am trying to go back and normalize the tables.

One of the mistakes I made was to make my TagNo an autonumber field and had code to find the next tag number. Of course, this caused problems when trying to join to other tables so I copied the number to another field that is Number data type and deleted the autonumber field. Now when I try to enter a new order, the new tag number will populate but when it creates the new line below that, it tries to use the same number, thus creating an error. What did I do wrong?

Here is the code I am using to get the new number:
Public Function GetNextTag() As Double
Dim strSQL As String
Dim lsMaxTagNo As Double
Dim rst As Recordset
Dim db As Database
Set db = CurrentDb
strSQL = "SELECT Max([TagNo]) AS MaxTagNo FROM tblTagDetails ORDER BY Max([TagNo]);"
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

lsMaxTagNo = rst!MaxTagNo + 1


GetNextTag = lsMaxTagNo
End Function

This worked perfectly when it was an autonumber field.
Yes, the data type on the TagNo field is double.

Please help if you can.

[ssanfu]

You should have/need a reference set to "Microsoft DAO 3.6 Object Library"

Your code works fine. I did restructure it a little....

Public Function GetNextTag() As Double
   Dim db As DAO.Database
   Dim rst As DAO.Recordset
   Dim strSQL As String
   Dim lsMaxTagNo As Double

   Set db = CurrentDb    '<< this is DAO syntax

   strSQL = "SELECT Max([TagNo]) AS MaxTagNo FROM tblTagDetails;"
   Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

   ' calc next number
   lsMaxTagNo = rst!MaxTagNo + 1

   'set return value
   GetNextTag = lsMaxTagNo

   'clean up
   rst.Close
   Set rst = Nothing
   Set db = Nothing

End Function

[Dal Jeanis]

ssanfu - glad you got rid of the ORDER BY in a select for MAX. It was bugging me.

[Cheryl R]

Thank you for your help! It kinda works, in that when I open the form, it is a new number but it still gives me the same number on the next line and gives the error. This happens before the current line is entered completely. I have that field's default value as =getnexttag(). That's what it should be, correct?

[Cheryl R]

I forgot to mention this is in a subform where I enter multiple tag numbers per order. I'm not sure if you tried it that way, but it might help to know. I am sure it is just something simple I am missing.

[Missinglinq]

Here's a typical Auto-incrementing Number hack. As you can see, it's usually done using the DMax() function rather than opening and searching a RecordSet.

This first code example would be for an IDNumber that is defined in the table as a Number datatype.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
  If RecordsetClone.RecordCount = 0 Then
    Me.IDNumber = 1
  Else
    Me.IDNumber = DMax("[IDNumber]", "YourTableName") + 1
  End If
End If
End Sub

If IDNumber is defined as Text

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then
   If RecordsetClone.RecordCount = 0 Then
   Me.IDNumber = "1"
  Else
   Me.IDNumber = DMax("val([IDNumber])", "YourTableName") + 1
  End If
End If
End Sub

The above would be appropriate in a Bound Form. If your Form is Unbound, instead of using the Form_BeforeUpdate event, you could use the same code just before committing your Record.

IDNumber and YourTableName would be replaced with your actual names for these items.

The only caveat for this code is that it cannot be used, as is, with Data Entry set to Yes. In that case the RecordCount is always 0 when a new session is started, regardless of the number of Records in the Table.

Linq ;0)>

[Dal Jeanis]

Note - this post was written before linq's post

The second number shouldn't appear until the first record has been created, and the GetNextTag() function should return a different number...

Has to be a timing issue. Perhaps the second (new blank) record is being populated before the first record is saved.

Question - do the numbers have to be purely sequential? You could have a persistent variable inside of GetnextTag that makes sure to give the next higher number when numbers are issued in quick succession, but this might occasionally result in a missed number.

[Cheryl R]

The recordset function was written by my tutor, who is currently out of the country (nice!). This is why from now on, I need to KNOW how and why things do what they do. When this field was an autonumber field, this code worked perfectly.

I tried linq's suggestion. I put the code on the form_before update and temporarily removed the =GetNextTag() and nothing appeared in the tagNo field for either record.

Dal, I think it IS a timing issue. As soon as I click on the second field on the first record, it automatically creates a new line with the same tagNo, meaning to me it is not saving the record right away. I have tried adding a docmd.save record and even a me.dirty = false on the Make(second field)_GotFocus.

I would like to keep the numbers sequential, but I don't really care if we miss one here or there.

Thank you again for your help!

[Cheryl R]

This is still not working for me... anyone have any suggestions I can try?

[Dal Jeanis]

This is a shot in the dark, but you can try to refresh the query using the subform's Form_LostFocus. No, that would have the unfortunate side effect of refreshing the query every time you move from one subform records to another.

[Dal Jeanis]

Try this -

1) Take the default value out of the control, disable the control,
2) use the BeforeInsert event of the subform to calculate the new tag number and assign it to the control.

That way, it won't be calculated until you start typing in the new subform record.

[ssanfu]

Cheryl, Tell me if this is right:

You have a main form, the record source table is tblOrders and the PK of the table is the field OrderID - autonumber.

You have a sub form, the record source table is tblTagDetails and the PK of the table is the field TagNo. The field TagNo is type Double (defined in the table)

The tables are linked on tblOrders.OrderID - tblTagDetails.OrderID

You are NOT using tblTagDetails.TagNo to link to other tables. (NOTE: is is really a bad idea to use any type except a Long Integer to link tables.)

Can a main form record (1) have sub form records 1, 2, 3, 4
AND
main form record (2) have sub form records 1, 2, 3, 4?????


BTW, You are using a test (backup) dB to do this testing until the bugs have been worked out!! Right?????

You need to get the max tag number, based on the main form PK (linking field).

OK, open the subform and remove "=GetNextTag()" from the control "TagNo" Default property.

Add code for the subform before update event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   Me.TagNo = GetNextTag(Me.Parent.OrderID)
End Sub

Change the GetNextTag code to:

Public Function GetNextTag(pOrder As Long) As Double
   Dim db As DAO.Database
   Dim rst As DAO.Recordset
   Dim strSQL As String
   Dim lsMaxTagNo As Double

   Set db = CurrentDb

   strSQL = "SELECT Max([TagNo]) AS MaxTagNo "
   strSQL = strSQL & " FROM tblTagDetails"
   strSQL = strSQL & " WHERE [OrderID] = " & pOrder & ";"

   Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

   ' calc next number
   lsMaxTagNo = Nz(rst!MaxTagNo) + 1

   'set return value
   GetNextTag = lsMaxTagNo

   'clean up
   rst.Close
   Set rst = Nothing
   Set db = Nothing

End Function

[Dal Jeanis]

steve - quick question -

What happens if someone comes back and changes another field on the subform the next day. Wouldn't that subform record get a new ID number?

That's why I was looking at subform.BeforeInsert.

Dal

[ssanfu]

You are right. I just did a test and the tag number changed. Nice catch...


This change would take care of the edit scenario:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   If Me.NewRecord Then
      Me.TagNo = GetNextTag(Me.Parent.OrderID)
   End If
End Sub

Also, would still need to lock the control. In my test dB, the tag number control can be edited, which shouldn't be allowed to happen.

[Dal Jeanis]

Okay, Cheryl, you have two possible architectures for the same fix, and either should work.