No Data on a form based on a query.

[bill crumpton]

I have a query that shows its results on a form. The Select query had a parameter criteria for the user to enter a date. If the user puts in a date that no data exists I would like a message box to appear and give the user a choice to close or to retry another date. It is easy to do with reports because there is the "On No Data" property for a report. I have played with macros to try to get this to work with forms but am unsuccessful. Any help is greatly appreciated.

BAC

 

[R. Hicks]

Bill, basically what you need to do is use the DCount function in your code before you execute the opening of the form in question. If the DCount function, using the criteria you supply, returns 0 (zero records) then give the message that there where no matching records and exit the sub before the form opens. But if DCount returns a number > (greater than) 0, then there are records that match then open the form. You can find info on how to use the DCount function in Access Help files. Use this in conjunction with an If..Else statement to do what you need.

HTH
Merry Christmas
RDH

[This message has been edited by R. Hicks (edited 12-23-2000).]

 

[bill crumpton]

Rhicks,
Thanks for your quick response. It all makes sense and I think it will work, however I am not very adept with vba yet. Would it be possible for you to send a sample code so I could use as a template? Thnaks alot for your time and Merry Christmas to you and yours.

BAC

 

[Jack Cowley]

I hope my friend R. Hicks will not be cross at me for trying to answering this question.... Try some code similar to this:

If DCount("*","QueryName")<= 0 Then
MsgBox "There is no data for the date you selected."
Exit Sub
End If

And a Merry Christmas to you and yours....

 

[bill crumpton]

Thanks Jack,
I am sure Rhicks appreciates your help. Will that msg box allow the user to enter another date or to cancel, whatever he chooses to do?

BAC

 

[Jack Cowley]

The message box dialog will have just an OK button. When they click OK it will return them to the form or where ever they were, depanding on how you use your parameter query. I do not know how you are calling your query so I do not know how to advise you handle this situation. You can have a Yes and No button on the message box, or other buttons, and depending on which one they select you can decide where the code will go from there. If you need more assistance with this just let us know.....

 

[R. Hicks]

I'll add a liitle more to Jack's code. Maybe this will make it a little clearer. The code below will check the number of records being returned by the query that the form you are attempting to open returns. If the count is <=0, then there were no matching records, the code gives a message, then exits the sub to give the user the opportunity to enter different criteria. But if the DCount function returns a number greater than 0, then the form opens without a message. I don't know how you are triggering the code, so just give you the basics.

Dim strMsg As String, strTitle As String
strMsg = "There were no records returned for criteria given."
strTitle = " No Matching Records"
If DCount("*", "YourQueryName") <= 0 Then
MsgBox strMsg, vbExclamation + vbOKOnly, strTitle
Exit Sub
Else
DoCmd.OpenForm "YourFormName"
End If

PS: Jack can answer for me any time. I don't mind at all.

HTH
RDH

 

[spalmateer]

Hi,
I'm kinda having the same problem with my form. To what event would you add the code listed above? My forms record source changes from a number of parameter queries depending on the button that was pressed on the switchboard. For example if the user wants to search by Company Name then they press the button on the switchboard that runs a macro to change the form's record source to a company name parameter query and opens the form. So the problem is that if I use that code on the open event of the form, my query names will not match because they change depending on the users button selection (and I have one main form). Does anyone have any suggestions? Is it possible to have the query name in the code listed above equal the recordsource? Thanks Alot & Merry Christmas (although it's officially over- 26th)!
Scott

 

[bill crumpton]

Jack and R.Hicks,
Thanks again to both of you this worked great. Keep up the great help.

BAC

 

[R. Hicks]

Bill, I received your email message stating the problem you were having.

This code I posted will only work if it is triggered from another form. I was assuming that you had a cmdbutton, or some other event to open the form you wanted to check for records. What I mean here is that you open form "B" from form "A". If this what you are doing, then the code needs to be triggered from form "A", before form "B" opens. This could be done with a cmdbutton, or some other event from form "A".

Using the On Open event of form "B" will not work as you say you are trying.
As we say here in the south:
"The Horse is already out of the barn by that time".

HTH
RDH

[This message has been edited by R. Hicks (edited 12-26-2000).]

 

[bill crumpton]

Thanks for your quick response R. Hicks. I see your point. Is there a way that I could achieve the same results without using a form to launch the query?

Thanks,
BAC

 

[R. Hicks]

Bill, how are you opening the form in question? Maybe I, or someone can figure out a solution.

RDH

 

[bill crumpton]

I have a command button on the switchboard that I have set to open form in edit mode. The query is bound to the form.

 

[R. Hicks]

If the db is written in Access 97, zip it up and send me a copy. I should be able to attach the code to the cmdbutton in the switchboard form

RDH

 

[bill crumpton]

Unfortunately it is in A2K. Can you tell me how to go about it?

BAC

 

[R. Hicks]

There should be a feature in Access 2000 to save the db to a previous version. I think it may under the Tools menu. See if you can locate this feature, then send the converted db.

RDH

 

[bill crumpton]

I can convert it and send it to you but I will have to delete the info in the tables because it is a law enforcement database with sensitive information, but I can do that if you need to look at it.


BAC

 

[R. Hicks]

I don't need the info, just delete the sensitive info and maybe place a couple of dummy records in the tables so we make sure it works.

RDH

[This message has been edited by R. Hicks (edited 12-26-2000).]

 

[bill crumpton]

R. Hicks I am experiencing tremendous difficulty trying to convert this db to 97. I keep getting error messages stating that certain forms are not going to be converted. I'll keep trying. Thanks for your patience.

BAC