Syncing Combo boxes, which are also lookup values

[adrienne_r30]

Hey All,

So I have a bit of a problem. I have two tables,

tbl_Retainer
tbl_Retainer_Grant_Funding

tbl_Retainer has the field,

Retainer_ID

and tbl_Retainer_Grant_Funding has the fields,
Retainer_ID (a lookup field from tbl_Retainer)
Agreement_Num (a lookup field from tbl_Grant)

I have a form based off of a query(not sure if that matters), that uses that tables, tbl_Assignment and tbl_Assignment_Grant_Funding. These tables have the above fields as lookup fields.
So...what happens is, if an Assignment has a Retainer, I want the Agreement_Num box to show only the Agreement_Num's associated with that Retainer, otherwise just show all the Agreement Num's.

In my form, I have Retainer_ID with the row source,
SELECT tbl_RETAINER.Retainer_ID FROM tbl_RETAINER;

And Agreement_Num with the row source,
SELECT [tbl_GRANT].Grant_ID, [tbl_GRANT].Agreement_Num FROM tbl_GRANT ORDER BY [Agreement_Num];

in my AfterUpdate event for Retainer_ID I have,

Private Sub Retainer_ID_AfterUpdate()
Dim strSql As String
strSql = "SELECT [Retainer_ID]," & _
"[Agreement_Num]," & _
"FROM tbl_RETAINER_GRANT_FUNDING" & _
"WHERE [Retainer_ID] = " & Me.Retainer_ID.Value

Me.Agreement_Num.RowSource = strSql
Me.Agreement_Num.Requery
End Sub

When I am in my form and choose a Retainer ID, the Agreement_Num box goes blank, and there are no choices to choose from. I am wondering if this is because the Agreement_Num's are sourced from tbl_Grant and not from tbl_Retainer_Grant_Funding.

Can someone please help me with this issue??
Thanks so much

 

[Cronk]

If you added after the strSQL line

debug.print strSQL

you'd probably see in the immediate window something like

SELECT [Retainer_ID],[Agreement_Num], FROM tbl_RETAINER_GRANT_FUNDING WHERE [Retainer_ID] =...

...

The SQL is invalid because of the second comma.

Incidentally, changing the row source creates a requery event. So your requery is not necessary.

 

[adrienne_r30]

so I put that line in my code and it shows the Code that you referenced above, not sure how that helps my though. It's showing the correct Retainer_ID number, but when I apply it in my form, instead of out putting the Agreement_Num it outputs the Retianer_ID.

 

[Cronk]

Try pasting the output in the immediate window into the sql window in query design and let me know what response you get when you run the query.

 

[adrienne_r30]

After a lot of searching around I figured out how to make it work:

Private Sub Retainer_ID_ON_UPDATE

Dim strSql As String
strSql = "SELECT tbl_RETAINER_GRANT_FUNDING.[ID], tbl_GRANT.[Agreement_Num], tbl_GRANT.Grant_ID " & _
"FROM tbl_GRANT INNER JOIN tbl_RETAINER_GRANT_FUNDING ON tbl_GRANT.[Grant_ID] = tbl_RETAINER_GRANT_FUNDING.[Agreement_Num] " & _
"WHERE tbl_RETAINER_GRANT_FUNDING.[Retainer_ID] = " & Me.Retainer_ID

Me.Agreement_Num.RowSource = strSql

End Sub


But now I have a different problem. When I choose a selection from the combo box, it doesn't save to the table. It is still there in the form if I close and open it, but the info isn't saved any where. any suggestions?

 

[Cronk]

Is the combo box control bound to the field in which you want the data stored?

 

[adrienne_r30]

after much frustration, I figured it out. The SELECT coding has to be in a precise order. The above code was telling access that the Agreement_Num had the tbl_RETAINER_GRANT_FUNDING ID instead of the tbl_GRANT ID. So I just switched the order to:
"SELECT tbl_GRANT.Grant_ID, tbl_GRANT.[Agreement_Num], tbl_RETAINER_GRANT_FUNDING.[ID] "

and it worked perfectly!!