number populate for a recordset

[ksgirl]

I am very new to all of this, but I need some help! Currently I have two forms. These forms are for entering new data into the database. The first form's primary key is the 'Clearance ID' field which has an autonumber format. After all info is filled into the first form then the user is prompted to fill out the second form. This is where I need help.....The second form is referenced to the 'Clearance ID' given in the first form. This second form, 'Tag Info', allows the user to add tag placement information. Right now the 'tag' field is a number field, where the user has to input the tag #, such as 1, 2, 3, 4...... for each record they add. Ultimately I want this field to be auto-populated, starting with 1 and increasing by 1, each time a field is added. Any suggestions?

 

[ksgirl]

OK, this is an old post and I thought I would see if anyone could give me some advice. I was given some help on this situation but couldn't get it to work, therefore I lost track of it, and I really need to get it figured out! This is what I've tried so far:

Private Sub Form_Open()

Dim dbCurrent As Database
Dim rstTagID As DAO.Recordset
Dim Tag As Long

Set dbCurrent = CurrentDb()
Set rstTagID = dbCurrent.OpenRecordset("Clearance Tag List", dbOpenSnapshot)

If rstTagID.EOF Then
Tag = 1
Else
rstTagID.MoveLast
Tag = rstTagID!Tag + 1
End If

Me.Tag = Tag

End Sub

Does anyone have any suggestions?
~Nicole

 

[Fizzio]

I'm not a 100% sure about what you are trying to acheive but have you looked at the DMax function in Access Help?
You could then potentially use DMax +1 (simplified)

 

[Rich]

Why not use DMax +1

 

[ksgirl]

Hey thanks for your replies! I looked up DMax, and have changed everything to this:

Private Sub Form_OnOpen()

Dim rstTagID As DAO.Recordset
Dim Tag As Long

Set rstTagID = DMax("[Tag]", "Clearance Tag List")

If IsNull(rstTagID) Then
Tag = 1
Else
rstTagID.MoveLast
Tag = rstTagID!Tag + 1
End If

Me.Tag = Tag

End Sub

But it still won't do anything! Any other thoughts?

 

[ksgirl]

Hey thanks for your reply Pat, but I changed the 'Me.Tag' line according to your suggestion and now it won't let me open the form from another form. I know there is something wrong with the code, but everytime I look at it and make changes to it, nothing happens! Do you see anything else that could be the cause of my problem?? Thanks again!
~Nicole

 

[Rich]

Is Tag a reserved word in access?

 

[ksgirl]

I'm not sure what you mean?? Let me do some looking....

 

[ksgirl]

No it is not. Any ideas?

 

[ksgirl]

I know everyone is sick of seeing this, and so am I! This question is for Pat.... Your suggestion still didn't do the trick. I'm sure it is something simple that I'm missing. I currently have this in the OnOpen of the form...should it be somewhere else? I've tried changing all parts of the code several times, but I still can't open the form from the first! I can only open it in design mode! IS there something wrong with my IF statement? Thanks again!
~Nicole