OK, I'm praying that this can be done...

[wrek]

Hi,

Someone please tell me that variables can be used in SQL statements? Is there any possible way?

Heres the SQL:
SELECT [Equipment Docs 1].EQ_COMPONENT_TAG, ORSUSR_TIDECDRW.DOCUMENT_TYPE, ORSUSR_TIDECDRW.DOCUMENT_SUB_TYPE, ORSUSR_TIDECDRW.DOCUMENT_NBR, ORSUSR_TIDECDRW.DOC_SHEET_NBR, Null AS Action, Null AS [Action Note] INTO [Equipment Docs Temp]
FROM [Equipment Docs 1] INNER JOIN ORSUSR_TIDECDRW ON [Equipment Docs 1].oc = ORSUSR_TIDECDRW.OWNER_CODE_50;

for the INTO clause I want to be able to specify,

INTO [Equipment Docs Temp] & [Forms]![Form - Change Record]![Subform Admin].[Form]![ChangeID];

Is this possible?

(The reason is I have a make table query that runs before a form is open - bound to that temp table. Then after the user inputs/modifies in that form, I run anappend query, appending the changes to the Master table. I want to make sure the above make table query makes a table with a different name every time so two different users dont corrupt each others temp data.)

[This message has been edited by wrek (edited 08-09-2001).]

 

[Pat Hartman]

You cannot parameterize the table name in a query. You would need to build the query in code and run the constructed SQL string. However, I have an easier solution. Rather than making temp tables which also require changing the recordsource of the form (which is itself going to be a problem in a multi-user database), use a common table with an additional column that contains a userId. That way the form always points to the same table and each user has a separate set of records in the common table.

 

[wrek]

Hi Pat & others,

Here's why I can't make just one table:
http://www.angelfire.com/pw/access/MakeTableprob.doc

 

[wrek]

Isn't there a way I can execute SQL statements through VBA?

The Execute method accomplish this?

 

[shacket]

DoCmd.RunSQL

 

[Pat Hartman]

I read your explaination and I still don't see the problem. The table would be exactly the same as the one you are trying to clone with the addition of a userID column. Of course you would need to change the form's recordsource query so that it only retrieved rows with the matching userID.

You can of course build the Make Table query in VBA and run it. However, if the users don't each have their own local copy of the front end db, changing the recordsource for a form affects all users of the database. I also think you may have a problem if you're working in A2K because it does not allow objects to be saved if the database is currently in use by multiple users. What if someone has the form open and you try to save it with a different recordsource?

 

[wrek]

Hi Pat,

I've restructured my queries to reflect what you've suggested - making one Master table, with an additional ID field. That was helpful. Thanks.

One more question though. Should I attach the subforms to the master table (the recordsource, I mean)...or should I make a query pulling out the relevant UserID?

 

[Pat Hartman]

I always use querydefs as the recordsource for forms and reports. And, in this case you want to filter by userId so I would do that in the query.