Synchronize Combo Box and Text Box

  • Thread starter Thread starter h2fcell
  • Start date Start date
H

h2fcell

Hello,
I have a Form with an unbound Combo Box and Text Box. The Form also has a
“Vote†Option Group that has a control source of tblTempVote. I’d like to
synchronize what appears in the Text Box with what is chosen in the Combo
Box. The Form is used to collect Vote data for an append query.

The Combo Box has the following Row Source that works fine.

SELECT Table1.TitleCode FROM Table1 LEFT JOIN tblVote ON
Table1.TitleCode=tblVote.TitleCode WHERE (((tblVote.TitleCode) Is Null));

After the selection is made in the Combo Box, I would like the appropriate
date to appear in the Text Box. MeetingDate in Table1 has Short Date format.

I have tried several ways to get this to work but each way I try gives me a
problem.

I first tried to assign the following code to the After Update event of the
Combo Box.

Private Sub Combo8_AfterUpdate()
Me.Text13 = "SELECT Table1.MeetingDate FROM" & _
" Table1 WHERE TitleCode = " & Me.Combo8â€
End Sub

Instead of a short date, the below SQL is returned to the text box.
SELECT Table1.MeetingDate FROM Table1 WHERE TitleCode = R-2009-3

The SQL did not seem to run.


Second, I removed the After Update event and tried to assign the below
default to the Text Box.

=(SELECT Table1.MeetingDate FROM Table1 WHERE
((([Table1]![TitleCode])=[Forms]![Vote]![Combo8]));)

But that gives me an immediate #Name? and when I pick a Code in the Combo
Box I still get #Name?


Third, I tried to assign the below default to the Text Box.
=DLookUp([Table1]![MeetingDate],"Table1",[Table1]![TitleCode]=[Combo8])

This also gives me the #Name? in the Text Box.

I’ve done this sync using two Combo Boxes, is it not possible to sync a Text
Box and Combo Box?
I know I’m probably overlooking something very simple.
 
The SELECT statement is to build a recordset. Can't set a textbox equal to a
recordset, you have simply set the value of the textbox to a string. Try the
DLookup domain function;
Me.Text13 = DLookup("fieldname", "tablename" , "TitleCode = '" & Me.Combo8 &
"'")
Remember, need apostrophe delimiters for text in SQL statements. The last
argument of DLookup acts as the WHERE clause of an SQL statement. Access
Help has more details.
 
I see now you were trying the DLookup function but don't think the syntax was
right, more complex than need be. Don't put in Default property of the
textbox, put in After Update event of the combobox.

June7 said:
The SELECT statement is to build a recordset. Can't set a textbox equal to a
recordset, you have simply set the value of the textbox to a string. Try the
DLookup domain function;
Me.Text13 = DLookup("fieldname", "tablename" , "TitleCode = '" & Me.Combo8 &
"'")
Remember, need apostrophe delimiters for text in SQL statements. The last
argument of DLookup acts as the WHERE clause of an SQL statement. Access
Help has more details.

h2fcell said:
Hello,
I have a Form with an unbound Combo Box and Text Box. The Form also has a
“Vote†Option Group that has a control source of tblTempVote. I’d like to
synchronize what appears in the Text Box with what is chosen in the Combo
Box. The Form is used to collect Vote data for an append query.

The Combo Box has the following Row Source that works fine.

SELECT Table1.TitleCode FROM Table1 LEFT JOIN tblVote ON
Table1.TitleCode=tblVote.TitleCode WHERE (((tblVote.TitleCode) Is Null));

After the selection is made in the Combo Box, I would like the appropriate
date to appear in the Text Box. MeetingDate in Table1 has Short Date format.

I have tried several ways to get this to work but each way I try gives me a
problem.

I first tried to assign the following code to the After Update event of the
Combo Box.

Private Sub Combo8_AfterUpdate()
Me.Text13 = "SELECT Table1.MeetingDate FROM" & _
" Table1 WHERE TitleCode = " & Me.Combo8â€
End Sub

Instead of a short date, the below SQL is returned to the text box.
SELECT Table1.MeetingDate FROM Table1 WHERE TitleCode = R-2009-3

The SQL did not seem to run.


Second, I removed the After Update event and tried to assign the below
default to the Text Box.

=(SELECT Table1.MeetingDate FROM Table1 WHERE
((([Table1]![TitleCode])=[Forms]![Vote]![Combo8]));)

But that gives me an immediate #Name? and when I pick a Code in the Combo
Box I still get #Name?


Third, I tried to assign the below default to the Text Box.
=DLookUp([Table1]![MeetingDate],"Table1",[Table1]![TitleCode]=[Combo8])

This also gives me the #Name? in the Text Box.

I’ve done this sync using two Combo Boxes, is it not possible to sync a Text
Box and Combo Box?
I know I’m probably overlooking something very simple.
Click to expand...
Click to expand...
 
I see now you were trying the DLookup function but don't think the syntax was
right, more complex than need be. Don't put in Default property of the
textbox, put in After Update event of the combobox.

