Results 1 to 4 of 4
  1. #1
    sesproul is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Posts
    50

    Form with Subform based on Linked Many-to-Many Relationship.

    Hello,



    This may get posted as a different Thread name once I can figure out exactly the specifics of my question.

    Here is the background. I have a product Table, and I have a Parts Table, (Both externally linked MySQL Tables) I have been using ACCESS as a front end to the database for basic data entry, but I am trying to get a little more out of the FORMS.

    I created this type of form based on this Website http://articles.techrepublic.com.com...1-5285168.html at a previous position to allow for a quick way to attach items in one table to another using an intermediate link table. I found it to be a useful tool.

    Here is the problem though. I now need to update the Link Table to include the QTY field of the Parts. i.e. 1 Car: 4 tires, but when I add this field, the subform does not allow for this functionality. I don't claim to understand exactly why but I believe it is because the original Design The Subform can determine the Main table FK, and based on the selection from the Combo can determine the FK of the dependent table, the Primary key of the Link table is autocomplete, but there is no way to slide the new QTY value into the mix.

    So I instead was thinking after the part gets added, to throw up a Form that prompts for qty. I was looking into the FKPartID_Change() event. But the question I have is

    how do i get the new PK of the newly entered record in the link table, and then what is the best method to update that record. (I do understand ADODB connections, though I am not familiar with capturing the Local connection - that I believe ACCESS has a property somewhere to capture)

    So any ideas?


    BTW, I have found this forum to be very helpful, and am actively reading the other threads, but it may take a few days to get an answer that way.


    Thanks

    Steve





    ( I have about 8+years as a VB6 programmer, so I understand basic VBA stuff, but I sometimes struggle with the Access API, and properties available to the

  2. #2
    sesproul is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Posts
    50
    I seem to have the update to work. Here's the routine that fires after the Item is added, and the field is exited

    Private Sub FKPartID_Exit(Cancel As Integer)
    Dim oconn As New ADODB.Connection
    Dim oRs As ADODB.Recordset
    Dim sInput As String

    Set oconn = CurrentProject.Connection
    sInput = InputBox("Enter Quantity", "Parts Quantity")

    If Not IsNumeric(sInput) Then
    MsgBox "not a number"
    ElseIf CLng(sInput) < 0 Then
    MsgBox "Not a Positive Number"
    Else
    Set oRs = oconn.Execute("Select max(ProdpartID) From ProdParts")
    oconn.Execute "Update ProdParts SET qty = " & CLng(sInput) & " Where ProdPartID =" & oRs(0)
    End If

    End Sub


    But It seems to fire too quickly, I really need to limit it to when the specific record is added., also if I am not just adding to the end, it only captures the Last record and updates that one.

    Any thoughts?


    Steve

  3. #3
    sesproul is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Posts
    50

    Form_AfterUpdate, Form_AfterInsert events

    The code has moved to these, but the problem How do I determine the record that was updated? For AfterINSERT, that's easy, but what about for the other records that are simply being updated? How can I retrieve the Key for them?


    Steve

  4. #4
    sesproul is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Posts
    50

    Found A Solution

    Hello,

    I did manage to make the Form Work. Here is a Screen Shot:

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Linked table Query based form
    By sesproul in forum Forms
    Replies: 1
    Last Post: 01-21-2010, 08:46 PM
  2. Replies: 0
    Last Post: 06-23-2009, 03:01 PM
  3. Replies: 6
    Last Post: 06-03-2009, 02:01 PM
  4. Open form based on Subform criteria
    By Suzan in forum Programming
    Replies: 0
    Last Post: 04-25-2006, 02:28 PM
  5. Replies: 0
    Last Post: 02-16-2006, 09:11 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums