No results w/empty form

[Bruce]

Access-03. Im using a form button to run a query that requires user input of 2 variables. If the query finds a match it returns the record in form view. If it finds no matching record, an empty form is displayed. I would like a msg box to show up instead of a blank form. The two criteria values are order_id and L4ID that come from two different tables, orders and workorders. Can't figure out how to use the button onclick event, allow the user input and then add an If statement to handle the result output.



Any code samples are appreciated. Thanks!

[hertfordkc]

You have a form Unboud, not tied to a query?
On that form, there is a button that does what? Show two user input fields, or starts a parameter query?
Do you know how to go to the property sheet of a button and open the VBA window for the onclick event?
If you were in the VBA window, could you use the Dlookup function to determine if there were any records matching your criteria?
If no records were returned, you want to create a msgbox, otherwise a query would run.
Elaborate and I'll take another look.

[Bruce]

Yes, the button on the form executes the query. The query requires the user to input two criteria, then the query runs and displays the results on another form.
I am familiar with the onclick event for the button and semi-familiar with the VB code. My question is how to code the user input criteria to perform a Dlookup. I have a similar Dlookup function running on another onclick event but it pulls criteria from data on the form. The new one I am trying to create needs to pull criteria from user input prompts related to the query. Tks!

[hertfordkc]

query? From your reply, I'd guess you'd know how to get the DLookup info from textboxes. If thats the case, I don't know how to retrieve the results of response to parameter queries.

[Bruce]

I made a mistake in my process description. The button click event execs. an open form command. That form is linked to the query. I don't have access to the code right now and for the next few days (vacation), however, I assume
the on-load event of the form execs. the run query command. The query prompts for 2 criteria then runs and displays the result in the form.

From here I think I need to add some code to the on-load event of the form that checks "If the query result is null, do cmd close form, else continue to load form".

[hertfordkc]

I made a mistake in my process description. The button click event execs. an open form command. That form is linked to the query. I don't have access to the code right now and for the next few days (vacation), however, I assume
the on-load event of the form execs.the run query command. The query prompts for 2 criteria
and does a Dlookup to see if there are any records meeting the criteria. If not, the display a msgbox and close the form.
then runs and displays the result in the form.

From here I think I need to add some code to the on-load event of the form that checks "If the query result is null, do cmd close form,
Else here
else continue to load form".

How will you check to see if the query result is null?

[DJDJDJDJ]

My guess is that your query prompts the user to input data upon runtime. Rather than doing that, have text fields on the form where user can enter the data. Then the user can click on a GO button on the form and query can get its input from the fields on the form. In the GO button's code, you can do whatever you want including DLookup as suggested.

HTH

[bcmarshall]

I realize this is a very old thread, but I just stumbled upon it and it appears that there was no resolution offered. Actually, what you wanted to do is quite simple to accomplish, without code or anything else.

In the criteria line of the query for each of the two fields in question, order_id and L4ID, just enter the following:
Like [Whatever message you want to be displayed as the box opens]

The text within square brackets will be displayed as the prompt, and a box asking for the information to be manually input will be automatically generated. If you have two similar criteria lines than there will be two boxes that open, one after the other.

Now when you open the form, of course the query will run and the boxes will appear. What happens in a query if there are no results to return? You get an empty recordset, of course. And the form which is based on that query will open with a blank record.

What happens when you open a query where there are matching records? Of course that recordset will be returned, and when the form is based on that query, the form will open with matching records as well.

You shouldn't need to do a thing more than what I've outlined. Boxes will come up, and the form will either open to a blank, new record or it will open to the existing record(s) you're seeking.

Sorry about the late reply. Hope it helps.

[Bruce]

I realize this is a very old thread, but I just stumbled upon it and it appears that there was no resolution offered. Actually, what you wanted to do is quite simple to accomplish, without code or anything else.

In the criteria line of the query for each of the two fields in question, order_id and L4ID, just enter the following:
Like [Whatever message you want to be displayed as the box opens]

The text within square brackets will be displayed as the prompt, and a box asking for the information to be manually input will be automatically generated. If you have two similar criteria lines than there will be two boxes that open, one after the other.

Now when you open the form, of course the query will run and the boxes will appear. What happens in a query if there are no results to return? You get an empty recordset, of course. And the form which is based on that query will open with a blank record.

What happens when you open a query where there are matching records? Of course that recordset will be returned, and when the form is based on that query, the form will open with matching records as well.

You shouldn't need to do a thing more than what I've outlined. Boxes will come up, and the form will either open to a blank, new record or it will open to the existing record(s) you're seeking.

Sorry about the late reply. Hope it helps.

Thanks for the offer to help. Your synopsis is correct however, Instead of a blank form record being displayed upon an empty query result, I was looking for a pop-up box that states "No records found".
I'll probably need to implement a D-Lookup in MS-SQL that runs prior to the "open form" cmd.

[bcmarshall]

It's still a simple matter to solve with a macro.

Put your DLookup statement in the conditions line of an MsgBox command. If it is true, then the MsgBox will pop. Follow the MsgBox with another line with elipses (...) in the conditions line, and that action would be StopMacro. The third line, without conditions, would be your OpenForm Command.

Now, as you run the macro, if the DLookup is true it'll only pop the message and stop. If it's false, it will open the form with the desired records displayed. In this case, my earlier comments about the Like statement in the query no longer apply.