Solved

Run Query then compare 2 fields Failure

Posted on 2014-12-07
19
199 Views
Last Modified: 2014-12-09
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 !
0
Comment
Question by:Ernest Grogg
  • 9
  • 7
  • 3
19 Comments
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 167 total points
Comment Utility
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
 

Author Comment

by:Ernest Grogg
Comment Utility
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
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
a typo:

frm_MG_3055-1!BadgeIssued & "")

/gustav
0
 

Author Comment

by:Ernest Grogg
Comment Utility
sorry It still gives me the same error...?  I tried the & earlier and nothing and still nothing now...
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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
 

Author Comment

by:Ernest Grogg
Comment Utility
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
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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
 

Author Comment

by:Ernest Grogg
Comment Utility
yes....
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 333 total points
Comment Utility
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
 

Author Comment

by:Ernest Grogg
Comment Utility
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
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
Great. Thanks for the feedback.

/gustav
0
 

Author Comment

by:Ernest Grogg
Comment Utility
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
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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
 

Author Comment

by:Ernest Grogg
Comment Utility
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
 

Author Comment

by:Ernest Grogg
Comment Utility
With that said...what would be the best way to go about this?
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 333 total points
Comment Utility
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
 

Author Comment

by:Ernest Grogg
Comment Utility
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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now