How to Use the DoCMD set parameter statement

[Mikezy56]

I am going blue in the face trying to set the parameter "chosenorder1" and pass it into form frmChooseUnit.
Then open the form .
The form is based upon a query called qryUnits.
I am sure this should be very easy but it just does not work for me

 

[mdlueck]

Please copy / paste a sample of what you have been trying. Please remember to wrap your code in [#] code tags which is found on the button bar just on top of the message reply box.

Perhaps you mean to pass the value in to be obtained by the form making a call to Me.OpenArgs ??? If so, this sample should set you on you way...

  Dim strDocName As String

  strDocName = "metoolingassociate"

  DoCmd.OpenForm FormName:=strDocName, _
                 [B]OpenArgs:=Me.Name[/B]

 

[Mikezy56]

Michael
Is this the way to send it please?
#
Dim stDocName As String
Dim chosenorder As Integer
stDocName = "frmChooseUnit"
chosenorder = Me!Combo7.Value
DoCmd.SetParameter "qryunits!chosenorder1", chosenorder
DoCmd.OpenQuery qryUnits, acViewNormal, acReadOnly
DoCmd.SetParameter "frmchooseunits chosenorder1", chosenorder
DoCmd.OpenForm stDocName, , , "[chosenorder1] = chosenorder "

Exit_Command13_Click:
Exit Sub#

 

[mdlueck]

DoCmd.OpenForm stDocName, , , "[chosenorder1] = [B]chosenorder[/B] "

That bold bit is being treated as it if were a static string value, not the variable of that same name. Try this:

   DoCmd.OpenForm FormName:=strDocName, _
                  [B]WhereCondition:="[chosenorder1] = " & chosenorder[/B]

 

[Mikezy56]

Thank you Michael
I tried that the snippet now looks like this
#################################
Private Sub Combo7_AfterUpdate()
On Error GoTo Err_Command13_Click

Dim stDocName As String
Dim chosenorder As Integer
strDocName = "frmChooseUnit"
chosenorder = Me!Combo7.Value
DoCmd.OpenForm FormName:=strDocName, _
WhereCondition:="[chosenorder1] = " & chosenorder

Exit_Command13_Click:
Exit Sub

Err_Command13_Click:
MsgBox Err.Description
Resume Exit_Command13_Click

End Sub
########################################
Unfortunately it brought up an alert box enter parameter value.
If I do enter a parameter value it is ignored.
I probably should have said chosenorder1 is the parameter for the query which drives the chooseunit form.

 

[mdlueck]

I tried that the snippet now looks like this

Dim [B]stDocName[/B] As String
    [B]strDocName[/B] = "frmChooseUnit"
    DoCmd.OpenForm FormName:=[B]strDocName[/B], _

Watch your variable names...

I probably should have said chosenorder1 is the parameter for the query which drives the chooseunit form.

So this form is based on a query? Then yes, you need VBA code to update the DAO.QueryDef object, not args to the OpenForm.

I have sample code which builds brand new DAO.QueryDef objects here:

Example of DAO.QueryDef objects downloading records from a SQL BE DB via Pass-Through query and populating a FE temp table with them
http://www.access-programmers.co.uk/forums/showthread.php?p=1119605#post1119605

You can modify the code to open/edit pre-existing DAO.QueryDef objects instead of creating brand new ones.

 

[Mikezy56]

Thank you Michael it will take me a while to absorb that but I am now much more hopeful of resolving this issue based on your code.

 

[mdlueck]

You are most welcome, Mikezy56. Please check back in to report on progress.

 

[Mikezy56]

Michael
I guess that I need to change the string names in that API to reflect my field names.
Is that correct?

 

[mdlueck]

The building block principles are correct, schema needs to change and become specific to your database.