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, 01:11
- Joined
- May 10, 2002
- Messages
- 255
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:
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
Similar threads
