query form 12-29-09

I have a form that opens when you run a query for an employee name. It does 
exactly what it is suppose to do. It displays all information that I have 
requested. The problem I am having is when I enter a name of an employee that 
is not in the database the form comes back as a grayed out box. Is there any 
way to set the form or query to prompt  the user that the name is not in the 
database and allow the user to run the query again.

Eric
0
Utf
12/29/2009 6:53:01 PM
access.forms 6864 articles. 2 followers. Follow

3 Replies
571 Views

Similar Articles

[PageSpeed] 44

Check to see if record exist before you open the form.


If you are using a command button to open your form  containing code like 
the following to open your form to the correct records

docmd.OpenForm "yourForm", , , "EmployeeName='" & txtEmployeeName & "'"


Before you open the form check the table to see if the employeename exists. 
use Dcount or Dlookup

if Dcount("EmployeeName","EmployeeTable","EmployeeName='" & txtEmployeeName 
& "'")=0 then
    msgbox txtEmployeename & " Doesn't exist"
else
    docmd.OpenForm "yourForm", , , "EmployeeName='" & txtEmployeeName & "'"
Endif


Replace the names I used with you own.



"Eric Starn" <EricStarn@discussions.microsoft.com> wrote in message 
news:51F15633-9AB2-439F-99BB-CD0C6B5B2621@microsoft.com...
>I have a form that opens when you run a query for an employee name. It does
> exactly what it is suppose to do. It displays all information that I have
> requested. The problem I am having is when I enter a name of an employee 
> that
> is not in the database the form comes back as a grayed out box. Is there 
> any
> way to set the form or query to prompt  the user that the name is not in 
> the
> database and allow the user to run the query again.
>
> Eric 


0
Chegu
12/29/2009 8:26:55 PM
Thanks for the help

Question, where do I put that code?
In the "OnClick" of the command button or some where else.
Also, I already have the query running in the OnClick spot. Is this going to 
be a problem?

Eric

"Chegu Tom" wrote:

> Check to see if record exist before you open the form.
> 
> 
> If you are using a command button to open your form  containing code like 
> the following to open your form to the correct records
> 
> docmd.OpenForm "yourForm", , , "EmployeeName='" & txtEmployeeName & "'"
> 
> 
> Before you open the form check the table to see if the employeename exists. 
> use Dcount or Dlookup
> 
> if Dcount("EmployeeName","EmployeeTable","EmployeeName='" & txtEmployeeName 
> & "'")=0 then
>     msgbox txtEmployeename & " Doesn't exist"
> else
>     docmd.OpenForm "yourForm", , , "EmployeeName='" & txtEmployeeName & "'"
> Endif
> 
> 
> Replace the names I used with you own.
> 
> 
> 
> "Eric Starn" <EricStarn@discussions.microsoft.com> wrote in message 
> news:51F15633-9AB2-439F-99BB-CD0C6B5B2621@microsoft.com...
> >I have a form that opens when you run a query for an employee name. It does
> > exactly what it is suppose to do. It displays all information that I have
> > requested. The problem I am having is when I enter a name of an employee 
> > that
> > is not in the database the form comes back as a grayed out box. Is there 
> > any
> > way to set the form or query to prompt  the user that the name is not in 
> > the
> > database and allow the user to run the query again.
> >
> > Eric 
> 
> 
> .
> 
0
Utf
12/30/2009 1:58:01 PM
Where do you have the code or macro to open your form?

That code (whereever it is) should be part of an 'IF" statement (as I showed 
below)

I suspect it is in the onclick event of your button.  Change that.  Put it 
in the 'If' statemenmt.



