Create Item List

[DaronSpicher]

I have a single database with two tables.
The "Inventory" table contains 3 fields Number(key), Name, Country. This table has 15 records. All 3 fields are TEXT.

The "Load" table is the same format. Number, Name, Country. All 3 are TEXT.

I have a Form called "Scan" where I can input text and upon enter, I go to my VB code and throw up a messagebox that contains the text I put in the Scan textfield.

What I need to do, is to search Inventory.Number field for this text. If I find it, I want to put all 3 fields from that row into the Load table.

Practical application is such that I can then scan a can of soup with a barcode scanner which would enter in a number "499500394" into my "Scan" field. Then, my VB would search the Inventory.Number field. If it finds a match there, copy all 3 fields into the "Load" table, creating a new record. After scanning 100 items, I would be able to have a printable list of things in the "Load" table.

If it is not found, I would like to handle that too. But for now, I need the successful case to work right.

I think this should be fairly easy, but I haven't gotten it yet. I hope someone with experience has time to help me out.

Thank you,

Daron Spicher

 

[hollifd]

Daron,

Have you considered...

create an append query that uses a field on your "Scan" form as the criteria...something like: =[forms]![frmScan].[text0].[text]

This append query would append records to your "Load" table.

upon exiting this field (or some other event)on your Scan form, have your VBA run this append query...something like:

DoCmd.OpenQuery "qryAppendItem"

I know there is some code that will tell you if the query has no records, but I do not know what it is right now. This code could alert you to the fact that your item was not found in your inventory table.

I have never tried anything like what you are trying to do, but maybe this idea will work for you or at least will give you some other ideas.

David

 

[parry]

Hi Daron, this should work. First off you need to check you have a reference to the DAO library, so within the VB Editor select Tools-References and place a tick against a library called Microsoft DAO 3.6 Object Library if it isnt already ticked.

Now place the following code in your form:-

Private Sub cmdFind_Click()
'Uses value in txtFind field on form to search for corresponding number in the
'Inventory table. If a match is found a new record in the Load table is created
'with the same values as the found record. If no match, then a message box appears.

On Error GoTo Err_cmdFind_Click
Dim Rs As Recordset, Rs2 As Recordset
Dim Db As Database
Dim Num, Nam, Coun

'Open 2 recordsets. Rs for Inventory table and Rs2 for Load table
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("Inventory")
Set Rs2 = Db.OpenRecordset("Load")

'Set the key field (Number) to lookup in the Inventory table
Rs.Index = "Number"

'Seek the value thats in the txtFind textbox
Rs.Seek "=", Me.txtFind

'If no match found then produce message and exit procedure
If Rs.NoMatch = True Then
MsgBox "Record not found"
Exit Sub
End If

'Capture the Number, Name and Country values for the matching record
Num = Rs("Number")
Nam = Rs("Name")
Coun = Rs("Country")

'Create a new record in the Load table
    With Rs2
        .AddNew
        !Number = Num
        !Name = Nam
        !Country = Coun
        .Update
        .Bookmark = .LastModified
    End With


Rs.Close
Rs2.Close


Exit_cmdFind_Click:
    Exit Sub

Err_cmdFind_Click:
    MsgBox Err.Description
    Resume Exit_cmdFind_Click
    
End Sub

This code presumes that the textbox where you put the bar code to look for is called "txtFind" and the command button to click to execute the code is called "cmdFind". Just change the name of these in the code to the names of your controls. You didnt specify what you would like to happen if no match was found in the Inventory table so I just created a message box saying it wasnt found. We can change this if needed.

_________________
cheers
Parry
This message was edited by parry on 2003-01-22 05:25