Scan QR code to either add a record or if record exists add +1 to [count]

[Abhean]

I am working on an reagent inventory db.

Currently I have 2 tables
1) Roche with [UPC][Count] and some calculated fields based off of [UPC]
2) MaterialNum which provides the name of the reagent based on the calculated data from Roche Table.

I have a form that I can search for the UPC and if it exists populates the form and I can modify the [Count] and save from there.
If the record is not found through the search box I can add a new record and add it, and adjust the [Count] from there.

This really is inefficient. What I want to be able to do is scan the QR, that check for record, if not found add it. And once added or found to be there a +1 is added to the [Count].

[twinnyfo]

Abhean,

I am really confused. You say that your current process is really inefficient, but your proposed solution performs the same exact functions.

Exactly what, specifically, are you asking for in this thread?

[Abhean]

It would do the same function. The trouble is the process. User will not like having to scan an item twice if it is not found in the table. Once for the search and then again for the add record.

To be able to scan once, run it through the logic of looking for the record then adding if not there and not trowing an error if it is there, then adding a +1 in the [count] of the selected record. This would be more user friendly and faster for the user.

[twinnyfo]

Then, I am still confused. If you have the code that you are looking for, why do you need to scan it twice?

But again, I still don't know exactly what you are asking from us here.

You need to help us out and ask a specific question.

[Abhean]

I am using the built in functions of the combo box to check to see if a record is existing. If I do so within the text box (Stock Number)and the record is already there it will throw an error stating that the record already exists (which it should).
I have conditional formatting in place between the search box and stock number, green tells the user that the record is already there and they can simply modify the record they see in the count field. Red tells them that they will have to click on the add record button and scan the item in again in the Stock number text field.

Now, the specific Question. Is there a way to code the text box so that it will scan the records to see if the # placed in it (the text box) is there, if the record exists, simply add +1 to the count field of that record? If the record does not exist add it and add +1 to the count on that new item?

And thank you for taking time with me. My Access is very weak. :)

[twinnyfo]

Thank you for the clarification. So, here is where the inefficiency comes:

Abhean:
Red tells them that they will have to click on the add record button and scan the item in again in the Stock number text field.

The process is not inefficient; it has been designed inefficiently. I think you have all the working parts to make this work. I still have a few more questions to clarify for myself, and a recommendation at the end, which I think will get you moving along the right path.

Question:

I am using the built in functions of the combo box to check to see if a record is existing. If I do so within the text box (Stock Number)and the record is already there it will throw an error stating that the record already exists (which it should).

What? How are you using the combo box? Does someone click on the combo box and scroll through all the stock numbers to find the one they want?

Second, why would you ever weant your DB to throw an error when it has found a record that you want to update? Very unclear how this is intended to work.

However, you say:

green tells the user that the record is already there and they can simply modify the record they see in the count field.

Are you saying that if the DB finds the stock number, all you are doing is finding the record and the user increments the toal on hand by one?

Finally,

Red tells them that they will have to click on the add record button and scan the item in again in the Stock number text field.

Here is the big question--which also leads to my final recommendation--So, you have a method of scanning in a code and that code then populating a text box, yes?

The recommendation is quite simple. It appears you've put the cart before the horse. Why not simply begin by scanning in a code. PERIOD. If you have a means of scanning a code into a text box, then do it. ONCE. The user scans a code, clicks "Add item"--or whatever you want to have on the button. The DB uses the scanned in code. First, it looks for the existence of that code. If it exists--no errors here--it simply increments the stock by one. If it cannot find that stock number, it creates a new record, with an inventory of 1. If the DB requires additional information on new items, then a form would pop up requesting the desired information. Clicking save would close that form and add that record.

It's really quite straightforward. I think you just got your process steps out of place.

[Abhean]

You sir, are bang on!

Combo boxes make very easy search boxes when based on a single primary key. Basing a form from a table, again easy and straight forward. The combo box came after I attempted to scan a number into the Stock number text box and received the error that the number entered was already in the database. In can the combo box as the first step (now) to make sure the user would check # before going to the records. The green and red background were just as a double check.

But, your summary is dead on. How would I code a text box to check a number entered, if there simply do a ++ to the count field, and if not add it as a record and do the ++

And again thanks for your patients.

[twinnyfo]

I will return to this on the morrow, if you can wait.

[Abhean]

oh absolutely! Again thanks.

[twinnyfo]

So, I think the easy solution is to use some of the code you already use. It would be helpful to post the procedures you currently use, but I suspect these will change significantly during this overhaul.

