passing dynamic sql to criteria field

[Peter Bailey]

Could someone please tell me how to pass criteria as if it were a parameter.

I have a routine now that creates the sql string (well almost). at present
the parameter is [Get DOS module] so I can pass one item ie Module M10S.
Want I want to do is send 1 or more parameters ie M10S OR M10SA OR ......

The query works with one parameter can I send the dynamic sql from vba as a
complete parameter string once the form calls the query?

also I notice the "air" code I have uses the hidden field of the list box ie
the PK is being returned can I get the descriptive text ie in my case
[Module description (text field)] "M10S" I couldnt see how to get the text
string from another column.

regards
Peter

[Jeff Smith]

Hi Peter,

Please don't start a second thread if it's still related to the first. It
makes finding the answer to your original question easier.

To get the second column of a list box use
YourForm!YourLi stBox.ItemData( varItems).Colum n(1)
Column(0) is the first column (which is the default), Column(1) is the
second column, Column(2) is the third column, etc.

Use the dynamic sql string to create a query. Add a few more lines to the
code I posted earlier to create a query for the recordsource for your form.

Dim db As DAO.Database
Dim qdfTemp As DAO.QueryDef
Set db = CurrentDB
*** The code posted earlier***
Set qdfTemp = db.CreateQueryD ef("YourQueryNa me", strSQL)

On Error Resume Next
Set qdf = Nothing
Set db = Nothing

Place all that code into the OnOpen event of your form.

You will need to write a good error trap routine to trap a zero record
recordset and if the query you're creating already exists.

In the OnClose event place this code
On Error Resume Next
DoCmd.DeleteObj ect acQuery, "Your Query Name"

Jeff
"Peter Bailey" <pe*********@an daluz.fsbusines s.co.uk> wrote in message
news:ch******** **@newsg2.svr.p ol.co.uk...

Could someone please tell me how to pass criteria as if it were a parameter.
I have a routine now that creates the sql string (well almost). at present
the parameter is [Get DOS module] so I can pass one item ie Module M10S.
Want I want to do is send 1 or more parameters ie M10S OR M10SA OR ......

The query works with one parameter can I send the dynamic sql from vba as a complete parameter string once the form calls the query?

also I notice the "air" code I have uses the hidden field of the list box ie the PK is being returned can I get the descriptive text ie in my case
[Module description (text field)] "M10S" I couldnt see how to get the text
string from another column.

regards
Peter

[Peter Bailey]

That was really bad nettiqette on my behalf, sorry for that Jeff and thank
you.

"Jeff Smith" <No***@Not.This .Address> wrote in message
news:ch******** **@news.wave.co .nz...

Hi Peter,

Please don't start a second thread if it's still related to the first. It
makes finding the answer to your original question easier.

To get the second column of a list box use
YourForm!YourLi stBox.ItemData( varItems).Colum n(1)
Column(0) is the first column (which is the default), Column(1) is the
second column, Column(2) is the third column, etc.

Use the dynamic sql string to create a query. Add a few more lines to the
code I posted earlier to create a query for the recordsource for your form.
Dim db As DAO.Database
Dim qdfTemp As DAO.QueryDef
Set db = CurrentDB
*** The code posted earlier***
Set qdfTemp = db.CreateQueryD ef("YourQueryNa me", strSQL)

On Error Resume Next
Set qdf = Nothing
Set db = Nothing

Place all that code into the OnOpen event of your form.

You will need to write a good error trap routine to trap a zero record
recordset and if the query you're creating already exists.

In the OnClose event place this code
On Error Resume Next
DoCmd.DeleteObj ect acQuery, "Your Query Name"

Jeff
"Peter Bailey" <pe*********@an daluz.fsbusines s.co.uk> wrote in message
news:ch******** **@newsg2.svr.p ol.co.uk...

Could someone please tell me how to pass criteria as if it were a parameter.

I have a routine now that creates the sql string (well almost). at present the parameter is [Get DOS module] so I can pass one item ie Module M10S.
Want I want to do is send 1 or more parameters ie M10S OR M10SA OR .......
The query works with one parameter can I send the dynamic sql from vba as a

complete parameter string once the form calls the query?

