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
helo experts I really need your help on this! Pls....
What code have you got and what part isn't working right?
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.
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
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...
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
_____
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
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
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
____
Set cn = Nothing
remove that line
Now it will work
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.
did you implement what i wrote in last post ?
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
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
yes sir, i did already.
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.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.