Passing a variable to a query

[ryetee]

I have a query ListSalesAll that is used in a number of places
I want to be able to pass a parameter to this to limit the number of rows returned as once the database gets growing it could be quite large.
I tried doing this

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("ListSalesAll")

qdf!formtype = "A"
qdf!Formsalesperson = pubUserName
qdf.Execute
qdf.Close

Set qdf = Nothing
Set dbs = Nothing

WHich worked on an update query but it doesn't seem to like a "SELECT" query, and besides I wasn't sure how to process each row returned.

I then tried this

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String


Set db = CurrentDb

strSQL = "ListSalesAll"

Set rs = db.OpenRecordset(strSQL)



Do While Not rs.EOF
If rs!TypeMain <> "A" Then
'exit
Else
If rs!CopyRecord Then
'do something here
Else
'exit
End If


End If
rs.MoveNext
Loop

which works but it returns all the ROWS and I want to limit it to the ROWS for the end user.

ANy ideas?

 

[jdraw]

What exactly do you mean by Limiting the number of Rows returned for the end user? What would cause the number of records to be limited for a particular user?

Have you considered things like:

Select top 10, or Top 25....
Select * from XXX where [some condition]....

If I recall correctly, only action queries(ADD, MODIFY, DELETE) are "executed".
Can you show us the SQL for query "ListSalesAll"?

 

[theDBguy]

Hi. Not sure exactly what you're trying to do but let's say, for example, you have the following query:


SELECT * FROM TableName


This will return all the records from your table. Now, if you want the user to filter the results at some point, I guess you could add a WHERE clause to it. For example:


SELECT * FROM TableName WHERE FieldName = TempVars!VarName


In the above, I used a TempVar to store the filter value in a global variable. So, with this query, you can modify the value of the TempVar based on what you want the user to see. For example


TempVars!VarName="Active"


Now, if you run the query after assigning that value to the variable, the query will be filtered to match all FieldName with a value of "Active."


Hope it helps...

 

[ryetee]

OK solved!!
I had this in but was getting an error which wasn't related. SIlly me.
Solution is

Change
strSQL = "ListSalesAll"
to strSQL = "ListSalesAll where USer = 'Fred'"

 

[ryetee]

What exactly do you mean by Limiting the number of Rows returned for the end user? What would cause the number of records to be limited for a particular user?

Have you considered things like:

Select top 10, or Top 25....
Select * from XXX where [some condition]....

If I recall correctly, only action queries(ADD, MODIFY, DELETE) are "executed".
Can you show us the SQL for query "ListSalesAll"?

Thanks I've got this working but bascially the user can mark the ROWS he wants copying. In the VBA I want to return those ROWS only to run a create and copy query. I've got this working to some extent but hitting a wee snag as detailed in my (soon to be published) next post on this thread.

 

[ryetee]

Hi. Not sure exactly what you're trying to do but let's say, for example, you have the following query:


SELECT * FROM TableName


This will return all the records from your table. Now, if you want the user to filter the results at some point, I guess you could add a WHERE clause to it. For example:


SELECT * FROM TableName WHERE FieldName = TempVars!VarName


In the above, I used a TempVar to store the filter value in a global variable. So, with this query, you can modify the value of the TempVar based on what you want the user to see. For example


TempVars!VarName="Active"


Now, if you run the query after assigning that value to the variable, the query will be filtered to match all FieldName with a value of "Active."


Hope it helps...

Ye sit does. I actually had that coded but removed it as I was getting an error and assumed it was because of the where and it wasn't!!

I do have another problem which I'm just about to type in.

 

[ryetee]

OK I spoke too soon. This almost works. I've changed the query a bit. Basically a list of records/rows are displayed. The user can click a check box on the rows that he wants copying. The check box is copyrecord. The following works but doesn't process one of the records unless there is only 1 record. The code to the above has slightly changed and is as follows...

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String


Set db = CurrentDb

strSQL = "select * from ListSalesAllCopy where copyrecord = True"

Set rs = db.OpenRecordset(strSQL)


Do While Not rs.EOF
MsgBox rs!CopyRecord
If rs!TypeMain <> "A" Then
MsgBox rs!TypeMain
Else
If rs!CopyRecord Then
MsgBox rs![Sales Person] & " " & rs!TypeMain & " " & rs!SeriesNumber
rs.Edit
rs!CopyRecord = False
rs.Update
Else
MsgBox "nooooo" & rs!CopyRecord & " " & rs![Sales Person] & " " & rs!TypeMain & " " & rs!SeriesNumber
End If
'rs.Edit
'rs!SomeField = "Abc"
'rs!OtherField = 2
'rs!ADate = Date
'rs.Update


End If
rs.MoveNext
Loop
Me.Refresh

The MSGBOX are there for following the code.

 

[ryetee]

Right I know what's happening but don't know what to do.
When the user clicks the copyrecord checkbox it's always the last one that isn't processed. I assume this is because focus hasn't been taken off the row and the row therefore hasn't been updated. Hwever I use a button in the form header to kick off an event to process this. I though that would have shifted the focus.
What to do!?

 

[theDBguy]

Hi. You could force a save of the record. At the top of your routine, try adding the following:

If Me.Dirty Then Me.Dirty=False

 

[ryetee]

Hi. You could force a save of the record. At the top of your routine, try adding the following:

If Me.Dirty Then Me.Dirty=False

Yeah that works, thanks Mr theDBguy

 

[theDBguy]

You're welcome. Glad we could assist. Good luck with your project.