How can I get a combo box to get data based on a text box?

[tyler.deutsch]

Hi,

I have a text box that looks up an ID number from a table. I want the
User to type in an ID and then have a combo box on the same form that
gives a list of dates that correspond to that ID in the text box.
Right now, the combo box displays all the dates from all the ID's. I
want it to only show the dates that are linked to that ID.


Please help,

Thanks

 

[kingston via AccessMonster.com]

Use the text box's AfterUpdate event to modify the combo box's RowSource
property:

Me.ComboBox1.RowSource = "SELECT ... FROM ... WHERE FieldID=" & Me.TextID &
";"

 

[tyler.deutsch]

Use the text box's AfterUpdate event to modify the combo box's RowSource
property:

Me.ComboBox1.RowSource = "SELECT ... FROM ... WHERE FieldID=" & Me.TextID &
";"

Ok...

I am still confused. Do I type that whole coding into the after
update of the text box? Which parts of the coding need to be changed
according to my form's actual names?

Lastly about the "Select...From..." part of the above code... Should
that be selecting from the text box's data or from the combo box's
data?


Thanks

 

[kingston via AccessMonster.com]

Open the form in design mode and look in the combo box's Properties window
for it's Row Source. That should give you a good start. All you're doing is
programmatically modifying that SQL statement by adding a WHERE clause that
refers to the input text box.

Open the form in design mode and the text box's Properties window. Go to the
Event tab and in the After Update line, select [Event Procedure]. Click the .
.. button at the end of the line and a VB editor should open. Type the code
I presented before and modify ComboBox1, the SELECT statement, and TextID.
This assumes that the ID field contains numbers, not text.

Use the text box's AfterUpdate event to modify the combo box's RowSource
property:

[quoted text clipped - 17 lines]
Ok...

I am still confused. Do I type that whole coding into the after
update of the text box? Which parts of the coding need to be changed
according to my form's actual names?

Lastly about the "Select...From..." part of the above code... Should
that be selecting from the text box's data or from the combo box's
data?

Thanks

 

[tyler.deutsch]

Open the form in design mode and look in the combo box's Properties window
for it's Row Source. That should give you a good start. All you're doing is
programmatically modifying that SQL statement by adding a WHERE clause that
refers to the input text box.

Open the form in design mode and the text box's Properties window. Go to the
Event tab and in the After Update line, select [Event Procedure]. Click the .
. button at the end of the line and a VB editor should open. Type the code
I presented before and modify ComboBox1, the SELECT statement, and TextID.
This assumes that the ID field contains numbers, not text.

Use the text box's AfterUpdate event to modify the combo box's RowSource
property:

[quoted text clipped - 17 lines]

Ok...

I am still confused. Do I type that whole coding into the after
update of the text box? Which parts of the coding need to be changed
according to my form's actual names?

Lastly about the "Select...From..." part of the above code... Should
that be selecting from the text box's data or from the combo box's
data?

Thanks

In my Textbox VB code I have the following:

Private Sub Part_AfterUpdate()


Me.Date.RowSource = "SELECT [Rateset APS].[RATE_SETUP_DATE] FROM
[Rateset APS]"

WHERE FieldID = " & Me.Part & "
";"

End Sub


In my ComboBox I have the following in the "RowSource"

SELECT [Rateset APS].[RATE_SETUP_DATE] FROM [Rateset APS];





"Part" is the name of my textbox and "Date" is the name of my
combobox.


What am I doing wrong?


Thanks

 

[kingston via AccessMonster.com]

Watch your line breaks and use of quotation marks. The following should be
contained in a single line unless you use a line continuation marker.

Me.Date.RowSource = "SELECT [Rateset APS].[RATE_SETUP_DATE] FROM [Rateset APS]
WHERE FieldID = " & Me.Part & ";"

Open the form in design mode and look in the combo box's Properties window
for it's Row Source. That should give you a good start. All you're doing is

[quoted text clipped - 32 lines]

- Show quoted text -

In my Textbox VB code I have the following:

Private Sub Part_AfterUpdate()

Me.Date.RowSource = "SELECT [Rateset APS].[RATE_SETUP_DATE] FROM
[Rateset APS]"

WHERE FieldID = " & Me.Part & "
";"

End Sub

In my ComboBox I have the following in the "RowSource"

SELECT [Rateset APS].[RATE_SETUP_DATE] FROM [Rateset APS];

"Part" is the name of my textbox and "Date" is the name of my
combobox.

What am I doing wrong?

Thanks

 

[tyler.deutsch]

Ok,

I put your previous coding exactly as you had it into the text box VB
code of the "after update" function. An error message came back
saying. "Access cannot find the macro,'Me' " or "invalid syntax."
What do you think I'm doing wrong and what should I enter into the
rowsource area of the combobox?


Thanks

 

[kingston via AccessMonster.com]

What version of Access are you using? Avoid field and control names such as
Date. In the VB editor, go to Debug -> Compile... Does it show you exactly
where the error is? Try putting a debug stop on the line and running the
code. When you type Me. in the editor, do both Date and Part appear in the
Intellisense list that automatically appears? If not, your control names may
not be Date and Part. Is FieldID a numeric field? If not, add single quotes
around the parameter. Are FieldID and Part of the same data type? The
technique works, but it's hard for me to pinpoint what's wrong in your case.
The actual error might be somewhere else entirely. So be very specific about
what you're doing and what error messages appear.

 

[tyler.deutsch]

Ok.

What I am trying to accomplish is to type in a part # into a text box
then choose a date from a combo box. Both the text box and combo box
are pulling from the same table [Rateset APS]. "Part" is the name of
the text box and "Date" is the name of the combo box. Both are
pulling numbers and not letters. I am using Access 2000.

