Passing Criteria to append Parameter query through VBA

[JFHBIFF]

Hello Everybody,

I have an append query that contains a parameter [PTIdent] under the field named PTID. I would like to be able to set the parameter through VBA from a control on a form eg Form!PTID

I use the following code as normal

Private Sub Command19_Click()
On Error GoTo Err_Command19_Click

    Dim stDocName As String

    stDocName = "apNewPres"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command19_Click:
    Exit Sub

Err_Command19_Click:
    MsgBox Err.Description
    Resume Exit_Command19_Click
    
End Sub

Is there a way within this code to do this? I realise I could set the form criteria within the query itself. However I wanted to be able to use this query from multiple forms.

Thanks for any help

 

[DCrake]

Simple Software Solutions

Hi

I have discussed this issue with previous threads but essentially as you say you have one query and many forms which need to call the one query.

Let assume that you are passing a stard date and an end date to the query to filter out a specific range of data.

1. Create two public variable

Public DteDateLower As Date
Public DteDateUpper As Date

2. Create two functions

Public Function GetDateLower() AS Date

GetDateLower = DteDateLower

End Function


Public Function GetDateUpper() As Date

GetDateUpper = DteDateUpper

End Function


3. Design an existing form
In the Form that is using the underlying query when you click on the button that, say runs a report based on a date range supplied by the form. On the OnClick Property of the button enter the following:-



'Pass the date range to the public variables

DteDateLower = Me.TxtDateLower
DteDateUpper = Me.TxtDateUpper

4. Design the query

In the design of your single query under the column that hold the filter date enter the following in the criteria section

Between GetDateLower() And GetDateUpper()

Now when you run the query the Where condition will call both function to supply the date range.

In this senario you can use different forms to call a single query without specfically nominating a certain form. As long as you have a date for both the lower and upper dates it will run without erroring.

Once you have mastered this you will find that you can manipulate this functionality to cover several requirements.

Code Master:

 

[JFHBIFF]

Thank you very much for your help DCrake. This worked perfectly. Have a great week