Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Searching an Access Database and return data

Posted on 2001-06-18
15
Medium Priority
?
175 Views
Last Modified: 2010-05-02
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.
0
Comment
Question by:flosoft
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
15 Comments
 
LVL 4

Expert Comment

by:wileecoy
ID: 6204321
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?
0
 
LVL 4

Expert Comment

by:wileecoy
ID: 6204341
Clarification:  I asked what kind of database are you using.  What I meant to ask was, what version of Access are you using?
0
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 6204414
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

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

rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly


??????????????????
0
 
LVL 12

Accepted Solution

by:
DRRYAN3 earned 800 total points
ID: 6204764
To use my code as I wrote it (not sure what your experience level is, so if some of this is obvious please don't be offended) ...

You must first add the Microsoft ActiveX Data Objects to your project using Project->References.

You must also have an Access database created with the path and name you will find in the DBLocation constant.

That database needs to contain a table named Items

The Items table must contain at least four columns:
   ItemSKU
   ItemDesc
   ItemType
   ItemPrice

You must have defined and named four textbox controls on your form just as I named them:  

   txtItemDesc
   txtItemType
   txtItemPrice
   txtItemSKU

On entry into the LostFocus event handler it is also assumed that there is an Item number in the control txtItemSku.

0
 

Author Comment

by:flosoft
ID: 6204891
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
0
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 6206812
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






0
 

Author Comment

by:flosoft
ID: 6207568
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.

0
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 6207580
Flosoft,

Did you receive my email and attachments?
0
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 6207587
Flosoft,

Did you receive my email and attachments?
0
 

Author Comment

by:flosoft
ID: 6207827
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
0
 

Author Comment

by:flosoft
ID: 6207838
I created the database in MS Access 2000.
0
 

Author Comment

by:flosoft
ID: 6207884
Please send emails instead to vbnewbie@yahoo.com so I can get them from where I am at.

Thanks
0
 

Author Comment

by:flosoft
ID: 6208510
Thanks a Bunch for all the help!!

Tom
0
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 6208572
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question