Results 1 to 9 of 9
  1. #1
    5 Star Lounger kmurdock's Avatar
    Join Date
    Feb 2003
    Location
    Pacific Grove, California, USA
    Posts
    716
    Thanks
    10
    Thanked 34 Times in 28 Posts

    Modeless dialog box (Word 2003 VBA)

    Can anyone tell me what the heck I'm doing wrong here?

    I've created modeless dialogs before, to display names and such in a list box. The user can then insert info into a document, reposition their cursor and insert again. When the user's done, they dismiss the dialog. I've pulled data from an Access db before.

    The attached template accesses a really simple db with one table (Recipes) and three fields (EntryID, RecipeName and Description). EntryID is an AutoIncrementing number and indexed, RecipeName is a 50 character text field and Description is a memo field.

    I populate the listbox with the recipe names and the Entry ID. I want to be able to click on different entries and have the text box below display the description. I have not created an array, as it could get very big and I just wanted to see if I could do it this way. The final product will not display recipes.

    So, the dialog box displays with the listbox populated, but it is frozen and Word crashes -- none of the controls are responsive.

    I just can't see where I've gone awry. <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    Help! <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>
    Thanks, Kim
    Attached Files Attached Files

  2. #2
    5 Star Lounger kmurdock's Avatar
    Join Date
    Feb 2003
    Location
    Pacific Grove, California, USA
    Posts
    716
    Thanks
    10
    Thanked 34 Times in 28 Posts

    Re: Modeless dialog box (Word 2003 VBA)

    P.S. I did find one little transposition at the end of lbxRecipes_Click() but as far as I can tell, I never even get there.

    "strDescription = txtDescription.Text" should read
    "txtDescription.Text = strDescription"

    K <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Modeless dialog box (Word 2003 VBA)

    Try these things:

    - Comment out the lines in PopulateListBoxData where you set the recordset and database to Nothing.
    - Do the same in lbxRecipes_Click.
    - Add oRecordSet.MoveFirst before your Do loop in lbxRecipes_Click.

    That's as far as I followed it, but hope this helps unfreeze the dialog.

  4. #4
    5 Star Lounger kmurdock's Avatar
    Join Date
    Feb 2003
    Location
    Pacific Grove, California, USA
    Posts
    716
    Thanks
    10
    Thanked 34 Times in 28 Posts

    Re: Modeless dialog box (Word 2003 VBA)

    Thanks Jefferson,

    It's clear that there's something horribly wrong with the lbx_Recipes sub...

    I can display the dialog and now click either Cancel or OK to dismiss the dialog. But if I click anything in the Recipe listbox, I'm toast.
    K (still <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>)

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

    Re: Modeless dialog box (Word 2003 VBA)

    Could you attach a small sample of the Sample Data.mdb database (zipped)?

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

    Re: Modeless dialog box (Word 2003 VBA)

    It won't solve your problem, but you should close the recordset etc. in the UserForm_Terminate event:

    Private Sub UserForm_Terminate()
    oRecordSet.Close
    Set oRecordSet = Nothing
    oDataBase.Close
    Set oDataBase = Nothing
    oWorkSpace.Close
    Set oWorkSpace = Nothing
    End Sub

    And I wouldn't use

    Set frmModeless = Nothing

    Unload already removes the form from memory.

  7. #7
    5 Star Lounger kmurdock's Avatar
    Join Date
    Feb 2003
    Location
    Pacific Grove, California, USA
    Posts
    716
    Thanks
    10
    Thanked 34 Times in 28 Posts

    Re: Modeless dialog box (Word 2003 VBA)

    Hans,

    Here's the entire database. It's very small.

    And you're right about setting frmModeless to nothing -- that was left over from earlier code. Oops. Still, I never get that far...

    Thanks,
    Kim
    Attached Files Attached Files

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

    Re: Modeless dialog box (Word 2003 VBA)

    OK. The code can be much simpler.

    1) Open the recordset with the dbOpenDynaset parameter:

    Set oRecordSet = oDataBase.OpenRecordset(strDBTable, dbOpenDynaset)

    2) Don't set the recordset and database to nothing after filling the list box.
    3) Don't reattach to the database each time you click the list box.
    4) Use this short On Click code for the list box:

    Private Sub lbxRecipes_Click()
    oRecordSet.FindFirst "EntryID=" & lbxRecipes.Column(1)
    txtDescription.Text = oRecordSet.Fields("Description")
    End Sub

    No need to loop at all!

    See attached version.
    Attached Files Attached Files

  9. #9
    5 Star Lounger kmurdock's Avatar
    Join Date
    Feb 2003
    Location
    Pacific Grove, California, USA
    Posts
    716
    Thanks
    10
    Thanked 34 Times in 28 Posts

    Re: Modeless dialog box (Word 2003 VBA)

    HANS! It's perfect and I've now learned about the dbOpenDynaset parameter. Fabulous!

    Thank you thank you thank you! <img src=/S/kiss.gif border=0 alt=kiss width=34 height=15> This sure had me talking to myself!

    Kim

Posting Permissions

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