Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2002
    Location
    VA
    Posts
    24

    Unanswered: Please Help!!! Opening a subform within a form from another form

    Hi everyone,

    I'm am stuck on opening a form with a subform. It's kind of hard to explain.

    I have a form with lots of text boxes that is used to filter to many other forms. I didn't want the user to use filter by form because the users didn't want to type the same type of information that they have for one form to another form. So I created a form with text boxes to retrieve what user type in there. I'm able to retrieve all the values they type in but I'm stuck on when the user wants to retrieve a particular record from a subform of the form. the main form contains only 3 items, the division, object class and fiscal year. Those 3 criteria are linked to retrieve the subform. but if i am on the entry form typing in the transaction number to retrieve from the subform but, I'm not able to retrieve that record.

    stdocname = "OPSALLOCATIONPLAN"

    If Not IsNull(Me!fisYear.Value) Then
    intFy = Me!fisYear.Value
    Else
    MsgBox "You can not leave fiscal year blank, please enter value for fiscal year"
    Exit Sub
    End If

    If Not IsNull(Me!Division.Value) Then
    stDivision = Me!Division.Value
    Else
    MsgBox "You can not leave Division blank, please enter value for division"
    Exit Sub
    End If

    If Not IsNull(Me!Obj.Value) Then
    stobj = Me!Obj.Value
    stobjops = " and [Obj_name] = " & "'" & stobj & "'"
    Else
    stobj = ""
    End If

    If Not IsNull(Me!TransNub.Value) Then
    stTransNub = Me!TransNub.Value
    stnulltransnub = " AND [forms]![OpsAllocationPlan]![OPS].[form]![OPS_TRANS_NUM] = " + "'" & stTransNub & "'"
    Else
    'when trans# is blank, don't retrieve
    stnulltransnub = ""
    End If

    stLinkCriteria = "[DIVISION_CODE]= " + "'" + Me![Division] + "'" + " and [ops_fy]=" + Me!fisYear & stobjops & stnulltransnub

    DoCmd.OpenForm stdocname, , , stLinkCriteria

  2. #2
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    i'm not sure if this is what your after but i did something simalar with this

    in a form called grade i did:-
    Code:
    Form_AddAccident.SetFocus
    Call Form_AddAccident.doSave
    Message = "SELECT '" & Form_AddAccident.Date.Value & "' as 
    AccidentDate, '" & Form_AddAccident.Time.Value & "' asAccitentTime,'" 
    & Form_Employees.empName.Value & "' as empName, '" & 
    Form_Employees.DepartmentName.Value & "' as empDept, " &
    (Year(Date - Form_Employees.Birthdate.Value) - 1900) & " AS 
    empAge, '" & Form_Employees.Address & "' As empAddress INTO 
    Investigate ;"
    DoCmd.RunSQL (Message)
    
    'DoCmd.SendObject , "", "", "JOE YARDLEY", "", "", "Accident Needs 
    Investigating", "Reported on " & 
    Form_AddAccident.date_Reported.Value & 
    Form_AddAccident.time_Reported.Value, True, ""
    
    Me.link.HyperlinkAddress = "./Investigation.doc"
    Me.link.Hyperlink.Follow
    DoCmd.Close acForm, "AddAccident", acSave
    so in grade i changed the focus to the other form and grabed it's subform data
    Last edited by m.timoney; 11-04-02 at 11:43.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  3. #3
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    or else if i've got i the wrong way round then try


    Code:
    Private Sub data_update()
        Form_AddAccident.Emp_Num = me.Emp_Num
        Form_AddAccident.SetFocus
        DoCmd.Requery ("employees")
        Form_Grade.SetFocus
    End Sub
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  4. #4
    Join Date
    Nov 2002
    Location
    VA
    Posts
    24
    thanks for the help, but that wasn't it.

    lets say, u have form A and it contains 10 text boxes. you want Form A button to open form B. Form B contains Form C. Form C has 10 fields and Form B contains only field1, field2, field3. Form C is link to Form B through field1, field2 and field 3.

    user field in the empty text boxes on textbox1, textbox2, textbox3, textbox4 and that's it to be able to view form B with Form C as subform
    textbox1 link to field1 of Form B
    textbox2 " " field2 of Form B
    textbox3 " " field3 of Form B
    textbox4 " " field4 of Form C

    when u docmd.openform "form B",,,

    when entering the criteria for opening form B
    what was enter in the text boxes only compared textbox1-3 instead of also filtering what contains in texbox4 to retrieve only that information in that subform within the form.

    I hope you guys understand.

    Thanks for trying

  5. #5
    Join Date
    Nov 2002
    Location
    VA
    Posts
    24
    thanks for the help, but that wasn't it.

    lets say, u have form A and it contains 10 text boxes. you want Form A button to open form B. Form B contains Form C. Form C has 10 fields and Form B contains only field1, field2, field3. Form C is link to Form B through field1, field2 and field 3.

    user field in the empty text boxes on textbox1, textbox2, textbox3, textbox4 and that's it to be able to view form B with Form C as subform
    textbox1 link to field1 of Form B
    textbox2 " " field2 of Form B
    textbox3 " " field3 of Form B
    textbox4 " " field4 of Form C

    when u docmd.openform "form B",,,

    when entering the criteria for opening form B
    what was enter in the text boxes only compared textbox1-3 instead of also filtering what contains in texbox4 to retrieve only that information in that subform within the form.

    I hope you guys understand.

    Thanks for trying

Posting Permissions

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