Auto-populate a Text Box in a Form

[JRA]

How do I auto populate a text box in a form. Example: In my table I am following a sequence for my ID Number (AB1, AB2, AB3, ... YX97, YZ98, YZ99) and when I open the form I want the next ID Number to already appear in the text box following the sequence.

I also need help setting the date to auto populate as well.

Thanks,
James.

 

[moonlight]

To autopopulate the date, Just put Now() as the default value for the text box. This will pick up the current system date. When you make another entry in the record the date will be set.

As for the Index... That is more difficult. because you cannot increment the text... If you were doing AA1.. AA2 etc, you could hide the an autonumber field and add your letters in a new text box... But it looks that you want to go through the alphabet

I am sure that you cannot do it the way you want

Hope this helps

 

[Vika]

To auto populate your text box with the next Id where Id contains alpha characters and numbers, It's gonna take you some coding. What you want to do is on Open Form event do the next.


Private Sub Form_Open(Cancel As Integer)


'This Function will auto populate text box with the next Id
'taking that Id contains of 4 characters
'(2 leading alpha characters + 2 trailing numeric characters).
'Note: It will only work if 2 leading alpha characters are in Upper Case.


Dim db As Database
Dim rs As Recordset
Dim sql As String

Set db = CurrentDb


'The below Select statement will return the Maximum Id value.


sql = "Select Max(Id) as MaxId From Table1"
Set rs = db.OpenRecordset(sql)

If IsNull(rs!MaxId) = False Then 'At least 1 Id already exists

With rs
If Mid(!MaxId, 3, (Len(!MaxId) - 2)) = 99 Then 'If 2 traling Numbers has
'reached 99
If Asc(Mid(!MaxId, 2, 1)) = 90 Then 'If second character equals "Z"

Text0 = Chr(Asc(Left(!MaxId, 1)) + 1) & "A" & "1" 'next character in
'alphabet + "A" + "1"

Else
Text0 = Left(!MaxId, 1) & Chr(Asc(Mid(!MaxId, 2, 1)) + 1) & "1" 'first character +
'next character in alphabet + "1"

End If
Else
Text0 = Left(!MaxId, 2) & Mid(!MaxId, 3, (Len(!MaxId) - 2)) + 1 'Max(Id) + 1

End If
End With
Else
Text0 = "AA1" 'Create first Id

End If
End Sub


I hope it works for you!