Results 1 to 11 of 11
  1. #1
    osupratt is offline Advanced Beginner
    Windows 98/ME Access 2007
    Join Date
    May 2011
    Posts
    39

    message box not displaying


    i have a data entry form to where the user selects a Customer from a drop down. data source is a query from View on sql back-end. the user selects the customer and several fields on form will auto-fill from this data. I want a message box to appear when a credit limit field reaches a criteria. anyhow i am noticing two things. when testing other fields it seems to work (as far as vba code for msgbox). but when trying on an 'auto-filled' field it doesn't. when trying on an unbound auto-filled text box it doesn't. how do i get this to work? the field in question is an unbound text box that is one of the columns from the underlying datasource query. thanks.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What is your code? Once filled, it doesn't matter how the control was filled. If it was bound, I'd suggest making sure the record was committed, but that doesn't sound like the case here.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    What is the event that triggers the msgbox to appear, where do you have the code to display the message? Either it would occur when the customer is selected from the dropdown or when some amount is entered which causes the credit limit to change.

  4. #4
    osupratt is offline Advanced Beginner
    Windows 98/ME Access 2007
    Join Date
    May 2011
    Posts
    39
    i have it on the after update event. so i've noticed that any field that is 'auto-filled' from drop down list query does not work with msgbox vba code. conditional formatting does for all except the one field i am concerned about that is the unbound text box. here is code from one of the fields from drop down that is autofilled and the conditional formatting works but not vba msgbox (not that field is a text field data type):

    Private Sub CustomerNumber_AfterUpdate()
    If CustomerNumber= "154" Then
    MsgBox "Please Select a Business Unit!", vbInformation
    End If
    End Sub

  5. #5
    osupratt is offline Advanced Beginner
    Windows 98/ME Access 2007
    Join Date
    May 2011
    Posts
    39
    selecting the customer from the drop down is the trigger. i mean it populates customer fields for sales order processing but will have a message appear if that customer has gone over their credit limit. so the auto population of the credit limit number into the field should throw the message i would think.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The update events don't fire if a control is populated programmatically. You'd want that code in the update event of the drop down.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    So you populate the credit limit number from the combo box selection(based on customer) but what does it check against to say it is over the limit. Is there another field on the form that has the actual credit borrowed value? Or do you need to do a separate query to sum all the credit amounts for that selected customer and then see if it is over that credit limit? Sounds like the AfterUpdate on the combo box is the right place to check the values and show a msgbox if needed but still not clear on the full process.

  8. #8
    osupratt is offline Advanced Beginner
    Windows 98/ME Access 2007
    Join Date
    May 2011
    Posts
    39
    the credit limit percentage is figured in the back-end sql View. if it is 90 (90 percent) or over it should throw the message box when auto filled into the unbound txt box.

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That code needs to be in the same event as your autofill code, not in the destination textbox.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    osupratt is offline Advanced Beginner
    Windows 98/ME Access 2007
    Join Date
    May 2011
    Posts
    39
    Ok. i moved it to the after update of the drop down and YES it worked. Greatness. one question remaining is why the conditional formatting doesn't work on this unbound text box? Thanks for your help on this as now i feel like a dunce but i could've been stuck a lot longer.

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What is the conditional formatting expression? What value does the textbox hold?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 01-08-2018, 10:16 PM
  2. message box after if/end if not displaying
    By Paintballlovr in forum Macros
    Replies: 5
    Last Post: 08-15-2014, 01:52 PM
  3. Replies: 2
    Last Post: 01-23-2014, 12:40 PM
  4. Replies: 15
    Last Post: 11-01-2013, 03:24 PM
  5. Replies: 9
    Last Post: 09-26-2012, 12:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums