Message Box on Event

[Benjiro]

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

[mrojas]

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 ...

[Benjiro]

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?

[orange]

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

[Benjiro]

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

[orange]

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.

[Benjiro]

Thanks for the tips Orange, will be sure to post the completed Database when its done.

[mrojas]

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

[Benjiro]

I tried the second code, but when I run it I get this error message.

[mrojas]

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?

[Benjiro]

Main Form is Property Screen
Sub Form is Property View Sub Form.
The field in the sub form is CountofProperty ID

[mrojas]

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.

[Benjiro]

I now get another error message.