Dropdown Box using a Parameter Query?

[bernadou]

I have a form, that needs to show a drop down that includes all options that
currently are not assigned to the record in view.

So, if the record looks like this

Parent Record
Child 1
Child 2

The drop down should look like this:
Child 3
Child 4
Child 5

Right now, the drop down looks like this:
Child 1
Child 2
Child 3
Child 4
Child 5

So to get the records for the dropdown to show this kind of situation I have
(so far) had to use two queries. The first gets all the records that are
currently related to the parent by way of a parameter. I’ve then created a
second query that uses null matching against the first query to get all the
records from the child table that are not currently returned by the first
query.

So, everything works great when I’m in the query design portion of access.
When I’m using the form with the dropdown, I’m trying to pass the PK of the
parent record into the query by using the following code:

cmboChildRecords.RowSource = “mySecondQuery “ & PK_Value

If you are with me this far, then you probably know that it doesn’t work.
It doesn’t throw an error, it just doesn’t return any records. What I can’t
seem to do (I think) is pass the parameter into the query so that it is read
by the second query and returns the appropriate results.

If I change the code to
cmboChildRecords.RowSource = “mySecondQueryâ€
then allow the prompt to pop-up and enter the appropriate PK_Value manually,
everything works fine.

I'm thinking this is simple, what am I missing?

Thanks

 

[Douglas J Steele]

You can't pass parameters to queries like that.

Ensure that PK_Value is on the form (it doesn't have to be visible), and
have mySecondQuery point to that field.