Solved Openargs value in select statement

[Momma]

Hi everyone
I want to open a form a subform based on a value assigned to openargs. The new form have a listbox with a select statement to filter the records based on the openargs value. The field value to pass is BirthDdate.

SELECT qryDogs3.DogID, qryDogs3.[Puppy Name], qryDogs3.BirthDate, qryDogs3.Mother, qryDogs3.PuppyNumber, qryDogs3.Location, qryDogs3.LocationID FROM qryDogs3 WHERE (((qryDogs3.LocationID)=3)) ORDER BY qryDogs3.Mother, qryDogs3.PuppyNumber;

 

[Badger1961Uk]

I'm not sure why you want to use OpenArgs when you can reference the calling form directly in the query, something like Forms!MyForm!BirthDate

 

[Gasman]

Use a TempVar()?
However you could open the form filtered to that date (remember dates need #) in the OpenForm command and refer to the form control as mentioned previously, as long as that query is not used elsewhere where the form might not be open?

 

[ebs17]

Private Sub Form_Open(Cancel As Integer)
    Dim sSQL As String
    If Not IsNull(Me.OpenArgs) Then
        sSQL = "SELECT ... WHERE BirthDate = " & CONVERT(Me.OpenArgs)   ' CONVERT is aircode
        Me.lstAnyListbox.RowSource = sSQL
    End If
End Sub

OpenArgs creates a string from the transfer. This must therefore be converted into a usable date string for filtering.

 

[Momma]

Private Sub Form_Open(Cancel As Integer)
    Dim sSQL As String
    If Not IsNull(Me.OpenArgs) Then
        sSQL = "SELECT ... WHERE BirthDate = " & CONVERT(Me.OpenArgs)   ' CONVERT is aircode
        Me.lstAnyListbox.RowSource = sSQL
    End If
End Sub

OpenArgs creates a string from the transfer. This must therefore be converted into a usable date string for filtering.

Thank you for your reply ebs17. I'm getting an error on the Select Statement. Not sure how to correct it?

Dim sSQL As String
    
    If Not IsNull(Me.OpenArgs) Then
        sSQL = "SELECT qryDogs3.DogID, qryDogs3.[Puppy Name], qryDogs3.BirthDate, qryDogs3.Mother, qryDogs3.PuppyNumber, " _
            & " qryDogs3.Location, qryDogs3.LocationID FROM qryDogs3 WHERE ((BirthDate = " & Convert(Me.OpenArgs)) And " _
            & " ((qryDogs3.LocationID)=3)) ORDER BY qryDogs3.Mother, qryDogs3.PuppyNumber"
        Me.lstPuppies.RowSource = sSQL
    End If

 

[Momma]

Use a TempVar()?
However you could open the form filtered to that date (remember dates need #) in the OpenForm command and refer to the form control as mentioned previously, as long as that query is not used elsewhere where the form might not be open?

Thanks for your reply, Gasman. Could you explain how to use TempVar(), please?

 

[ebs17]

Not sure how to correct it?

How about if you don't just copy comments in the code and under the code, but read through them and try to understand them?

' CONVERT is aircode

OpenArgs creates a string from the transfer. This must therefore be converted into a usable date string for filtering.

So you should make sure what exactly is being passed to OpenArgs and how to get a valid expression for filtering.
Since I didn't know what you were doing exactly, I abbreviated it to "CONVERT". Of course, this is not a valid general purpose function in Jet-SQL.

 

[Gasman]

Thanks for your reply, Gasman. Could you explain how to use TempVar(), please?

You set a Tempvar and then use that in the query
tempvars("mydate")=Date

then in the query

SELECT tblDaily.*
FROM tblDaily
WHERE (((tblDaily.DailyDate)=[tempvars]![Mydate]));

 

[Momma]

You set a Tempvar and then use that in the query
tempvars("mydate")=Date

then in the query

SELECT tblDaily.*
FROM tblDaily
WHERE (((tblDaily.DailyDate)=[tempvars]![Mydate]));

Thank you, Gasman!