SQL, VBA, VAR's & stored QueryDefs

[shkrebs]

Hi there.

I have been reading a looooot of posts regarding these subjects but I can't find a good ex. who helps me.

I've seen that Pat is allwas saying that "if you can - use Stored QueryDefs (SQ)" and I would love to - but I'm in doubt about how.

If I have understood SQ correctly then it's a query made in QBE and saved and then you are using VBA to send the WHERE clause to this query???

In my case I would like to use the data from a table as WHERE clause in a query, and I guess I shall "send" them as parameteres to the query. I have been trying with this model, but it doesn't work:

SQLstr1 = "PARAMETERS PSerie String, PNumero Integer; '" & SQLstr & "' ;"
Set Qdef = DBname.CreateQueryDef("TestQ", SQLstr1)

Using the debug I can see that the SQL is correct but the parameter block seems to be just like it's written here, and I get the message that A97 expects a SELECT, INSERT INTO etc. etc.

Can anybody give me a helping hand like an exsample on how to use this PARAMETERS function or how to make a SQ??

Thanks very much.
Soren

 

[shkrebs]

Yes I've done that already, but maybe I'm complicating my life too much. I better give some more info.

I'm want to print an invoice. We are using a system with a SERIE (A-V or A-D) and then a number (XXXX) There can of course only be 1 combination of SERIE and NUMBER but you see, it's two parameteres I have to user as a search criterium for the report, and that's my problem. By using joins I've now got 1 query which makes 1 table with all the invoice-head-info and all the invoice-lines, but I can't make the report understand this:

FOR EACH SERIE+NUMBER PRINT AN INVOICE

Therefore I was trying to make some code which was doing the same but I'm dying in the parameters problem.

Any idea?

Best regards
Soren

 

[Rich]

Use two combo boxes, one for the letter and one for the number, reference each one in the criteria on their relevant fields in the underlying query

 

[shkrebs]

Hi Rich.

Yes, it's what I've done to select the invoices and I'm ending up with a database with all the selected invoices.
I'm going to send that database to another company who will print out the invoices, pick the things from a warehouse and send it to the customer.
So I'm trying to make that print-out function as automatically as possible and my problem is still to make this:
"FOR EACH SERIE+NUMBER PRINT AN INVOICE AND GOTO NEXT"

Best regards
Soren

 

[Rich]

OK the easiest way is just to add an identifier to the records you've selected
ie. DateSelected
Use that field to select the records to print
ie [DateSelected]=Date()

 

[shkrebs]

Hi Rich.

Well that's an option to, but you know what .... sometimes you can't see the forrest for the trees

Since I have the invoicenumber for each line I just made a report using the group-function and then I put a pagebreak after each group, wola - there I have a perfect invoice on each page (or over more than one) an all in one printing process

I can't believe I've spend so much time looking in another direction

A BIIIG thanks to U and Pat for your input. This page is incrediable, so much knowledge in one place

Best regards
Soren