June7 said:
The SELECT statement is to build a recordset. Can't set a textbox equal to a
recordset, you have simply set the value of the textbox to a string. Try the
DLookup domain function;
Me.Text13 = DLookup("fieldname", "tablename" , "TitleCode = '" & Me.Combo8 &
"'")
Remember, need apostrophe delimiters for text in SQL statements. The last
argument of DLookup acts as the WHERE clause of an SQL statement. Access
Help has more details.

h2fcell said:
Hello,
I have a Form with an unbound Combo Box and Text Box. The Form also has a
“Vote†Option Group that has a control source of tblTempVote. I’d like to
synchronize what appears in the Text Box with what is chosen in the Combo
Box. The Form is used to collect Vote data for an append query.

The Combo Box has the following Row Source that works fine.

SELECT Table1.TitleCode FROM Table1 LEFT JOIN tblVote ON
Table1.TitleCode=tblVote.TitleCode WHERE (((tblVote.TitleCode) Is Null));

After the selection is made in the Combo Box, I would like the appropriate
date to appear in the Text Box. MeetingDate in Table1 has Short Date format.

I have tried several ways to get this to work but each way I try gives me a
problem.

I first tried to assign the following code to the After Update event of the
Combo Box.

Private Sub Combo8_AfterUpdate()
Me.Text13 = "SELECT Table1.MeetingDate FROM" & _
" Table1 WHERE TitleCode = " & Me.Combo8â€
End Sub

Instead of a short date, the below SQL is returned to the text box.
SELECT Table1.MeetingDate FROM Table1 WHERE TitleCode = R-2009-3

The SQL did not seem to run.


Second, I removed the After Update event and tried to assign the below
default to the Text Box.

=(SELECT Table1.MeetingDate FROM Table1 WHERE
((([Table1]![TitleCode])=[Forms]![Vote]![Combo8]));)

But that gives me an immediate #Name? and when I pick a Code in the Combo
Box I still get #Name?


Third, I tried to assign the below default to the Text Box.
=DLookUp([Table1]![MeetingDate],"Table1",[Table1]![TitleCode]=[Combo8])

This also gives me the #Name? in the Text Box.

I’ve done this sync using two Combo Boxes, is it not possible to sync a Text
Box and Combo Box?
I know I’m probably overlooking something very simple.
Click to expand...
Click to expand...
 
Sorry, I should have explicitly put in your field and table names:
Me.Text13 = DLookup("MeetingDate", "Table1" , "TitleCode = '" & Me.Combo8 &
"'")

June7 said:
I see now you were trying the DLookup function but don't think the syntax was
right, more complex than need be. Don't put in Default property of the
textbox, put in After Update event of the combobox.

June7 said:
The SELECT statement is to build a recordset. Can't set a textbox equal to a
recordset, you have simply set the value of the textbox to a string. Try the
DLookup domain function;
Me.Text13 = DLookup("fieldname", "tablename" , "TitleCode = '" & Me.Combo8 &
"'")
Remember, need apostrophe delimiters for text in SQL statements. The last
argument of DLookup acts as the WHERE clause of an SQL statement. Access
Help has more details.

h2fcell said:
Hello,
I have a Form with an unbound Combo Box and Text Box. The Form also has a
“Vote†Option Group that has a control source of tblTempVote. I’d like to
synchronize what appears in the Text Box with what is chosen in the Combo
Box. The Form is used to collect Vote data for an append query.

The Combo Box has the following Row Source that works fine.

SELECT Table1.TitleCode FROM Table1 LEFT JOIN tblVote ON
Table1.TitleCode=tblVote.TitleCode WHERE (((tblVote.TitleCode) Is Null));

After the selection is made in the Combo Box, I would like the appropriate
date to appear in the Text Box. MeetingDate in Table1 has Short Date format.

I have tried several ways to get this to work but each way I try gives me a
problem.

I first tried to assign the following code to the After Update event of the
Combo Box.

Private Sub Combo8_AfterUpdate()
Me.Text13 = "SELECT Table1.MeetingDate FROM" & _
" Table1 WHERE TitleCode = " & Me.Combo8â€
End Sub

Instead of a short date, the below SQL is returned to the text box.
SELECT Table1.MeetingDate FROM Table1 WHERE TitleCode = R-2009-3

The SQL did not seem to run.


Second, I removed the After Update event and tried to assign the below
default to the Text Box.

=(SELECT Table1.MeetingDate FROM Table1 WHERE
((([Table1]![TitleCode])=[Forms]![Vote]![Combo8]));)

But that gives me an immediate #Name? and when I pick a Code in the Combo
Box I still get #Name?


Third, I tried to assign the below default to the Text Box.
=DLookUp([Table1]![MeetingDate],"Table1",[Table1]![TitleCode]=[Combo8])

This also gives me the #Name? in the Text Box.

I’ve done this sync using two Combo Boxes, is it not possible to sync a Text
Box and Combo Box?
I know I’m probably overlooking something very simple.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top