By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 1,221 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

Update Combo Box from a Text Box Help

P: n/a
I am trying to make a combo box dependent on a text box. I cannot get
the combo box to pull the related data from the text box. I have been
all over this user group & have tried several versions of code to no
avail.

I would like to display all contact names (in the combo box) related
to the customer number in the text box.

Here's what I have:

Table: TOrdAck
Fields: OACustID, OAContact

Table: TCust
Fields: CustID, CompanyID, CompanyName

Note: The OACustID and the CustID establishes the relationship between
the two tables.

Query: QCust
Fields: TOrdAck. OACustID, TOrdAck.OAContact, TCust.CustID,
TCust.CompanyID

Form name: FOACust
Record Source: QCust

Textbox Name: CustID
Control Source: OACustID

Control Box Name: Combo78
Row Source Type: Table/Query
Row Source: <empty>

In the After Update event in my textbox CustID, I have the following
code:
*******************
Private Sub CustID_AfterUpdate()

Dim strSQL As String
strSQL = "Select " & Me!CustID
strSQL = strSQL & " from TOrdAck"
Me!Combo78.RowSourceType = "Table/Query"
Me!Combo78.RowSource = strSQL

Me!Combo78.Requery

End Sub
********************
I would like to have the combo box display any OAContacts that match
the CustID. All I get is a blank combo box even if there are multiple
contact names that match the CustID.

I have also tried the following code, but get the same result:

********************
Private Sub CustID_AfterUpdate()

Me!Combo78.RowSource = "SELECT [OAContact],[OACustID] FROM [QCust]
WHERE_
"[OACustID] =" & Me!CustID & " ORDER BY [OAContact];"

Me!Combo78.Requery

End Sub
*******************

Any suggestions? I'm a novice at VBA so any detail you can provide
will be appreciated.

Thanks

Jun 13 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On Wed, 13 Jun 2007 13:56:33 -0700, Swinky <sw********@lunt.com>
wrote:

It appears your sql statement for the combobox is wrong. Create it
first as a query, then switch to SQL view to see what the sql
statement should look like. Initially you'll put a test customernumber
as the criteria.
Temporarily bind this query to your combobox, and make sure you have
the column count and width set correctly.

Then, smarter still, in your query refer back to the textbox to pick
up the customernumber using syntax like:
=Forms!FOACust!CustID
for the criteria.

If you're lucky you end up with a solution entirely created at design
time, and not a single line of code. Even .Requery may not be needed.

-Tom.

>I am trying to make a combo box dependent on a text box. I cannot get
the combo box to pull the related data from the text box. I have been
all over this user group & have tried several versions of code to no
avail.

I would like to display all contact names (in the combo box) related
to the customer number in the text box.

Here's what I have:

Table: TOrdAck
Fields: OACustID, OAContact

Table: TCust
Fields: CustID, CompanyID, CompanyName

Note: The OACustID and the CustID establishes the relationship between
the two tables.

Query: QCust
Fields: TOrdAck. OACustID, TOrdAck.OAContact, TCust.CustID,
TCust.CompanyID

Form name: FOACust
Record Source: QCust

Textbox Name: CustID
Control Source: OACustID

Control Box Name: Combo78
Row Source Type: Table/Query
Row Source: <empty>

In the After Update event in my textbox CustID, I have the following
code:
*******************
Private Sub CustID_AfterUpdate()

Dim strSQL As String
strSQL = "Select " & Me!CustID
strSQL = strSQL & " from TOrdAck"
Me!Combo78.RowSourceType = "Table/Query"
Me!Combo78.RowSource = strSQL

Me!Combo78.Requery

End Sub
********************
I would like to have the combo box display any OAContacts that match
the CustID. All I get is a blank combo box even if there are multiple
contact names that match the CustID.

I have also tried the following code, but get the same result:

********************
Private Sub CustID_AfterUpdate()

Me!Combo78.RowSource = "SELECT [OAContact],[OACustID] FROM [QCust]
WHERE_
"[OACustID] =" & Me!CustID & " ORDER BY [OAContact];"

Me!Combo78.Requery

End Sub
*******************

Any suggestions? I'm a novice at VBA so any detail you can provide
will be appreciated.

Thanks
Jun 14 '07 #2

P: n/a
Hi,
What is the datatype of OACustID and CustID???
Did you try building this in a query first to ensure it works?
bobh.

On Jun 13, 4:56 pm, Swinky <swinkel...@lunt.comwrote:
I am trying to make a combo box dependent on a text box. I cannot get
the combo box to pull the related data from the text box. I have been
all over this user group & have tried several versions of code to no
avail.

I would like to display all contact names (in the combo box) related
to the customer number in the text box.

Here's what I have:

Table: TOrdAck
Fields: OACustID, OAContact

Table: TCust
Fields: CustID, CompanyID, CompanyName

Note: The OACustID and the CustID establishes the relationship between
the two tables.

Query: QCust
Fields: TOrdAck. OACustID, TOrdAck.OAContact, TCust.CustID,
TCust.CompanyID

Form name: FOACust
Record Source: QCust

Textbox Name: CustID
Control Source: OACustID

Control Box Name: Combo78
Row Source Type: Table/Query
Row Source: <empty>

