Run Query then compare 2 fields Failure

Hello,

I have the following for a field on my form but can't get it to run...

Private Sub BadgeIssued_Exit(Cancel As Integer)
Run Query!qry_MG_BadgeSearch
If Query!qry_MG_BadgeSearch!BadgeIssued =Forms!frm_MG_3055-1!BadgeIssued Then
        If Query!qry_MG_BadgeSearch!TimeOut = IsNull Then
        MsgBox "Please Enter a Different Badge as that badge is already assigned to a customer...and You must sign out the badge first"
        End If
Else
If Query!qry_MG_BadgeSearch!TimeOut = IsNotNull Then
End If


States that qry_MG_BadgeIssued  must be part of the collection.  Can't figure out what is wrong...Plus the query needs to run in the background to do this procedure....


Any Help is appreciated...

Thanks !
Ernest GroggAsked:
Who is Participating?
 
Gustav BrockCIOCommented:
I guess it would be something like this:

Private Sub BadgeIssued_Exit(Cancel As Integer)

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Select * From qry_MG_BadgeSearch Where BadgeIssued = " & Forms!frm_MG_3055-1!BadgeIssued & "")
    If rst.RecordCount > 0 Then
        If IsNull(rst!TimeOut.Value) Then
            MsgBox "Please enter a different badge as that badge is already assigned to a customer, and you must sign out the badge first."
        Else
            ' Something else ...
        End If
    End If
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing

/gustav
0
 
Ernest GroggAuthor Commented:
hmmm....

It is giving me a syntex error here:

 Set rst = dbs.OpenRecordset("Select * From qry_MG_BadgeSearch Where BadgeIssued = " & Forms!frm_MG_3055-1!BadgeIssued "")
0
 
Gustav BrockCIOCommented:
a typo:

frm_MG_3055-1!BadgeIssued & "")

/gustav
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Ernest GroggAuthor Commented:
sorry It still gives me the same error...?  I tried the & earlier and nothing and still nothing now...
0
 
Gustav BrockCIOCommented:
Then probably one of these four is missing:

Query: qry_MG_BadgeSearch
Field in query: BadgeIssued
Form: Forms!frm_MG_3055-1
Control on form: BadgeIssued

or the form isn't open.

/gustav
0
 
Ernest GroggAuthor Commented:
Well I did find that

Forms!frm_MG_3055-1  

should be

Forms!FRM_MG_3055-1

but everything else is there and the form is open because I am filling in the field(s)

Still the compile error Syntex error...

frustrating...
0
 
Gustav BrockCIOCommented:
Then create a new query, go to SQL view and insert:

Select * From qry_MG_BadgeSearch

Will it run and return a field "BadgeIssued"?

/gustav
0
 
Ernest GroggAuthor Commented:
yes....
0
 
Gustav BrockCIOCommented:
Then you may not have saved the value in the form. Try running this:

    Dim dbs As DAO.Database
     Dim rst As DAO.Recordset
     Set dbs = CurrentDb
     MsgBox "*" & Forms!frm_MG_3055-1!BadgeIssued & "*"
     Exit Sub
     Set rst = dbs.OpenRecordset("Select * From qry_MG_BadgeSearch Where BadgeIssued = " & Forms!frm_MG_3055-1!BadgeIssued & "")
     If rst.RecordCount > 0 Then
         If IsNull(rst!TimeOut.Value) Then
             MsgBox "Please enter a different badge as that badge is already assigned to a customer, and you must sign out the badge first."
         Else
             ' Something else ...
         End If
     End If
     rst.Close
     Set rst = Nothing
     Set dbs = Nothing

and see what the MsgBox shows.

 /gustav
0
 
PatHartmanCommented:
NEVER, EVER run this type of validation code in the exit event of a control.  The whole point of validation code is to prevent bad data from being saved so you need to use the correct event.  Even if you cancel the exit event (which you are not doing), you can't stop the form from closing and that will save the record with the BAD data!!!!!  Also, there is no need to run the validation code if the user is simply tabbing through the fields and didn't change anything.  You ONLY need to run the code if the user made a change.

That leaves you with two choices.  The BeforeUpdate event of the control or the BeforeUpdate event of the form.  If BadgeIssued is optional, then you can use the control's BeforeUpdate event to validate it.  If BadgeIssued is required, then you should have it defined as required on the table definition and a better place to validate it would be the FORM's BeforeUpdate event because there you can also check for a null value.  You will NEVER identify missing values UNLESS you use the FORM's BeforeUpdate event.

Private Sub Form_BeforeUpdate(Cancel As Integer) 
If Me.BadgeIssued & "" = "" Then      'empty or null
    Msgbox "Badge is required.  Please enter a Badge Number.", vbOKOnly
    Me.BadgeIssued.SetFocus
    Exit Sub
