John Nurick [Microsoft Access MVP]-Looping through linked tables 3rd request

[C. Pete Straman via AccessMonster.com]

John:

The reason my code made a tmp table and appended too many records was
because I was still calling out an individual table name in the "From"
portion of the SQL statement rather than the tabledef.name. It now works OK.
I want to now set a variable when I initialize my form or do my first
command on the form.
It should work like this:
1) I go out and read the data base name which will always include the
facility number IE-060002 Kansas, 060003 New York, etc.
2) I concatenate that name and use only facility number to make my new
table and append data IE-060002, 060003, etc.
3) This variable table is used all thru my program/sub routines.

Can you offer some help on coding this into my form?

The facility number is also part of the original table I start with. I
could search, find, and concatenate it to make my variable as well.

Currently I hard code the facility id in all locations of my code. I use
find/replace when I transfer the form from one mdb to another.

Thanks in advance.

C. Pete Straman

 

[Douglas J. Steele]

Pete: You're carrying this discussion on in multiple threads. Please confine
yourself to a single thread in fairness to all who are replying.

 

[John Nurick]

I don't really understand what you're trying to do, Pete, but with luck
the following will help.

1) You can use the code at http://www.mvps.org/access/api/api0001.htm to
invoke the standard File Open dialog to locate an mdb file and return
its filespec (its path and name). If you've got this you can parse out
the facility number, e.g. with
strFacNum = Mid(strFileSpec, 4,6)

2) Once you've got the facility number in a variable you can concatenate
that into your SQL statement wherever it's needed , e.g. instead of

strSQL = "INSERT INTO 060004" _
...
& " WHERE facilityid=60004;"
use
strSQL = "INSERT INTO " & strFacNum _
...
& " WHERE facilityid=" & strFacNum & ";"

 

[C. Pete Straman via AccessMonster.com]

I apologize for any inconvenience. I was not sure what subject matter my
question fell under. I assume you are telling me this because if I post
once under any subject the same people have access.
If not I do not know what you mean by the thread comment. Regardless one
posting it is.

Pete.Straman