hi, good day!

I have problem in coding this;

combo box list - positions like Manager & supervisor

and a label for the Rate of each position.

When I click the manager, the label below will auto display the rate for Manager which these two data, Position and Rate, are from database.

Need your help on this. Tnx

romy

Edited 3 Years Ago by kimangel

Private Sub cboPosition_Click()

Set rs = New ADODB.Recordset
rs.Open "Select * from Rates", cn, adOpenKeyset, adLockOptimistic
If Not rs.EOF Then

Ado.RecordSource = "Select Position, Position_Rate from Rates where ID Like '" & cboPosition & "'"

lblRate.Caption = rs!Position_Rate


rs.Close
Set rs = Nothing
End If

End Sub

Sir, above is my code. When I click Manager in the combo box the position rate (300) will display in the label - lblRate. My problem is the rate will not change when I change the position, example supervisor. The rate of supervisor in the database is 400.

Edited 3 Years Ago by happygeek: fixed formatting - please use code button when submitting code

What is your table name and the fields that you need to get the data from?

Once you have clicked on the combobox you have a value that you can search from now.

Make the connection to your database, create your sql statement to search the table with the returned value from combobox, display the values.

and when I added ado.refresh below Ado.RecordSource... a runtime error occured. "method refresh of object "IAdodc" failed".

Sir my database table is Rates, Position and Position_Rate are the fields.

connection is thru adodc which is successful already. I think there's lacking in my code above.

Try the following -

Private Sub cboPosition_Click()
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM Rates where ID ='" & cboPosition.Text & "'"", cn, adOpenKeyset, adLockOptimistic
''Change LIKE to = because you are returning a specific rate from your combobox...
lblRate.Caption = vbNullString

If Not rs.EOF Then
''Ado.RecordSource = "Select Position, Position_Rate from Rates where ID Like '" & cboPosition & "'"
Remove this duplicated code, not needed...
lblRate.Caption = rs!Position_Rate

rs.Close
Set rs = Nothing
End If
End Sub

use the following code(On form load event) to additems to combobox from database table

con.Provider = "Microsoft.Jet.OLEDB.4.0"
con.Open App.Path & "\db.mdb"
rec.Open "select * from rates", con, 2, 3
While (rec.EOF = False)
Me.Combo1.AddItem rec.Fields(0)
rec.MoveNext
Wend
con.Close
Set con = Nothing

Now on the click event of combobox write the following code

con.Provider = "Microsoft.Jet.OLEDB.4.0"
con.Open App.Path & "\db.mdb"
rec.Open "select * from rates where position='" & Combo1.Text & "'", con, 2, 3
While (rec.EOF = False)
Label1.Caption = rec.Fields(0) & Space(10) & rec.Fields(1)
rec.MoveNext
Wend
con.Close
Set con = Nothing

Note :- i assumed that there is a table containing two fields namely position(field 1) and rates(field 2)

hops this helps you . . .

Hi experts,

I still have unresolved error on my payroll system in vb6 language, the error is Method Open of Object_Recordset Failed. below is my code. pls help me on this.

Sorry for the late reply, i just got back my net connection. Many thanks.

Private Sub cboPosition_Click()

Set rs = New ADODB.Recordset
rs.Open "select * from Rates where Position='" & cboPosition.Text & "'", cn, 2, 3

lblRate.Caption = vbNullString

If Not rs.EOF Then

lblRate.Caption = rs!Position_Rate

While (rs.EOF = False)

lblRate.Caption = rs.Fields(0) & Space(10) & rs.Fields(1)
rs.MoveNext

Wend
rs.Close
Set rs = Nothing
End If

End Sub

Edited 3 Years Ago by happygeek: fixed formatting - please use CODE button when posting code

Try the following -

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.Open "select * from Rates where Position='" & cboPosition.Text & "'", cn, 2, 3

Make sure that cn (your connection) is opened as well. The error is because the recordset could not be opened...

Edited 3 Years Ago by AndreRet

Same error Sir, the italic code below points the error when I click debug.

_____
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
**rs.Open "select * from Rates where Position='" & cboPosition.Text & "'", cn, 2, 3**

lblRate.Caption = vbNullString

If Not rs.EOF Then

lblRate.Caption = rs!Position_Rate

While (rs.EOF = False)

lblRate.Caption = rs.Fields(0) & Space(10) & rs.Fields(1)
rs.MoveNext

Wend
rs.Close
Set rs = Nothing
End If

End Sub
_____

Edited 3 Years Ago by happygeek: fixed formatting

i think that you are getting error due to not declaraing or using cn. so at general declaration use the follwing code

Dim cn As New ADODB.Connection

And Now use the following code(On form load event) to additems to combobox from database table

con.Provider = "Microsoft.Jet.OLEDB.4.0"
con.Open App.Path & "\db.mdb"
rec.Open "select * from rates", con, 2, 3
While (rec.EOF = False)
Me.Combo1.AddItem rec.Fields(0)
rec.MoveNext
Wend
con.Close
Set con = Nothing