also I notice the "air" code I have uses the hidden field of the list

box ie

the PK is being returned can I get the descriptive text ie in my case
[Module description (text field)] "M10S" I couldnt see how to get the

text string from another column.

regards
Peter

[Peter Bailey]

My apologies AGAIN i thought I had tagged this to my original question! of
yesterday.

"Peter Bailey" <pe*********@an daluz.fsbusines s.co.uk> wrote in message
news:ch******** **@news7.svr.po l.co.uk...

The following code correctly creates the string and if I paste this into a
query it works everytime. It doesnt work though with the query Exists
function in as much that if the query is already there the set querydefs
part of the code doesnt update the sql so I get the string from when it ran previously.

If I delete the query everytime then the createquerydef part works.

I am not sure why. regards in advance

peter

vba follows:

Private Sub Command4_Click( )

Dim db As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String

Dim varItems As Variant
Dim strSelect As String
Dim strWhere As String
Dim strMods As String
Set db = CurrentDb
strSelect = "SELECT CDate(Format(DO SMBK.Date," & """d/m/yy" & """))" & " AS DateDisp, DOSMBK.[DOSM-DSName]" & " FROM DOSMBK"

For Each varItems In Me.ListDOS.Item sSelected

If strMods = vbNullString Then
strMods = "=" & """" & Me.ListDOS.Colu mn(1, varItems) & """"
Else
strMods = strMods & " Or (DOSMBK.[DOSM-DSName])=" & """" &
Me.ListDOS.Colu mn(1, varItems) & """"
End If

Next varItems

strWhere = " WHERE (((DOSMBK.[DOSM-DSName])" & (strMods) & "));"
strSQL = strSelect & strWhere
If Not QueryExists("Qr ygetdosmodulebo okings") Then
Set qdfTemp = db.CreateQueryD ef("Qrygetdosmo dulebookings", strSQL)
DoCmd.OpenQuery "Qrygetdosmodul ebookings"
Set qdf = Nothing
Set db = Nothing
Else
Set qdfTemp = db.QueryDefs("Q rygetdosmoduleb ookings")
DoCmd.OpenQuery "Qrygetdosmodul ebookings"
Set qdf = Nothing
Set db = Nothing
End If

'On Error Resume Next

'Debug.Print strSQL

End Sub

"Jeff Smith" <No***@Not.This .Address> wrote in message
news:ch******** **@news.wave.co .nz... Hi Peter,

Please don't start a second thread if it's still related to the first. It
makes finding the answer to your original question easier.

To get the second column of a list box use
YourForm!YourLi stBox.ItemData( varItems).Colum n(1)
Column(0) is the first column (which is the default), Column(1) is the
second column, Column(2) is the third column, etc.

Use the dynamic sql string to create a query. Add a few more lines to the
code I posted earlier to create a query for the recordsource for your form.
Dim db As DAO.Database
Dim qdfTemp As DAO.QueryDef
Set db = CurrentDB
*** The code posted earlier***
Set qdfTemp = db.CreateQueryD ef("YourQueryNa me", strSQL)

On Error Resume Next
Set qdf = Nothing
Set db = Nothing

Place all that code into the OnOpen event of your form.

You will need to write a good error trap routine to trap a zero record
recordset and if the query you're creating already exists.

In the OnClose event place this code
On Error Resume Next
DoCmd.DeleteObj ect acQuery, "Your Query Name"

Jeff
"Peter Bailey" <pe*********@an daluz.fsbusines s.co.uk> wrote in message
news:ch******** **@newsg2.svr.p ol.co.uk...

Could someone please tell me how to pass criteria as if it were a parameter.

I have a routine now that creates the sql string (well almost). at present the parameter is [Get DOS module] so I can pass one item ie Module M10S.
Want I want to do is send 1 or more parameters ie M10S OR M10SA OR .......
The query works with one parameter can I send the dynamic sql from vba as a

complete parameter string once the form calls the query?

also I notice the "air" code I have uses the hidden field of the list

box ie

the PK is being returned can I get the descriptive text ie in my case
[Module description (text field)] "M10S" I couldnt see how to get the

text string from another column.

regards
Peter