Solved

Run Query then compare 2 fields Failure

Posted on 2014-12-07
19
209 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
ID: 40486295
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
ID: 40486805
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
ID: 40486821
a typo:

frm_MG_3055-1!BadgeIssued & "")

/gustav
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:Ernest Grogg
ID: 40486843
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
ID: 40486909
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
ID: 40487049
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
ID: 40487070
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
ID: 40487093
yes....
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40487103
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
 
LVL 36

Assisted Solution

by:PatHartman
PatHartman earned 333 total points
ID: 40487201
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
ID: 40487427
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
ID: 40487440
Great. Thanks for the feedback.

/gustav
0
 

Author Comment

by:Ernest Grogg
ID: 40487450
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
ID: 40487471
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
ID: 40487535
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
ID: 40487551
With that said...what would be the best way to go about this?
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40487565
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 36

Assisted Solution

by:PatHartman
PatHartman earned 333 total points
ID: 40487570
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
ID: 40489247
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

820 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