I have three subs that control the flow of my data entry table
This sub increments the cedula_no + 1 after a form is saved and printed
Private Sub Form_Current()
UsingSaveButton = False
if Me.NewRecord = True Then
Me.cedula_no = Nz(DMax("cedula_no", "cedula_tbl"), 1) + 1
End If
If Not IsNull(Me.OpenArgs) Then
Me.TxtuserID = Me.OpenArgs
End If
This is to print the form
Private Sub Print_Record_Click()
UsingSaveButton = True
DoCmd.RunCommand acCmdSaveRecord
DoCmd.PrintOut
DoCmd.GoToRecord , , acNewRec
Dim sql As String
Dim sql1 As String
Dim collect As Integer
Dim db As DAO.Database
Set db = CurrentDb
collect = Me.TxtuserID
If Not IsNull(collect) Then
sql = "UPDATE cedula_tbl SET collector=('" & collect & "') WHERE cedula_no=DMax('cedula_no', 'cedula_tbl')"
db.Execute (sql)
End If
sql1 = "UPDATE control_tbl, Query_last SET control_tbl.CurrentNo = [Query_Last].[cedula_no]"
db.Execute (sql1)
this sub asks the encoder to enter new cedula_no. My problem is every time I enter a new cedula_no. whenever I print the form it increments the previous cedula_no and not the one I entered.
each receipt has 50 stubs so for example I entered 1000 as the starting cedula_no the receipt will end at 1050 and the receipts are sometimes not numerically in order so the encoder needs to input the starting cedula_no every time he/she gets finish the whole stub. and I wish to make an automatic input box when it reaches the endNo.
Private Sub Command106_Click()
Dim message As String
Dim title As String
Dim MyValue As Long
Dim sql As String
Dim sql1 As String
Dim sql2 As String
Dim db As DAO.Database
Set db = CurrentDb
message = "Enter Starting Cedula Number"
title = "Enter Starting Cedula Number"
MyValue = InputBox(message, , default)
If Not IsNull(MyValue) Then
Me.cedula_no = MyValue
sql = "UPDATE control_tbl SET StartNo=('" & MyValue & "') WHERE cedulano=DMax('cedulano', 'control_tbl')"
sql1 = "UPDATE control_tbl SET EndNo=('" & MyValue & "') + 50 WHERE cedulano=DMax('cedulano', 'control_tbl')"
sql2 = "UPDATE control_tbl SET CurrentNo=('" & MyValue & "') WHERE cedulano=DMax('cedulano', 'control_tbl')"
db.Execute (sql)
db.Execute (sql1)
db.Execute (sql2)
End If
End Sub
This sub increments the cedula_no + 1 after a form is saved and printed
Private Sub Form_Current()
UsingSaveButton = False
if Me.NewRecord = True Then
Me.cedula_no = Nz(DMax("cedula_no", "cedula_tbl"), 1) + 1
End If
If Not IsNull(Me.OpenArgs) Then
Me.TxtuserID = Me.OpenArgs
End If
This is to print the form
Private Sub Print_Record_Click()
UsingSaveButton = True
DoCmd.RunCommand acCmdSaveRecord
DoCmd.PrintOut
DoCmd.GoToRecord , , acNewRec
Dim sql As String
Dim sql1 As String
Dim collect As Integer
Dim db As DAO.Database
Set db = CurrentDb
collect = Me.TxtuserID
If Not IsNull(collect) Then
sql = "UPDATE cedula_tbl SET collector=('" & collect & "') WHERE cedula_no=DMax('cedula_no', 'cedula_tbl')"
db.Execute (sql)
End If
sql1 = "UPDATE control_tbl, Query_last SET control_tbl.CurrentNo = [Query_Last].[cedula_no]"
db.Execute (sql1)
this sub asks the encoder to enter new cedula_no. My problem is every time I enter a new cedula_no. whenever I print the form it increments the previous cedula_no and not the one I entered.
each receipt has 50 stubs so for example I entered 1000 as the starting cedula_no the receipt will end at 1050 and the receipts are sometimes not numerically in order so the encoder needs to input the starting cedula_no every time he/she gets finish the whole stub. and I wish to make an automatic input box when it reaches the endNo.
Private Sub Command106_Click()
Dim message As String
Dim title As String
Dim MyValue As Long
Dim sql As String
Dim sql1 As String
Dim sql2 As String
Dim db As DAO.Database
Set db = CurrentDb
message = "Enter Starting Cedula Number"
title = "Enter Starting Cedula Number"
MyValue = InputBox(message, , default)
If Not IsNull(MyValue) Then
Me.cedula_no = MyValue
sql = "UPDATE control_tbl SET StartNo=('" & MyValue & "') WHERE cedulano=DMax('cedulano', 'control_tbl')"
sql1 = "UPDATE control_tbl SET EndNo=('" & MyValue & "') + 50 WHERE cedulano=DMax('cedulano', 'control_tbl')"
sql2 = "UPDATE control_tbl SET CurrentNo=('" & MyValue & "') WHERE cedulano=DMax('cedulano', 'control_tbl')"
db.Execute (sql)
db.Execute (sql1)
db.Execute (sql2)
End If
End Sub