"Eric Starn" <EricStarn@discussions.microsoft.com> wrote in message 
news:8449FD1F-C5F7-412B-A75B-9FDE011D252A@microsoft.com...
> Thanks for the help
>
> Question, where do I put that code?
> In the "OnClick" of the command button or some where else.
> Also, I already have the query running in the OnClick spot. Is this going 
> to
> be a problem?
>
> Eric
>
> "Chegu Tom" wrote:
>
>> Check to see if record exist before you open the form.
>>
>>
>> If you are using a command button to open your form  containing code like
>> the following to open your form to the correct records
>>
>> docmd.OpenForm "yourForm", , , "EmployeeName='" & txtEmployeeName & "'"
>>
>>
>> Before you open the form check the table to see if the employeename 
>> exists.
>> use Dcount or Dlookup
>>
>> if Dcount("EmployeeName","EmployeeTable","EmployeeName='" & 
>> txtEmployeeName
>> & "'")=0 then
>>     msgbox txtEmployeename & " Doesn't exist"
>> else
>>     docmd.OpenForm "yourForm", , , "EmployeeName='" & txtEmployeeName & 
>> "'"
>> Endif
>>
>>
>> Replace the names I used with you own.
>>
>>
>>
>> "Eric Starn" <EricStarn@discussions.microsoft.com> wrote in message
>> news:51F15633-9AB2-439F-99BB-CD0C6B5B2621@microsoft.com...
>> >I have a form that opens when you run a query for an employee name. It 
>> >does
>> > exactly what it is suppose to do. It displays all information that I 
>> > have
>> > requested. The problem I am having is when I enter a name of an 
>> > employee
>> > that
>> > is not in the database the form comes back as a grayed out box. Is 
>> > there
>> > any
>> > way to set the form or query to prompt  the user that the name is not 
>> > in
>> > the
>> > database and allow the user to run the query again.
>> >
>> > Eric
>>
>>
>> .
>> 


0
Chegu
12/30/2009 5:04:21 PM
Reply:

Similar Artilces:

Send Query To Excel
I am using Access 2003 and have been using the code below to transfer query results to a template in Excel but lately it appears that the Clear contents is not working because the excel spreadsheet contains more records (73) than the query results (64). Does the code look correct: Private Sub Label100_Click() Dim objXL As Excel.Application Dim objWkb As Excel.Workbook Dim objSht As Excel.Worksheet Dim db As Database Dim rs As Recordset Dim intLastCol As Integer Const conMAX_ROWS = 20000 Const conSHT_NAME = "property" Const conWKB_NAME = "P:\Loan Service\Insuran...

