comparing fields in two differnt tables

[RickK]

I have a form with several tables. One table (SerialTest) has a field (SerialNumber) that the operators select a serial number from a list. I need to check another field (Serial_Number) on a different table (OpenAlert) to make sure that the serial number is not in that field. If it is in the second table then pop up a message " serial number is all ready open" , with an OK button and then allow entry.
I know I need to use Before update on the SerialNumber field on the SerialTest table but I am not sure what is wrong with my entry. Here is what I have now.

Private Sub SerialNumber_BeforeUpdate(Cancel As Integer)
If DLookup("*", "OpenAlert", "[Serial_Number]=#" & Me.SerialNumber & "#") > 0 Then
   MsgBox "This already exists", vbExclamation, "Record Exists"
   Cancel = True
   Me.SerialNumber.Undo
End If
End Sub

The serial numbers in the list contain alpha and numeric characters.

 

[sneuberg]

You probably want DCount rather than DLookup and unless SerialNumber is a date you don't want the pound sign delimiter.

Try

If DCount("*", "OpenAlert", "[Serial_Number]= " & Me.SerialNumber ) > 0 Then

 

[RickK]

I need to make a correction , in case it matters. I said that I want to compare it to a table on that form . Actually when the form is opened I run a query and I display the results on the form. The query name is OpenAlert and the field name is Serial Number.

Any result in the query represents a service ticket that is already in an open status and I don't want anyone opening another service ticket against the same serial number. So if someone tries to select serial number 3 and serial number 3 already has an open call I want it to warn them with a message.

So , I tried that and this is what my code looks like now.

Private Sub SerialNumber_BeforeUpdate(Cancel As Integer)
If DCount("*", "OpenAlert", "[Serial_Number]=" & Me.SerialNumber) > 0 Then
   MsgBox "This already exists", vbExclamation, "Record Exists"
   Cancel = True
   Me.SerialNumber.Undo
End If
End Sub

But when I tested it came back with this error:
Run-Time '3075': Syntex error (missing operator) in query expression '[Serial_Number]=MX 6-1/MX60544'

 

[sneuberg]

Since [SerialNumber] is a text field it needs to be delimited with single quotes so it would be:

If DCount("*", "OpenAlert", "[Serial_Number]= '" & Me.SerialNumber & "'") > 0 Then

 

[RickK]

I made the change but now I get this error:

Run-time error '2471': The expression you entered as a query parameter produced this error: '[Serial_Number]'

Any ideas what is causing this?

Thanks for all of your help
Rick

 

[sneuberg]

You could get that error if [Serial_Number] is misspelled or not in the query "OpenAlert". Maybe you need to lose the underscore.

Try [Serial Number] if that's what's in the query.

 

[sneuberg]

This is weird. I just tested my assertion that if [Serial_Number] were [Serial Number] in the query you would get that error. This doesn't seem to be true. In my test if they differ like this the DCount just returns 0 no matter what. On the other hand if for example I change [Serial Number] to [Serial Number1] in the DCount then I get the 2471 error.

Could you upload your database?

 

[RickK]

You could get that error if [Serial_Number] is misspelled or not in the query "OpenAlert". Maybe you need to lose the underscore.

Try [Serial Number] if that's what's in the query.

This is what the problem was. I thought that it needed the underscore because it had a space between Serial and Number. I removed the underscore and it worked.

Thanks for the help

 

[RickK]

So this code works with one exception.
I use it in a field under Got Focus and it pops up when your on the same ticket. I need to add a statement to my code to prevent this from running if the field named WorkorderId on this form (Workorders) matches the same field WorkordersID on the same query OpenAlert.

In other words, I only want the below code to run if the WorkordersID field in the form Workorders is different than the field WorkordersID in the query/form OpenAlert.

Here is my exsiting code;

If DCount("*", "OpenAlert", "[SerialNumber]='" & Forms![Workorders]![SerialNumber] & "'") > 0 Then
   MsgBox "There is already a call open for this machine. THIS CALL MUST BE FLAGGED AS A DUPLICATE CALL !", vbExclamation, "Record Exists"
   Cancel = True
   'Me.SerialNumber.Undo
