Add consecutive numbers to a Continuous Sub Form

[noboffinme]

Hi

I have a Continuous Sub Form with one of the field names as Sub_ID & I can't use Autonumber as a datatype.

I have it as a number data type & I want it to increment + 1 as the new field appears.

So the user fills in the continuous form which initially is set to a default of [Sub_ID] = 1, & as they type data into it the next field that automatically appears should have the [Sub_ID] = 2 & so on...

Thanks

 

[raghuprabhu]

Hi I have used the following code to increment...


*****************qryForArmy*****************
SELECT tblTheLot.numTXNNo
FROM tblTheLot
WHERE (((tblTheLot.numYear)=CInt(Format(Date(),"yyyy"))) AND ((tblTheLot.numService)=1) AND ((tblTheLot.numSource)=[Forms]![frmMainMenu]![List0]))
ORDER BY tblTheLot.numTXNNo;

*****************Code reqd.*****************
Private Sub Form_Current()
If Me.NewRecord = True Then
lgTransNumber = Nz(DMax("numTXNNo", "qryForArmy"))
Me.numTXNNo = lgTransNumber + 1
End If
End Sub


See if this helps.... If not I will post a small database.
Cheers

 

[noboffinme]

Thanks for that

I've adapted your code to suit my form but am getting an error using DMax. any ideas??

Private Sub Form_Current()
Dim max_sub_no As Integer
If Not Me.Sub_Issue_No Is Null Then
max_sub_no = (DMax(Sub_Issue_No))
End If
If Me.NewRecord = True Then
Me.Sub_Issue_No = max_sub_no + 1
End If

End Sub

 

[boblarson]

any ideas??

Yes, place your cursor on the word DMAX (in the VBA window) and hit F1 to bring up the help. If you compare the syntax shown in the help and in raghuprabhu's code with yours you will see that you are not doing it any way near what it should be.

 

[noboffinme]

Here's the answer without using DMax for anyone who wants it.

I couldn't use DMax as I couldn't refer to a table or query, I just wanted to hold the last or highest value for a numeric field & add + 1 each time a new text field was created by the Continuous form.

Movelast was the answer as it held that value for me to use.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim NextIssueNo As Integer
Dim DB As Database
Dim Rst As Recordset
Set DB = CurrentDb()
Set Rst = DB.OpenRecordset("Enter your SELECT Statement")
If Rst.RecordCount = 0 Then
NextIssueNo = 1
Else
Rst.MoveLast
NextIssueNo = Rst(0) + 1
End If
Rst.Close

Me.Sub_Issue_No = NextIssueNo
End Sub

 

[raghuprabhu]

Sorry for not getting back earlier.

If you have not sorted it yet, please post your sample and I will do it for you.

Cheers
Raghu Prabhu