Results 1 to 13 of 13
  1. #1
    Benjiro is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    7

    Message Box on Event

    Hi,

    I'm have a good enough knowledge of Access, but Visual Basic is another matter. So in short I have to produce a Database for an Estate Agency for my IT Course, I have a Property Form, with a Viewing Sub Form in it which counts how many times a Property ID has been viewed in the Viewing Table. But I want a Message Box to pop up when no viewings have been scheduled for that specific property and when viewings are scheduled it shouldn't pop up.


    For instance in this situation the message box should pop up with a simple OK answer button.



    Whereas in this circumstance the message box shouldn't appear when the Property has Viewings arranged.



    I initially tried doing it via macro but I was told it was a VBA Job which I am totally new to so any help would be greatly appreciated

    Thanks

  2. #2
    mrojas is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2013
    Location
    Concord California
    Posts
    72
    There are many ways of achieving this, and most likely you'll see a few replies. I would use the following chunk of code in the "On Current" event.

    If DCount("*","SubformRecordSource")<=0 then
    ' Display message
    MsgBox "Blah, Blah, Blah..."
    endif

    Another way would be to add to the subform, in the form's footer, a text box with its source as follows " =Count(*)", then on the main form, again in the On Current event, the following code
    if val([Mainform].[Form]![txtCouny] )>0 then
    msgbox ...

  3. #3
    Benjiro is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    7
    Hi MrRojas,

    Thanks for the reply, this may sound dumb but forgive me with the the SubForm Record Source would this be the name of the Actual Subform or would I copy the code exactly as is?

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,608
    Can you show us a jpg of your tables and relationships?
    WHAT exactly is/are your course/project specifications?

    I don't know your expertise with respect to database design, so I'm providing a list of free videos on the subject.

    Good luck with your studies.


    http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

    http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

    http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

    http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

    http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

    http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

    http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

    http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

  5. #5
    Benjiro is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    7
    Hi Orange

    Here's the specification for the project or at least the specific section I'm currently working on.



    And now for the tables.

    Property Table



    Theres also an image Column that has an Attachement Data Type with images of all the properties


    Buyer Table



    Viewing Table




    Relationships




    Forms



    as you can see there is a View Count Sub Form in the property Form which was made from this Viewing Query.



    Then into a form and then placed into the Property Form as a Sub Form.


    This is the layout of the database, if you need to know anything else just ask.
    Thanks for the links as well btw

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,608
    Looks good. I have 2 comments (neither is critical) but for reference.

    1) I would not use a naming convention that has/allows embedded spaces or special characters. Use only alphanumeric(A-Z, 0-9) and underscore "_" in names of fields and objects. You will save much syntax error frustration.

    2) When posting relationships lengthen the tables in the diagram to show all fields.

    Good luck with your project. Please post a copy of your database when completed.

  7. #7
    Benjiro is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    7
    Thanks for the tips Orange, will be sure to post the completed Database when its done.

  8. #8
    mrojas is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2013
    Location
    Concord California
    Posts
    72
    The subform's RecordSource would be either a table or a query, most likely a query. Assuming the subform's query's name is qryPropertyViewings, the code would look something like this:
    If DCount("*", "qryPropertyViewings")<=0 then


    If you decide to go with my second suggestion, the one where you add a text box in the subform and the built-in Count("*") function as its source, the code, assuming the names of the main form and sub are "frmProperty" and "frmPropertyViewingsSub" respectively, the code, in the frmProperty form, under the On Current event, would look something like this:
    If Val([frmProperty].[Form]![frmPropertyViewingsSub].[txtCount])>=0 then
    your message, blah, blah, blah

  9. #9
    Benjiro is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    7
    I tried the second code, but when I run it I get this error message.


  10. #10
    mrojas is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2013
    Location
    Concord California
    Posts
    72
    What are the names of, your main form? the subform? the field in the subform with the count of "Viewings" records?
    Could you send all your code behind the form and sub?

  11. #11
    Benjiro is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    7
    Main Form is Property Screen
    Sub Form is Property View Sub Form.
    The field in the sub form is CountofProperty ID

  12. #12
    mrojas is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2013
    Location
    Concord California
    Posts
    72
    In the main form's On Current event, the code would then read as follows:
    if Val(Forms![Property Screen].Form![Property View Sub Form].[CountofProperty ID])<=0 then
    MsgBox "No viewing found"

    I'm using square bracket because the name of your objects have spaces in them.
    In the future, try omitting these; for example, the Property Screen form would be frmPropertyScreen, the subform frmPropertyViewSubform, etc.

  13. #13
    Benjiro is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    7
    I now get another error message.


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

Similar Threads

  1. Replies: 9
    Last Post: 09-26-2012, 12:20 PM
  2. Replies: 18
    Last Post: 06-22-2012, 12:49 PM
  3. Replies: 3
    Last Post: 05-07-2012, 12:17 PM
  4. Information message on current event
    By justauser in forum Forms
    Replies: 5
    Last Post: 02-07-2012, 12:15 PM
  5. Replies: 1
    Last Post: 02-25-2011, 11:32 AM

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 - Senior Forums