Create one form to add new records to multiple tables 1

[HeatherFeuer]

I am trying to create a form, with or without subforms to add new records to multiple tables in a database for work. I tried creating a select query with all the fields I would need and all the tables are linked. However, when I create the form, I can't get it to allow new records - even though I have the form properties for allow additions set to "yes" and data entry set to "yes."

I already have a form with nested subforms set up to view existing records with those properties set to "no." When I change them to "yes," I can add a new record in the main form but not the subforms. I am really frustrated right now. Can someone give me suggestions? Thanks!!!

 

[rbowes]

This may help you, make the form unbound and when the user presses the save button just run a series of "Insert Into" queries. Make sure that you start with the table with greatest priority.

With a database application that I am developing, I log donors and donations. I save "Family" and "Individual" information separately. If it is a new "Family" and "Individual", I insert the record for the family first and then individuals are saved with a FamilyID (both the FamilyID and IndividualID are AutoNumber Fields). It looks like this:

Private cmdSave_Click()
Dim rs as adodb.recordset
dim con as adodb.Connection
dim strSQL as string
dim vID as variant

Set con = CurrentProject.Connection
strSql = "INSERT INTO tblFamily ( FamilyName)" & _
" SELECT '" & Me.txtFamilyName & "'
con.Execute strSql
'Now to retrieve the ID of the Family Record that I just Created
strSql = "Select @@Identity"
With rst

.ActiveConnection = con
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.Source = strSql
.Open
Vid = .Fields(0).Value
End With

Strsql = "INSERt INTO tblInividual (FamID, FName, LName)"
" SELECT " & VID & ",'" & txtFirstName & "','" & txtLastName & "'"

Con.Execute strSQL
(This is not a full example, but illustrates the point)

...........


And continue on from there. I know a lot of people say this is what subforms are for, but doing it this way gives you complete control of how records are added. By the way, @@Identity returns the value of the last ID (autonumber) field created and works for SQL Server and Jet 4.0 (Access 2000 or higher)

 

[planetdrouin]

RBowes,

I am trying to do something similar to your post. I must admit that I am new to Access and have yet to master the more complex aspects. What I am trying to do is use one form to alter two tables. The form is a time keeper and has the following six fields: LogID(autonumber), Date, EmployeeID, ClientID, TimeLogged and Notes. What I would like it to do, is save those six fields into one table (TblTimeDetail) as a new record. At the same time, I would like it append another table (TblTimeSum) by adding the TimeLogged by EmployeeID and ClientID. This table will also keep track of the amount of time Billed and Notcharged by EmployeeID and CLientID. Any help you or anyone else can provide is greatly appreciated.

Lawrence

 

[young]

The easy way to do it is create two(or more) append query and use docmd.openquery to run those query after form close or control.after update.

 

[HeatherFeuer]

Rbowes,

I'm afraid my programming knowledge isn't sufficient to completely understand what you've done. <sigh> As a result, I've tried to limit the amount of actual code needed in my database.

I have been working with my form and I've figured out a solution that I hope will also help planetdrouin. I created a select query that contains all the fields from the various tables that I want to add records to. In my business table I have a streetID field and a zipID field that I am filling by using a combo box. The important part is that I selected the form properties and under the data tab, set the dynaset property to &quot;dynaset (inconsistent updates).&quot; NOW I can add new records. I don't know if this is the same in Access 2000, I'm building my database in Access 97.

I've learned more stuff building this database!!

Heather