Results 1 to 12 of 12

Thread: Subform?

  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subform?

    OK...I should know this, but my memory is failing me.

    I have an opt btn on my subform, sfrmMain, in the OnClick Event I call a function from a module. A portion of the code is below:

    Dim strProAdd As String, strNewProAdd As String

    strProAdd = Nz(Forms!sfrmMain!ProAdd)
    strNewProAdd = IIf(strProAdd = -1, "yes", "no")

    Set DB = CurrentDb()
    Set rsLog = DB.OpenRecordset("tblRepFollowUp")

    With rsLog

    .AddNew
    .Fields("ProName") = Nz(Forms!sfrmMain!PVDName, "x")
    .Fields("ProEIN") = Nz(Forms!frmMain!cboEIN.Column(1), "x")

    The problem is, I continue to get an error msg saying "Access can't find the form "sfrmMain" ....odd...because that is the name of the subform.

    What am I doing wrong?

    I think it should be something like Forms!Forms!sfrmMain!PPA.....but I'm not sure.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform?

    Hi Roberta,

    From which form is the code snippet running? If it's from the subform can you use Me instead? If it's from the main form you must use the name of the subform control. I seem to remember reading in another post something about subforms not being part of the forms collection so access doesn't think they are open. Anyway, hope this helps.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform?

    Hi Paul,

    I'm not calling the procedure from either the Main or Sub form, in the OnClick Event of the opt btn which is in the subform, I'm calling the function: "repFU Me"

    The function is in a Module. On errror, I select debug, and this line is highlighted : strProAdd = Nz(Forms!sfrmMain!ProAdd) stating that sfrmMain can't be found....Access thinks the form isn't there, but it is.

    Hope I made myself more clear....Sorry for the misunderstanding.

    Thanks

    ps....if I put the entire code in the OnClick event, I still get the same error msg. I shouldn't have to put the entire code in the OnClick event. I should be able to put the code in a function and call the function for the event.........hmmmmmmmmmmmm.... I'm lost!
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform?

    Well, I was going to suggest passing the form object to the function but instead I would pass the values you need to the function and reference these passed values instead of referencing the form objects from the function. I'm looking at some code I have in one of my app's subform and I consistently use the Me keyword. You could also try that (moving the function to the click event of the opt grp.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform?

    I think the syntax that you are looking for is:
    Forms![yourMainform]![yourSubform].Form![yourField]

  6. #6
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform?

    I think this will work if you use the name of the subform control rather than the name of the subform.

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Subform?

    Don't you have to pass the name of the form thru to the Function as a parameter of the function?
    Pat

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Subform?

    Roberta,

    Subforms aren't open forms in the sense that you're trying to address it, which is why you get the error message. If the code is running from behind the button on the subform, you don't need a form reference at all, only a reference to the control ProAdd. I couldn't tell if your code snippet was from the OnClick event of the button or the function it calls, so I can't give you a definitive answer on how best to handle passing the values to it. One way would be to pass the subform itself as a form object. Then the function could get all the values it needs directly from the subform.

    However, if the code is that tightly bound to the controls on a subform, I don't see much purpose in having it in a standard module. You might as well just make it a private function in the subform's module and save yourself some trouble.
    Charlotte

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform?

    Tx Charlotte,

    In the OnClick Event I have "RepFU Me". This calls the function but that is where I get there error msg. I've tried making it a Private function, but I still get the same error msg.

    I agree with you that I need to refrence the subform as a control, but what I've tried so far doesn't work. I know I'm doing something wrong...just can't figure out what.

    I am including the entire code for the purpose of making more sense.

    Thanks everyone for your suggestions.
    ************************************************** *********
    Public Function RepFU(obj As Object)
    Dim DB As Database
    Dim rsLog As Recordset
    Dim strProAdd As String, strNewProAdd As String


    strProAdd = Nz(Forms!sfrmMain!ProAdd)
    strNewProAdd = IIf(strProAdd = -1, "yes", "no")

    Set DB = CurrentDb()
    Set rsLog = DB.OpenRecordset("tblRepFollowUp")

    With rsLog

    .AddNew
    .Fields("ProName") = Nz(Forms!sfrmMain!PVDName, "x")
    .Fields("ProEIN") = Nz(Forms!frmMain!cboEIN.Column(1), "x")
    .Fields("ProSSN") = Nz(Forms!sfrmMain!SSN, "x")
    .Fields("ProPlanArea") = Nz(Forms!frmMain!PPA, "x")
    .Fields("ProAdd") = strNewProAdd
    .Fields("ProUnaf") = Nz(Forms!sfrmMain!ProUnaf, "x")
    .Update
    .Close

    End With

    Set rsLog = Nothing
    Set DB = Nothing

    End Function
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subform?

    As was pointed out by Charlotte and ThomasW, you can't refer to a subform as

    Forms!sfrmMain

    because the Forms collection contains only forms that have been opened by themselves, not the subforms on them.

    The way you must do it is

    Forms!frmMainForm!sfrmMain

    where frmMainForm is the name of the, well, main form.

    You pass the subform as the obj parameter to RepFU. In the code as posted, you do nothing with it. But you could use obj instead of Forms!frmMainForm!sfrmMain.

    So to take one line in your code as example: replace

    .Fields("ProName") = Nz(Forms!sfrmMain!PVDName, "x")

    by either

    .Fields("ProName") = Nz(Forms!frmMainForm!sfrmMain!PVDName, "x")

    or

    .Fields("ProName") = Nz(obj!PVDName, "x")

  11. #11
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform?

    Hans,

    I've tried what you are suggesting:

    forms!frmMain!sfrmMain.ProAdd
    forms!frmMain!sfrmMain!ProAdd

    I've tried it many ways, but when I do that I get an error msg that says "Access can't find the FIELD I'm referring to"......

    I made the changes in the first line of code that calls the subform...and that is where it crashes. Like I said in my past post, I agree I need to reference the subform as an obj rather than a form, but no matter what I do, it fails. Perhaps it's in the punctuation.

    Thanks Hans.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  12. #12
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform?

    Thanks Everyone!!!!!!!!! I got it!!!!!!!!!!

    I appreciate the suggestions from everyone. Once I changed "frmMain!sfrmMain!ProAdd" to "obj!ProAdd" it ran beautifully.

    Have a great day all.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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