asked on

Searching an Access Database and return data

Hi,
I am trying to search an access database with items (Inventory), the scenario is this:
The database table items has many fields - The 4 I need to work with are Description, SCU , Price and Type. I need to Scan the item SCU into a textbox - The program then needs to find that SCU in the database SCU field and then return to a listbox the description and another listbox the price and a 3rd listbox the item type - it also needs to return the price to a label (Subtotal). Then clear the textbox and set the focus back to the the textbox for another scan.

This is for a point of sale system, thus the scanner - I am having great difficulties getting this to work, therefore and offering maximum points for an answer.

wileecoy

What is your current code for scanning? That will help with the rest (or are you starting from scratch?)

Also - what kind of database are you using and what kind of data access are you using?

wileecoy

Clarification: I asked what kind of database are you using. What I meant to ask was, what version of Access are you using?

DRRYAN3

You could start with code like the following which assumes some field names and some control names and uses ADO against an Access database. Most of the wedge (inline) scanners I've worked with let you set whether or not to end the scanned data with a carriage return - that's why I use the lostfocus event to perform the search.

Public cn As New ADODB.Connection
Public rs As New ADODB.Recordset
Const DBLocation = "C:\MYDATA\MYDATABASE.MDB"

Private Sub Form_Load()
Dim cnString As String
cnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBLocation & ";" & _
"Mode=Read|Write|Share Deny None;Persist Security Info=False"
cn.Open cnString
txtItemSKU.SetFocus
End Sub

Private Sub txtItemSKU_Lostfocus()
Dim strSQL As String
strSQL = "SELECT ItemSKU, ItemDesc, ItemType, ItemPrice from Items " & _
"WHERE ItemSku = '" & txtItemSKU.Text & "'"
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly
If rs.BOF And rs.EOF Then
txtItemSKU.SetFocus
Else
txtItemDesc.Text = rs!ItemDesc
txtItemType.Text = rs!ItemType
txtItemPrice.Text = rs!ItemPrice
txtItemSKU.SetFocus
End If
End Sub

Obviously, this is not a complete app so there is no error checking, etc.

flosoft

ASKER

I keep getting a required parameter missing error from the code from DRRYAN3 - It stops on this line:

rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly

??????????????????

ASKER CERTIFIED SOLUTION

DRRYAN3

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

flosoft

ASKER

Hi,
I checked and all seem's to comply - I am going to try to explain exactly what the project has so far:

MS Access database:
Table = Items
Field:SKU
Field:Description
Field:Price
Field:Type

No ActiveX Control - However I don't care if I use one so activex control code is welcome - I am using VB6 with the latest updates.

I am using a barcode scanner to scan the SKU's - It's a regular hand held scanner that you see in many stores - The database is larger than the data I need to retrieve - I only need the 3 fields data returned - Decription, Price and Type - I am unsure why the commments / answers don't work so far - All efforts are appreciated - Also my employer on this project may pay $$ for help in completing it via paypal - Any assistance will be re-warded in completing this project. - Further comments or questions can be sent to tom@icon2.com

Thanks,
Flosoft

DRRYAN3

flosoft

I have sent you via email a working form and access database. A copy of the form code is shown below:

Public cn As New ADODB.Connection
Public rs As New ADODB.Recordset
Public txtLastSKU As String
Const DBLocation = "C:\Program Files\Microsoft Visual Studio\VB98\DBTEST\MYDATABASE.MDB"

Private Sub Command1_Click()
On Error Resume Next
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
Unload Me
End Sub

Private Sub Form_Load()
Dim cnString As String
cnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBLocation & ";" & _
"Mode=Read|Write|Share Deny None;Persist Security Info=False"
cn.Open cnString
txtLastSKU = ""
End Sub

Private Sub txtItemSKU_Lostfocus()
Dim strSQL As String
If txtItemSKU.Text = txtLastSKU Then Exit Sub
txtLastSKU = txtItemSKU.Text
strSQL = "SELECT ItemSKU, ItemDesc, ItemType, ItemPrice from Items " & _
"WHERE ItemSku = '" & txtItemSKU.Text & "'"
On Error Resume Next
rs.Close
On Error GoTo 0
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly
If rs.BOF And rs.EOF Then
txtItemSKU.SetFocus
txtItemSKU.SelStart = 0
txtItemSKU.SelLength = Len(txtItemSKU.Text)
Else
txtItemDesc.Text = rs!ItemDesc
txtItemType.Text = rs!ItemType
txtItemPrice.Text = rs!ItemPrice
' Set focus to SKU and select current text for replacement
txtItemSKU.SetFocus
txtItemSKU.SelStart = 0
txtItemSKU.SelLength = Len(txtItemSKU.Text)
End If
End Sub

flosoft

ASKER

I continue to get an error on this line

rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly

The error is no value given for One or more required parameters.

I am at a loss - This should be working.

DRRYAN3

Flosoft,

Did you receive my email and attachments?

DRRYAN3

Flosoft,

Did you receive my email and attachments?

flosoft

ASKER

Hi,
I recieved the e-mail, but there was no form1 - So I Couldn't check to see what may be wrong. You are not placing an ADO control on the form are you?

Thanks for the help thus far - I await your answer.

Tom

flosoft

ASKER

I created the database in MS Access 2000.

flosoft

ASKER

Please send emails instead to vbnewbie@yahoo.com so I can get them from where I am at.

Thanks

flosoft

ASKER

Thanks a Bunch for all the help!!

Tom

DRRYAN3

To be complete here on EE, the problem was a difference in our respective abbreviation for Stock Keeping Unit (SKU/SCU). Once all field names were the same, the examples worked.