So, again, the first thing ht user should do is scan the code. When that code is entered into a text box, then, simply add a button that initiates the process.

First, it checks to see if there is no value in the code text box (we don't want to do anything if there is nothing there).

Second, it searches for that code in your table. If it finds it, then it simply adds one to the inventory.

Third, if the search does not find anything, it brings up a product form, which is similar to the form you are currently using (wihtout the search stuff on it), which should really just be bound to your products table, and move to a new record (you should have data entry set to true on this form, so folks can't change records that already exist). The default value for inventory should be one (because you have one and only one--this does not even need to be displayed). Save that record and scan another code.

[Abhean]

Ok Here is what I am attempting to work with currently.

It appears that it does find the Duplicates and the records that need to be added.

I do get a syntax error that something is not correct in the INSERT INFO

the Text40 is the textbox I am using to scan in the numbers.

Private Sub Text40_AfterUpdate()
    'Search finds record in table
    If (DLookup("[OrgUPC]", _
                      "[Roche]", _
                      "[OrgUPC]='" & Me!Text40 & "'")) Then
        MsgBox "Duplicate!"
    'Search does not find record
    Else
        MsgBox "New Record"
        DoCmd.RunSQL "INSERT INTO [Roche] " & _
                     "([OrgUPC] " & _
                     "VALUES ('" & Me.Text40 & "')"
    End If
 
    'clear the text box
    Me.Text40 = Null
    'ready for next scan
    Me!Text40.SetFocus

EDIT: Ok I have changed the ELSE section to

Else
        MsgBox "New Record"
        DoCmd.RunSQL "INSERT INTO Roche (OrgUPC)Values (Text2)"

I also am doing this on a new form so the the text has changed to "Text2"
This seems to be working, but is there anyway to stop the warning box that makes sure I wish to append the record?

[twinnyfo]

Abhean:
This seems to be working, but is there anyway to stop the warning box that makes sure I wish to append the record?

Just delete the line that calls for the message box!

Here is a modified version of what you have. Please note that I have renamed your text box to something that actually makes sense. Notice also that I use your INSERT statement to also include a number of inventory (I don't know what hte field name is, so I simply used "Inventory".

Also note that it is a good habit to use variables for things like this. Notice that there is a variable for the UPC, then we evaluate that variable. We also check to see if the search finds anything--and if not, we set the variable to a predefined value. There is also a variable for the SQL statmeent. Then we use that variable. In the long run, this helps you with troubleshooting.

Private Sub txtUPCSearch_AfterUpdate()
    Dim strUPC  As String
    Dim strSQL  As String
 
    strUPC = Nz( _
                DLookup("[OrgUPC]", _
                        "[Roche]", _
                        "[OrgUPC]='" & Me.txtUPCSearch & "'"), _
                "")
    If Not strUPC = "" Then
        'Add one to the counter
        'Add your code here
        'Probably an UPDATE query......
    Else
        'Add new record
        strSQL = _
            "INSERT INTO Roche ( OrgUPC, Inventory ) " & _
            "Values ( " & txtUPCSearch & ", 1 );"
        DoCmd.RunSQL strSQL
    End If
 
    'clear the text box
    Me.txtUPCSearch = Null
    'ready for next scan
    Me!txtUPCSearch.SetFocus
 
End Sub

Hope this hepps!

[Abhean]

Ohh yea! Almost there! The data appears to be passing but is being changed.
Example: Scan in 010401563093975610315914021719073124007027664190
But it is entering 1.04015630939756E+46 in the field.
The field is set as short text.

[twinnyfo]

Chang line 18:

"Values ( '" & txtUPCSearch & "', 1 );"

Forgot to treat it as text.

[Abhean]

Working like a dream. Couple of questions.
1) When setting the Var strUPC, why the Nz?
Whould strUPC = NULL if textUPCSearch is not found in[OrgUPC] and if found returns the value of textUPCSearch?

2) Why put the insert statement into a var?

I am just trying to understand how things are working. :)

Now, a question about incrementing the count.I am trying:

[Count] = DMax("[Count]", "Roche") + 1

Roche being the table, count being the field. This is done within the "If Not" statement. Getting error that it cant write to this field.

Edit: Found "Count" was reserved word so changed it to OnHand. Now passes through but does not add to the count.

[twinnyfo]

1) When setting the Var strUPC, why the Nz?
Whould strUPC = NULL if textUPCSearch is not found in[OrgUPC] and if found returns the value of textUPCSearch?

