Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    17

    Unanswered: Problems adding records to a form linked to 6 tables.

    I've created a form with 46 fields connecting to six tables which serves as an input form for a credit card database and i'm having trouble adding new records to the database. Say i just want to type the bare minimum of information about one credit card such as the name and add the record to the database, when i close the form and open it again, the record disappears.

    I know this is because the primary key linking the 6 tables is not being inserted into all 6. When i go through the 6 tables physically and insert say Primary_ID = 80(same as the record i added) and reopen the form, the record now appears. I have referential integrity and cascade on updates active but the form only inserts the primary key into the tables which have information entered into them. This has been annoying me for quite some time, any help is appreciated.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    You say the form is linked to 6 tables? Do you mean that the form is bound to a table/query that is based on 6 tables? How are you updating the records of these tables? Are you allowing the form to do it? Do you have any code that writes out to the other tables?

  3. #3
    Join Date
    Sep 2003
    Posts
    17
    Originally posted by M Owen
    You say the form is linked to 6 tables? Do you mean that the form is bound to a table/query that is based on 6 tables? How are you updating the records of these tables? Are you allowing the form to do it? Do you have any code that writes out to the other tables?
    In form.recordsource i have 6 tables and all of the fields that the controls are using. The controls themselves have their controsource set to the field that it reflects. There is no code writing out to the tables.

    Ps thanks for a speedy reply

  4. #4
    Join Date
    Sep 2003
    Posts
    17
    I'm attempting to write some visual basic code which will check all of the tables linked to the main table Program_Info for the key, if it does not exist to add it to the table. Here is what i have now. Keep in mind i'm merely intermediate at VB and cannibalized the VB example.

    Private Sub Form_AfterInsert()
    Dim Conn As ADODB.Connection
    Dim Table As ADODB.Recordset
    Dim Program_ID As String
    Dim strConn As String
    Dim Lookup As Integer

    ProgramID.SetFocus
    Program_ID = ProgramID.Text

    Set Conn = New ADODB.Connection
    Rem Conn.Open "Provider=sqloledb;"
    strConn = "Provider=sqloledb;" & "Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=;"
    If (DLookup("Program_ID", "Product_Info", "Program_ID=" & Program_ID) Is Null) Then
    Table.Open "Product_Info", cnn1, , , adCmdTable
    Table.AddNew
    Table!Program_ID = ProgramID
    Table.Update
    Table.Close
    End If
    If (DLookup("Program_ID", "Program_Info_products", "Program_ID=" & Program_ID) Is Null) Then
    Table.Open "Program_Info_products", cnn1, , , adCmdTable
    Table.AddNew
    Table!Program_ID = ProgramID
    Table.Update
    Table.Close
    End If
    If (DLookup("Program_ID", "Program_Info_Volume", "Program_ID=" & Program_ID) Is Null) Then
    Table.Open "Program_Info_Volume", cnn1, , , adCmdTable
    Table.AddNew
    Table!Program_ID = ProgramID
    Table.Update
    Table.Close
    End If
    If (DLookup("Program_ID", "Program_Info_marketing", "Program_ID=" & Program_ID) Is Null) Then
    Table.Open "Program_Info_Marketing", cnn1, , , adCmdTable
    Table.AddNew
    Table!Program_ID = ProgramID
    Table.Update
    Table.Close
    End If
    If (DLookup("Program_ID", "Rewards_Info_communication", "Program_ID=" & Program_ID) Is Null) Then
    Table.Open "Rewards_Info_Communication", cnn1, , , adCmdTable
    Table.AddNew
    Table!Program_ID = ProgramID
    Table.Update
    Table.Close
    End If
    If (DLookup("Program_ID", "Rewards_Info_Program_Rules", "Program_ID=" & Program_ID) Is Null) Then
    Table.Open "Rewards_Info_Program_Rules", cnn1, , , adCmdTable
    Table.AddNew
    Table!Program_ID = ProgramID
    Table.Update
    Table.Close
    End If
    If (DLookup("Program_ID", "Rewards_Info_Redemption", "Program_ID=" & Program_ID) Is Null) Then
    Table.Open "Rewards_Info_Redemption", cnn1, , , adCmdTable
    Table.AddNew
    Table!Program_ID = ProgramID
    Table.Update
    Table.Close
    End If

    Table.Open "Rewards_Info_Redemption", cnn1, , , adCmdTable
    Table.AddNew
    Table!Program_ID = Program_ID
    Table!Description = "Placeholder! Do not delete!"
    Table.Update

    Table.Close
    Conn.Close

    End Sub

    I cant test my coding yet because i cant figure out the best place to put it in the form. If i put it in form_afterinsert i just get the error "You cannot change a record because a related record is required in table 'Program_Info_Products'. I need this code to be run right before the record is inserted but not before the program_id is generated.

Posting Permissions

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