Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Posts
    6

    Unanswered: Synching 2 subforms

    Hi,

    I'm only a beginner with Access and VBA etc so apologies up front.

    I have a form, with a tab control. Each page in the tab thing relates to a different table which seems to make things a bit more complicated, as I will explain.

    On one of the tab pages, I have a subform, which has is a continuous form displaying the CompanyName field from the Companies table. This has a search script in the header so you can filter and search for various companies.

    What I did last time was set this up so that the parent form contains all of the details field (phone, address, email etc) for the companies, and the subform is driving the selection of the record in the parent form.

    But because this is all within a tabbed page, I need to place the main details within another subform within the main form.

    So what I need to do is to get the details subform to link/synch to the selection from the list/search subform.

    I thought I could use the:

    FormName = "frmCOMPANYDETAILSSUB"

    Set F = Forms(FormName)
    Set rs = F.Recordset.Clone

    If InStr(1, Me.contactName, "'") > 0 Then
    SyncCriteria = "[ContactName] =" & Chr(34) & Me!contactName & Chr(34)
    Else
    SyncCriteria = "[ContactName] ='" & Me!contactName & "'"
    End If

    kind of script that I found on the web which sits on the "onCurrent" Event of the list/search subform. Unfortunately, I have no idea how to point the script to the other subform - the details subform (which is Child6 within the parent form, and named frmCOMPANYDETAILSSUB).

    Can anyone give me any clues as to how I should go about this? It would be appreciated.

    Thanks.

  2. #2
    Join Date
    Nov 2002
    Location
    Hamilton,Ontario
    Posts
    132

    Re: Synching 2 subforms

    [QUOTE][SIZE=1]Originally posted by EdwardWWW
    the details subform (which is Child6 within the parent form, and named frmCOMPANYDETAILSSUB).

    'declare this variable
    Dim formname as Form

    'set the formname value
    'Use this in ur Query or Result
    set formname=Forms![Parent Form]![frmCOMPANYDETAILSSUB].Form

    'If you need to use another subform then
    'delcare another subform variable
    Dim formname2 as Form
    set formname2= Forms![Parent Form]![frmChild2].Form
    'now use this formname2 in ur programming


    HOpe this Helps if thats ur question.

  3. #3
    Join Date
    May 2003
    Posts
    6

    Subform problem thing

    Hi,

    thanks for your reply - I tried that but it chucks up "Microsoft Access can't find the field 'frmCOMPANYDETSUB' referred to in your expression.

    frmCOMPANYDETSUB is actually the name of the subform I want to control from the first subform - I typed it in twice to make sure it wasn't a typo.

    This is what I've got so far...
    ___________________________________________

    If IsNull(Me.CompanyName) Then
    Exit Sub
    End If

    Dim formname As Form, SyncCriteria As String
    Dim F As Form, rs As Object
    Set formname = Forms![frmBABYMAININTERFACE]![frmCOMPANYDETSUB].Form

    'the bit above is the bit it keeps highlighting as the error

    Set F = Forms(formname)
    Set rs = F.Recordset.Clone

    If InStr(1, Me.CompanyName, "'") > 0 Then
    SyncCriteria = "[CompanyName] =" & Chr(34) & Me!CompanyName & Chr(34)
    Else
    SyncCriteria = "[CompanyName] ='" & Me!CompanyName & "'"
    End If

    rs.FindFirst SyncCriteria

    If rs.EOF Then
    MsgBox "No match exists!", 64, formname
    Else
    F.Bookmark = rs.Bookmark
    End If
    _____________________________________________

    I haven't looked at the actual synching bit yet, coz I haven't yet figured out how to get the 2 to reference each other.

    Any other ideas? Your help is appreciated.

    Thanks.

  4. #4
    Join Date
    Nov 2002
    Location
    Hamilton,Ontario
    Posts
    132

    HOpe this Helps

    this is the code I have behind my subform,which works perfectly fine

    Private Sub Category_AfterUpdate()
    If ErrorTrapping = True Then
    On Error GoTo Err_Handler
    End If
    Dim curdb As Database
    Dim Form As Form, formname As Form
    Dim Category As String, RecipeItemNo As String
    Dim QtyReqd As Long
    Set curdb = CurrentDb()
    Set Form = Forms![yworkorder2].Form
    Set formname = Forms![yworkorder2]![zworkOrderLineItem].Form
    If Not IsNull(Me![Category]) Then
    Category = Me![Category]
    SetBarcodeCategoryWorkOrder Me, Category, curdb
    If Category = "RIPPED" Then
    If Me![Item Transfer].Visible = True Then
    Me![Item Transfer].Visible = False
    End If
    Me![Unit] = "LF"
    Call GetQuantityToProduceFirstRow(Form, formname, curdb)
    RecipeItemNo = Form![Item Number]
    Call GetRecipe(RecipeItemNo, curdb)
    Else
    If Category = "PLANNED" Then
    If Me![Item Transfer].Visible = False Then
    Me![Item Transfer].Visible = True
    End If
    Me![Unit] = "BF"
    End If
    End If
    End If
    curdb.Close
    Set curdb = Nothing
    Exit Sub
    Err_Handler:
    DispError "Fetching Planned Items", "zworkOrderLineItem"
    Exit Sub
    End Sub


    __________________________________________________ _________________________________
    This is ur code that I have modified...

    On error Goto procecdurename_Err_Handler

    If IsNull(Me.CompanyName) Then
    Exit Sub
    End If

    Dim Mainform as Form,formname1 As Form,Dim formname2 As Form
    Dim SyncCriteria As String
    Dim curdb as database'( make sure to add DAO3.6 in References if u get error message)
    Dim rs As Object
    'this will detect the current database
    set curdb=currentdb()
    'urmainform goes here
    Set Mainform=forms![MainForm].Form
    first subform goes here
    Set formname1 = Forms![MainForm]![firstSubform].Form
    second subform goes here
    Set formname2=Forms![mainForm]![secondsubform].Form


    'the bit above is the bit it keeps highlighting as the error

    Set rs = puturfornamehere.Recordset.Clone

    If InStr(1, Me.CompanyName, "'") > 0 Then
    SyncCriteria = "[CompanyName] =" & Chr(34) & Me!CompanyName & Chr(34)
    Else
    SyncCriteria = "[CompanyName] ='" & Me!CompanyName & "'"
    End If

    rs.FindFirst SyncCriteria

    If rs.EOF Then
    MsgBox "No match exists!", 64, formname
    Else
    F.Bookmark = rs.Bookmark
    End If
    rs.close
    set rs=Nothing
    curdb.close
    set curdb=Nothing
    Exit sub
    procedurename_Err_Handler:
    msgbox Err.number & " " & Err.Description
    err.clear
    exit sub
    end sub



    Hope this helps.

  5. #5
    Join Date
    Nov 2002
    Location
    Hamilton,Ontario
    Posts
    132

    Re: Synching 2 subforms

    [QUOTE][SIZE=1]Originally posted by EdwardWWW

    I am not sure which form ur connecting with here.

    If InStr(1, Me.CompanyName, "'") > 0 Then
    SyncCriteria = "[CompanyName] =" & Chr(34) & Me!CompanyName & Chr(34)
    Else
    SyncCriteria = "[CompanyName] ='" & Me!CompanyName & "'"
    End If


    lets say i wanna access one subfrom from another so i would go
    'accessing the first from
    'thats where you are at right now
    If InStr(1, formname1![CompanyName], "'") > 0 Then
    SyncCriteria = "[CompanyName] =" & Chr(34) & formname1![CompanyName] & Chr(34)
    Else
    'accesssing the secondsubfrom from teh first subform
    SyncCriteria = "[CompanyName] ='" & formname2![CompanyName] & "'"
    End If



    Hope this helps
    if u still having problems
    email me the project, will try to fix it for you

Posting Permissions

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