Yes, it would be Null. However, whenever working with variables, I always try to avoid those variables having a Null value. They can sometimes be tricky and some variable types will even throw an error if you try to assign Null to them. By using the Nz() function, you control the value of your variable. Then you know that it will be either a known value or "".

2) Why put the insert statement into a var?

First, this is an excellent question. Second, I am glad you noticed and that you asked.

The primary reason for assigning the SQL string to a variable is for troubleshooting. Let's say you simply build your string within the function itself and each time you execute that query, you come up with an error? How do you troubleshoot something you don't know the value of? But, if it is a variable, you can always comment out the execution of the SQL and then Debug.Print strSQL to see the actual value of your SQL string. Then, you tweak the actual string until it works, then tweak your code so that it produces an identical string.

This is a best practice among DBAs--at least those on this forum.

Incrementing the [Count].
First: change the name of your field. Count is a reserved word within Access and you are simply asking for trouble. For the code below, I am assuming you've changed the field name to "Inventory". Modify the code approriately to suit your changes.

So.......... Even though we have used a DLookup() in this code, a "general" but not hard and fast rule is to try to avoid Domain Aggregate functions in your code. This is not because they are bad, but because they are generally less efficient than working directly with your tables.

So let me throw this question out to you. What if you could open your table, find the UPC you were looking for, and if you found it, just add one to the inventory, but if you don't find it, add it to the table? WHAT IF........?

Prepare to have your mind blown:

Private Sub txtUPCSearch_AfterUpdate()
    Dim db      As DAO.Database
    Dim rst     As DAO.Recordset
 
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("Roche")
    With rst
        If Not (.BOF And .EOF) Then
            Call .MoveFirst
            .FindFirst "OrgUPC = '" & Me.txtUPCSearch & "'"
            If Not .NoMatch Then
                'Found it!
                Call .Edit
                !Inventory = !Inventory + 1
                Call .Update
            Else
                'Didn't find it
                Call .AddNew
                !OrgUPC = Me.txtUPCSearch
                !Inventory = 1
                Call .Update
            End If
        End If
        Call .Close
    End With
    Call db.Close
    Set rst = Nothing
    Set db = Nothing
 
End Sub

This brief snippet of code is the sum total of everything you need to solve this problem. Notice again its elegance and simplicity! Open the table. Search for the UPC. Found it: add 1; Not found : new record.

Any questions, let me know!

Hope this hepps!

[Abhean]

OMG Yea questions I will have.
First off the getting error line 10. Operation is not supported for this type of object.

.FindFirst "OrgUPC = '" & Me.txtUPCSearch & "'"

The commands like .movefirst and .NoMatch those are just built in functions?

And the commands like !Onhand or !OrgUPC make those fields active?

[twinnyfo]

When you go to your VBA Editor: Debug | Compile, is that also the error that pops up? You shouldn't have to, but you may have to add Microsoft DAO 3.6 Object Library. Mine is not added but it compiles and functions fine without it.

.MoveFirst etc. are all built in to the DAO library.

!OnHand etc. - this is how you refer to fields within a DAO Recordset when it is open.

You may note that there is a funny little statement With rst ... End With at lines 7 and 25. This means that anything wthin these bounds has an implied rst leading it. So, within the code, Call .MoveFirst is iinterpreted and understood as Call rst.MoveFirst.

I'm puzzled over the error, but you may have to play with it. Mine works fine.

[Abhean]

Wahooo! Twinnyfo, thank you so much for your time and effort! Everything appears to be working and more importantly I have learned & relearned so much from this.

I was able to take care of that 3251 run time error by adding dbOpenDynaset to the "set rst" on line 6 Ill post the code below.

Private Sub txtUPCSearch_AfterUpdate()
   Dim db      As DAO.Database
    Dim rst     As DAO.Recordset
 
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("Roche", dbOpenDynaset)
    With rst
        If Not (.BOF And .EOF) Then
            Call .MoveFirst
            .FindFirst "OrgUPC = '" & Me.txtUPCSearch & "'"
            If Not .NoMatch Then
                'Found it!
                Call .Edit
                !OnHand = !OnHand + 1
                Call .Update
            Else
                'Didn't find it
                Call .AddNew
                !OrgUPC = Me.txtUPCSearch
                !OnHand = 1
                Call .Update
            End If
        End If
        Call .Close
    End With
    Call db.Close
    Set rst = Nothing
    Set db = Nothing
 
End Sub

[twinnyfo]

Yep, sometimes your recordsets will require the , dbOpenDynaset argument--you can include it always if you want. I just omitted it this time because it was working with my table without it.

Glad I could hepp!

Let us know if you need anything else.