How To Modify The following Code

[Firefly]

i have a table called tblMember which has the following
MemberID(PK)(auto)
Surname
FirstName
BatchID(Left Blank Until Report is Printed)

I also have a table called tblBatch
BatchID(PK)AutoNumber
BatchDateTime

Basically what the below code does is assign a batch# when ever new records are entered and the user clicks create batch command button will record the batch number automatically to all new members which a report hasnt been printed yet. What i want to do is modify the code so that a user can enter a batch number manually instead of access assigning a number and once batch # is assigned manually will apply that number to all the new members.

Option Compare Database
Option Explicit

Private Sub cmdCreateBatch_Click()
'On Error GoTo Err_Handler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String
Dim lngBatchID As Long
Dim lngKt As Long

'Create the new batch, and get the number.
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblBatch", dbOpenDynaset, dbAppendOnly)
rs.AddNew
rs!BatchDateTime = Now()
lngBatchID = rs!BatchID
rs.Update
rs.Close

'Give this batch number to all members who have not been printed.
strSql = "UPDATE tblMember SET BatchID = " & lngBatchID & " WHERE BatchID Is Null;"
db.Execute strSql, dbFailOnError
lngKt = db.RecordsAffected

'Show the response.
Me.lstBatch.Requery
MsgBox "Batch " & lngBatchID & " contains " & lngKt & " member(s)."

Exit_Handler:
Set rs = Nothing
Set db = Nothing
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "cmdCreateBatch_Click()"
Resume Exit_Handler
End Sub

Private Sub cmdPrintBatch_Click()
'On Error GoTo Err_Handler
Dim strWhere As String
Const strcDoc = "rptMemberList"

If IsNull(Me.lstBatch) Then
MsgBox "Select a batch to print."
Else
'Close the report if it's already open (so the filtering is right.)
If CurrentProject.AllReports(strcDoc).IsLoaded Then
DoCmd.Close acReport, strcDoc
End If
'Open it filtered to the batch in the list box.
strWhere = "BatchID = " & Me.lstBatch
DoCmd.OpenReport strcDoc, acViewPreview, , strWhere
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, ".cmdPrintBatch_Click"
Resume Exit_Handler
End Sub

Any Ideas Thanks.

 

[Firefly]

sample database

Here is the sample database i'm trying to modify for my own form

 

[qdogfball]

First, you will need to kill the Autonumber and replace it with Integer value in the tblBatch.
(You can include an autonumber, just name it different than BatchID)

You will need to make the new field BatchID the PK. (I think)

Then for the following:

rs.AddNew
rs!BatchDateTime = Now()
lngBatchID = rs!BatchID
rs.Update
rs.Close

You are going to take out the rs!BatchId and replace it with what ever field you have on your form for the person to enter.

Say you have an unbound text box with a number format and it is named BaNumber, then

rs.AddNew
rs!BatchDateTime = Now()
lngBatchID=BaNumber  ' new user inputs this number
rs.Update
rs.Close

I think that should give you a good head start.