populating text boxes 1

[tag141]

I think I tried to run before I could crawl! I've made an access db which was successful and is being used at the moment. I now want to use vb to connect to it. I can connect to the db but I want to populate text boxes from the fields when a button is clicked (a search if you like.) I type in the name I want and it brings up that person in the boxes. My db is called address, fields are surname and first_name. I can do some things but not populate the boxes. I don't really want to use the data control to go through the fields one by one. Any pointers would be well appreciated or directions to an extremely simple tutorial.

Private Sub command1_click()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source = E:\address.mdb"
conn.Open

Set rs = conn.Execute("SELECT first_name FROM tblAddress " & "WHERE Surname= '" & txtText3 & "'")

rs.Close
conn.Close

txtText1.Text = dbRecordset.Field(surname)
txtText2.Text = dbRecordset.Field(first_name)

Set rs = Nothing
Set conn = Nothing
End Sub

TIA

 

[fattyfatpants]

txtText1.Text = dbRecordset("surname")
txtText2.Text = dbRecordset("first_name")

should put the requested values into the textboxes

 

[Transcend]

Actually if you want ot populate the first name AND the surname you have to select them both in your query!! Currenly you are only selecting one field

Set rs = conn.Execute("SELECT first_name, surname FROM tblAddress WHERE surname= '" & txtText3.text & "'")

Also I wasn't sure why you had a break and a & in the middle of that string so I have removed it ...

Transcend

 

[Transcend]

This won't work

rs.Close
conn.Close

txtText1.Text = dbRecordset.Field(surname)
txtText2.Text = dbRecordset.Field(first_name)


you need

txtText1.Text = rs.Fields("surname")
txtText2.Text = rs.Fields("first_name")

rs.Close
conn.Close

You can't close the recordset till you have used it, and what is dbRecordset?

Transcend

 

[tag141]

Thanks Transcend. I see what you mean by closing before using, and the break? After a while of frustration I'll try anything. I know this should be one of the easiest things to do but it's driving me nuts. I now have this but it still doesn't seem to want to work for me.

Private Sub command1_click()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source = c:\address.mdb"
conn.Open

Set rs = conn.Execute("SELECT first_name, surname FROM tblAddress WHERE Surname= '" & txtText3 & "'")

txtText1.Text = rs.Fields("SURNAME")
txtText2.Text = rs.Fields("FIRST_NAME")

Set rs = Nothing
Set conn = Nothing

rs.Close
conn.Close

End Sub

Is there something missing in the way I connect to the db? Do I have to make the text boxes bound to those fields? Sorry for all the q's.

 

[Transcend]

What do you mean by 'doesn't work' exactly. Do you get an error? Is nothing happening? You probably shouldn't capitalise surname and first name as they aren't capitalised in your query

txtText1.Text = rs.Fields("surname")
txtText2.Text = rs.Fields("first_name")

Put a stop in your code on this line
txtText1.Text = rs.Fields("surname")

by clicking on it and pressing F9.

Run your code and when it breaks type this into the immediate window

?rs.eof

What do you get? Make sure you put a surname in the textbox that exists in your database to get some results.

Transcend

 

[Transcend]

Change your code like so to stop errors from happening when no data is returned

Private Sub command1_click()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source = c:\address.mdb"
conn.Open

Set rs = conn.Execute("SELECT first_name, surname FROM tblAddress WHERE Surname= '" & txtText3 & "'")

if not rs is nothing then
if not rs.eof then
txtText1.Text = rs.Fields("SURNAME")
txtText2.Text = rs.Fields("FIRST_NAME")
else
msgbox "No results"
else
msgbox "No results"
end if

Set rs = Nothing
Set conn = Nothing

End Sub


If you set rs = nothing and THEN try to close it you will get an error.

Transcend

 

[tag141]

Thanks Transcend. I took a step back and came back to it with your help as well. I suddenly noticed a whole heap of errors. My db name was wrong, the button was command2 not 1 and it works now. Cheers for the replies.

 

[Transcend]

Glad it's working

Transcend