473,320 Members | 1,804 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Update Combo Box from a Text Box Help

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
4 3003
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Ron | last post by:
I am new to .NET and am trying to build a little prototype web app that deals with information about loans. I have been reading several books, which has been helpful, but I am having a terrible...
1
by: OhFiddleSticks | last post by:
Does anyone know if there is a way to add a text value to a combo box (the text box part, not the rowsource) in VBA without triggering an update event? I've tried everything I can think of without...
1
by: Burghew | last post by:
Hi all, I have 3 unbound combo boxes in my form which basically helps me to filter for values. I need to update a text box which will be stored with the value I receive after filtering with the...
4
by: faiyeah | last post by:
Private Sub PubName_AfterUpdate() Dim sql As String sql = "select PubCode from Publisher where PubName = '" & PubName.Value & "';" Set rst = CurrentDb.OpenRecordset(sql) MsgBox sql If Not...
3
by: sparks | last post by:
I have a combo box that when you go to the next combo box it is filtered based on the value in the previous one. so if combo box 1 has fred as its value then combo box 2 has 1 2 3 for values....
1
by: Rodney Frisard | last post by:
Can anyone help me get this working. table1 has 3 field, CustId, CustName, CustRate table2 has 7 fields TimeId, TimeCustId, TimeCustName, TimeDate, TimeStartTime, TimeEndTime, TimeCustRate,...
28
by: jverri01 | last post by:
First, I am relatively new to working with variables. Most of my experience has been with interface design. i am using ACCESS ver. 2003, running in Windows XP. Second, I spent an hour searching...
8
by: jmartmem | last post by:
Greetings, I have an Access 2002 continuous form called "Project Update Form" in which users can update project values presented in a series of combo boxes and text boxes. I have three combo boxes...
4
by: dougmeece | last post by:
Morning Everyone... I have a table that needs to be append to and also updated. All the fields in the table are populated with data from the text boxes and combo boxes on a form. The Date...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.