Combo1-->SearchDataBase-->Result in Text1 2

[jajinder]

Hi... Newbee question.

After a long search with a lot of abused language.. hèhè.. I finaly got the values from a database in a Combo. After much, much, much more filthy language I hope someone can help me out with this:

I created an AccesDatabase: (Example)
Row1 (Fieldname: Postcode) = 1,2,3,4 etc
Row2 (Fieldname: Plaats) = A,B,C,D etc

If I choose "2" in Combo1 I want Text1.Text to be "B".

Someone know how to do that? Or perhaps a link where I can find how to do that? (want to learn)

The code I use to fill the items from the database into the Combo:

Option Explicit

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Private Sub Form_Load()    
    Set cn = New ADODB.Connection
    With cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = _
            "Data Source = C:\SEARCH.MDB;" & "Persist Security Info = False"
        .Open
    End With

    Set rs = New ADODB.Recordset
    With rs
        .ActiveConnection = cn
        .CursorLocation = adUseServer
        .CursorType = adOpenDynamic
        .Source = "Blad1"
        .Open
    End With

    If Not rs.EOF And Not rs.BOF Then
        rs.MoveFirst
        Do While Not rs.EOF
            Combo1.AddItem rs!postcode
            rs.MoveNext
        Loop
    End If

    rs.Close
    Set rs = Nothing

    cn.Close
    Set cn = Nothing
End Sub

---------------------------------------
This will be the day when all of God’s children will be able to sing with a new meaning, “My country, ‘tis of thee, sweet land of liberty, of thee I sing. Land where my fathers died, land of the pilgrim’s pride, from every mountainside, let freedom ring. - Marten Luther King

 

[Swi]

Option Explicit

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Private Sub Combo1_Click()
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM Blad1 WHERE Postcode = '" & Combo1.Text & "'", cn, adOpenDynamic
If Not rs.EOF And Not rs.BOF Then Text1.Text = rs.Fields("Plaats").Value
rs.Close
Set rs = Nothing
End Sub

Private Sub Form_Load()
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = _
"Data Source = C:\SEARCH.MDB;" & "Persist Security Info = False"
.Open
End With

Set rs = New ADODB.Recordset
With rs
.ActiveConnection = cn
.CursorLocation = adUseServer
.CursorType = adOpenDynamic
.Source = "Blad1"
.Open
End With

If Not rs.EOF And Not rs.BOF Then
rs.MoveFirst
Do While Not rs.EOF
Combo1.AddItem rs!postcode
rs.MoveNext
Loop
End If

rs.Close
Set rs = Nothing

End Sub

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
If cn.State = adStateOpen Then cn.Close
Set cn = Nothing
End Sub


Swi

 

[Swi]

You may find this tutorial on SQL useful:

http://www.1keydata.com/sql/sql.html

Swi

 

[jajinder]

Hi Swi, thanx for reaction..

Data type mismatch in criteria expression
[highlight]SELECT * FROM Blad1 WHERE Postcode = '" & Combo1.Text & "'", cn, adOpenDynamic[/highlight]

So I looked it up.... "The error is usually caused by use of incorrect delimiters in your SELECT SQL query statement"
(8O)... What does this mean?

---------------------------------------
This will be the day when all of God’s children will be able to sing with a new meaning, “My country, ‘tis of thee, sweet land of liberty, of thee I sing. Land where my fathers died, land of the pilgrim’s pride, from every mountainside, let freedom ring. - Marten Luther King

 

[johnwm]

A general help for building any SQL query is to build it to a variable and then Debug.Print it. Many problems are then self-evident.

In this case you have added the single quotes either side of the postcode value, which would be correct if postcode was a string value, but are not needed if postcode field is numeric (as suggested in your first post)

Try:

SELECT * FROM Blad1 WHERE Postcode = " & Combo1.Text , cn, adOpenDynamic

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[jajinder]

WOOOOOOOOOOOOOOHOOOOOOOOOOOOOOOOOOOOO Thanx Guyzzzzz... You both deserve a shiny..



---------------------------------------
This will be the day when all of God’s children will be able to sing with a new meaning, “My country, ‘tis of thee, sweet land of liberty, of thee I sing. Land where my fathers died, land of the pilgrim’s pride, from every mountainside, let freedom ring. - Marten Luther King