Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2009

    Post Unanswered: use one form field to enter into multiple tables

    Hi all, i am a newbie to access and need some help.

    I am trying to enter an unique number into one field in a form and have that same number inserted into multiple tables.
    Each table contains different data relating to the unique number i will use, and therefore will be using that number to link the tables to pull reports and eneter data in other forms.

    Any suggestions?

  2. #2
    Join Date
    Nov 2007
    Adelaide, South Australia
    Use Relationships and you won't need to do it.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Oct 2009


    Thanks for the reply, i did get around the problem using VBA. I am new at VBA so the code may not look that pretty but it works.

    Example of my code.

    Private Sub Submit_Click()

    Dim Num As String
    Dim LastName As String
    Dim FirstName As String
    Dim DOB As Date
    Dim Sex As String

    Dim NumCheck As Integer
    Dim SQLStr As String
    Dim db As ADODB.Connection
    Dim rs As ADODB.Recordset

    Dim SQLInserttbl1 As String
    Dim SQLInserttbl2 As String
    Dim SQLInserttbl3 As String

    ' Check that no fields are left empty
    If IsNull(Me.Num.Value) Then

    MsgBox "Please enter the number"
    Exit Sub

    ElseIf IsNull(Me.LastName.Value) Then
    MsgBox "Please enter the Surname"
    Exit Sub

    ElseIf IsNull(Me.FirstName.Value) Then
    MsgBox "Please enter the First Name"
    Exit Sub

    ElseIf IsNull(Me.DOB.Value) Then
    MsgBox "Please enter the Date of Birth"
    Exit Sub

    ElseIf IsNull(Me.Sex.Value) Then
    MsgBox "Please enter the Gender"
    Exit Sub

    End If

    'Set the variables with the field values
    Num = Me.Num.Value
    LastName = Me.LastName.Value
    FirstName = Me.FirstName.Value
    DOB = Me.DOB.Value
    Sex = Me.Sex.Value

    ' Check if the current number already exists in table 1(if it exists in tbl1 then it should exist in the other tables too) and advise user
    Set db = CurrentProject.Connection
    Set rs = New ADODB.Recordset

    SQLStr = "select [id] from [tbl1] where ([num] = '" & Num & "')"

    With rs
    .Source = SQLStr
    .ActiveConnection = db
    End With

    'Check if record exists - produce error and exit sub
    If Not rs.EOF Then

    MsgBox "The number is already loaded!"
    Exit Sub

    'Insert the data into the different tables
    SQLInserttbl1= "INSERT INTO [tbl1] ([Num])VALUES('" & Num & "')"
    SQLInserttbl2 = "INSERT INTO [tbl2] ([Num],[LastName],[FirstName],[DOB],[Sex])VALUES('" & Num & "','" & LastName & "', '" & FirstName & "', '" & DOB & "', '" & Sex & "')"
    SQLInserttbl3= "INSERT INTO [tbl3] ([Num])VALUES('" & Num & "')"

    CurrentDb().Execute (SQLInserttbl1)
    CurrentDb().Execute (SQLInserttbl2)
    CurrentDb().Execute (SQLInserttbl3)

    MsgBox "New record has been loaded!"

    End If


    Set rs = Nothing

    end sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts