Help with setting up query

[hwmueller]

Hi

I have a query that when run asks for the user to enter the name of the staff member whose details are required. When entered correctly the query runs fine. When no name or a wrong name is entered the query just produces a blank screen. I wish to change that so a message appears stating either "Please enter a name" or "That name is spelt incorrectly or doesn't exist". How do I do this??

Thanks

Hans

 

[braindead2]

Test to see if there is a match first
something like!!!
dim rst as recordset,db as database,strsql as string
strsql = "select name from yourquery where name = '" & yourfieldname & "'"
set db= Currentdb
set rst = db.openrecordset (strsql)
if rst.recordcount <1 then
msgbox &quot; No records found Please Reenter Name&quot;

else
docmd.openquery &quot;Yourqueryname&quot;
end if
rst.close

 

[hwmueller]

thanks. I have some further questions about the above.

What is the Private Subs name that should be used?
Is the above module entered in under the form corresponding to the query? If not where then?

Thanks

 

[hwmueller]

Hi

After much trying and little success I have now written below exactly how my query looks:

COLUMN 1

FIELD: Staff details*
TABLE: Staff details

COLUMN 2

FIELD: Surname
TABLE: Staff details
CRITERIA: [Enter Staff Member's Name]

In the properties the recordset type is on Dynaset.

So, where do I enter the code written above and how do I link it to this query? At the moment I have entered the code in the corresponding form window. When I run it without entereing a name or using a wrong one, all I get is a blank screen

Thanks

 

[braindead2]

I apologize I assumed you were launching the query from a form. I do not know of a way to make what you want just using SQL.
I would suggest that you consider using a form.

create a unbound form (no source)
on the tools menu create a combobox make sure the wizard button (the wand with the stars) is pressed when you select the combo box.
place the combo box on the form. when the wizard opens.
Select look up the values in a table or query
select staffdetails table as the source
select surname as the field
adjust the field width then hit finish
right click on the new combo box and open the properties window. name the combo &quot;Namesearch&quot; then be sure the limit to list property is set to yes.
This combo box will now ony show surnames of those that are avaliable, ensuring the query will return data.
Now save the form as &quot;Search form&quot;
open your query and in the criteria type
Forms![Search Form]![Namesearch].
save the query as &quot;surnamesearchQRY&quot;
now open your from in design mode and go back to the combo boxes properties.
In the after update event click the 3 dots to the right and select code
this will open the code window
place this code in between where it says
Private Namesearch_afterupdate() and End sub
docmd.openquery &quot;surnamesearchQRY&quot;
now open the from type or select a surname hit the enter key and the query will pop up

 

[hwmueller]

Hi

Thanks very much for this. I have tried it but think that I am still doing something wrong. Essentially what I want is that when selected in the switchboard, a question pops up which says &quot;Enter Staff Members Name&quot;. If this corresponds with an actual name, then a form opens with the details corresponding to that name. If no such name exists, a second pop up should appear which say so. Is this possible??

Thanks

Hans