Please correct my codes...

[zach007]

Dear Experts,

Has anyone could help me please for these code?

visual basic code:
Private Sub cmdCheckCurrentRecord_Click()

Dim con As ADODB.Connection
Set con = New ADODB.Connection
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\exercise.mdb"
Set rst = Execute_SQL_ReturnRecordSet("Select * from Item", False, "C:\exercise.mdb")

rst.MoveFirst
Do While rst.EOF = False
If UserForm1.txtqty.Text = rst.Fields("Item_Code").Value Then
Exit Do
End If
rst.MoveNext
Loop

TextBox3.Text = rst.Fields("Qty")

Application.ScreenRefresh
End Sub






When I select item no 1, then when I press "Check Current Stock" it show its Qty...BUT, when I select other item numbers, let say item code number 3 it still show the Qty for item number 1...???....

Please help with code corrections...

Thank you so much...

[Hack]

From what are you selecting the item? A combo box? Listbox? What?

It would appear you would need to add a WHERE clause in your SELECT statement to uniquely identify the specific item and gets its quantity, but I don't know how you are making your selection as it is.

[zach007]

Hi,

Thanks for your reply...it is from a combobox...would you please correct my code please....????..I tried many time and finally get frustrated....

OR do you want to see all codes (I mean the file, so you can have a look trough it..???)..

Thank you so much...

[Hack]

My SELECT would be something like

"Select * from Item WHERE fieldname = '" & Combo1.List(Combo1.ListIndex) & "' "

Where fieldname is the name of your database field that stores the item and Combo1 is the name of your combo box.

[zach007]

Hi,

I did what you directed but received error message:

I did like this:

Private Sub cmd_CheckCurrent_Click()
 
Dim con As ADODB.Connection
    Set con = New ADODB.Connection
    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\exercise.mdb"
        Set rst = Execute_SQL_ReturnRecordSet("Select * from Item WHERE fieldname = '" & cmb_item.List(cmb_item.ListIndex) & "' ", False, "C:\exercise.mdb")
    
         rst.MoveFirst
Do While rst.EOF = False
    If UserForm1.txtqty.Text = rst.Fields("Item_Code").Value Then
       Exit Do
    End If
    rst.MoveNext
Loop
 
    
        
    Application.ScreenRefresh
 
End Sub

I did following error: Run Time Error '381': Could not get the list property. Invalid property array index.

If you dont mind...would you please have a look into my file to see what happens please..???....It is on the "Check Current Stock"...

Thank you so much for your kind helps....

[Hack]

Is the name of the field in your database fieldname?

[zach007]

Hi,

I already changed it to my fieldname 's name...but still get the same error which is Run Time Error 381....

[zach007]

Hi,

Sorry...when I changed the fieldname with Item_Code...there is a new error message:

Run Time Error 3021: Either BOF or EOF is True, or the current record has been deleted. Requested operations requires a current record. And "rst.MoveFirst" was yellow highlight....

[dee-u]

Have you made a selection in your combobox?