Fill in textboxes with dbCombo?

vbfthis
10-27-2004, 09:19 AM
hello...i was wondering if it was possible to automatically fill in textboxes when a field is selected from a dbCombo...

i have this dbCombo thats linked to a table and it lists Names(i have it pointing to Names within the table)...when i click on that field in the drop down i want it to populate the correct boxes, i.e. address, phone number, etc....
thanks for your help...

Dennis DVR
10-27-2004, 09:45 AM
is the name field set as primary key or has it unique value in your table? basically, you would need to re-open your recordset using SELECT statement with WHERE clause and point it to the name selected in the combo, but that would contradict your current approach, since you are using bound controls, if I were you i'll use unbound control and abandon any bound controls in your application.

btw: are you using VBA Access or VB using Access Database as Backend?

vbfthis
10-27-2004, 10:32 AM
thanks for the reply...if i use unbound controls how can i populate the combo box?...im using vba with access backend..."Name" is just a field in a table...my primary key is just an autonumber...thanks again...

vbfthis
10-27-2004, 01:17 PM
im now using a combo box that i fill with the "Name field"...i now need to autofill the textboxes when a field is selected from the combo box...

from_load()
Dim db As Database
Dim rs As Recordset

Set db = OpenDatabase(App.Path & "\test.mdb")
Set rs = db.OpenRecordset("Select * From testing")
Do Until rs.EOF
cbo.AddItem rs("Name")
rs.MoveNext
Loop

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
end sub

----------------------------------

now im stuck...i cant seem to get the autofill working...if anyone has an idea...thanks...

vbfthis
10-27-2004, 04:30 PM
i then try to use this cod eto grab the fields in the db and place them in the textboxes...but, it does not work...
Private Sub cboCustomer_Click()

Me.txtCoName = Me.cboCustomer.ListIndex(0)
Me.txtAd1 = Me.cboCustomer.ListIndex(1)
Me.txtAd2 = Me.cboCustomer.ListIndex(2)
Me.txtCity = Me.cboCustomer.ListIndex(3)
Me.txtPhone = Me.cboCustomer.ListIndex(4)
Me.txtState = Me.cboCustomer.ListIndex(5)
Me.txtZip = Me.cboCustomer.ListIndex(6)

End Sub

----------------------------------
i would appriciate any help...thanks...

Dennis DVR
10-27-2004, 08:56 PM
If your application is going to run within Access application, I don't think you need to open database using OpenDatabase function, you can directly open the open recordset using the currentdb object i.e.

Dim rs As DAO.Recordset
Set rs = Currentdb.OpenRecordset("Select [Name] From testing")
'the rest of the code


and about your problem
Could you try?
if you are using bound combo control

Private Sub cboCustomer_Click()
Dim rs AS DAO.Recordset
Set rs = cboCustomer.Recordset
Me.txtCoName.Value = rs.Fields("NameFieldHere").Value
Me.txtAd2.Value = rs.Fields("AddressFieldHere").Value
Me.txtCity.Value = rs.Fields("CityFieldHere").Value
Me.txtPhone.Value = rs.Fields("PhoneFieldHere").Value
Me.txtState.Value = rs.Fields("StateFieldHere").Value
Me.txtZip.Value = rs.Fields("ZipFieldHere").Value
rs.close
Set rs = Nothing
End Sub

if you are using unbound combo control

Private Sub cboCustomer_Click()
Dim rs AS DAO.Recordset
Set rs = Currentdb.OpenRecordset("Select * From testing WHERE [Name] = '" & cboCustomer.Text & "'")
If Not rs.EOF And Not Rs.BOF Then
Me.txtCoName.Value = rs.Fields("NameFieldHere").Value
Me.txtAd2.Value = "" & rs.Fields("AddressFieldHere").Value
Me.txtCity.Value = "" & rs.Fields("CityFieldHere").Value
Me.txtPhone.Value = "" & rs.Fields("PhoneFieldHere").Value
Me.txtState.Value = "" & rs.Fields("StateFieldHere").Value
Me.txtZip.Value = "" & rs.Fields("ZipFieldHere").Value
End If
rs.close
Set rs = Nothing
End Sub


Note : I just use the NameFieldHere, but you should change it to the name of the field occording to the name of your name,address, etc... field.

vbfthis
10-28-2004, 06:13 AM
i am a bit new at this...what is bound and unbound...

your solution did work...thank you very much...the one thing it does give me as an error is when i don't have anything in the field in the database, i.e. if the customer doesn't have a second address (txtAd2) they leave it blank...well, when i try to click on that customer with a blank in that field it gives mean error saying "Invalid use of Null"...any ideas...thanks again...

Dennis DVR
10-28-2004, 07:17 AM
i am a bit new at this...what is bound and unbound...

your solution did work...thank you very much...the one thing it does give me as an error is when i don't have anything in the field in the database, i.e. if the customer doesn't have a second address (txtAd2) they leave it blank...well, when i try to click on that customer with a blank in that field it gives mean error saying "Invalid use of Null"...any ideas...thanks again...

That's why I included the "" string before the field value to trap for possible NULL values. (see my second code)

Me.txtAd2.Value = "" & rs.Fields("AddressFieldHere").Value

Unbound -> If the control is not link to a table at designtime
Bound -> control is linked to a table

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum