Continuous form Update

[CanWest]

I have a continuous form that has the following fields
ActivityID
ContactID
TaxreceiptAmount
ReceipNumber

I need to populate the ReceiptNumber field with numbers that are incremented. Currently I am using the following code and it works perfectly

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = Forms!pfrm_ActivityRecNumberList.Recordset

Do Until rst.EOF
   Me.ReceiptNumber = Nz(DMax("ReceiptNumber", "tbl_Activity")) + 1
   rst.MoveNext
Loop

rst.Close
Set rst = Nothing

This looks for the highest receipt number and then simply adds 1 to it on each loop. I need one thing more

Instead of looking for the highest number I want to specify a starting number in a text box txtStartingRecNumber

The problem I am having is all records are then set to the value of txtStartingRecNumber plus 1. It does not increment through. I understand why this is happening, I just don't know what to do to make it work the way I want it.

 

[GinaWhipp]

I'm not sure I understand, where is txtStartingRecNumber in your code?

What I would do is have a Table, hmm, let's name it tblStartingNumber then run an UPDATE query that changes the value everytime you run DMax(). Below is a sample using tblGetCoilID which holds the *next* CoilID...

Sub IncCoil()
    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE tblGetCoilID SET tblGetCoilID.gcValue = DMax('crCoilID','tblCoilReceiving')+1  WHERE (((tblGetCoilID.gcItem)='Next Coil'));"
    DoCmd.SetWarnings True
End Sub

...and this is the code to assign a new value...

Private Sub cmdNewCoilNumber_Click()
    If Dirty Then Exit Sub
        DoCmd.GoToRecord , , acNewRec
    Me.txtCoilID = DLookup("gcValue", "tblGetCoilID", "gcItem='Next Coil'")
    Me.txtCoilID = DMax("crCoilID", "tblCoilReceiving") + 1
    DoCmd.SetWarnings False
        IncCoil
    DoCmd.SetWarnings True
End Sub

As a side note, you will need to write some code to check for duplicates. I can see a scenario where Users try to use the same ID number.