Forms, SubForms and Queries

[DreamGenius]

Apologies if this has already been answered but I couldn't find a previous thread that answered it in the first fifteen pages returned by my search

I have a form with some combo boxes, populated by queries from larger tables, and a subform linked to a query which is supposed to return the subset of data from a larger table where the query restricts the return to those records matching the choices selected in the combo boxes on the form.

The challenge I'm having is that one combo box, which checks a text field, works as expected/hoped but the two based on numeric fields populate but don't cause the query to return any records.

Clues? Please?

 

[DreamGenius]

What are the row source, bound column, column count, Column widths of your combo box set to?

For the one that works:

• SELECT distinct [LOCAL_WWUAPINVAPPR].[ACCT_UNIT] FROM [LOCAL_WWUAPINVAPPR]
• 1
• 1
• 2.011cm

For those that don't:

• SELECT distinct [ULTMAST_WWEMPLOYEE].[W16_EMPLOYEE] FROM [ULTMAST_WWEMPLOYEE] WHERE [ULTMAST_WWEMPLOYEE].[W16_TERM_DATE]=#01/01/1700# Or [ULTMAST_WWEMPLOYEE].[W16_TERM_DATE]>Now()
• 1
• 1
• 2.408cm

The second combo box that doesn't work is very similar to the one above apart from the fact that the field can be null while the one above can't. The first is for an Approver and the second for a Proxy - not always required.

The form and subform are linked on Acct_Unit, if that's relevant. Hoping this helps you drive me towards the answer!

 

[DreamGenius]

Does this work OK as a stand alone Query?

Yes. The Where clause refers to a leaving date field in a linked Oracle table which can be either null (which shows as 01/01/1700 but don't ask me why) or a proper date.

By using Now() and Null, I'm only grabbing those employees that have not yet left. These combo boxes populate okay but the sub-form does not show the corresponding results, while it does when using the other combo box.

Hoping this makes sense!

 

[DreamGenius]

try the query with actual values

You may be onto something there - replacing the Form references with static values in the Query appears to have made no difference to the functionality of the Form! Back to the drawing board