Update Combo Box from a Text Box Help

[Swinky]

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.OAConta ct, 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_AfterUpd ate()

Dim strSQL As String
strSQL = "Select " & Me!CustID
strSQL = strSQL & " from TOrdAck"
Me!Combo78.RowS ourceType = "Table/Query"
Me!Combo78.RowS ource = strSQL

Me!Combo78.Requ ery

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_AfterUpd ate()

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

Me!Combo78.Requ ery

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

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

Thanks

[Tom van Stiphout]

On Wed, 13 Jun 2007 13:56:33 -0700, Swinky <sw********@lun t.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.OAConta ct, TCust.CustID,
TCust.CompanyI D

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_AfterUpd ate()

Dim strSQL As String
strSQL = "Select " & Me!CustID
strSQL = strSQL & " from TOrdAck"
Me!Combo78.RowS ourceType = "Table/Query"
Me!Combo78.RowS ource = strSQL

Me!Combo78.Requ ery

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_AfterUpd ate()

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

Me!Combo78.Requ ery

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

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

Thanks

[bobh]

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...@lun t.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.OAConta ct, 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_AfterUpd ate()

Dim strSQL As String
strSQL = "Select " & Me!CustID
strSQL = strSQL & " from TOrdAck"
Me!Combo78.RowS ourceType = "Table/Query"
Me!Combo78.RowS ource = strSQL

Me!Combo78.Requ ery

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_AfterUpd ate()

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

Me!Combo78.Requ ery

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

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

Thanks

[Swinky]

On Jun 13, 8:13 pm, Tom van Stiphout <no.spam.tom7.. [e-mail]:

On Wed, 13 Jun 2007 13:56:33 -0700, Swinky <swinkel...@lun t.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...cre ated 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.OACustI D, TOrdAck.OAConta ct, TCust.CustID
FROM TCust RIGHT JOIN TOrdAck ON TCust.CustID=TO rdAck.OACustID WHERE
(((TOrdAck.OACo ntact) 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 "FOAtabSear ch".
So I tried the above code changing it to =Forms!FOAtabSe arch!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.

[Tom van Stiphout]

On Thu, 14 Jun 2007 08:13:28 -0700, Swinky <sw********@lun t.com>
wrote:

=Forms!FOAtabSe arch!FOACust!Cu stID should be changed to:
=Forms!FOAtabSe arch!FOACust.Fo rm!CustID
Why?
I think of it as:
=Forms!FOAtabSe arch: this is my parent form.
=Forms!FOAtabSe arch!FOACust: this is the subform CONTROL on the parent
form.
=Forms!FOAtabSe arch!FOACust.Fo rm: this is the subform in the subform
control.

-Tom.

>On Jun 13, 8:13 pm, Tom van Stiphout <no.spam.tom7.. [e-mail]:

>On Wed, 13 Jun 2007 13:56:33 -0700, Swinky <swinkel...@lun t.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...cre ated 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.OACustI D, TOrdAck.OAConta ct, TCust.CustID
FROM TCust RIGHT JOIN TOrdAck ON TCust.CustID=TO rdAck.OACustID WHERE
(((TOrdAck.OAC ontact) 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!Cust ID
-------------------------------------
So the CustID number is not getting passed to the combo box. :(

The combo box is really on a subform of my main form "FOAtabSear ch".
So I tried the above code changing it to =Forms!FOAtabSe arch!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.