Now on the click event of combobox write the following code

con.Provider = "Microsoft.Jet.OLEDB.4.0"
con.Open App.Path & "\db.mdb"
rec.Open "select * from rates where position='" & Combo1.Text & "'", con, 2, 3
While (rec.EOF = False)
Label1.Caption = rec.Fields(0) & Space(10) & rec.Fields(1)
rec.MoveNext
Wend
con.Close
Set con = Nothing

Note :- i assumed that there is a table containing two fields namely position(field 1) and rates(field 2)

Now your problem should solved but if still facing errors then check that have you added reference for adodb or not ?

if you haven't added , then goto the project menu and then select reference option and then select Microsoft ActiveX Data Object 2.5

hops this helps you . . .

by the way sir, below is my module 1 entry.

Public cn As ADODB.Connection
Public rs As ADODB.Recordset
Sub main()

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.ConnectionString = "provider=Microsoft.jet.oledb.4.0; data source=" & (App.Path & "\automatedpayrollsystem.mdb") & "; persist security info  = false"
cn.Open
Form03.Show

End Sub

Edited 3 Years Ago by happygeek: fixed formatting (please use the CODE button when posting code)

try this

rs.Open "select * from rates where [position]='" & Combo1.Text & "'", cn, 2, 3

the word "position" is already defined in vb 6 so you can't use it directly

hope this helps you

Edited 3 Years Ago by rishif2

sir, below is my formload code, which is now ok, it displays fields from database.

Below also is the codes for mycboPosition_Click(). When I run and click Manager position from the combobox, the error is this, the connection cannot be used to perform this operation. It is either invalid or closed in this context. The error points to > rs.Open "select * from Rates where Position_Name='" & cboPosition.Text & "'", cn, 2, 3.

I'm not really good on this sir. Thanks for helping.

_____
Private Sub Form_Load()

Set rs = New ADODB.Recordset
rs.Open "Select * from Rates", cn, 2, 3

While (rs.EOF = False)
Me.cboPosition.AddItem rs.Fields(0)
rs.MoveNext
Wend
rs.Close
Set cn = Nothing

End Sub
____
____
Private Sub cboPosition_Click()

Set rs = New ADODB.Recordset
rs.Open "select * from Rates where Position_Name='" & cboPosition.Text & "'", cn, 2, 3

lblRate.Caption = vbNullString

If Not rs.EOF Then

lblRate.Caption = rs!Position_Rate


While (rs.EOF = False)

lblRate.Caption = rs.Fields(0) & Space(10) & rs.Fields(1)
rs.MoveNext

Wend
rs.Close
Set rs = Nothing
End If

End Sub
____

Edited 3 Years Ago by happygeek: fixed formatting

Nope, the problem is with a recordset selector that is already open...

Before you call the rs.Open, do the following code...

If rs.State= 1 Then rs.Close

rs.Open ........

same error sir, either i put it after or before.

do we have other option sir? or recode (other way) with same output.

yes sir, this one --If rs.State= 1 Then rs.Close-- is what i mean earlier.

i meant that read the post posted by me about removing a line

Edited 3 Years Ago by rishif2

yes sir, this one --If rs.State= 1 Then rs.Close-- is what I mean earlier

well if still not solved the see below
first i am gonna write what you wrote in previous posts . . .

 _____
    Private Sub Form_Load()

    Set rs = New ADODB.Recordset
    rs.Open "Select * from Rates", cn, 2, 3

    While (rs.EOF = False)
    Me.cboPosition.AddItem rs.Fields(0)
    rs.MoveNext
    Wend
    rs.Close
    Set cn = Nothing

    End Sub
    ____
    ____
    Private Sub cboPosition_Click()

    Set rs = New ADODB.Recordset
    rs.Open "select * from Rates where Position_Name='" & cboPosition.Text & "'", cn, 2, 3

    lblRate.Caption = vbNullString

    If Not rs.EOF Then

    lblRate.Caption = rs!Position_Rate

    While (rs.EOF = False)

    lblRate.Caption = rs.Fields(0) & Space(10) & rs.Fields(1)
    rs.MoveNext

    Wend
    rs.Close
    Set rs = Nothing
    End If

    End Sub

now i am gonna write what you should do . . .

Private Sub Form_Load()

Set rs = New ADODB.Recordset
rs.Open "Select * from Rates", cn, 2, 3

While (rs.EOF = False)
Me.cboPosition.AddItem rs.Fields(0)
rs.MoveNext
Wend
rs.Close

End Sub



Private Sub cboPosition_Click()
Set rs = New ADODB.Recordset
rs.Open "select * from Rates where Position_Name='" & cboPosition.Text & "'", cn, 2, 3
While (rs.EOF = False)
lblRate.Caption = rs.Fields(0) & Space(10) & rs.Fields(1)
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
cn.close
set cn=Nothing
End Sub

sir, thank you very much. It's now ok. But I deleted these codes, cn.close and set cn=nothing, error will occur if I included these.

This question has already been answered. Start a new discussion instead.