VBA and Queries

[carl6885]

Hi

I need help with a query and vba if possible.....

I need to create a query that selects all from a table where two fields are the values returned in a function residing in its own module.

Once I have returned this query I then need to run another query to group and count the number of occurrences returned.

Upon having this I want to then add the returned information to a listbox (this part I can do)

All of the above I have achieved using the design view in Access but I need to do programmatically.

Any help would be appreciated.

Thanks

Carl

 

[jzwp22]

Are either of the queries parameter queries? If not, then why can't you just use the second query as the row source for the list box?

If you want to do it in code you would just need to transcribe the second query into a string variable in VBA and then assign it to the listbox (air code not tested)

dim mySQL as string
mySQL= "SELECT...."
me.mylistboxname.rowsource=mySQL

If you really need to construct both queries in code then you would nest the first query within the second. You can do this in the query SQL view for test purposes and then transcribe the SQL text into the string variable.

 

[coolpal9]

Pl help....

1. I have created 6 forms in ms access 2003.
2. Also I created blank tables in ms access.
3. Then using Database Splitter I split the database into front end (fe) and backend (be)
4. But the problem I'm facing is that howsoever I fill up the mock form, I am not getting the data back in my backend.

I don't know what to do....

pl help....

 

[jzwp22]

If you look in the form properties, what is listed in the Record Source property? (open the form in design view and then open the property sheet-->data tab)

 

[coolpal9]

hey there's nothing called record source property there....in fact the data tab is completely empty...

 

[jzwp22]

You have to make sure that you are on the form while in design view and not a control or section of the form (the upper left had corner of the form has to have a black square in it).

 

[jzwp22]

If you want you could zip and post a copy of your database (with any sensitive data removed) and I can take a look at it. Please specify the name of the form with which you are having trouble.