Query Miscounting
Hi Guys. Me again. I am busy with a stock control database. I have several tables, the ones that I am having a problem with are as follows: tbl_Stock - Stock Descriptions, reorder levels, etc tbl_Invoicing - When items are booked out of stock tbl_GrvItems - The specific items that are received tbl_GrvSuppliers - Lists what supplier, their Inv num, etc. I Have a query that is supposed to give me the current stock levels (Here is the SQL) --------- SELECT tbl_Stock.Description, Sum(tbl_GrvItems.Qty) AS GRV, Sum(tbl_Invoicing.Qty) AS Inv, Sum(Nz([tbl_GrvItems].[Qty])-nz([tbl_...

Design Forms/Templates in OL2007
Is it possible to design a form or template in OL2007 that can eaisly be called up in place of the default email form? I can't seem to find any how-to to do that? I see forms on the tool bar but have no clue what I'm looking at nor could I find any how-to help? Thanks John Yes. you can either store a template in a folder in outlook and open it or publish it to the inbox. do you have any experience working with custom forms? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outl...

unable to open a query in disabled mode
I just upgraded to MSOffice 2007 and went to open a query. A message "this actioon or event has been blocked by disabled mode" What is that about? If you don't tell A2007 that your db is in a trusted location, none of the code runs. The setting is under: Office Button | Access Options | Trust Center | Trust Center Settings More info on adapting to the new version: http://allenbrowne.com/Access2007.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot...

Cell format newbie query
I have a worksheet where all the cells in a column are formatted as text but there are numbers entered in the column. Changing the format of the column, or even any individual cell, to number, does not remove the triangle in the cell top left corner. What should I be doing to fix the situation? Thanks Brian Tozer Hi Brian! One quick way: Select a blank cell Copy Select the range with data in it Edit > Paste Special Add OK You'll note that this converts the format of the selected range to General -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Exc...

Forms and Controls
I am coding using VBA for Excel 2000. I have a form, on this form I want to have a label (or text box) and 3 radio buttons, each radio button will have different values, 0,3 and 5 for example. What these are are questions with one possible answer, Low, Medium or High (low,medium and High will not always be worth 0,3 and 5). Somehow I would like to have this form read a spreadsheet for the questions and values for the radio buttons. I think if I knew how to create control arrays dynamically this would work. Can anyone tell me how to do that or give me another idea on how to do this. Than...

Send/Receive 12-07-09
In trying to download an urgent email recently at home, Outlook told me that there was in excess of 10Mb of emails, and with a slow connection I realised that the large email was getting in the way of me downloading the smaller, urgent one. I cancelled the download and logged into the webmail version so I could pick up the info I needed quickly. I deleted the emails via webmail, and went on with my work..... Later, expecting a further email, I clicked Send/Receive to discover that Outlook now seemingly 'hangs', like it's looking for the emails that I deleted from...

Criteria is entered in 2 columns, query is only utilizing one set of criteria
Access 2007 I have a flight column and a date column. There are specific flights for specific dates I want to see data for. The query is returning the right flights, but ALL dates, not limited to the ones I want. If I remove the flight criteria, it limits my dates as desired. What is the deal? WHY are the simplest of tasks made so much more difficult in this release of Office??? Why post twice? Folks volunteer their time here, so if no one responds right away, maybe they're busy or don't know or ... Regards Jeff Boyce Microsoft Access MVP -- Disclaim...

Why can't I see my OLE data in form view but can in table view
I have an icon on a subform which I have linked word/pdf documents to in order to view them. This has been performing well until recently and now I can't save any new data in the subform let alone inserting a new file. However when I go to table view the data is in fact there and I can also double click in the appropriate field to view the word/pdf document whilst double clicking on the icon returns ' OLE object is empty' - I'm confused !! ...

How do I change the order that my records appear in in Forms
I've entered all my data, but now I want it to be displayed in Forms in order by a specific field. How can I do that? Create a query. Use the Sort row in query design to specify the sort order. Choose this query in the RecordSource property of your form. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Bethany.Ryan" <BethanyRyan@discussions.microsoft.com> wrote in message news:69CAB1BA-4E89-4569-AC25-38CDFBFBE806@microsoft.com......

How do I make editable PDF forms using Excel?
First off let me say I apologize for not keeping my Microsoft Office skills up to par. I just spent a few hours making a very nice-looking, printable member registration form using Excel. Unfortunately, I now want to be able to convert it to PDF, and make it so that the answer fields are editable in the PDF. ...but don't see how. Is there a way to do that using Excel? (I suppose my first problem is that I need help inserting editable form fields into excel worksheets? It seems to me I used to be able to do that, but I have completely forgotten to do that. I can in Word...but copying...

Emailing from a query 02-08-08
Could someone please lead me in the right direction to get instruction on how to do the following (NOTE: I have both Outlook 2007 and Access 2007 loaded on my machine): I have a query that pulls certain contacts based on a set of criteria. The data results can be anywhere between 10-250 contacts. I want to be able to trigger an email to go out to those contacts from the query. How would I do this? Thanks! Robert Queries don't have events so they are not capable of doing this. You need vba code that reads through the recordset created by the query and sends the desired email...

Creating a Calendar for use in a form
I've created a form within Word that I plan to import into excel. I ha to use word because excel doesn't allow you to use dropdown menus.... I need to know if there is a way that I can add a calendar button, tha people can click on and choose the necessary date from. Can anyone hel me out with this? I am not that familiar with macros....does it requir macros -- Message posted from http://www.ExcelForum.com Hi see: http://www.rondebruin.nl/calendar.htm -- Regards Frank Kabel Frankfurt, Germany > I've created a form within Word that I plan to import into excel. I > h...

Modified Form With VBA
Hi Gurus, I have a client who has had the Sales Transaction Entry form modified with VBA. The developer is now gone. The modification includes a report button on the screen that allows them to generate custom quote, order or invoice print out in Report Viewer-(Windows Internet Explorer browser). The problem is this button works in the main company but not in the new companies that they just setup. For the new companies, the following error message comes up in report viewer: "The 'SOPNUMBE' parameter is missing a value" Any ideas? Thanks. LR It appears the code nee...

query
hi friends i wanted to connect a *.dbf file to visual basic 6.0 what control should i use.......please help me out........... ...

Follow-Up Activities 06-09-06
We used to use Goldmine before Microsoft CRM, and one of the features that I really miss was the ease in creating a follow-up activity. For example, I could create a phone call activity for June 1. When I call, I gather info (and log it in the call), but discover that I need to call again in another month. With Goldmine, I could click a button that would save the current activity, and essentially copy it (along with all the notes) to a new activity. Then all I needed to do was change the date. This was an excellent method to ensure that all opportunities always had a future activity...

Upgrade to 12.0.1 fails even though software is on the volume
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: intel You cannot install Microsoft Office 2008 for Mac 12.0.1 Update on this volume. A version of the software required to install this update was not found on this volume. Count me in on this error. When I go thru AutoUpdate there is no update found. When I download the 12.0.1 updater from Mactopia I can run it, but when I get to the destination screen, my drive has a stop sign under it and the message "You cannot install Microsoft Office 2008 for Mac 12.0.1 Update on this volume. A version of the software required to i...

add employee pictures to form
upgraded to acess 07, need to add employee pictures to form. On Nov 14, 11:36 am, A Cobb <A C...@discussions.microsoft.com> wrote: > upgraded to acess 07, need to add employee pictures to form. Hi. You can go into the table that feeds the form and add the pictures there. In the properties of the field in the table, you can choose "OLE Object". Then pull that field into the form. Once you're in the form, you can copy and paste the picture into the field on the form. Good luck. Dorothy ...

Append Query
Hi All I have a table linked to an Excel spreadsheet, which has formulas in it like If(a2="","",now() ). When i go to the linked table in Access, it shows <>"#Num!" whenever I have the formula and there is no data in A2, of course... But I want to create an append query from this table, and only append records that do not have #Num! recorded. Is there a criteria I can input that says <>"#Num!" that will work for this situation? Hope someone can help, Kind regards Adam The problem is that Now is a numeric field, wherea...

Problem with Xpath query !!!
This is a multi-part message in MIME format. ------=_NextPart_000_002B_01C41FE0.0229B680 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I have some troubles with Xpath when I'm using it with a XmlDocument = containing two namespaces. Here is the XmlDocument : <grammar = xmlns:sapi=3D"http://schemas.microsoft.com/Speech/2002/06/SRGSExtensions"= xml:lang=3D"en-US" tag-format=3D"semantics-ms/1.0" version=3D"1.0" = root=3D"Root" mode=3D"voice" = xmlns=3D"http://www...

Record time report, macro or query runs
I need to track each time a report runs in my Access database, and append the timestamp to a simple table. Along with the timestamp I want to put in the user id (I already have a function that gets this) and the report or macro name that just ran. What is the best way to do this? Use the Open event of the report to record that fact that it ran. In the event, execute an Append query statement that add the record to your logging table. You can use RunSQL in a macro, or Exeucte in code. Here's more detail about how to run an action query, and which is better: Action queries: suppres...

Updating with forms
Using Access 2002. A form based on a query in general allows for data entry. However when I add another specific table and its link to the main source of the query (another table) the query/form prohibits produces a result but does not allow for data entry/update? How do i determine what is causing this? i remove the linked table from the query and the resultant form now allows for updating/data entry. Victor Use "updateable query" as a search topic in Access HELP or on-line. Not all queries are updateable, since Access may not be able to tell from the query's fi...

Query's
Hi. I have encountered the problem of not knowing whether it is possible to assign a macro to a query to change the value it looks for. I currently have a "data entry" sheet that is acting as a day book, and i an "Invoice" sheet acting as a statment page. I would like to create a query to search for a specific value that can be changed, and to search for the amount of "jobs, costs ect." I am finding this very difficult and cannot get it to work. Any help will be greatly appreciated. Cheers Keowaz@hotmail.co.uk ...

change the default opening of a form to enter a new record
How can you stop a form defaulting to record 1 when you want to type in a new record. The only way we are getting round this at the moment is to click on the icon New record. However, every time we open the form to enter another record it is always showing record 1 -- Thank you for reading my post. Hopefully you can answer my querie Shazza - Set the form property Data Entry to Yes. That will default to opening the form for new records. -- Daryl S "Shazza" wrote: > How can you stop a form defaulting to record 1 when you want to type in a new > r...

Need help with a formula 11-23-09
Hello, I have a large worksheet with data (columns "a", "b" & "c"). (I'm using only a few records for reference). Here are the columns: Order # Employee Hrs. 1000 Mike 2 2000 Mike 1 1000 Paul 2 3000 Rose 3 8000 Mike 1 3000 Mike 2 1000 Rose 1 4000 Paul 1 7000 John 3 5000 Rose 2 1000 John 3 6000 Mike 1 9000 Mike 3 1000 Mike 2 I filtered the data of column "a" (Order #) and reflected it on column "d", and sorted in ascending order. Then...