End If

Thanks
Rick

 

[sneuberg]

I can't see where all this stuff is and now it relates. Could you upload your database?

 

[RickK]

I can't see where all this stuff is and now it relates. Could you upload your database?

It is full of Customer data that I cannot release. I will try to import a few of the forms and tables into a new DB and upload that.

 

[RickK]

I can't see where all this stuff is and now it relates. Could you upload your database?

I removed a lot of stuff from my DB just so that I can upload it. It is attached.

Use the form Workorders by Customer. Then click on workorder button. Check out my code under the Date_Finished Got Focus. When you click on Date Finished it runs this code and compares the serial number selected on this form to the serial number on the Query/form OpenAlert that you will see on the main form WorkOrders By Customer.

The problem is it is running this code when it sees this same ticket (record) on the query. I don't want it to run if the serial number on the query OpenAlert and the serial number on the Workorders form match AND the workordersID match on both. Only run when the WorkorderID is different.

The end result would be , if there is a record present in the OpenAlert query with serial number 1234 and someone attempts to open another record against the same serial number they receive the warning.

Clear as mud?
Thanks

 

[RickK]

The same code is in the serial number box which warns the person entering the call when there is a record already open. The warning is just a little different than the Date Finished box message.

 

[sneuberg]

Maybe

Private Sub DateFinished_GotFocus()
If DCount("*", "OpenAlert", "[SerialNumber]='" & Forms![Workorders]![SerialNumber] & "'") > 0 _
    And Me.WorkorderID <> Nz(DLookup("[WorkorderID]", "[OpenAlert]", "[SerialNumber]='" & Forms![Workorders]![SerialNumber] & "'")) Then
   MsgBox "There is already a call open for this machine. THIS CALL MUST BE FLAGGED AS A DUPLICATE CALL AND NOT A MACHINE CALL!", vbExclamation, "Record Exists"
   Cancel = True
   'Me.SerialNumber.Undo
End If
End Sub

is what you are looking for but I'm not sure and I can't test it as the database you uploaded seems to have been gutted too much. When I open the Workorders by Customer I get a message about a missing record source. I came up with the code above by studying the Workorders form in design view.

Your choice of the GotFocus event to test this is unusual. Usually the beforeupdate event of the serial number combo box would be used as this would fire no matter where the user moved the focus to. Also Cancel = True isn't doing anything in this code as the GotFocus event doesn't pass that. Just something for you to think about.

 

[RickK]

Maybe

Private Sub DateFinished_GotFocus()
If DCount("*", "OpenAlert", "[SerialNumber]='" & Forms![Workorders]![SerialNumber] & "'") > 0 _
    And Me.WorkorderID <> Nz(DLookup("[WorkorderID]", "[OpenAlert]", "[SerialNumber]='" & Forms![Workorders]![SerialNumber] & "'")) Then
   MsgBox "There is already a call open for this machine. THIS CALL MUST BE FLAGGED AS A DUPLICATE CALL AND NOT A MACHINE CALL!", vbExclamation, "Record Exists"
   Cancel = True
   'Me.SerialNumber.Undo
End If
End Sub

is what you are looking for but I'm not sure and I can't test it as the database you uploaded seems to have been gutted too much. When I open the Workorders by Customer I get a message about a missing record source. I came up with the code above by studying the Workorders form in design view.

Your choice of the GotFocus event to test this is unusual. Usually the beforeupdate event of the serial number combo box would be used as this would fire no matter where the user moved the focus to. Also Cancel = True isn't doing anything in this code as the GotFocus event doesn't pass that. Just something for you to think about.

That worked as I wanted it to. Actually, I had concerns that the message would pop up on ANY record if there were duplicates and not just any other than the first one. But it looks like it is throwing the message only on duplicate records and not the first, which is GREAT!.

By the way, I do use this on BeforeUpdate on the serial number box, but for some reason I could not get it to work beforeUpdate on the DateFinished. I had to use OnFocus, which but it works and I am happy.

Thanks