Checking records on subform against text box on main form?

[Lol Owen]

Hi, hope someone can be of help here please? First, apologies for kength of post.

I have a database which is to replicate an EPOS, in particular the barcode input (I plan to buy a scanner for this part later). The tables are as follows:

Product: Barcode (Key), Product ID (Indexed to not allow duplicates), Description, Cost Price, Sell Price

Transactions: Transaction Number (Key), User ID, Date, Transaction Total

User: User ID (Key), User Details

Transaction/Product Transition: Transaction Number and Barcode set as composite key to link the many to many relationship between Transactions table and Product table.

I've created a form with subform Transactions/TranSub

On the Transactions form are the following fields:
Transactions.Transaction Number
.User ID
. Date
.Transaction Total
In addition to this I have created a textbox, Input, into which data is to be fed to drive the actions on the form, namely the barcode when scanned.

On the TranSub subform are the following:

TranSub.Barcode
TranSub.Quantity
Product.Description
Product.Sell Price

Now to the crux. Access will not allow me to make multiple scans of the same item since the field, Barcode, is a primary key in the Transactions table. I could run a system where quantity is manually entered to get around this but in reality that is not how it would work in the field and the idea behind this demo is to get a theoretical client to adopt a EPOS on their delivery vans.

I need to write some code whereby when an input is made into the Input box, this is automatically checked against existing barcode entries for that transaction. if none exist the same then it will enter the data into the Barcode field on the subform, thus bringing up the rest of the product details. if however the barcode already exists in this transaction, the quantity of the existing entry is incremented by 1.

i'm sure it's possible but i've read about 3 books, from Dummies to professional tome and i am no further forward. Could someone please help me with this?

many thanks, Lol

 

[Jon K]

I have added a query in the database to test for repeated items:

SELECT Count(*) AS Num
FROM [Transaction/Product Transition]
WHERE Barcode=forms!Transactions!Input And [Transaction Number]=forms!Transactions![Transaction Number];

In the AfterUpdate event of the barcode Input box, I used DLookup() to run the query. If DLookup() returns 1, it's a repeated item. otherwise it's a new item.

Private Sub Input_AfterUpdate()

   Dim SQL As String
   
   DoCmd.RunCommand acCmdSaveRecord
   
   If DLookup("Num", "qryIsRepeatedItem") = 1 Then
      SQL = "Update [Transaction/Product Transition]" _
          & " set Quantity=Quantity+1" _
          & " where BarCode=" & Forms!Transactions![Input] _
          & " and [Transaction Number]=" & Me.[Transaction Number] & ";"
   Else
      SQL = "Insert Into [Transaction/Product Transition]" _
          & " ([Transaction Number],[BarCode], [Quantity])" _
          & " values (" & Me.[Transaction Number] _
          & "," & Me.Input & ",1);"
          
   End If
 
   CurrentProject.Connection.Execute SQL
   
   Me.TranSub.Form.Requery
   Me.[Transaction Number].SetFocus
   Me.Input.SetFocus
   Me.Input = Null

End Sub

The Cycle property of the main form has been set to "Current Record".


Note:
To register a new transaction, you must enter a UserID before inputting a barcode number.

You may also need to add code to ensure a valid barcode number is entered.

It is considered bad practice to include embedded spaces and special characters in table names, field names and control names.
.

 

[Lol Owen]

Thanks Jon. I apprecciate your help on this. I was actually considering another tack last night where I create a recordset, check the recordset against the Input value, if it's a duplicate then increment the quantity else add another record for the new scan.
I noticed that a user id must be input for a new record to be created through the transaction number. I was wondering if there was some code, that could be perhaps put into the form load area, that would activate the autonumber? I plan to have the form in data entry mode so it will be refreshed for each new transaction.

To be honest I'm a bit of a prototyper I guess. I just start knocking rough things together to test principles and my own skills and then when I'm happy, re-make everything using correct syntax etc.

Thanks again for your help.

Cheers, Lol