USing VBA to Create tbl based on a parameter query

[jane30]

Hi, I'm Access97 user. In my master database, I had query1(select query) and query2(a parameter query asking for Dept code) and query3 (make-table query join query1 and query2 to create a table in a dept database). I'm doing this manually now. That it, I changed the destination database name to the dept I want to query about in query3 first. Then enter the dept# when query3 prompts me for Dept#. I'm thinking to do this in VBA code. I know about VBA code at certain level. Is there anyway I can define Dept# as variable, and use the Dept# passed by parameter query as the destination database name? For example, if query3 prompts me for Dept#, I entered Dept1, then VBA code will create the table for Dept1 in database Dept1.mdb. Any suggestions? Thanks a bunch.

 

[JeremyNYC]

Jane,

Make a table that lists departments and the related databases, with full path. Use that in your query to get the right database.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.

 

[jane30]

Jeremy, are u suggesting me to use db.openrecordset to get the dept# in the Dept# table and pass this variable value to my sql statement in VBA? If so, how can I choose which dept to create? I don't intent to create the table for all the dept#. So, I need a mechnism to allow me to pick the dept#. How should I do this? Thank you very much

 

[JeremyNYC]

Jane,

No, much more basic. Make the table (or add a field to an existing Department table, if there already is one). Put the path/filename in the appropriate field for each Dept. Add this table to your query in design view and add the Path/Filename field to your query. Then you've got the data right there.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.

 

[cmmrfrds]

Can you create a variable and use in a query? Yes, but return it from a function - put function in standard module. You can build sql in query builder but I will include an example in vba code.

In Standard Module
Public myvar as string

Public Function ReturnVar() As String
ReturnVar = myvar
End Function

Your resultant SQL that gets resolved from string variable.
Select * from mytable where mydept = "dept1"

In Form
Build SQL - text var needs quotes around.
Dim sq1 as String
sql1 = "Select * from mytable where mydept = " & _
""" & ReturnVar() """

Me.RecordSource = sql1