I am looking for the best approach to validating that a record has been printed prior to allowing the user to move onto the next record.
I have a hidden field named "RecordLock", I need to check prior to the entry of new record that no records have a blank value for Recordlock - if there is then force the user to return the that record and finish the processing (meaning click the Print button)
Here is my code so far:
On beforeupdate of the named combo. See the ;>> I need to be able to return to the previous record and print then set the "RecordLock" field = true. This is where I am having an issue.
PS. keep in mind that this is a Mainform/suform and field in question is on the subform - but the Print button is on the Mainform
Private Sub cboVendorName_BeforeUpdate(Cancel As Integer)
Dim curDB As DAO.Database
Dim strSQL As String
Dim rs As DAO.Recordset
Dim X As Integer
Dim ctl As Control
Dim frm As AccessObject
Set curDB = CurrentDb
gContractID = Me.ContractNumber
gInvID = Me.IDInvoice
strSQL = "SELECT RecordLock" & _
" FROM tblinvoice" & _
" WHERE (((ContractNumber)=" & Chr(34) & gContractID & Chr(34) & ") AND ((RecordLock)=False))"
Set rs = curDB.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Select Case MsgBox("Sorry unable to process request until previous Invoice has been printed." _
& vbCrLf & "" _
& vbCrLf & "Do you wish to print last Invoice entry?" _
, vbYesNoCancel Or vbCritical Or vbDefaultButton1, "Invoice Needs to be Printed")
DoCmd.OpenReport "rptAPCodingSlip", acViewPreview, "IDInvoice = " & Chr(39) & gInvID & Chr(39) & ""
DoCmd.OpenReport "rptInvoiceActivity_Slip", acViewPreview, "ContractNo = " & Chr(34) & gContractID & Chr(34) & ""
'>>>>> If X = 0 Then
DoCmd.RunSQL "Update tblinvoice Set RecordLock = -1 where IDInvoice = " & gInvID & ""
Case vbNo Or vbCancel