I'm trying to autofill a textbox (txtdeptname) based on the value in a combobox (combodeptnum). I have a department table that has the info in it (dept_num, dept_name). I tried DLookup, but it keeps giving me an error that the "Object doesn't support this property or method". Thanks in advance for any help!!
Autofill textbox
- Thread starter DeeDee77
- Start date
CJBIRKIN
Drink!
- Local time
- Today, 20:25
- Joined
- May 10, 2002
- Messages
- 256
Hello
Just had a quick try of this. Created a combobox based on a table in a form. Then created a textbox on the form. Then selected the
AfterUpdate option for the combobox and selected code builder.
Private Sub Combo2_AfterUpdate()
Text4.SetFocus
End Sub
Then select the GotFocus property of the textbox and then codebuilder
' if my thinking is correct you will only be returning one value for ' each time you use the combobox????
Private Sub Text4_GotFocus()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim str As String
Set db = CurrentDb
set
On Error GoTo exitsub
' this query would return a single value (time) from table1 where the ID(PK) is equal to the selection in the combobox
Set rst = db.OpenRecordset("SELECT Table1.time FROM Table1 WHERE (((Table1.ID) =" & [Forms]![Form1]![Combo2] & "));")
rst.MoveFirst
'this iterates through the recordset and collects the value in the variable called str
Do While Not rst.EOF
str = rst(0)
rst.MoveNext
Loop
' this returns that value to the text box
Me.Text4.Text = str
exitsub:
End Sub
Chris
Just had a quick try of this. Created a combobox based on a table in a form. Then created a textbox on the form. Then selected the
AfterUpdate option for the combobox and selected code builder.
Private Sub Combo2_AfterUpdate()
Text4.SetFocus
End Sub
Then select the GotFocus property of the textbox and then codebuilder
' if my thinking is correct you will only be returning one value for ' each time you use the combobox????
Private Sub Text4_GotFocus()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim str As String
Set db = CurrentDb
set
On Error GoTo exitsub
' this query would return a single value (time) from table1 where the ID(PK) is equal to the selection in the combobox
Set rst = db.OpenRecordset("SELECT Table1.time FROM Table1 WHERE (((Table1.ID) =" & [Forms]![Form1]![Combo2] & "));")
rst.MoveFirst
'this iterates through the recordset and collects the value in the variable called str
Do While Not rst.EOF
str = rst(0)
rst.MoveNext
Loop
' this returns that value to the text box
Me.Text4.Text = str
exitsub:
End Sub
Chris
Last edited:
- Local time
- Today, 15:25
- Joined
- Feb 19, 2002
- Messages
- 47,594
No coding is required. Usually when using a combo, the bound field of the combo is the unique identifier field which in your case would be deptnum. But the visible field is the descriptive text which in your case would be deptname. Try rebuilding the combo box with the wizard active to acheive this setup. You don't need to store both deptnum and deptname. You only need to store deptnum. You can join to the dept table to obtain deptname whenever you need it.
Following on what Pat has already stated. Even if you want the bound field to be the deptNum for whatever reason, you could still get the string of the deptname string into your textbox by setting its (the textbox) properties to combo.column(1) . column 0 is the deptnum usually out of sight and column 1 would be the text which the text box could pick up. a requery of the form on the afterdate event of the combo would ensure the textbox changes as the combo does
