[Access 2003] Can't figure out how to insert records based on form parameters...

[elleg]

If someone can point me in the right direction, I would really appreciate it. I know there are probably already posts answering my question, so I can only assume that I can't figure out how to phrase my question to actually find those answers....

What I'm trying to do is insert new records into two tables based on user-provided values on a form. I don't really care how this is accomplished--creating a query or two, and calling those queries when a button on the form is pressed, or just writing a macro that performs the 2 necessary queries. (I'm pretty sure I need 2 queries, 1 for each table that needs new records inserted...).


Here are the basic details of what I'm trying to do:

I am creating a very simple, very small database that will eventually be migrated to an Oracle database. I am trying to avoid creating any actual relationships between tables (actual, meaning, defining the relationships within Access) so that my tables can be easily replicated in a new database later on. (And, maybe, that's the wrong way to go about this; I don't know).

However, even though I'm not relating the tables, the data definitely is related, and I do need to maintain the integrity of those relationships. So, what I'm trying to do is just ensure that my forms update everything that needs to be updated to maintain relationships.

Here's my example problem:

Tables:
Table 1: Groups
Group      Description
---------------------------------
A             Cells in location A 
B             Cells in location B
C             Cells in location C
---------------------------------

Table 2: Cells
Cell          Department
----------------------------
Drill         1000
Cut           1000
Assy          2000
Shape         3000
Turn          3000
Mill          4000
Grind         4050
----------------------------

Table 3: Departments
Department
--------------
1000
2000
3000
4000
4050
--------------

Table 4: Cell Groups
Group     Cell
-------------------
A            Shape
A            Turn
A            Cut
B            Drill
B            Mill
B            Grind
C            Assy
-------------------

I have a form to add a cell. The form has 3 data objects on it, a text box for the cell, a drop-down menu for the department, and a drop-down menu for the group. I populate the drop-down for department from the Departments table, and the group drop-down from the Group column of the Groups table. The user just needs to enter a name for the new cell and choose a group and a department for that cell. When they click a button (which I have on the form, but haven't tied it to anything), I want it to update both the Cells table (Cell, Department) and the Cell Groups table (Group, Cell).

My biggest problem is that I can't figure out how to do an update query using data that's in a form. I'm used to dealing with existing Oracle databases, using ADO objects and writing PL/SQL to query all my data in Excel....I'm finding getting familiar with Access to be a challenge.

[elleg]

Should I have put this question under Office Development?

[si_the_geek]

Thread moved to 'Office Development' forum at elleg's request