Validate one field in form based on query (1 Viewer)

[Cold Zero]

I'm not that good in VBA and trying to get this figure out if you can help with a code or Macro.

Attached you can find example of what I'm trying to do. The database have two tables.

tblDrivers: with list of all drivers and license expire date
tblJourney: Where the journey information is added along with the driver ID

I have a query (queValidDrivers) that contain only the drivers with valid license

In the form (frmAddJourney), when entered driver ID doesn't have a valid license (based on the mentioned query) a massage to highlight that the driver doesn't have valid license.

I have tried If dcount in BeforeUpdate event of the form with no luck

 

[plog]

I have a query (queValidDrivers) that contain only the drivers with valid license

No you don't, you have a query that tells you the status of every driver, both valid and invalid. I would suggest you add criteria to that query so that only valid drivers appear in it.

Then for the form, don't check for valid drivers, only let the user select valid drivers. Make the driver input a combo box based on your query so that the user has only valid drivers to choose from.

https://support.office.com/en-us/ar...ombo-box-70abf4a9-0439-4885-9099-b9fa83517603

 

[Cold Zero]

No you don't, you have a query that tells you the status of every driver, both valid and invalid. I would suggest you add criteria to that query so that only valid drivers appear in it.

Then for the form, don't check for valid drivers, only let the user select valid drivers. Make the driver input a combo box based on your query so that the user has only valid drivers to choose from.

Thanks for the suggestion.

The attached database is only an example, the one I'm working on will have like 60 drivers from different departments and would like to make it as a textbox if it can be done.

 

[Cold Zero]

I tried below code in BeforeUpdate but keep receiving errors

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strMsg As String
    strMsg = "Data has changed."
    strMsg = strMsg & "Do you want to add New Request?"
    strMsg = strMsg & "Click Yes to Save or No to Discard changes."
    If MsgBox(strMsg, vbQuestion + vbYesNo, "Add New?") = vbYes Then
    If (DCount("*", "queValidDrivers", "[DriverID] = forms![frmAddJourney]!txtDriverID") > 0) Then
        'do nothing'
    Else
        DoCmd.RunCommand acCmdUndo
        Beep
        MsgBox "Driver is not authorized", vbOKOnly, "Error"
        
        
        'For Access 95, use DoMenuItem instead
        'DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
    End If

End Sub

 

[Micron]

Rule #1 - always state the error and which line it occurs on.
Rule #2 - you cannot include object/variables as literal strings within the quotes of your concatenation. So "[DriverID] = forms![frmAddJourney]!txtDriverID") needs to be
"[DriverID] = '" & forms![frmAddJourney]!txtDriverID & "'") if this value is text. If not, the single quotes would not be correct.

EDIT - Hit click too soon. If that is not where you're raising the error, see rule #1. There could be more than one issue that we ought to be dealing with at the same time rather than in multiple replies.

 

[Cold Zero]

Rule #1 - always state the error and which line it occurs on.
Rule #2 - you cannot include object/variables as literal strings within the quotes of your concatenation. So "[DriverID] = forms![frmAddJourney]!txtDriverID") needs to be
"[DriverID] = '" & forms![frmAddJourney]!txtDriverID & "'") if this value is text. If not, the single quotes would not be correct.

EDIT - Hit click too soon. If that is not where you're raising the error, see rule #1. There could be more than one issue that we ought to be dealing with at the same time rather than in multiple replies.

Thanks for your reply Micron.

Was able to figure it out after your reply with the following code in from BeforeUpdate

Private Sub Form_BeforeUpdate(Cancel As Integer)

If ((DCount("DriverID", "queValidDrivers", "[DriverID]= " & Me.txtJourDriver & " And [CerStatus]= 'Valid'")) > 0) Then
        Dim strMsg As String
        strMsg = "Data has changed."
        strMsg = strMsg & "Do you want to add?"
        strMsg = strMsg & "Click Yes to Save or No to Discard changes."
        If MsgBox(strMsg, vbQuestion + vbYesNo, "Add New?") = vbYes Then
    
    Else
        DoCmd.RunCommand acCmdUndo
        End If
Else
    Beep
    MsgBox "The Driver doesn't have a valid driving license", vbOKOnly, ""
    Me.txtJourDriver.SetFocus
    Cancel = True
End If

End Sub

 

[Micron]

Did you omit code for a Yes response that was in your prior version?
In case you're interested, here's a version that's shorter because the logic is reversed and the default flow is used.

Dim strMsg As String

If (DCount("*", "queValidDrivers", "[DriverID] = forms![frmAddJourney]!txtDriverID") = 0) Then
  DoCmd.RunCommand acCmdUndo
  Beep
  MsgBox "Driver is not authorized", vbOKOnly, "Error"
  Exit Sub
End If

strMsg = "Data has changed. Do you want to add New Request?" & vbCrLf
strMsg = strMsg & "Click Yes to Save or No to Discard changes."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add New?") = vbNo Then Me.Undo

You might want to consider Yes No Cancel instead. If user made an entry error and wants to change it, clicking No will wipe all changes for the form. If you allow Cancel, they can go back and correct without losing everything they've done.