MS Access 2003 - Linking one form to multiple tables, and saving data on click

Question

So I have a form that I want the user to use to update multiple tables with. I do not want the form to be bound to the tables, as I would rather do this with VBA (I think, I don't know).

So here is a little table example

UserTable

UserID
First
Middle
Last
Division
Title

EquipTable

EquipID
AssignedLaptop
Model
Barcode

SoftTable

SoftID
SoftwareName
License#
Custom (running sum to calculate how many licenses are left....another question)

ExchangeTable

UserID
SoftID
EquipID

So that is how I set the tables up. All these text/cmb boxes exist on one table, and I would like to be able to save this data from this one form to all relative tables with a button click.

So can anyone help me with the following:

If I have this unbound form populate after a Create New button click, how then can I tell hidden text boxes (that ideally are to contain the " "ID numbers), to populate new ID numbers (auto-numbers), for each of the tables, in them, so that I can assign an UPDATE SQL statement to a SAVE button click, in the VB to save the data?

Could I set it up so that I just the "exchange table" (no idea why i named it that) populates the ID numbers for the other tables, instead of visa versa?

Am I going about this the wrong way

EDIT:

I was just trying to give you an example to see if what I was trying to do is possible with multiple tables (ultimately multiple Keys) on one form, and if so how does it differ from doing it with one form/table: I use the unbound approach alot because of the forms I have to build, and the need to constantly pas parameters from one form to the next in the VBA. I think it is faster to code it myself? So for this concept I always have a hidden text box on the form that usually has the IDnumber of the relative table. So to click save would just require a simple SQL = * WHERE Tbl_ID = frmID kinda idea.

All I was wondering were these questions?

When you run an INSERT, does the ID number need to be present in the STATEMENT or will access just assign the next relative one for you when you execute?

If not Considering the method above, how can i determine this number (ID I need to use)myself, with code?

Another question? How do you defeat the mouse wheel scroll through records function on a bound form?

Answer 1

I see what you are saying, wording was strange on this but I do see where you are goign with this.

This is what you have:

• You have an unbound form that is not linked to any table
• This unbound form that is in no way linked to any table will be designed by you by adding some text boxes and also combo boxes and buttons
• Within these textboxes you have some textboxes that are going to store the ID (The key value) of the row. So that when you do a save the ID number is set back to the text box
• Once the ID number is set to this hidden field you can then issue updates to all tables that need this ID number

Does that sound right? If so it is very simple. All you have to do is once you click the "Create Button" perform the insert, something to this effect:

 ' Command to Insert Records.
      Dim cmdInsert As New OleDbCommand()
      cmdInsert.CommandText = "INSERT INTO AutoIncrementTest (Description) VALUES (?)"
      cmdInsert.Connection = cnJetDB
      cmdInsert.Parameters.Add(New OleDbParameter("Description", OleDbType.VarChar, 40, "Description"))
      oleDa.InsertCommand = cmdInsert

Then you issue another command to grab the IDENTITY back, namely your ID:

  ' Create another command to get IDENTITY value.
  cmdGetIdentity = New OleDbCommand()
  cmdGetIdentity.CommandText = "SELECT @@IDENTITY"
  cmdGetIdentity.Connection = cnJetDB

You can always get an identity with SELECT @@IDENTITY

So in your code behind you set the textbox to the value of SELECT @@IDENTITY. I haven't done access in over 5 years but I remember doing something like this. Once you have this identity you place it in your hidden text box. This will help you perform your UPDATES that use this identity.

Here is a good place to start: http://support.microsoft.com/kb/815629 Here is another great article that may help you: http://support.microsoft.com/kb/815629