Value of a control being used as criteria in a query

[chuckgoss]

Hi all,
I have 2 questions:
Q1) I am trying to use the contents of an unbound control on my form to be one of 2 criteria used by a query in a combo box (ComboBoxB). The unbound control gets automaticly filled in after the update of Combo Box A using the following code:[UBPartNumber] = [ComboBoxA]. I want the 2nd Combo Box (ComboBoxB) to have 2 criteria: One is satisfied by prompting the user to [Enter the location where the part will go], but I want the 2nd criteria in the query to be the value of the unbound control 'UBPartNumber' and to be automaticly "looked at" by the query without prompting the user for any further keying. What code will I need to put as criteria in the Part Number field of my query so that it looks at the unbound control for it's data? The ultimate goal here is to Relocate a part from one location to another and determine if that part number pre-exists at the new location so that in the event that it does, the quantity there will be increased by an amount, and if there is not, a new record will be added showing the new part number at that location.
Q2) If the failure of the query to 'get a hit' on "both" criteria brings up no records found, would the 'NotInList' event be able to determine that, so that it will run code to do procedure x? Otherwise, if there is a record found matching both criteria, then I can run some code to do y?

Thanks in advance

chuck

 

[charityg]

In the query grid use

=forms![yourformname]!UBPartNumber

on the criteria line of the PartNumber field

 

[charityg]

For your question two, use the Dcount function with your query criteria to determine if the query will return any records like

IF DCount("[PartNumber]", "tblName", _

"[PartNumber] = '" & forms!formname!UBPartNumber & "' AND [OtherField] = 'whatever'")>0 then

you have data

else

you don't

endif

HTH
Charity

 

[chuckgoss]

charityg
thanks for the quick response! I tried the code in the query grid as you suggested, but it prompts me with an 'Enter Parameter Value' message for the criteria: Forms![Test Fix for Relocate form]!UBPartNumber

If I manually enter the part number in the field it wants me to, I get the correct result.

How can I eliminate the need for the manual 2nd prompt? I tried to point it to a bound control as well, but with no change.

chuck

 

[charityg]

Is "Test Fix for Relocate form" the name of your form?!? That's a long name that doesn't obey ANY naming conventions!

Normally you would use something like

frmRelocateTestFix

anyway, the solution I gave you should work, if you use the right names for the form and the control (and the form must be open)

I would recheck your names

Charity

 

[chuckgoss]

Charity!!!
I renamed my form as you suggested, and it works GREAT!!! I'll look into naming conventions right away...

Thank you so much!

chuck

ps...I'll work on the 2nd part to my question now, and use your response to that as well...thanks again cg