combo box selection with auto display in label from database

Question

kimangel 13 Junior Poster in Training

11 Years Ago

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

visual-basic-6

Edited 11 Years Ago by kimangel

4 Contributors
25 Replies
1K Views
2 Weeks Discussion Span
Latest Post 11 Years Ago Latest Post by kimangel

All 25 Replies

tinstaafl 1,176 Posting Maven

11 Years Ago

What code have you got and what part isn't working right?

AndreRet 526 Senior Poster

11 Years Ago

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.

AndreRet 526 Senior Poster

11 Years Ago

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

rishif2 57 Posting Whiz in Training

11 Years Ago

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 . . .

AndreRet 526 Senior Poster

11 Years Ago

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 11 Years Ago by AndreRet

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

kimangel 13 Junior Poster in Training · Answer 1 · 2013-01-24T04:30:14+00:00

kimangel 13 Junior Poster in Training

11 Years Ago

helo experts I really need your help on this! Pls....

kimangel 13 Junior Poster in Training · Answer 2 · 2013-01-24T07:46:25+00:00

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.

kimangel 13 Junior Poster in Training · Answer 3 · 2013-01-24T07:53:18+00:00

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

kimangel 13 Junior Poster in Training · Answer 4 · 2013-01-24T07:57:57+00:00

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.

kimangel 13 Junior Poster in Training · Answer 5 · 2013-02-04T09:44:14+00:00

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

kimangel 13 Junior Poster in Training · Answer 6 · 2013-02-05T02:57:21+00:00

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
_____

rishif2 57 Posting Whiz in Training · Answer 7 · 2013-02-05T05:09:46+00:00

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 . . .

kimangel 13 Junior Poster in Training · Answer 8 · 2013-02-05T07:25:21+00:00

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

rishif2 57 Posting Whiz in Training · Answer 9 · 2013-02-05T08:13:21+00:00

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

kimangel 13 Junior Poster in Training · Answer 10 · 2013-02-05T09:34:47+00:00

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
____

rishif2 57 Posting Whiz in Training · Answer 11 · 2013-02-05T09:53:15+00:00

rishif2 57 Posting Whiz in Training

11 Years Ago

Set cn = Nothing

remove that line

Now it will work

Edited 11 Years Ago by rishif2

AndreRet 526 Senior Poster · Answer 12 · 2013-02-05T17:09:36+00:00

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 ........

kimangel 13 Junior Poster in Training · Answer 13 · 2013-02-06T12:42:17+00:00

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

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

rishif2 57 Posting Whiz in Training · Answer 14 · 2013-02-06T13:07:56+00:00

rishif2 57 Posting Whiz in Training

11 Years Ago

did you implement what i wrote in last post ?

kimangel 13 Junior Poster in Training · Answer 15 · 2013-02-06T14:09:23+00:00

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

rishif2 57 Posting Whiz in Training · Answer 16 · 2013-02-06T14:29:13+00:00

rishif2 57 Posting Whiz in Training

11 Years Ago

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

Edited 11 Years Ago by rishif2

kimangel 13 Junior Poster in Training · Answer 17 · 2013-02-06T14:30:25+00:00

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

rishif2 57 Posting Whiz in Training · Answer 18 · 2013-02-06T15:04:04+00:00

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

kimangel 13 Junior Poster in Training · Answer 19 · 2013-02-07T09:01:31+00:00

kimangel 13 Junior Poster in Training

11 Years Ago

yes sir, i did already.

kimangel 13 Junior Poster in Training · Answer 20 · 2013-02-07T09:35:24+00:00

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.

combo box selection with auto display in label from database

Recommended Answers Collapse Answers

All 25 Replies

Recommended Answers