Dcount or Dlookup

[Eric Starn]

I have a query that when run opens into a form, however when there is no
matching record in the database the form opens as a grayed out box. This is
fine for me because I can get around in the database just fine, but this
database is to be used by other users, so I am trying to eliminate this
problem. It was suggested to me to have the query look and see if a record
exists before opening the form.
So my question is, is this the right thing to do and if so how do you do it?

The query runs a search for an employee using the field - [Last Name]

Thanks

[XPS350]

On 4 jan, 15:16, Eric Starn <EricSt...@discussions.microsoft.com>
wrote:

IMHO you open a form that uses the query. Therefore the solution is
with opening the form.

Use a button on a form to open the form and commands like these in the
click event of the button:

If DCount("*", "YourQuery") > 0 Then
DoCmd.OpenForm "YourForm"
Else
MsgBox "No data te be diplayed on the form"
End If

Groeten,

Peter
http://access.xps350.com

[Dorian]

I've never seen a query that opens a form, normally a form is opened before a
query using any of its controls is run.
However, you can do what you ask, lookup the syntax of DLookup in Access help.
If IsNull(DLookup([column name],[table name],"[Lastname] = '" &
nametosearchfor & "'") Then
<name does not exist>
Else
<name does exist>
End If

It seems dangerous to use only last name since there could be many different
people with the same last name surely?

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

[theDBguy at dot]

Hi Eric,

I agree with the others of using a form. So, here's another possible
solution (really just a variation of the others already given). In the form's
Open event:

If Me.Recordset.BOF And Me.Recordset.EOF Then
Msgbox "Sorry, no record found.", vbOKOnly, "No Record Found!"
Cancel = True
End If

Hope that helps...

[Eric Starn]

Ahh finally works
Thanks

One more question
I added a command button to the form to re-run the same query. Is there
somewhere I need to add that code to also get the desired results?

Eric

[theDBguy at dot]

Hi Eric,

Where did you add the button? On the same form you are opening earlier? If
so, where is the parameter coming from? To requery the form, you would use

Me.Requery

but I suspect that there is a portion of this scenario that I am missing.
Don't you want to close the form first before you do the requery? If not, you
could try repeating the original code in the form's Current event.

[Eric Starn]

OK,
What I did was create a switchboard which then the user can select to run a
search for an employee.
This then runs a query which asks for a parameter from the user "Enter Last
Name", then opens in a form that I made.
By request from the user they wanted a button on that form to allow them to
search for another employee without having to go back to the switchboard.
So I added a command button to run the query again.
Everything worked just fine until you entered a name that was not in the
database, then you just got a grayed out box.
With your help I fixed the problem when you run the query from the
switchboard, however when you use the command button from the form it still
does it the other way. It is fine if the form grays out, I just want it to
prompt the user what is going on and that they should close and return to the
switchboard. That is assuming that I can not get it to automatically close
and return to the switchboard.

Thanks

[Eric Starn]

OK,
I created a switchboard that runs a query with a parameter set by the user,
"Enter Last Name", to search for an employee in the database.
When run the query returns the info and displays it in a form I made.
By request of the user , they wanted me to add a button to the form to allow

them to search for another employee without having to go back to the
switchboard.

So I added a command button to the form to run the same query.
Everything worked fine until you searched for a name that was not in the
database.
With your help, the problem was solved when the query is run from the
switchboard, however when you run the query from the form using the command
button the result is as before. (grayed out box)
All I am looking for, when using the form's command button, is to prompt the
user that there is no record and to return to the switchboard.
That is assuming that I can not get the form to close and automatically
return to switchboard.

Thanks

[theDBguy at dot]

Hi Eric,

Can you post the code you are using for your button? Have you tried also
putting the code I gave you earlier in the form's Current event?

[Eric Starn]

I do not have any code written for the command button. All I have done is set
the macro that runs the query in the OnClick box.

I did put the code in the form's current event, however still no luck

[theDBguy at dot]

Sorry, Eric. I am truly lost and can't quite imagine what you're dealing
with. I am new to this forum so I am not sure if it's possible for you to
attach a sample db with test data that illustrate your problem.

[Gina Whipp]

Eric,

The problem is...

1. Enter parameter value...
2. Form opens already filtered
3. You Me.Requery is only requerying the filtered query

You're going to need something like...

Me.Filter = ""
Me.Requery

OR

Me.RecordSource = "Query" 'Though not sure if this will work with a
parameter

However, if it where me, I have them open a form and have them select the
parameter value directly on the form that opens. Would give you a wee bit
more control and you could have a [Set Filter] and [Clear Filter] button.
You could also filer by more then Last Name.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Eric Starn" <Eric...@discussions.microsoft.com> wrote in message
news:4068F1FD-CB8D-4F12...@microsoft.com...