Enter Parameter Value

[caferacer]

Hi All,

I have a subform which has a find record combo with selection criteria to restrict the combo to a VendorID on a parent form.

However, I also wish to use the subform independently, but on doing so, because the combo box is no longer being supplied a VendorID value, then a “Enter Parameter Value” box appears. I can simply cancel this and continue to use the subform with no problems, but is there a neat way of getting around this issue?

I could duplicate the subform and remove the criteria from the combo box on one, or create two stand alone queries, one with and one without selection criteria but it seems a bit of overkill.

Is there a way of suppressing this “Enter Parameter Value” message box or passing a default value to the query should a VendorID code not be available?

Bit rusty on Access and apols if my DB terminology and logic is unconventional?!

Any ideas gratefully received.

Thanks

 

[bob fitz]

When the main form is opened, Access will open the subform before it opens the main form, so I would try this:
Save the subform without any criterea.
Put some code in the On Open event of the main form to change the Row Source property of the combo box on the subform.

 

[caferacer]

Hi Bob,

Okay, unfortunately I'm not proficient enough with VBA code to know how to "change the Row Source property of the combo box". I can get the code in the right place, but not the generation of the code itself!

Could I get an idea of what code would be required? I'm okay editing simple code, so any examples I can probably modify?

Thanks in advance

Regards

CR.

 

[bob fitz]

We can help you with the code, if you can tell us:

1) The name of the main form.
2) the name of the sub form control on the main form. This may or may not be the same as the name of the sub form.
3) The name of the combo box.
4) Show us the SQL statement that you used in the Row Source property of the combo box when it was restricted to a VendorID

 

[caferacer]

Hello Bob.

Details as requested. After investigating you are correct, the sub form name (frm Equipment) looks to be the same as the sub form control.

Many thanks for your help.

1). frm Vendors
2). frm Equipment
3). Combo44
4).

SELECT [tbl Equipment].EquipmentID, [tbl Equipment].NSN, [tbl Equipment].EquipmentName, [tbl Vendors].CompanyName, [tbl Equipment].VendorID FROM [tbl Vendors] INNER JOIN [tbl Equipment] ON [tbl Vendors].VendorID = [tbl Equipment].VendorID WHERE ((([tbl Equipment].VendorID)=[Forms]![frm Vendors]![VendorID])) ORDER BY [tbl Equipment].NSN;

 

[bob fitz]

Try the following code in the On Open event of the main form:

Dim str As String
  str = "SELECT [tbl Equipment].EquipmentID, [tbl Equipment].NSN, [tbl Equipment].EquipmentName, [tbl Vendors].CompanyName, [tbl Equipment].VendorID FROM [tbl Vendors] INNER JOIN [tbl Equipment] ON [tbl Vendors].VendorID = [tbl Equipment].VendorID WHERE ((([tbl Equipment].VendorID)=[Forms]![frm Vendors]![VendorID])) ORDER BY [tbl Equipment].NSN;"
  Me.[frm Equipment].Form.Combo44.RowSource = str

 

[caferacer]

Hi Bob,

After tripping myself up a couple of times, it works. Thank you so very much.

Does the code of the main form therefore effectively over write the sql default sql in the sub form, as I would have thought this would have created a conflict??

Thanks once again.

Regards

Mark.

 

[bob fitz]

Does the code of the main form therefore effectively over write the sql default sql in the sub form

It doesn't change the SQL Select statement of the sub form (the Record Source property). When the main form opens, the code changes the Row Source property of the combo box on the sub form. The change is not saved but discarded when the forms close.

, as I would have thought this would have created a conflict?

I'm not sure what you mean by this. A conflict with what?

 

[caferacer]

No worries, just interested to know how. It works that's the main thing

Regards and thanks again