Need help setting up first query

  • Thread starter Thread starter Jdougal via AccessMonster.com
  • Start date Start date
J

Jdougal via AccessMonster.com

I have a form setup that has multiple dropdown boxes. The one dropdown box is
used to select the number of people working the shift. I need the value
entered here to restrict the data available on the next drop down box (called
assignment).

I have two tables where this information is

tbl SignUp
# people

tbl Assignment
# people
Assignment

In this table I have the values that correlate between the two aka 2 then the
assignment, 2, then the other possible assignment etc.

the form is called SignUp1. Can anyone help me with this?
 
Dear J:

The technique would be to assign the SQL for the second "Assignment" combo
box whenever a selection in the first "SignUp" combo box is made. The Click
event of the SignUp combo box would be ideal for this.

If the focus (Got Focus event) is placed in the "SignUp" combo box and there
is no "Assignment" selected, you may want it to display nothing, perhaps
exhibit a MsgBox, and pass the focus back to the "Assignments" comb box.

Tom Ellison
 
Mr. Ellison,

Thanks for your help thus far,

I'm very new to Access and have no SQL access. Is there anyway you could
provide me with a little futher assistance?

Also thanks for the second suggestion for the message, I did not even think
of that.
 
Dear J:

Just address me as Tom, if you please.

SQL is the "code" for a query. Any query you can write may be expressed as
SQL. This code may be placed into the RowSource of the combo box ("drop
down") for assignment to change its behavior.

Your original post said:

"I need the value entered here to restrict the data available on the next
drop down box (called assignment)."

This indicated to me that the combo box's list would change every time
something was selected in the SignUp. Right?

There is an "event" that occurs every time the SignUp comb box is changed,
called "Click". In the code for that event, you can recover the new value
selected in the SignUp and use that to filter the query to be used as the
Assignment combo box. Perhaps you used the automated methods to create the
current query that operates the Assignment combo box. If you would look at
the RowSource property of the Assignment combo box, you may see there is
some code placed there by your programming. Copy that into your next
message. Perhaps I can figure out something from that. Also, please
provide the name of the SignUp and the Assignment combo box controls.

Tom Ellison
 
Tom,


This indicated to me that the combo box's list would change every time
something was selected in the SignUp. Right?
Click to expand...

That is correct. Whenever "# People" is input I want to then restrict the
options you are able to select in "Assignments"

I currently do not have a query bringing back the current assignments, this
area has been left blank. The two control sources or names are "# people"
which comes from the tbl SignUp, and "Assignment" which comes from the tbl
Assignment.

Thanks for any assistance you may be able to provide.
 
Dear J:

If I am following what you need, then the query for the Assignments combo
box would be:

SELECT Assignment
FROM tblAssignment
WHERE [# people] =
[Forms]![YourFormName]![ComboBox]

In the above, substitute the actual name of the form where it says
YourFormName, and the name of the # people combo box where it says ComboBox.

The click event of the # people combo box needs to requery the Assignment
combo box, so it can generate a new list.

In addition, you should set the value of the Assignment combo box to ""
whenever the click event occurs in the # people combo box. Otherwise, the
former value may be one that is not in the list, but would be falsely
retained.

There is an alternative of checking to see if the value in the Assignment
combo box is still in the new list, and retaining it. This is what we
always do, but it's a bit more advanced.

Tom Ellison
 
Back
Top