Access project. How to resolve parameter, instead of prompt box

[cmmrfrds]

I have a stored procedure that takes 1 parameter and my form is bound to the stored procedure. Under the data tab, I put the variable name to send to the stored procedure. It prompts me for a value, but the variable should already have a value. How do I get the variable resolved without the prompt?

Thank you,
Jerry

 

[huzza]

Hi Jerry,
where is the variable defined? Does it refer to a control on the form? What part of the data tab have you put the paramter under and can you say exactly what you've put there?
Richard

 

[cmmrfrds]

Right now it is defined in a public module called PublicMod. In the input parameter area on the data tab is: @parentID int=PublicMod.pubParentID, @keyID int=PublicMod.pubKeyID
As you can see there are 2 parameters. The stored procedure is the data source for a sub form. If I put the parameters in the main Form, and reference like this: Forms!frm_CompanyRates.parentID then it resolves the parameter. I wanted to use the public module since the sub form will be called from 3 forms so I can't reference the Form itsself like the example that works. In the 3 different Main Forms I would like to just store the parameters in the public module then the sub form will work without needing to know which main form it was in. I don't understand why I can't reference the module. It doesn't give and error or a prompt, and it doesn't resolve the variables.

Thank you very much for any assistance.
Jerry

 

[huzza]

Hi Jerry,
I don't think you need to specify the module name you should be able to just state pubParentID and pubKeyID if the variables are public/global.
I think you only use module references for callings functions and procedures where there is more than one of the same name. If there was more than one global/public variable with the same name then you would just get an error when the program tried to work out which to use (it may give an error when you tried to complier).
Can you try it with out the module name and let me know what happens.
Richard

 

[cmmrfrds]

I felt, just like you expressed, that a public variable in a standard module should be recognized. Yes, I tried the variable with and without the module name but the only thing that is recognized in the InputParameters area on the data tab is Forms!formname.fieldname. In fact, a public variable defined in the Form itsself was not recognized. So, I ended up making 3 different sub forms all with the same logic but a different source for the InputParameter. This must be a bug in Access since it is way too restrictive and it doesn't make sense.

I have noticed the query grids in the Access Projects (adp) are limited in building SQL that can be inserted in a recordsource for a form. The SQL will work in the forms but will give syntax errors in the query grid.

Thank you,
Jerry