Making data appear in several tables at once on input

[SpokaneTJ]

I have a database with 8 Tables, Table 1 has the Primary key of User Id, each of the other 7 tables have this same field. I have a form that I fill out that gives basic info about a user to include the user ID, is there a way to take the User ID info from table 1 and make it fill in the other 7 tables?

 

[Tranman]

Do you want to take the info from the table, or from the form?

You can build up a SQL command from the form data in whatever event is appropriate that will add rows to the other tables:

strSQL = "Insert into TB2 values('"
strSQL = strSQL & me.myField & "')"

Currentproject.Connection.Execute strSQL

Or you can insert data from one table into another:

strSQL = "Insert into myTableB Select ID, Name, DOB from myTableA"

Currentproject.Connection.Execute strSQL

Tranman

 

[TheAceMan1]

How are ya SpokaneTJ . . . . .

In the [blue]AfterUpdate Event[/blue] of [blue]UserID[/blue] on the [blue]Form1[/blue] (believing form1 updates table1), copy/paste the code below ([blue]you[/blue] substitute proper names in [purple]purple[/purple]).

[blue]Note: the code checks for and bypasses existing UserIDs.[/blue]

[blue]   Dim db As DAO.Database, rst As DAO.Recordset, tdf As DAO.TableDef
   
   Set db = CurrentDb()
   
   For Each tdf In db.TableDefs
      If (tdf.Name <> "[purple][b]Table1Name[/b][/purple]") And (Left(tdf.Name, 4) <> "MSys") Then
         Set rst = db.OpenRecordset(tdf.Name, dbOpenDynaset)
         
         rst.FindFirst "[[purple][b]UserID[/b][/purple]]=" & Me![purple][b]UserID[/b][/purple]
         
         If rst.NoMatch Then
            rst.AddNew
            rst![purple][b]UserID[/b][/purple] = Me![purple][b]UserID[/b][/purple]
            rst.Update
         End If
         
         Set rst = Nothing
      End If
   Next
   
   Set db = Nothing[/blue]

Note: if your UserID is not numeric:

[blue][purple]Change:[/purple]
rst.FindFirst "[[purple][b]UserID[/b][/purple]]=" & Me![purple][b]UserID[/b][/purple]
[purple]To:[/purple]
rst.FindFirst "[[purple][b]UserID[/b][/purple]]='" & Me![purple][b]UserID[/b][/purple] & "'"[/blue]

[blue]Thats it . . . give it a whirl & let us know . . .[/blue]

See Ya! . . . . . .

 

[SpokaneTJ]

TheAceMan1,

Thanks for the code, now I am going to ask what is probably a very stupid question, but, where do I put this code. I am actually using Access 2003 to input the data, not using a web page. I have never tried to put SQL code into actual access. Thanks for the help and the answer.

 

[TheAceMan1]

SpokaneTJ . . . . .

[blue]Did you read the beginning of my prior post?[/blue]

See Ya! . . . . . .