Using combobox values in query

[zfind]

I wanted to post it exactly as it came out of VBA in case there was too many spaces or something like that - I know it looked horrible, sorry.

I'm geussing the error is the INNER JOIN for tblPartnerset.PartnersetID? It looks out of place. The 4108 without quotes is ok though right? So I need to fix that INNER JOIN...

I think I need to study queries after this...

 

[namliam]

No your joins look to be fine!

Look at the select part! There is an empty line there... with only a comma, you have a comma to many just before the FROM .

On thing about spaces, you can never have to many... mostly the problem with spaces is that you have to few.

 

[zfind]

OK, update - after ironing out a few small issues, everything is working great! I'm really happy with the solution we worked out - it is very simple and clean and runs really fast, considering I have 300,000+ records.

The one final thing I need to do is to link my two macros - the first being the VBA code that edits the query criteria and the second being the Access-macro (i.e. built using the macro editor on the main screen). This is the macro that exports the edited query as XLS - I found it much easier to do it this way than to code it. I just need to know how to call an "Access macro" from a vba procedure.

If you help me with this one I promise to leave you alone

EDIT:

docmd.runmacro

Worked well. Thanks for all of your help on this, I learned a lot!

 

[namliam]

The one final thing I need to do is to link my two macros - the first being the VBA code that edits the query criteria and the second being the Access-macro

Noooooooooooooooooooooooooooooooooooooooooooooooooooooo!

Dont use MACRO's !!!!

Noooooooooooooooooooooooooooooooooooooooooooooooooooooo!

Macro's are worse than bad... why not simply code it ? Docmd.transferspreadsheet!
Much easier much more flexible and all your code (re-write the query + export) is in one place therefor much easier and to maintain.

 

[zfind]

OK, OK , OK. Is it just then docmd.transferspreadsheet ("querynamehere")?

Can I still get the 'save to' browse box to come up?

 

[namliam]

lookup the transferspreadsheet command in the help, it is pretty obvious...

A save to box is a little more complex (unfortunatly).
Samples can be found:
http://www.access-programmers.co.uk/forums/showthread.php?t=153350&highlight=Browse
More samples:
http://www.access-programmers.co.uk/forums/search.php?searchid=2355600

 

[zfind]

Hrmm, the solution you suggested is a bit more complex but I like the neatness and flxibility. The save as dialogue box wasn't so hard. It has all come together really nicely now, thanks!