Auto Increment Default Value

[jsmith1611]

Hello, I need to have some default values for fields that increment.

I have 3 tables for a database that tracks microfilm tapes: tape, group, and sequence. There are multiple groups per tape and multiple sequences per group.

I have a main form for the tape, group is a subform of tape, and sequence is a subform of group.

I want the group number on the group form to default to "A" and then increment automatically when I create a new record in this subform. I want the sequence number to default to "1" and increment every time I add a new row in the sequence subform.

I think I need to use variables for this, but I am not sure of where to do the incrementing and how to not increment for the first group on a tape or the first sequence in a group.

Can anyone help with this?

Thanks,

Josh

 

[HiTechCoach]

I would use the form's On Current event to check for a new record ( Me.NewRecord = True ). If a new record then use the DMAX() function to look ul the largest value and then increment it and set the value to the control on the form.

 

[Dennisk]

only use the dmax function if the the db is single user only, otherwise you will need to embed the dmax function in a locking function, otherwise 2 users WILL return the SAME number.

 

[BPBP]

Hi, can you please explain more on the locking function? in short, how do i prevent 2 users returning the same number?

 

[jsmith1611]

HiTechCoach,

I have tried to implement the dmax function to auto increment and default my group numbers. Here is the code from the form:

Private Sub Form_Current()
If Me.NewRecord = True Then
group_num = DMax(groupNum, Group)
groupNum = group_num
End If
End Sub

group_num is a public string variable and groupNum is the form field.

I am getting an "Invalid use of Null" error when I try to create a new group.

Any more help you can give will be greatly appreciated.

Thanks,
Josh

 

[HiTechCoach]

Try something like:

Private Sub Form_Current()

If Me.NewRecord = True Then
    group_num = Nz(DMax("[groupNum]", "[Group]"),0)
    Me.groupNum = group_num + 1
End If

End Sub

Your DMAX() is looking at the table/query named Group for the max of the field named groupNum. Is that correct?

 

[jsmith1611]

HiTechCoach,

Yes, group is the table and groupNum is the field. The problem may be that the group numbers are repeated on different tapes:

Tables:

tape
group
sequence

The tapeNum field is the foreign key on group. The groupNum field is a foreign key on sequence. All the tables have autoNumber primary keys.

 

[jsmith1611]

Here is the code that I am using on my sequence form:

Option Compare Database
Public increment As Integer
Private Sub Form_Current()
If Me.NewRecord = True Then
increment = DMax("seqNum", "Sequence")
seqNum = increment + 1
End If

End Sub

It is giving me a seqNum of 10 when it should be a 1. I think I need to include a condition in the dmax function that makes it look at only the current record when retrieving the dmax number. I am just not sure how to do that. The sequence form is a subform of the group form and is joined by the group_id number on both tables. The group form is a subform of the tape form and is joined by the tapeNum on both tables.

Thanks,

Josh