Melissa,
I'd recommend against concatenating two values together like that. Instead,
have a ClientID field to store (ABC, CDE, ...) and a numeric field (RptNum),
and create a composite index which contains both fields and requires that
combined they are unique (no duplicates).
Lets assume your form has a combo box (cbo_Client) and a text box
(txt_RptNum) that is mapped to the RptNum. You could do something along the
lines of the following in the AfterUpdate event of the combo box:
Private Sub cbo_Client_AfterUpdate
strCriteria as string
if me.newrecord then
strCriteria = "[ClientID] = '" & me.cbo_Client & "'"
me.txt_RptNum = NZ(DMAX("RptNum", "yourTable", strCriteria), 0) + 1
endif
Next Sub
When you create a new record, and select the client in the cbo_Client combo
box, Access will automatically generate the next higher value for that
client. The problem with this technique is that if you are developing a
multi-user database, then two users could attempt to create records for the
same client at the same time, and they could both end up with the same
RptNum. If you create the index mentioned above, then when the first user
saves their record, they will get no error, but when the second user tries
to save their record, they will get an error indicating that the action
would create duplicates. You could then trap for this error in your error
handler and recompute the RptNum for the second record.
There are other ways to do this as well, one would be to maintain a table
that tracks the NextNum for each of your clients. In this scenario, you
would create a table (tblClientNum) which contains just two fields
(ClientID, NextNum). You would then create a function that retrieves the
value from the NextNum field and then increments it. Because this would be
an almost instantaneous process, you would not have to worry about
duplicates in the RptNum field for the same client. The function might look
like:
Public Function fnNextNum(ClientID as String) as long
Dim rs as DAO.Recordset
set rs = Currentdb.Openrecordset("tblClientNum",,dbfailonerror)
rs.findfirst "[ClientID] = '" & ClientID & "'"
if rs.nomatch then
rs.AddNew
rs!ClientID = ClientID
rs!NextNum = 2
rs.Update
fnNextNum = 1
else
rs.edit
fnNextNum = rs!NextNum
rs!NextNum = rs!NextNum + 1
rs.Update
end if
End Sub
HTH
Dale
