Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2007
    Posts
    148

    Unhappy Unanswered: put primary key value to the foreign key in another table trought a form

    Hope someone can help me with the following problem.

    I have 2 tables and they are related by the ChangeControlFormDetailsID key.

    ChangeControlFormDetailsID is the primary key for table tblChangeControlFormDetails.
    ChangeControlFormDetailsID is the foregin key for table tblInfrastructureRes.

    tblInfrastructureRes has 3 fields: InfrastructureResID, ChangeControlFormDetailsID and InfrastructurePersonnelAndDepartment. NOTE: I have multiple record for ONE ChangeControlFormDetailsID. I have created a form frmInfrastructureRes which allow multiple selection in one action and updated the table tblInfrastructureRes provided that I hardcode the ChangecontrolFormDetailsID in the VB code.

    I have created the relationship between these 2 tables: tblChangeControlFormDetails and tblInfrastructureRes.

    I created a form (frmChangeControlFormDetails) for the table tblChangeControlFormDetails.

    I created a form (frmInfrastructureRes) for the table tblInfrastructureRes.

    In the frmChangeControlFormDetails form, if I add a subform (using the subform tools) and choose to create the subform using the table tblInfrastructureRes, I can see the ChangeControlFormDetailsID being created in the table tblInfrastructureRes properly. But, I can only add one new record to the table tblInfrastructureRes at a time.

    However, in the frmChangeControlFormDetails form, if I add a subform (using the form frmInfrastructureRes) in hope that the ChangeControlFromDetailsID will carry into the table tblInfrastructureRes. I could not get it working. Here is the code I use in my from frmIntrastructureRes. Please note If I set
    rst!ChangeControlFormDetailsID = 466 (466 is a radom ChangeControlFormDetails number I pick from the table tblChangeControlFromDetails), the table tblInfrastructureRes will get update properly except the ChangeControlFromDetailsID of course.


    In short, I hope someone can help me to put down the right code for this one line rst!ChangeControlFormDetailsID = ??????

    ************************************************** *


    Private Sub Command13_Click()
    Dim rst As Recordset
    Dim varItem As Variant

    '--- open the tblInfrastructureRes table
    Set rst = CurrentDb.OpenRecordset("tblInfrastructureRes")

    '--- loop through all selected empolyees in the list box
    ' adding empolyee name to the InfrastructureRes table

    For Each varItem In lstChosen.ItemsSelected
    rst.AddNew
    rst!InfrastructurePersonnelAndDepartment = lstChosen.ItemData(varItem)
    ******************************************
    rst!ChangeControlFormDetailsID = (Here is the problem)
    ****************************************
    rst.Update
    Next varItem

    '--- close the table
    rst.Close
    Set rst = Nothing


    End Sub

    *********************************************

    Please let me know if there are any more information I need to give to you all.

    Thanks
    Last edited by sweetmail; 12-12-07 at 17:16.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Isn't this automatically handled by Link Master Fields and Link Child Fields?

    Anyway, I think what you might be after is

    Code:
    rst!ChangeControlFormDetailsID = forms(me.parent.name).ChangeControlFormDetailsID
    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
    Sep 2007
    Posts
    148
    Thanks, I will try it today. Thanks

  4. #4
    Join Date
    Sep 2007
    Posts
    148
    I said

    rst!ChangeControlFormDetailsID = Forms(Me.tblChangeControlFormDetails.ChangeControl FormDetailsID).ChangeControlFormDetailsID

    I got an error message saying Compile error: Method or data member not found. It highlighted the Me.tblChangeControlFormDetails.

    Any assistance is greatly appreciated.
    Thanks

  5. #5
    Join Date
    Sep 2007
    Posts
    148
    rst!ChangeControlFormDetailsID = Forms!frmChangeOfControlForm!ChangeControlFormDeta ilsID


    This about VBA works. Ha Ha Ha. Thank you StarTrekker for pointing me to the right direction.

    Now, onto the next problem. Ha ha ha

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're welcome
    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

  7. #7
    Join Date
    Sep 2007
    Posts
    148
    I have a problem here which I have thought about it for a couple of days already with no success. Hope someone can help me.

    My two tables: tblChangeControlFormDetails and tblInfrastructureRes are related by be ChangeControlFormDetailsID field. This ChangeControlFormDetailsID is the primary key for the table tblChangeControlFormDetails and is an autonumber. I use the form frmChangeOfControlForm to update the table tblChangeControlFormDetails and I use the form frmInfrastructureResB to update the table tblInfrastructureRes. The form frmInfrastructureResB is called up from within the form frmChangeOfControlForm.

    I have no problem updating the tblInfrastructureRes table when the record is already exist in the tblChangeControlFormDetails table. That is the primary key (ChangeControlFormDetailsID) is already exist in the table tblChangeControlFormDetails. However, if it is a new record, that is the primary key for the table tblChangeControlFormDetails is new, I cannot update the tblInfrastructureRes table with this newly generated primary key *(ChangeControlFromDetailsID) as Access think that this record still doesn't exist in the table tblChangeControlFromDetails yet and refused to update the tblInfrastructureRes table with any new record associated with this new ChangeControlFromDetailsID. ( the code failed at the rst.Update statement) That is, if I would saved this record to the tblChangeControlFormDetails table first and then open the form again and update the tblInfrastructureRes table, I will have no problem. I tried to use a temporary table to hold the values for the tblInfrastructureRes first but it seems that the existing relationship between tblChangeControlFromDetails and tblInfrastructureRes resist any updated on tblInfrastructureRes unless the record is saved in the table tblChangeControlfromDetails.

    This is the error message " You cannot add or change a record because a related record is required in table "tblChangeControlFormDetails'.


    ************************************************** ***

    Private Sub Command16_Click()


    Dim rst As Recordset
    Dim varItem As Variant


    '--- open the tblInfrastructureRes table
    Set rst = CurrentDb.OpenRecordset("tblInfrastructureRes")

    '--- loop through all selected employees in the list box
    ' adding their employee number to the temp table

    For Each varItem In lstChosen.ItemsSelected
    rst.AddNew
    rst!ChangeControlFormDetailsID = Forms!frmChangeOfControlForm!ChangeControlFormDeta ilsID
    rst!CobbCountyContactID = lstChosen.ItemData(varItem)
    rst.Update
    Next varItem


    '--- close the table
    rst.Close
    Set rst = Nothing


    End Sub

    ************************************************** **
    Last edited by sweetmail; 12-18-07 at 14:12.

  8. #8
    Join Date
    Sep 2007
    Posts
    148
    I think I find a solution. I use Form close to update my tblInfrastructureRes table. I need help with VBA code. How to keep the recordset connect. Here is my code

    ************************************************** *

    Private Sub Form_Close()


    Dim db As Database
    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT * FROM _tmptblInfrastructureRes WHERE( 'ChangeControlFormDetailsID' And 'CobbCountyContactID')")
    Set rs2 = db.OpenRecordset("tblInfrastructureRes")

    Do

    With rs2
    .AddNew
    .Fields("ChangeControlFormDetailsID") = rs!ChangeControlFormDetailsID
    .Fields("CobbCountyContactID") = rs!CobbCountyContactID
    .Update
    .Close
    End With

    Loop While rs.EOF = False
    rs.Close
    End Sub

    ***********************
    I complain that .AddNew "Object invalid or no longer set. " Any help is greatly appreciated it.

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Get rid of the .Close

    Put a rs2.Close after your rs.Close
    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

  10. #10
    Join Date
    Sep 2007
    Posts
    148
    thank you . Will try that

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Let me know how ya go
    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

  12. #12
    Join Date
    Sep 2007
    Posts
    148
    It works perfectly. Thank you so much. Ha Ha ha. Oh! I also add the move forward command to it. Maybe I post my code up tomorrow when I go back into the office. Thanks

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Good news, happy to be of help
    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

  14. #14
    Join Date
    Sep 2007
    Posts
    148
    Here is the code:

    Thank you so much StarTrekker -- Star Trek: The Next Generation is one of my favorite TV series.

    **************************************************

    Private Sub Form_Close()

    'Declare the variables

    Dim db As Database
    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset

    ' Set rs to hold the records in the temparory table _tmptblInfrastructureRes --
    ' ChangeControlfromDetailsID and CobbCountyContactID fields
    ' Set rs2 to manage the records in table tblInfrastructureRes

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT * FROM _tmptblInfrastructureRes WHERE( 'ChangeControlFormDetailsID' And 'CobbCountyContactID')")
    Set rs2 = db.OpenRecordset("tblInfrastructureRes")

    'Copy all the records from the temparory table _tmptblInfrastructureRes onto table tblInfrastructureRes

    Do

    With rs2
    .AddNew
    .Fields("ChangeControlFormDetailsID") = rs!ChangeControlFormDetailsID
    .Fields("CobbCountyContactID") = rs!CobbCountyContactID
    .Update

    End With
    rs.MoveNext
    Loop While rs.EOF = False
    rs.Close
    rs2.Close

    ' release the memory back to the system

    Set rs = Nothing
    Set rs2 = Nothing

    End Sub

  15. #15
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Star Trek: The Next Generation is still one of my favorite TV shows too... beats all the "reality" TV rubbish by a long shot imo

    Thanks for posting the code
    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

Posting Permissions

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