NEW TO ACCESS PLEASE HELP

[hohai610]

Hello everyone
I'm trying to make a POS cashing form(i have a database table).i have 3 fields(UPC,UPC1,UPC2) in the table with different codes.
I would like to create a form that contains a text box and a list box when i input a code into text box , it will automatically search in UPC then UPC1 then UPC2 until getting the match record and input that one into list box then save that record into the list box and continously input more records when i change the text box data
Is there any way to do so?
Thank you so much for your time

 

[arnelgp]

use a Combobox with 3 Columns for UPC, UPC1 and UPC2.

 

[theDBguy]

Hi. Welcome to AWF!

This sounds like you may have a "repeating group" in your table, which would be considered a bad design. Can you show us your table structure?

 

[Uncle Gizmo]

I agree with DB guy, you are building your MS Access database like a spreadsheet, and although this works up to a point, you are heading for a whole load of misery!

I've written a blog about it on my website here:-

Excel in Access – Nifty Access

www.niftyaccess.com

I also provide a tool for transposing your 3 offending UPC Fields into a suitable table structure.

The tool is available for free to access world forum members, just contact me for instructions on how to get a free copy.

 

[hohai610]

Hi. Welcome to AWF!

This sounds like you may have a "repeating group" in your table, which would be considered a bad design. Can you show us your table structure?

Thank you for your reply Mr the DBguy
This is my stock tabe structure .The reason why I created 3 fields of UPC is because UPC is for company code(that is for managing inhouse system). UPC1 & UPC2 are the number on the barcode of the items i got from the suppliers . But sometimes they change the barcode number so i have 2 different barcodes for the same item.So to make it easier for cashiers .i would like to make a Form in Access ,when cashiers scan the barcode into text box it will search in UPC1 ->UPC2 until get the match record.
Is there any better way for me to make it easier .Please give instruction .

 

[hohai610]

Thank you uncle Gizmo
if I put only 1 UPC in the stock table, we don't have space to put more than 1 barcode number when the supplier change the barcode of item and we still have the previous barcode in stock
I'm glad to learn more about your tool .How can I contact you ,sir?

 

[Uncle Gizmo]

I'm glad to learn more about your tool

To get a Free copy, all I ask is that you sign up to my YouTube channel here:-

Nifty Access - Microsoft (If you have not already done so)

You can download the Transpose Tool here:- https://gum.co/NiftyTransposeTool

To get a free copy Use the coupon code:- CODE USED

Instructions on how you use it:-

Normalization Tool​

Skip down to:- Using the Tool - In Pictures

In your particular case I note the following:-

Your goal is to end up with a main form showing the product details, on that form you will have a subform showing the different barcodes related to that product. You can now query the UPC table and find the product that a particular UPC relates to.

Don't use sjcode(upc) as a link, use the auto number field.

Copy ALL the UPC's (including sjcode(upc)) into a new table using the Normalisation Tool as explained on my website.

The auto number field value will maintain the data integrity.

Make several copies of my tool and experiment duplicating the process.

Then make a copy of YOUR dB, don't run the unfamiliar code on your only copy, as it may take several attempts. ALLWAYS save a MASTER COPY of your dB. ALLWAYS make regular backups, at least once a day, and more often if you are working hard on some thing.

 

[hohai610]

