Query gets first record value only

[Guest]

Hi all, I have a query that accepts criteria from a combo box on my main
form. The SQL looks like this:

SELECT [Wedding Dates].WeddingDateID, [Wedding Dates].Date, [Wedding
Dates].Time, [Date] & " " & " " & [Time] AS [Combined DateTime],
Bride.WeddingDateID, Bride.WeddingDateID
FROM [Wedding Dates] LEFT JOIN Bride ON [Wedding
Dates].WeddingDateID=Bride.WeddingDateID
WHERE (((Bride.WeddingDateID)=Forms!Contacts![Wedding Date])) Or
(((Bride.WeddingDateID) Is Null))

The purpose of this query is to limit the records in the combo box to ones
that are available (i.e. Null values in the foreign key) but also display the
combo-box value for the current record (the first part of the WHERE clause).

The problem is that when I go to the form, the query appears to be only
executing for the first record that displays. When I navigate to other
records, the combo box does not display the Combo box value even though I
know it is set in the dbase table.

Is there a way to make this query re-execute each time I navigate to a new
record? I think if I could make it pass the new parameter each time I
navigate to a different record, the query would work perfectly.

If there's a better way to do this, I'm certainly open to suggestions.
Thanks very much for your help!

 

[Sandra Daigle]

Try using the Current Event of the form. It fires after record navigation.
In this event simply requery the combo:

me.myCombo.requery

 

[Guest]

Thanks so much! That seems to have done the trick. I appreciate the assistance.

Try using the Current Event of the form. It fires after record navigation.
In this event simply requery the combo:

me.myCombo.requery


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hi all, I have a query that accepts criteria from a combo box on my
main form. The SQL looks like this:

SELECT [Wedding Dates].WeddingDateID, [Wedding Dates].Date, [Wedding
Dates].Time, [Date] & " " & " " & [Time] AS [Combined DateTime],
Bride.WeddingDateID, Bride.WeddingDateID
FROM [Wedding Dates] LEFT JOIN Bride ON [Wedding
Dates].WeddingDateID=Bride.WeddingDateID
WHERE (((Bride.WeddingDateID)=Forms!Contacts![Wedding Date])) Or
(((Bride.WeddingDateID) Is Null))

The purpose of this query is to limit the records in the combo box to
ones that are available (i.e. Null values in the foreign key) but
also display the combo-box value for the current record (the first
part of the WHERE clause).

The problem is that when I go to the form, the query appears to be
only executing for the first record that displays. When I navigate to
other records, the combo box does not display the Combo box value
even though I know it is set in the dbase table.

Is there a way to make this query re-execute each time I navigate to
a new record? I think if I could make it pass the new parameter each
time I navigate to a different record, the query would work perfectly.

If there's a better way to do this, I'm certainly open to suggestions.
Thanks very much for your help!