By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,594 Members | 3,552 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,594 IT Pros & Developers. It's quick & easy.

passing dynamic sql to criteria field

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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!YourListBox.ItemData(varItems).Column(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.CreateQueryDef("YourQueryName", 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.DeleteObject acQuery, "Your Query Name"

Jeff
"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ch**********@newsg2.svr.pol.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

Nov 13 '05 #2

P: n/a
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!YourListBox.ItemData(varItems).Column(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.CreateQueryDef("YourQueryName", 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.DeleteObject acQuery, "Your Query Name"

Jeff
"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ch**********@newsg2.svr.pol.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


Nov 13 '05 #3

P: n/a
My apologies AGAIN i thought I had tagged this to my original question! of
yesterday.

"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ch**********@news7.svr.pol.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(DOSMBK.Date," & """d/m/yy" & """))" & " AS DateDisp, DOSMBK.[DOSM-DSName]" & " FROM DOSMBK"

For Each varItems In Me.ListDOS.ItemsSelected

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

Next varItems

strWhere = " WHERE (((DOSMBK.[DOSM-DSName])" & (strMods) & "));"
strSQL = strSelect & strWhere
If Not QueryExists("Qrygetdosmodulebookings") Then
Set qdfTemp = db.CreateQueryDef("Qrygetdosmodulebookings", strSQL)
DoCmd.OpenQuery "Qrygetdosmodulebookings"
Set qdf = Nothing
Set db = Nothing
Else
Set qdfTemp = db.QueryDefs("Qrygetdosmodulebookings")
DoCmd.OpenQuery "Qrygetdosmodulebookings"
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!YourListBox.ItemData(varItems).Column(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.CreateQueryDef("YourQueryName", 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.DeleteObject acQuery, "Your Query Name"

Jeff
"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ch**********@newsg2.svr.pol.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


Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.