Inserting information with a macro

[Helen]

I've got yet another problem! Here's the situation:

We have many products. Products are built in many rooms,
sometimes two products will use some of the same rooms,
sometimes all the rooms are different. Each room has its
own specific set of standards, but there's also a possible
user-defined standard (herein lies the problem).

I've got a form where I ask them to choose a room from a
predefined list box that only shows those rooms where that
specific product is built (I can't figure how they can
choose multiple rooms, but that's a problem for a
different day).

Once you choose a room, you get a whole bunch of
standards, for example, floors, interior walls, exterior
walls, ceilings.

I selected productA, my room list is narrowed down to just
those rooms that productA is built in, so I selected
Room4, and then I get to my standards form.

I have a separate option box for each of:
floors, interior walls, exterior walls, ceilings, and each
of these option boxes has a button for either "standard"
or "user defined".

Let's say I choose the standard definition for floors.
Then I want my macro to select only the standard for that
room for that specific component -- floors, and copy that
specific standardID number into my "FloorStandard" field
of my ProjectID table. However, if I choose "User
Defined", then I want my macro to select the User Defined
Standard ID number and copy that into my table.

I built two queries 1. -- qFloor, which narrows down the
standardID to the specific standard that belongs in the
room, for floors, and 2. -- qFloorUD, which lists the
standardID for the User Defined standard for floors.

I tried to get my macro to read the answer from qFloors
and insert that field into my table with the Update SQL
statement, but that didn't work.

So then I mashed the Update SQL statement with the SQL
code from the qFloor query (in SQL view), but the code was
too long and I can't copy it into the macro because of the
256 character limitation (I even tried shortening some of
my table and field names within the tables, but to no
avail).

Can anyone out there help me figure out what to do? I
know there's an easy solution, but apparently my Access
knowledge is more limited than I thought...

Thanks in advance!!
Helen

 

[Steve Schapel]

Helen,

Apparently you are trying to use a RunSQL macro, am I correct? I
suggest it may be simpler to make an Update Query to put the value in
your FloorStandard field, and then use an OpenQuery action to run it.

If you want to post the SQL view of the query you have tried, and expand
upon the meaning of "that didn't work", I'll try and make sense of it.

In the meantime, just reading between the lines I would suspect that
your work is being made unnecessarily difficult by an incorrect table
design. It looks like you might be trying to use a separate field for
the different standards, sort of like what you see in spreadsheets and
stuff like that, where it should be a separate table linked to the
Project table.

 

[Helen]

You are a genious. And if I haven't mentioned this
before, you are my hero. Again. Or for the third time,
I'm not sure.
I re-visited my tables, and sure enough I was doing
exactly what you said I was. I changed a couple of things
around and added a new table with just standardDetails. I
have one little glitch, but I think if I give it some more
thought, I might be able to figure it out.