Retrieve POS data

[sackymatt]

Hi guys

am new on VB6 and would like your urgent help, i have created my POS half way and have created a form for sales, now what i want is when i enter an item code all the other information related to the item diplays at the respective text boxes..

please help
regards
Sacky

[vb5prgrmr]

Use the data form wizard. Search my handle and those keywords to learn how to invoke it, use it, and what to do with it.

Good Luck

[johnly]

Place a data control on the form
Set the database and recordsource propery of the data control
Then place the text boxes to display the values.

Select the datasource property of each text box and set it to the data control.

Now set the datafield property of each textbox to the desired field name.

[sackymatt]

thanx guys for ur help..
i will follow all u telling me and see if i can archieve anything and come back to you

Sacky

[sackymatt]

hi johnly, i did exactly like how instructed and it worked very well.. but this does not allow me to enter the item code myself and retrieve the data associated to the item code.when i run the form, it automatically fills the areas with the data.
what i want is when i enter the item code, it matches with the one in the database and then retrieve the rest of the data associated to that item code.
for example, when i enter 2000 in the item code area, it matches that one with the one in my database and return the price, item name and tax associated to that item in the respective areas

Any further assistance will be appreciated
Sacky

[johnly]

Gr8...

For this you can add a new textbox called (txtItemCode). dont link it with the data control.

Now you can write the following code in the change event of this new textbox.

If the item code data type is text then you can write:

Private Sub txtItemCode_Change()
Data1.RecordSource = "select * from table1 where itemcode = '" & txtItemCode.Text & "'"
Data1.Refresh
End Sub

or if the datatype of the code in database is number the you can write the below

Private Sub txtItemCode_Change()
Data1.RecordSource = "select * from table1 where itemcode = " & val(txtItemCode.Text)
Data1.Refresh
End Sub

Pls note that you need to replace the table name (table1) and fieldname (itemcode) with the actual tablename and field name in your database.

[vb5prgrmr]

Only problem with that johnly is using the change event of the textbox. As the lookup code (number/alpha) gets longer your code will be executed each time there is a change! Which means for a four digit code, your code will pound the datbase four times with your look up query! Better to put the actual search under a command button (or perhaps the lost focus event of the text box if you want a high amount of automation).

sackymatt,

Have you even tried the Data Form Wizard and see what code it generates for you?

Good Luck

[sackymatt]

Thanx guys again, i really appreciate your time in this forum..
Fistly Jonhly, i tried the code in the txtcode box and when i run the form and try to enter the item code (barcode) a 'run time error '424' object required' keeps on popping up.
I also tried it in a command button, still the same error pops up.
in debug mode, it highlights the following
<code>
Private Sub txtcode_Change()
Data1.RecordSource = "select * from items where barcode = " & Val(txtcode.Text)
Data1.Refresh
End Sub
</code>

Does this have to do with the connection maybe? i have my database on ms access though.

vb5prgrmr,
How do i use Data Form Wizard? Please elaborate more if possible,
Thanx

[vb5prgrmr]

Start a new standard exe project>vb's ide menu>Add-Ins>Add-In Manager.
Hightlight VB 6 Data Form Wizard
Lower right corner of form where frame with caption says Load Behavior put a check in the box next to Loaded/Unloaded>click Ok

Add-Ins>Data Form Wizard
Follow wizard enough times to get every combination of form type along with code/class/control.
Save project for future reference.

Good Luck

[johnly]

Hi sackymatt,

Please check the following.

1. Is the name of your search textbox is (where you are entering the barcode) txtCode ?

2. Is the name of your data control is Data1 ?

3. The RecordsetType property of the data control should not be 0 - Table. It can be 1 - Dynaset or 2 - Snapshot.

You can have it as 2 - snapshot if you do not want to edit the values, and just want to view it.

I agree with Vb5Prgmr that there is a performance issue if you call the code in the change event of the textbox. Instead you can write this code in the click event of a commandbutton.

You can write the code in change event if you require the values to get automatically loaded in the textboxes when you type the code in the search textbox. But the other method (using a button) is preferrable if the table contains a large number of records.

[sackymatt]

Hi Johnly,
Hi vb5prgrmr,

Yah the name of the text box is where iam entering the barcode. And the name of the data control is actually Store i just forgot to change it when i posted the thread,.
This is actually a POS system, u know when a cashier enters a barcode/itemcode and display everything associated to that item,, thats exactly what i want to know how..

i tried the following code though to connect/open my database
<code>
Option Explicit