Thank you so much Mr Gizmo
i will try your tool
In the past i used a free pos software for the business. but i need more features than what those software has>So i came up with the idea that i will learn Access and use VBA to write a POS system for myself. i started with creating a Checkout (cashing) form.I don't want to spend time to look up for the product name in the stock table .just want the cashier scan the barcode and the product info (description, unit price, tax, qty) showup on the subform or listbox
RIGHT NOW I HAVE 2 PROBLEMS
1. IM ABLE TO SEARCH IN ONLY 1 FIELD WHEN I HAVE MORE THAN 1 BARCODE FIELDS
2. AFTER IT SEARCHED AND PUT OUT THE MATCH RECORD INTO LIST BOX .HOW TO MAKE THAT RECORD STAY IN THE LIST BOX OR SUBFORM ( BECAUSE WHEN I CHANGE THE TECTBOX 'S DATA THE PREVIOUS RECORD WILL DISAPPEAR

 

[Uncle Gizmo]

just want the cashier scan the barcode

That's a very good way of learning MS Access and VBA, having a project. Building a point-of-sale system is a very good way to go. I built one about 10 years ago and this is a clip of when I was experimenting in collecting barcodes:-

Barcode Scan - Nifty Access​

 

[hohai610]

that is exactly what I'm trying to do for my project .Can you give me more details and hints to know what to do or steps to learn
Very appreciate your help Mr Gizmo

 

[Uncle Gizmo]

Can you give me more details and hints to know what to do or steps to learn

As you can see in the YouTube video all that does is add a product when you scan it's barcode. In developing this project it soon became apparent that the operator would need to be able to remove an item. I added the option for the user to scan a barcode again and remove and item. Also it is quite common in shops for stock to be placed on shelves without the back-end database being updated with the items barcode. Another scenario that need to be catered for in the customers database.

Basically it's all run by functions and SQL Statements.

When you scan the barcode the last character passed through by the barcode scanner is usually the enter key. This triggers the after update Event of the textbox you are scanning the barcode into. The after update events triggers an SQL Statement which counts the number of times that barcode has been entered into the line item table. If the count is 0, it adds a new line item, if it's 1 or greater, then adds 1 to the count of the item instead of adding a new line item.

To be able to remove an item, I added a checkbox to the Pop-Up form. If the user checked this checkbox, then instead of adding an item it would remove an item. But then you have to cater for an instance where a user tries to remove an item that does not exist in the the line item table.

My intention was that instead of having the user have to check a checkbox to remove an item I was going to have a list of barcodes which they scanned which would open different forms for different scenarios. In the case of removing an item, the user would scan the relevant barcode, then the item would be removed instead of being added. I never got around to adding this functionality!

This is the after update event of the textbox

Private Sub txtBarcode_AfterUpdate()
DoCmd.RunCommand acCmdSaveRecord
Form_frmInvAndPayBIG.Refresh

        If fSerialExists(Me.txtBarcode) = True Then
            Call fAddRemove(prpInvID, Me.txtBarcode, Me.chkRemove, Nz(Me.txtAdd, 1))
            Form_frmInvAndPayBIG.Refresh
        Else
            MsgBox " >>> BARCODE NOT RECOGNISED"
   
        End If
   
    Me.txtBarcode = ""
    Me.txtAdd.SetFocus
    Me.txtBarcode.SetFocus
   
End Sub

This is the function "fAddRemove" that does all the work

Public Sub fAddRemove(lngOrderID As Long, strBarcode As String, blnRemove As Boolean, lngAddNumbItems As Long)
Dim strIn As String 'Form_frmInvAndPayBIG.OrderID

'strIn = "356341010670618" 'Simulate a Barcode Scan

    If fHasLineItem(lngOrderID, fIDFromSerial(strBarcode)) = 1 Then
        If blnRemove Then
            If fProdQuantity(lngOrderID, fIDFromSerial(strBarcode)) <= 1 Then
                Call fOrderDetailRow(lngOrderID, fIDFromSerial(strBarcode))
            Else
                Call fAddQuantity(lngOrderID, fIDFromSerial(strBarcode), -1)
            End If
        Else
                Call fAddQuantity(lngOrderID, fIDFromSerial(strBarcode), lngAddNumbItems)
        End If
    Else 'If it's Zero "0" Add a Row
        If blnRemove Then
            MsgBox " >>> THERE are NO MORE Items to REMOVE!!!   "
        Else
            Call fAddPurchase(lngOrderID, fIDFromSerial(strBarcode), fGetPrice(fIDFromSerial(strBarcode)), 1, 0)
            Call fAddQuantity(lngOrderID, fIDFromSerial(strBarcode), (lngAddNumbItems - 1))
        End If
    End If
End Sub      'fAddRemove

As you can see this function calls lots of other different functions which are mostly based on SQL Statements.

Notes:-
for counting See https://docs.microsoft.com/en-us/of...-reference/recordset-recordcount-property-dao --- 10 years ago I used ADO, but these days you need to use DAO)

 

[Uncle Gizmo]

When you scan the barcode the last character passed through by the barcode scanner is usually the enter key.

If you fancy adding this barcode scanning functionality to your project, then I would suggest you take "baby steps" to eventually achieve your goal. The first step I would recommend is that you set up a test database, in that test database, have a text box in which you can scan various barcodes and have the barcode displayed by a message box. If you don't have a barcode scanner setup, then just key in some numbers and press the enter key to simulate entering a barcode.

Once you've perfected this, then the next step is to add a table with a field containing barcodes. Use the Microsoft code I linked to above, see if you can count how many barcode occurrences are in the barcode field and return the number in a message box.

This is how I do my development, and I'm reasonably sure that other people do the same.

 

[hohai610]

I thank you so much for your opinions and your experience . I will follow those steps and see how much i can learn
These are very useful with new person like me

 

[hohai610]

good day Uncle Gizmo
I was looking over your code and your video . But to be honest to you , I really doesn't have any ideas how to create those forms or what are those variables in the code use for ?
Can you please elaborate more for me please? like what type of forms that you use
and name of those text box or names of bound text boxes inside the forms please .
Very thank you for that sir

 

[Uncle Gizmo]

have a text box in which you can scan various barcodes and have the barcode displayed by a message box.

Have you taken this "Baby Step"?