Unanswered: Populate Form with concatenate value based on criteria.
Populate Form with concatenate value based on criteria.
I am new to access and the Board . I have a button on a form that I need to
1 Create a new record
2 Look at the auto number Field (in the DB it ID1)
3 If the auto number is less than 2 the 00 + ID1 (Example ID1= 1 so it should equal 001 when all is said and done.)
Else if the = 2 than 0+ ID1 (Example ID1= 10 so it should equal 010 when all is said and done.)
4 Store the concatenated value in the new active record Field would be ID
This is the code
Private Sub New_issue_Click()
Dim rs As DAO.Recordset
Dim NewID As String
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Action_Items WHERE False")
If LenB(rs.Fields![ID1]) > 2 Then
NewID = "0" & "rs.Fields![ID]"
ElseIf LenB(rs.Fields![ID1]) > 1 Then
NewID = "00" & "rs.Fields![ID]"
rs.Fields![ID] = NewID
The SQL statement you use to open the recordset seems to be incorrect, though (... WHERE False).
Moreover, ID1 won't have a value when you create a new record until you save it. You should try something like:
NewID = Format(Nz(DMax("ID1", "Action_Items"), 0) + 1, "000")