Filtering Passthru queries 1

[Xzibit7]

dhookom gave a very helpful tip on how to filter the data with a passthru query using vb here is the code he suggested per my needs

Private Sub Command0_Click()
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs("ZDOR")
qd.SQL = "SELECT * FROM DSS_USER.V_Portal_ZDOR " & _
"WHERE NIIN IN ('011026065')"
docmd.openquery "ZDOR"

In his suggestion he said that this speeds up the process from about 30 minutes to 5 seconds..Unfortunately I can't seem to get this to work, when I click on Command) Access just gives me a hourglass and locks up. Am IO doing something wrong?

 

[Xzibit7]

Okay I got the above to work but how can I make the number a variable i.e. a list box in my form?

 

[dhookom]

Assuming the list box is single select, bound to a text field, and is on the form running the code:

Private Sub Command0_Click()
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs("ZDOR")
qd.SQL = "SELECT * FROM DSS_USER.V_Portal_ZDOR " & _
  "WHERE NIIN IN ('" & _
  Me.ListBoxInMyForm & "')"
docmd.openquery "ZDOR"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Xzibit7]

Yess thanks so much. You are the man

 

[Xzibit7]

Okay...I got that to work now my problem is that I have 6 passthru queries which go onto a form, 5 are subforms because the have multiple values for 1 NIIN. The problem is that I can get the NIIN I want for each passthru query and I put that into another select query for each, but I am unable to use those select queries as subforms. I do not want any child parent linking because the data that I want is already in each query. Is this possible to use the data that I have gotten from the passthru queries in a subform?

 

[Xzibit7]

Me again....I am using a separete database to do these changes in and if they work I am going to implement them into my own....Now for some reason access is acting squirrely with this database in particular(my database that has multiple users does not seem to have this problem) and when I pull up my main form it takes about 3 minutes just to maximize or do anything to it. Now I have tried to import the same form over from my database but that still does not work. So in particular I think if I set the parent child relationship to none this will work, However I am scared to do so in database that everyone is using because I feel I will botch everthing up I have also tried to import to another brand new database but this still goes very slow as well. Am I correct in my assumption that a passthru query will work as a subform if the parent child relaitonship is set to none?

 

[dhookom]

Try create a standard query that selects * from your pass-through.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Xzibit7]

okay thanks

 

[Xzibit7]

How would I make these passsthru queries into a table without doing a make table query..Which first erases the passthru query when I make a make table query from this passthru query (Go figure).

 

[dhookom]

A pass-through query with a maketable query will not erase the pass-through. You didn't make a table that has the same name as the pass-through did you?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Xzibit7]

yes I did and now i am finding that out(I was just about to post another comment) ....Thanks again

 

[dhookom]

That's just one of the many reasons we use naming conventions. All of my pass-through queries are named beginning with "qspt..." while tables are "tbl...". Make table queries are "qmak...."

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]