Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I think I'm in way over my head here and I sure could use some help understanding this whole topic.
    I have a form with several subforms. Each represents a one to many relationship: One contract, many milestones, many projects, many workers. I'm trying to work with the displayed data in vba code behind a button (i.e. click the button, the code creates a document with data from the form and subforms).
    For a while this code seemed to be working fine but I hadn't tested when there were no projects:

    If Me![Contract-Projects Subform].Form.Recordset.EOF And Me![Contract-Projects Subform].Form.Recordset.BOF Then
    'No projects
    Else
    strRec = ""
    Me![Contract-Projects Subform].Form.Recordset.MoveFirst
    Do While Not Me![Contract-Projects Subform].Form.Recordset.EOF
    strRec = strRec & Me![Contract-Projects Subform].Form.Recordset![Projects Name] & ", "
    Me![Contract-Projects Subform].Form.Recordset.MoveNext
    Loop
    End If

    When I tested more fully and investigated further I found that the .bof and .eof weren't set until after the .movefirst (maybe the recordset wasn't open?) For some reason the modified code below (I moved the .movefirst code up to the top) worked when I first tested with no projects (maybe the recordset was open during my testing until I closed the form?) but is now failing on the first line when I open the form and click the button. I get a message that there is no current record.

    Me![Contract-Projects Subform].Form.Recordset.MoveFirst
    If Me![Contract-Projects Subform].Form.Recordset.EOF And Me![Contract-Projects Subform].Form.Recordset.BOF Then
    'No projects
    Else
    strRec = ""
    Me![Contract-Projects Subform].Form.Recordset.MoveFirst
    Do While Not Me![Contract-Projects Subform].Form.Recordset.EOF
    strRec = strRec & Me![Contract-Projects Subform].Form.Recordset![Projects Name] & ", "
    Me![Contract-Projects Subform].Form.Recordset.MoveNext
    Loop
    End If

    Obviously that is due to the fact that there are no project records associated with this contract but I don't understand how to check for that. The help file mentions opening the recordset and that the .bof and .eof are set to true at that time if there are no records but I can't figure out how to open the recordset explicitly. I thought it would be open just because the form is open and the records are displayed on the subform but that doesn't seem to be the case.
    I can't find an open method for the recordset and the more I read about this the more confused I'm getting. What basic information am I missing? I'm trying to keep this simple and easy to understand but it seems I only have part of the story here. Is there a way, and need, to "open" the recordset? Is there a better way to determine if there are any projects (i.e. were any project records chosen in the subform and therefore have created any Projects records associated with the contract?)
    Please help me out with any suggestions you might have. I am pretty far down this path but I'm beginning to think I took a wrong turn somewhere.
    Thanks

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

    Code:
    If Me.[Contract-Projects Subform].Form.RecordsetClone.RecordCount = 0 Then
      ' No contracts
    Else
      ...
    End If

  3. #3
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts
    For a man of such few words, you teach me volumes! Thanks! I missed you last week.
    This works like a charm and now that I read a bit more I actually understand it!
    Thank you very much!

Posting Permissions

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