Here is what the After Update code in "Part"

Private Sub Part_AfterUpdate()

Me.Date.RowSource = "SELECT [Rateset APS].[RATE_SETUP_DATE] FROM
[Rateset APS]" WHERE FieldID = " & Me.Part & ";"

End Sub

Here is what the RowSource says in the "Date" box

SELECT [Rateset APS].[RATE_SETUP_DATE] FROM [Rateset APS];



Does that help explain what I am trying to do?

 

[kingston via AccessMonster.com]

Watch your quotation marks:

Me.Date.RowSource = "SELECT [Rateset APS].[RATE_SETUP_DATE] FROM [Rateset APS]
WHERE FieldID = " & Me.Part & ";"

You have an extra quotation mark before WHERE.

Ok.

What I am trying to accomplish is to type in a part # into a text box
then choose a date from a combo box. Both the text box and combo box
are pulling from the same table [Rateset APS]. "Part" is the name of
the text box and "Date" is the name of the combo box. Both are
pulling numbers and not letters. I am using Access 2000.

Here is what the After Update code in "Part"

Private Sub Part_AfterUpdate()

Me.Date.RowSource = "SELECT [Rateset APS].[RATE_SETUP_DATE] FROM
[Rateset APS]" WHERE FieldID = " & Me.Part & ";"

End Sub

Here is what the RowSource says in the "Date" box

SELECT [Rateset APS].[RATE_SETUP_DATE] FROM [Rateset APS];

Does that help explain what I am trying to do?

 

[tyler.deutsch]

Ok,

I entered in this part # "2061M60G27" into the text box

then i clicked the combo box and this came back:

syntax error (missing operator) in query expression 'FieldID =
2061M60G27'.


Any ideas?

 

[kingston via AccessMonster.com]

2061M60G27 is not a number. Are you sure FieldID is numeric? If it is not,
then you can qualify the input with single quotation marks:

... FieldID = '" & Me.Part & "';"

 

[tyler.deutsch]

Ok,

My FieldID is not numeric, sorry about that. It is a series of
numbers and letters.


I put single quotes in like you said, entered the part # in, then
click on the combo box to choose a date and then a prompt came up
asking for a parameter value for the FieldID. Its not suppose to do
this right? What do you think the issue is now?

 

[John W. Vinson]

Ok,

My FieldID is not numeric, sorry about that. It is a series of
numbers and letters.


I put single quotes in like you said, entered the part # in, then
click on the combo box to choose a date and then a prompt came up
asking for a parameter value for the FieldID. Its not suppose to do
this right? What do you think the issue is now?

Do you have a field in your table named FieldID? That's what it's asking for.
Replace the name FieldID in your code with the actual name of your field.

Neither Kingston nor I can see your database, and you have chosen not to post
any information about your table fieldnames. He's giving you EXAMPLES of code
that you can adapt to your particular database - not a finished, working
application including all your own information.

John W. Vinson [MVP]

 

[kingston via AccessMonster.com]

Is FieldID a field within the table [Rateset APS]?

 

[tyler.deutsch]

The name of the field in [Rateset APS] that the part number is coming
from is called "Item_Name"


In the coding I changed "FieldID" to [Rateset APS].[Item_Name]
but still did not work. Is that what I was suppose to do?

 

[kingston via AccessMonster.com]

Yes, that's what you were supposed to do. Again, when posting in these
forums, be very specific about what doesn't work. Do you get an error
message? If so what is it? Otherwise, does it not work because it doesn't
return the values you think it should return or is the combobox not getting
updated at all? In any event, the following will work provided you replace
the field and control names with your data and you use the correct syntax:

Me.Date.RowSource = "SELECT [Rateset APS].[RATE_SETUP_DATE] FROM [Rateset APS]

WHERE [Rateset APS].[Item_Name] = '" & Me.Part & "';"

The name of the field in [Rateset APS] that the part number is coming
from is called "Item_Name"

In the coding I changed "FieldID" to [Rateset APS].[Item_Name]
but still did not work. Is that what I was suppose to do?

 

[tyler.deutsch]

Sorry, yes I admit, this is my first time to post on this forum so
thank you for your patience.


Ok it worked!

One last issue... when I push the button to pull down the combo box,
it gives multiple entries for the same date. Lets say a part had 2
different dates, 10/03/2007 and 11/08/2007
The combo box would read like this:
10/03/2007
10/03/2007
10/03/2007
10/03/2007
10/03/2007
10/03/2007
11/08/2007
11/08/2007
11/08/2007
11/08/2007
11/08/2007


Then when I click on one of the dates it gives all the 10/03/2007
entries. In this case it would be 6 entries. How can I make it so
the combo box only shows one "10/03/2007" instead of 6 and still pops
out 6 when I run the query?

 

[kingston via AccessMonster.com]

Change SELECT ... to SELECT DISTINCT ...

Sorry, yes I admit, this is my first time to post on this forum so
thank you for your patience.

Ok it worked!

One last issue... when I push the button to pull down the combo box,
it gives multiple entries for the same date. Lets say a part had 2
different dates, 10/03/2007 and 11/08/2007
The combo box would read like this:
10/03/2007
10/03/2007
10/03/2007
10/03/2007
10/03/2007
10/03/2007
11/08/2007
11/08/2007
11/08/2007
11/08/2007
11/08/2007

Then when I click on one of the dates it gives all the 10/03/2007
entries. In this case it would be 6 entries. How can I make it so
the combo box only shows one "10/03/2007" instead of 6 and still pops
out 6 when I run the query?

 

[tyler.deutsch]

That helped! thanks!

Do you know how I can make the form pop up when someone opens Access
and then lock it so that only the form is showing and only I can
update the database?