How to use Docmd.SetParameter ?

[otto]

Hello everybody!


I'm new to this nice forum and first want to say hello to all and thank you for your efforts


My question is simple but I couldn't find the answer anywhere, so I hope some one knows it:


How is the use of

Docmd.SetParameter

?

See https : // docs.microsoft.com/de-de/office/vba/api/access.docmd.setparameter


I quote MS Docs:

Use the SetParameter method to create a parameter for use by the BrowseTo, OpenForm, OpenQuery, OpenReport, or RunDataMacro methods.


Syntax expression.SetParameter (Name, Expression)
expression A variable that represents a DoCmd object.



Parameter Name: The name of the parameter. The name must match the name of the parameter expected by the BrowseTo, OpenForm, OpenQuery, OpenReport, or RunDataMacro method.


Expression: An expression that evaluates to a value to assign to the parameter.

Example:

Private Sub cmdAddComment_Click()
DoCmd.SetParameter "prmComment", Me.txtComment  

DoCmd.SetParameter "prmRelatedID", Me.txtId  

DoCmd.RunDataMacro "Comments.AddComment"  

End Sub

I could not find any details on how to use the parameters that are set with SetParameter method.

Do you have a simple example how to use the parameters with OpenForm?

Thanks

Otto

 

[theDBguy]

Hi Otto.Welcome to AWF! If you don't mind me asking, why are you interested in the SetParameter method? What are you trying to achieve? From what you have posted, it seems like it's similar to a TempVar.

 

[theDBguy]

Hi Otto.Welcome to AWF! If you don't mind me asking, why are you interested in the SetParameter method? What are you trying to achieve? From what you have posted, it seems like it's similar to a TempVar.

Okay, after reading the article linked above, I do think it is very similar to TempVars with the exception that TempVars are more permanent than the parameters created by SetParameter.


I've never tried it before but I imagine it can be used as follows. Assuming a query like the following:

SELECT * FROM TableName WHERE ID=[EnterID]

We can try the following code:

DoCmd.SetParameter "EnterID", 4
DoCmd.OpenQuery "ParameterQueryName"

 

[otto]

Thanks for your quick response.


At the moment I'm refreshing my Access knowledge and I've read the documentation of DoCmd till this point and I couldn't figure out the purpose and way of using of SetParameter. So I searched for it a lot (the whole afternoon ) however without success.


From what I could find out till now is that SetParameter is new since Access 2010. And it seems like TempVars but more volatile as you said but I can't grasp what it es for?


I've searched for it all the internet but found no info.


Maybe someone knows the secret behind it

 

[otto]

SOLVED! How to use Docmd.SetParameter ?

Your assumption and thoughts are gold value and you fulfill your motto Thanks for that.


Analog to the query it works also with forms. If you have a filter in say Form2 like

ID=[EnterID]

And you have a Form1 with a text field txtID and a button btnForm2 then the code for the button in Form1 would look like this

Private Sub btnForm2_Click()
DoCmd.SetParameter "EnterID", Me.txtID
DoCmd.OpenForm "Form2"
 End Sub

Till next time

 

[theDBguy]

Re: SOLVED! How to use Docmd.SetParameter ?

Your assumption and thoughts are gold value and you fulfill your motto Thanks for that.


Analog to the query it works also with forms. If you have a filter in say Form2 like

ID=[EnterID]

And you have a Form1 with a text field txtID and a button btnForm2 then the code for the button in Form1 would look like this

Private Sub btnForm2_Click()
DoCmd.SetParameter "EnterID", Me.txtID
DoCmd.OpenForm "Form2"
 End Sub

Till next time

Hi. Glad to hear you got it sorted out. Thanks for bringing up this topic. I learnt something new today. Cheers!

 

[otto]

Solved! How to use Docmd.SetParameter ?

Hello,


nice to hear that topic wasn't boring. I should have asked the question 8 hours ago. Then I wouldn't have lost so much time. Next time I know where I can get help.

Bye

 

[theDBguy]

Access offers a lot of options. I don’t think I’ll ever know all of them.