Calling all access pros .. is this possible?

[smannes]

Here's what I'm trying to do. I have a database where I want to scan in a
UPC code. I have a field for UPC. I also have a field for quantity. Is it
possible that when I scan in a duplicate UPC, the quantity changes to 2 (and
so on)? Does that make sense? If it's possible, how do I do it? Any help
is greatly appreciated!

 

[smannes]

What I also want it to do is to not register the duplicate the upc but just
to update the quantity. Hope that makes sense.

 

[Tom Wickerath]

Yes, this should be possible. Can we assume that you are normally in the
process of adding a new record, when you perform the scan? Without knowing
the structure of your application, here is an outline of an approach:

1. You could use a DCount or DLookup statement in the BeforeUpdate event
procedure for the UPC textbox. DCount would probably be easier, since you
will get 0 returned if no other records exist, whereas with DLookup you would
need to deal with a null. The domain aggregrate function would include the
scanned UPC as a criteria.

DLookup Usage Samples
http://www.mvps.org/access/general/gen0018.htm

Although I'm not a big fan using macros, you might want to practice setting
up a Domain Aggregrate function with this example for Northwind:

How to Check for Duplicate Values in Primary Key Fields
http://support.microsoft.com/kb/209487

You can always convert a macro to VBA code afterwards. So, basically, you
would cancle the event, as shown in this macro, but you would also issue an
update query in VBA code to increment the quantity field by one.



Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Ken Sheridan]

First, as the UPC field's values must be unique, make sure that the UPC field
is defined as the table's primary key. Then in the form's Error event
procedure handle the key violation error by undoing the new record and
updating the row in the table with the matching UPC value by incrementing the
Quantity field's value by 1, using code along these lines:

Const conDUPLICATEKEY = 3022
Const conMESSAGE = "UPC already exists in another record. " & _
vbNewLine & "The quantity for that record will be incremented by one."

Dim cmd As ADODB.Command
Dim strSQL As String
Dim strUPC As String


If DataErr = conDUPLICATEKEY Then
strUPC = Me.UPC
MsgBox conMESSAGE, vbInformation, "Warning"
Response = acDataErrContinue
Me.Undo

'update row in table
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = strSQL

strSQL = "UPDATE YourTable " & _
"SET Quantity = Quantity + 1 " & _
"WHERE UPC = """ & strUPC & """"

cmd.CommandText = strSQL
cmd.Execute
End If

The above assumes that the UPC field is of text data type. If not you'll
need to change the strUPC variable declaration to the relevant data type and
remove the delimiting quotes around it in the expression for the SQL
statement.

Ken Sheridan
Stafford, England

 

[Tony Toews [MVP]]

First, as the UPC field's values must be unique,

Why? Maybe he is working on an order entry form where he wants to
ensure that the same item doesn't appear twice on that particular
order.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

Here's what I'm trying to do. I have a database where I want to scan in a
UPC code. I have a field for UPC. I also have a field for quantity. Is it
possible that when I scan in a duplicate UPC, the quantity changes to 2 (and
so on)?

Assuming that you are scanning the UPC as part of an order entry form
I would use some unbound fields such as UPC and quantity at the bottom
of the form. In the AfterUpdate event of the scanned UPC field I'd
insert the logic that Tom suggests looking to see if the UPC already
exists on this order. If it doesn't then I'd let the user update the
quantity, add the record, and requery the subform.

That way you wouldn't have to worry about having to undo records in
the subform.

However this would require some VBA code.

Tongs
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Pete D.]

I would also put a timer event on it like used at grocery stores. A 2
second to 3 second delay to prevent it going in twice with a good record
bad record sound as feed back from the computer. The scanner will beep but
feed back from the computer tells if it was recorded or not.

 

[Ken Sheridan]

That's possible, though on the basis of the OP its impossible to say whether
an order/invoice is being raised or an inventory updated. If it is an
order/invoice however the same methodology is appropriate as the key of the
OrderLines table would be the OrderID and UPC columns. The SQL statement
would merely need amending to incorporate the OrderID in the WHERE clause in
a Boolean AND operation.

Ken Sheridan
Stafford, England