trying to compare data in a text box with a table

[gregu710]

Hi, I'm a relative newcomer to Access, VBA & SQL programming, and have had to basically figure my way along. I am putting together a database which will allow me to scan in barcodes from a product that we are building, check the unit serial number against test data in our SQL Server to ensure the unit did pass it's end-of-line function test, and then concatenate the whole lot of serial numbers into a string together with a lot number which identifies which production line it came from and the "Julian Date" it was packaged on. So far, so good, pretty simple (sarcasm). But got it all working pretty well for a first go, I'm sure there's a lot of fat I can trim out as I "mature", but it's functional. I basically have 2 problems left to resolve before I can unleash it on the world. The first is in the inital Form which the operator uses to scan in a new barcode. This Form breaks the barcode into a Part Number, and Serial Number, and verifies that the Serial Number is not a duplicate. It was easy enough to format the table that I'm dumping the Part Number and Serial Number into to be indexed without duplicates. IF I scan in a duplicate number I get the standard Access warning that no records could be appended due to a key violation. However, I would like to compare that new serial number that's been scanned into the text box on my Form with the existing records in the table which will receive this data and see if it already exists, and then output a message telling the operator specifically that he is trying to input a duplicate Serial Number (rather than having him guess what the meaning of that message is). Here is the code I have to transfer the barcode data from the Form to the Table. Any recommendations on code to do that comparison and output a True/False result would be greatly appreciated. I know how to construct a IF THEN statement and even the output message (as shown), just can't get the comparison logic.
Public Sub Command15_Click()
Dim HousingID As String


Dim SerialNumber As String
HousingIDText.SetFocus
HousingIDString = HousingIDText.Text
'DoCmd.SetWarnings (False)
DoCmd.RunSQL ("Insert into tempHousingIDScan ([HousingID]) Values ('" + HousingIDString + "')")
DoCmd.RefreshRecord
DoCmd.SetWarnings (True)
'------------------------------
'If HousingIDText = "tempHousingIDScan.HousingID" Then
'Err.Description = "Duplicate Serial Number Found, Scan Different Label!"
'MsgBox (Err.Description)
'End If

HousingIDText.SetFocus
HousingIDText = " "

End Sub

I'll describe the second problem in a new post so as to keep this from getting too much longer.... Thanks for any help!

[pbaldy]

There are generally two schools of thought on the topic of errors. Letting the error happen and handling it, which is sort of the direction you appear to be going in, and preventing the error from occurring in the first place, which is my preference. Thus I'd test with something like:

If DCount("*", "tempHousingIDScan", "HousingID = '" & HousingIDString & "'") > 0 Then
  'already exists, warn the user
Else
  'your code to insert here
End If

By the way, you only have to set focus to HousingIDText because you later use the .Text property, which requires focus. If you use the .Value property, you don't need to set focus.

[gregu710]

Actually, the error is already prevented since my SQL Query won't put the duplicate Serial Number into a field in the target table if that Serial Number already exists, I am just looking for a better way to let the operator (who knows even LESS than I about Databases) know exactly what is going on when he sees an error. Let me give your code a try though, I'll let you know. Also, with regard to deleting a post, there doesn't seem to be anything under thread tools, but thanks for the pointer....

[pbaldy]

But you're relying on the system generated error, are you not? That's what I meant, and like I said I'd simply avoid it. If you want to do it the way you are, you can probably grab the error with an error trap:

http://www.baldyweb.com/ErrorTrap.htm

I'll ask a moderator to delete the duplicate thread. It's not a big deal, it just cuts down on the clutter.

[gregu710]

Thanks Paul, that bit of code did the trick and does exactly what I want!!!! One down, one to go!!! And yes, you're correct, I wanted to avoid relying on a generic error from the system.

[pbaldy]

Happy to help and welcome to the site!

[gregu710]

Happy to help and welcome to the site!

To Quote Arnold, "I'll be baaaack!"

[gregu710]

hi Paul, using that DCount function I should be able to do the same type of comparison with a longer string, correct? Attached is a file showing the Form I'm using and the Code behind it. Basically just checking that the two fields have duplicate values. Right now with duplicate values it's telling me (if I understand the logic) that I DON'T have duplicate values.

[pbaldy]

Are comparing the 2 strings in the attachment? They don't match, so given your logic I'd expect the "WARNING" message. Also, I've never messed with the Err.Description myself. I'd just do a message box:

MsgBox "Label OK!"

[gregu710]

No,you're right, my example didn't match, so I created a label from the original data (so that all the data is matching), and still get the same error...

[pbaldy]

You're getting an error or an unexpected result? To me your logic seems reversed. The >0 test would indicate a duplicate, but you have "Label OK" there. You'd get the "Warning" message for a non-duplicate. Is that what you want?

Also change to straight strings for the message boxes instead of using Err.Description, just in case that's causing an issue.

[gregu710]

I was looking for a duplicate in this case. Basically this part of the program was a failsafe to make sure that the operator didn't mix up a label somehow and end up putting the wrong label on the wrong pallet (shouldn't be possible anyhow, but I learned LONG ago about "Shouldn't"). But, I think I hit on a strategy that works using the DCount, so you're off the hook! Thanks anyway! I dumped the computed string and the scanned string into the table and then had no problem using DCount to compare the two fields in the Table, and output the correct Message (got rid of the Err.Description that you mentioned as well!)

[pbaldy]

Sounds like a lot of work, but I'm glad you have a solution that works for you.

[gregu710]

well, like I mentioned, right now i'm shooting for functionality, and I'm sure there's a TON of streamlining and fat reduction that can be done once I get a better grasp on the ins and outs of programming and language and stuff. Helps keep me off of the street corners and fill my weekends!

[pbaldy]

There's a lot to be said for functionality. I'd be embarrassed to show you stuff I did 10 years ago, but the fact is it is still working 24/7/365 to this day. I sometimes think about updating it, and then think "if it ain't broke, don't fix it".