Private Sub cmdAccept_Click()
Dim cn As New ADODB.Connection
Dim strCNString As String
Dim RS As New ADODB.Recordset
Dim Txt As String

On Error GoTo ErrHandler
'Connect to database
strCNString = "Data Source=Store.mdb"
cn.Provider = "Microsoft Jet 4.0 OLE DB Provider"
cn.ConnectionString = strCNString
cn.Properties("Jet OLEDB:Database Password") = "sakaria"
cn.Open
'Open recordsource
With RS
.Open "Select * from items where barcode='" & txtcode.Text & "' , cn, adOpenDynamic, adLockOptimistic

End With

ExitHere:
Exit Sub

ErrHandler:
MsgBox Err.Number & " " & Err.Description, vbCritical, "Sale ..."
cn.Close
End Sub
</code>

it worked well with my log in form but unfortunately not with the form in discussion.Is there maybe something wrong with the code?

vb5prgrmr, i tried to do wat u instructed me to do but unfortunately it didnt work, when i opened its telling me unrecognized database format.. but any how i checked the data wizard on the net how it works on the folowing link
http://msdn.microsoft.com/en-us/library/aa291437%28VS.71%29.aspx

[vb5prgrmr]

Okay, the reason your access database/vb6 creates that error is because VB6 is quite dated and to resolve that problem you would need to save your database to a previous format. Once that is done you should be able to run the wizard but as for converting back to the database format you want to use, I don't think the ADODC will be able to handle then new format (maybe it will if you change its connection string through code but I have never tried, I always use code).

Good Luck

[sackymatt]

Ok vb5prgrmr will try my best on that.
THANX

[oscarresonable]

if you are using sql or access then here it is.....

set rsItem = nothing
set rsItem = select * from TblItem where Item_No = '"& txtItemNo.text &"'

if rsitem.recordcount <> 0 then
txt1.text = rsitem!fieldname1
txt2.text = rsitem!fieldname2
end if

that's all....

[sackymatt]

oscarresonable, am using access and does rsItem supposed to be the database name or table name? and wat is the first code that i must place b4 this one..

[sackymatt]

Hi Johnly, Long time i didnt here from you..still up to assist me?
or any one else out there, still cant go thru' with retrieving of data from the data base..
Further assistance will be appreciated

[johnly]

Hi Sackymatt,

I totally agree with vb5prgmr, that writing code is better.

Ok since you have already tried some code then forget about all that data control and linking the texboxes with the data control and all those stuff.

Lets write some code.

First add a new reference for 'Microsoft ActiveX Data Objects 2.x library', if it is not added.

Now we will use ADODB objects to accees the database and write SQL query to fetch the record.

Try the following code.

Private Sub cmdAccept_Click()
    On Error GoTo ErrHandler
    Dim strCNString As String
    
    strCNString = "Provider=MSDASQL.1;Persist Security Info=False;DBQ="
    strCNString = strCNString & App.Path & "\store.mdb;" 'DefaultDir=" & App.Path & ";"
    strCNString = strCNString & "Driver={Microsoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;"
    strCNString = strCNString & "MaxBufferSize=2048;password=sakaria;UID=admin;"
    
    Dim Con As New ADODB.Connection
    Con.Open strCNString
    
    Dim RS As New ADODB.Recordset
    Dim strSQL As String
    
    strSQL = "select * from items where barcode = '" & txtCode.Text & "'"
    
    RS.Open strSQL, Con, adOpenForwardOnly, adLockOptimistic
    If Not RS.BOF Then 'at least one record found
        txtItemName.Text = RS!itemname & vbNullString
        txtItemDesciption.Text = RS!itemdescription & vbNullString
        txtUnitPrice.Text = Format(Val(RS!unitprice & vbNullString), "0.00")
    Else
        txtItemName.Text = vbNullString
        txtItemDesciption.Text = vbNullString
        txtUnitPrice.Text = vbNullString
    End If
    RS.Close

ExitHere:
Exit Sub
ErrHandler:
MsgBox Err.Number & " " & Err.Description, vbCritical, "Sale ..."
    
End Sub

in the above code I am opening a ADODB Connection object using a connection string. I am using MSDASQL provider.
Then opening a recordset by passing an SQL query. I am adding vbNullstring to handle the null values in the database.

Hope this will be a final solution:)

Regards...

[sackymatt]

Jonhly,can i still do this using my msaccess database?

[johnly]

Yes, you can

[sackymatt]

Thanx Johnly, the code worked very well,,and thanks to every one that participated in this thread.. my problem has been solved..