In the After Update event in my textbox CustID, I have the following
code:
*******************
Private Sub CustID_AfterUpdate()

Dim strSQL As String
strSQL = "Select " & Me!CustID
strSQL = strSQL & " from TOrdAck"
Me!Combo78.RowSourceType = "Table/Query"
Me!Combo78.RowSource = strSQL

Me!Combo78.Requery

End Sub
********************
I would like to have the combo box display any OAContacts that match
the CustID. All I get is a blank combo box even if there are multiple
contact names that match the CustID.

I have also tried the following code, but get the same result:

********************
Private Sub CustID_AfterUpdate()

Me!Combo78.RowSource = "SELECT [OAContact],[OACustID] FROM [QCust]
WHERE_
"[OACustID] =" & Me!CustID & " ORDER BY [OAContact];"

Me!Combo78.Requery

End Sub
*******************

Any suggestions? I'm a novice at VBA so any detail you can provide
will be appreciated.

Thanks

Jun 14 '07 #3

P: n/a
On Jun 13, 8:13 pm, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
On Wed, 13 Jun 2007 13:56:33 -0700, Swinky <swinkel...@lunt.com>
wrote:

It appears your sql statement for the combobox is wrong. Create it
first as a query, then switch to SQL view to see what the sql
statement should look like. Initially you'll put a test customernumber
as the criteria.
Temporarily bind this query to your combobox, and make sure you have
the column count and width set correctly.

Then, smarter still, in your query refer back to the textbox to pick
up the customernumber using syntax like:
=Forms!FOACust!CustID
for the criteria.

If you're lucky you end up with a solution entirely created at design
time, and not a single line of code. Even .Requery may not be needed.

-Tom.
OK, so I'm getting closer. I did as suggested...created a query,
tested it & copied the SQL to the combo box Row Source & changed the
query criteria for the CustID field to =Forms!FOACust!CustID.

Complete SQL is:
*****************
SELECT TOrdAck.OANo, TOrdAck.OACustID, TOrdAck.OAContact, TCust.CustID
FROM TCust RIGHT JOIN TOrdAck ON TCust.CustID=TOrdAck.OACustID WHERE
(((TOrdAck.OAContact) Is Not Null) And ((TCust.CustID)=Forms!FOACust!
CustID));
******************

All works well, EXCEPT that when I click on the combo box, I get the
following:
-------------------------------------
Enter Parameter Value:

FOACust!CustID
-------------------------------------
So the CustID number is not getting passed to the combo box. :(

The combo box is really on a subform of my main form "FOAtabSearch".
So I tried the above code changing it to =Forms!FOAtabSearch!FOACust!
CustID but still get the same Parameter Value message box.

Any ideas why CustID is not getting passed to the combo box? When I
view the form, it definately is populated with the CustID before I
click the combo box and get the parameter request.

BTW, both the CustID and the OACustID are set to text.


Jun 14 '07 #4

P: n/a
On Thu, 14 Jun 2007 08:13:28 -0700, Swinky <sw********@lunt.com>
wrote:

=Forms!FOAtabSearch!FOACust!CustID should be changed to:
=Forms!FOAtabSearch!FOACust.Form!CustID
Why?
I think of it as:
=Forms!FOAtabSearch: this is my parent form.
=Forms!FOAtabSearch!FOACust: this is the subform CONTROL on the parent
form.
=Forms!FOAtabSearch!FOACust.Form: this is the subform in the subform
control.

-Tom.
>On Jun 13, 8:13 pm, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
>On Wed, 13 Jun 2007 13:56:33 -0700, Swinky <swinkel...@lunt.com>
wrote:

It appears your sql statement for the combobox is wrong. Create it
first as a query, then switch to SQL view to see what the sql
statement should look like. Initially you'll put a test customernumber
as the criteria.
Temporarily bind this query to your combobox, and make sure you have
the column count and width set correctly.

Then, smarter still, in your query refer back to the textbox to pick
up the customernumber using syntax like:
=Forms!FOACust!CustID
for the criteria.

If you're lucky you end up with a solution entirely created at design
time, and not a single line of code. Even .Requery may not be needed.

-Tom.

OK, so I'm getting closer. I did as suggested...created a query,
tested it & copied the SQL to the combo box Row Source & changed the
query criteria for the CustID field to =Forms!FOACust!CustID.

Complete SQL is:
*****************
SELECT TOrdAck.OANo, TOrdAck.OACustID, TOrdAck.OAContact, TCust.CustID
FROM TCust RIGHT JOIN TOrdAck ON TCust.CustID=TOrdAck.OACustID WHERE
(((TOrdAck.OAContact) Is Not Null) And ((TCust.CustID)=Forms!FOACust!
CustID));
******************

All works well, EXCEPT that when I click on the combo box, I get the
following:
-------------------------------------
Enter Parameter Value:

FOACust!CustID
-------------------------------------
So the CustID number is not getting passed to the combo box. :(

The combo box is really on a subform of my main form "FOAtabSearch".
So I tried the above code changing it to =Forms!FOAtabSearch!FOACust!
CustID but still get the same Parameter Value message box.

Any ideas why CustID is not getting passed to the combo box? When I
view the form, it definately is populated with the CustID before I
click the combo box and get the parameter request.

BTW, both the CustID and the OACustID are set to text.
Jun 15 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.