end if
 If DCount("*", "qry_MG_BadgeSearch", "BadgeIssued = '" & Me.BadgeIssued & "'" > 0 Then
         MsgBox "Please Enter a Different Badge as that badge is already assigned to a customer...and You must sign out the badge first", vbOKOnly
         Cancel =True
         Me.BadgeIssued.SetFocus
         Exit Sub
 End If
Exit Sub
 

Open in new window

0
 
Ernest GroggAuthor Commented:
Gustav,

I got it working.  I created a new form and new queries, ect.

Here is what I got for me Save Record Command button....but after I update the Badge # so something good, how do I get the record to save with this:

 DoCmd.RunCommand acCmdSaveRecord
 DoCmd.GoToRecord , , acNewRec

I don't want the user to "tab" through to a new record but click the button...

Private Sub Command34_Click()
Dim strCtlName As String, NullCtl As String, Msg As String, ctl As Control
strCtlName = ""
For Each ctl In Me.Controls
    If ctl.Tag = "REQ" Then
        If Len(Me(ctl.Name) & "") = 0 Then
            strCtlName = strCtlName & ctl.Name & ";"
        End If
    End If
Next
If Len(strCtlName) > 0 Then
    NullCtl = Mid(strCtlName, 1, InStr(strCtlName, ";") - 1)
    Msg = "Please fill out the required fields!" & vbCr & vbCr
    Msg = Msg & Left(strCtlName, Len(strCtlName) - 1)
    MsgBox Msg, vbCritical, "Required Fields"
    Cancel = True
    Me(NullCtl).SetFocus
    Exit Sub
Else

 Dim dbs As DAO.Database
      Dim rst As DAO.Recordset
      Set dbs = CurrentDb
     ' MsgBox "*" & Forms!frmtest!BadgeIssued & "*"
      'Exit Sub
      Set rst = dbs.OpenRecordset("Select * From test Where BadgeIssued = " & Forms!frmtest!BadgeIssued & "")
      If rst.RecordCount > 0 Then
          If IsNull(rst!TimeOut.Value) Then
              MsgBox "Please enter a different badge as that badge is already assigned to a customer, and you must sign out the badge first."
          Me.BadgeIssued.SetFocus
          Else
               
 ' Something else ...
          End If
      End If
      rst.close
      Set rst = Nothing
      Set dbs = Nothing

End If
     

End Sub
0
 
Gustav BrockCIOCommented:
Great. Thanks for the feedback.

/gustav
0
 
Ernest GroggAuthor Commented:
after I update the Badge # so something good, how do I get the record to save with this:

  DoCmd.RunCommand acCmdSaveRecord
  DoCmd.GoToRecord , , acNewRec

 I don't want the user to "tab" through to a new record but click the button...
0
 
Gustav BrockCIOCommented:
You could try with:

If Me.Dirty = True Then
    Me.Dirty = False
End If

But I think like Pat that perhaps your concept is a bit flaky ...

/gustav
0
 
Ernest GroggAuthor Commented:
Pat,

I do see what you are saying and thinking much about it...

But I see that we are validating duplicates, and a duplicate is OK, per se...If the field: BadgeIssued matches one in the query, then we need to see if the TimeIn Field is empty/null. If it isn't null then OK, move on, but if it is empty we need to make sure that the duplicate gets issued to a customer.

For instance.

Customer A is in line, gets a badge issued:  51
Customer B is next in line and the data entry guru tries to issue 51 without first getting 51 back, is not allowed, that data entry person must issue another number, say 52.  

Now say this:

Customer A gets badge 51 comes back later in the day and turns this in...the data entry person enters the time returned...

Customer B comes and gets a badge 51.  This is OK because the time field is not empty...


there is the TimeOut field and a TimeIn Field  

One can issue a badge but not reissue the same badge if that badge is not properly noted and returned.
0
 
Ernest GroggAuthor Commented:
With that said...what would be the best way to go about this?
0
 
PatHartmanCommented:
Private Sub Command34_Click()
Ernest,
Let me restate - If you really want to validate data, you need to learn to use the correct event.  Although the code in this event will complain if a required control is empty, it will do NOTHING to stop the bad record from being saved.  Please verify that for yourself by simply closing the form.  To accomplish your goal, you need to move the code to the FORM's BeforeUpdate event AND you MUST set Cancel = True before you exit the event as I showed in my earlier example.  

I sometimes have save buttons on forms but they are basically a binky for the user.  It makes him feel better.  In the click event of such a button, I simply save the record.  Keep in mind that Access does everything it can to keep the data safe and that means that it saves it whenever it thinks a save is appropriate.  You can force a save yourself but it isn't necessary.  The key to understanding validation logic is to think of your data as being placed into the big end of a funnel.  At the small end is the FORM's BeforeUpdate event.  If everything is good, the trap opens and the data is saved.  If it is not good, either you or Access can stop the data from being saved by cancelling the BeforeUpdate event.  Sometimes, it is helpful to put validation logic closer to the data entry point and in those cases you would use the BeforeUpdate event of the control but you still might need final logic in the BeforeUpdate event of the FORM.  So, in your example, you could check for duplicates in the control level event but the ONLY place you can validly check for missing data is in the form level event.

If Me.Dirty Then
  DoCmd.RunCommand acCmdSaveRecord
End If

or if you prefer, use the TRICK suggested by Gus.  I say it is a trick because it is not obvious what the instruction will do so always add a comment that it will force the record to be saved.
0
 
PatHartmanCommented:
Our posts overlapped.
If you are checking for an empty issue date rather than existence in the table, then change the WHERE part of the DLookup() to also include null in the issue date field.
0
 
Ernest GroggAuthor Commented:
I moved everything to the beforeupdate event

thanksfs for all your help and helping me understand where the proper place would be to place it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.