Validation Rules in Access 2000

Afternoon,

Apologies if this topic has been covered but i struggled to find it if
it has. I'm a bit of a novice in Access so i was hoping someone could
help me.

At our warehouse we house alot of products from different companies,
when they come in the door we would have pallets of the same stock and
they would have a Product Code then "x" amount of days later some
stock would go out to a store. We have a basic DB to keep everything
in order but we are having problems with human error. i.e. a product
code might be 12345-A and the inputter might put 12345/A or even type
it wrong all together i.e. 122456-A.

I've been looking at various input masks and validation rules to
combat this situation. We have a list of the product codes that we
will have in and i was wondering if anyone could advise me if i could
put a validation rule in so that if the inputter tries to enter a
product code not on the list of valid product codes it wont let him/
her.

I've looked at the DLookUp function but had no joy.

Any help at all would be appreciated.

Thanks

Ross

0
rcoombe
11/5/2007 3:05:57 PM
access.forms 6864 articles. 2 followers. Follow

2 Replies
796 Views

Similar Articles

[PageSpeed] 19

How many different products do you have? If it's a reasonable number (a few 
hundred, say), you might be best off using a combo box rather than a text 
box. Set its LimitToList property to True, and they'll only be able to 
select values that are in the list.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


<rcoombe@amethystgroup.co.uk> wrote in message 
news:1194275157.955525.62880@o80g2000hse.googlegroups.com...
> Afternoon,
>
> Apologies if this topic has been covered but i struggled to find it if
> it has. I'm a bit of a novice in Access so i was hoping someone could
> help me.
>
> At our warehouse we house alot of products from different companies,
> when they come in the door we would have pallets of the same stock and
> they would have a Product Code then "x" amount of days later some
> stock would go out to a store. We have a basic DB to keep everything
> in order but we are having problems with human error. i.e. a product
> code might be 12345-A and the inputter might put 12345/A or even type
> it wrong all together i.e. 122456-A.
>
> I've been looking at various input masks and validation rules to
> combat this situation. We have a list of the product codes that we
> will have in and i was wondering if anyone could advise me if i could
> put a validation rule in so that if the inputter tries to enter a
> product code not on the list of valid product codes it wont let him/
> her.
>
> I've looked at the DLookUp function but had no joy.
>
> Any help at all would be appreciated.
>
> Thanks
>
> Ross
> 


0
Douglas
11/5/2007 3:26:27 PM
DLookup would be one way to do it; however, a better way would be to change 
the text box where they are entering the code to a combo box.  The combo will 
not accept a value that is not in the list if you set the Limit To List 
property to Yes.
-- 
Dave Hargis, Microsoft Access MVP


"rcoombe@amethystgroup.co.uk" wrote:

> Afternoon,
> 
> Apologies if this topic has been covered but i struggled to find it if
> it has. I'm a bit of a novice in Access so i was hoping someone could
> help me.
> 
> At our warehouse we house alot of products from different companies,
> when they come in the door we would have pallets of the same stock and
> they would have a Product Code then "x" amount of days later some
> stock would go out to a store. We have a basic DB to keep everything
> in order but we are having problems with human error. i.e. a product
> code might be 12345-A and the inputter might put 12345/A or even type
> it wrong all together i.e. 122456-A.
> 
> I've been looking at various input masks and validation rules to
> combat this situation. We have a list of the product codes that we
> will have in and i was wondering if anyone could advise me if i could
> put a validation rule in so that if the inputter tries to enter a
> product code not on the list of valid product codes it wont let him/
> her.
> 
> I've looked at the DLookUp function but had no joy.
> 
> Any help at all would be appreciated.
> 
> Thanks
> 
> Ross
> 
> 
0
Utf
11/5/2007 4:15:07 PM
Reply:

Similar Artilces:

Access and Daye Formats
Is there any way to make Access overrife the computer regional settings for date formats. I am having problems with mdbs using Canadian date formats DD/MM/YYYY when installed on systems that have US date formats in regional settings MM/DD/YYYY. A lot of Canadians it seems simply leave the US defaults on preinstalled Windows and it causes dates to be screwed up. Any help appreciated. Thank you. Ray Ray, take a look at: International Date Formats in Access at: http://allenbrowne.com/ser-36.html It discusses the 3 cases where Access is likely to misunderstand our d/m/y date set...

Can't connect to Exchange server after VPN access
I can connect to my office Exchange server when in the office (LAN) but I cannot connect outside with Outlook. I can only use the webmail after entering a web VPN access: - First portal for VPN : https://xxx.yyy.com --> I enter my user_VPN/password_VPN - On the next web page, I have the choice for the webmail and it's a link as https://xxx.yyy.com/go/webmail.yyy.com~ssl where I can enter my user_mail/password_mail So I can't enter the url in RPC as every slash is forbidden In my Outlook, the Exchange server (EXC.yyy.com) is not reachable by ping (or tracert) and I can't ...

Access 2007 12-17-09
I am building a contact data base for my church. How do I get the phone field to automatically format like this (xxx) xxx-xxxx when the numbers are typed in the cell? -- Thank You In the form design view select the phone control and open the properties dialog. Goto the Data tab and use the Input Mask to define the format you wish to use. If you click on the ... to the right hand side on the property you will get another dialog where there are predefined input mask or you can define your own. For instance, I use !\(999") "000\-0000;0;_ to format my telepho...

Language Support for all Office 2000 programs
Maybe I'm not looking in the right places, but I'm not finding any language support downloads for Office 2000. Does anyone have a specific link to send me to, so that I can do this? I have important documents that I have to print, but I recently got a new PC with 64-bit Vista. I loaded my 200 Office Suite on the PC, but it no longer is supporting the foreign language that I have throughout my documents. I just get block shapes instead. I need help. Thanks. Start here Deploying Office 2000 with Additional Language Resources http://office.microsoft.com/en-us/ork2000/HA011383...

Outlook 2000 #196
I have several computers with Outlook 2000 on them. When they try to exit outlook, outlook just hangs there and will not exit. Anyone have any suggestions on fixing this. Thanks Cindy <anonymous@discussions.microsoft.com> wrote: > I have several computers with Outlook 2000 on them. When > they try to exit outlook, outlook just hangs there and > will not exit. Anyone have any suggestions on fixing > this. Thanks See if something here helps: http://www.howto-outlook.com/Faq/outlookdoesntclose.htm -- Brian Tillman ...

re install Pub 2000 problem.
Hello and I hope I'm In the right newsgroup. My problem is I've had to re install my Office 2000 Disc2 and now when I want to start publisher the message box tells me I am trying to access a program that needs the cd. When I installed it, I checked for Publiher to "run all from my computer" I've un installed and re installed, still need disc. what am I doing wrong? Thanks for your help, John :) Johnny1r wrote: > Hello and I hope I'm In the right newsgroup. My problem is I've had to > re install my Office 2000 Disc2 and now when I want to start publis...

Remote access to another company's Outlook calendar?
Hello! I have one client on Exchange 2003 that wants to access the calendar of an employee (consultant?) at another company running Exchange (version not known yet). How can the remote company share this person's calendar with my client? He would need to access it and add appointments to it. Thank you for the help! Gregg Hill On Mon, 30 Aug 2004 22:15:24 -0700, "Gregg Hill" <bogus@nowhere.com> wrote: >Hello! > >I have one client on Exchange 2003 that wants to access the calendar of an >employee (consultant?) at another company running Exchange (version no...

Exchange 2003 SP1 to get DST patch & Exchange 2000 patch is available to anyone for $4,000
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C746B0.CFF31030 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable http://blogs.technet.com/hied_west_blog/archive/2007/02/01/dst-2007-updat= e-outlook-time-zone-data-update-tool-released-february-1-2007.aspx In the "New Highlights" section: "Exchange Server 2003 SP1 will now be = supported for an updated version of the Exchange CDO patch to be = released shortly." and "DST Hotfixes for products in Extended Support can now be purchased for = ...

Error 2455 Closing Access 2007 database with form open
I have a form with a subform that is requeried when you select a new key for the main form from a combo box. Everything works fine - usually. But sometimes if you have the form open when you close the database down you get the following error message (twice) in a pop up. You say OK (twice) and the database closes OK "2455 you entered an expression that has an invalid reference to the property form/report" If I close the form before the database I never get the error. If I do not touch the form before you close the database I don't get the error. If I update a field by t...

Inserting Excel into Access Reports
Office XP Have a great Access application that produces a nice template (headers & footers) report into which I'd like a spreadsheet inserted before going to the printer. In the past, I'd just print the Access reports, then reload them into the inkjet printer and run the Excel spreadsheets as needed. The heat of the new color laserjet turns the paper grey if it runs through too often, so it's time to get the reports printing on one pass. Any suggestions would be welcome. I've of course also got Word XP, MS Publisher XP, as well as Adobe Acrobat, if anyone thinks it m...

Simple Access counting queries
Hi, hoping someone can help a relative newbie with a pretty simple query. My database (Access 2007) has three tables: Customers Products Purchases (many-to-one links to both of the other tables, this is basically a linking table) I have two simple queries I'd like to get out of this database, but I'm a bit stuck on how to construct the SQL. Any direction you can give me would be helpful. 1. List of all customers who have purchased 2 or more products (or 3 or more products, or 4+, etc.) 2. List of all customers who have purchased both Product A and Product B (or A, B, and C, or B an...

Converting Money 2000 Canadian to Money 2006 Canadian
Hi, Can anyone confirm that such a conversion is possible? I was hoping to convert to Money 2007 but was unsuccessful. I'll live with Money 2006 Canadian but I want to ensure the conversion is possible first. I don't want to drop the money on it to find out it doesn't work. Thanks in advance, In microsoft.public.money, smurfgun wrote: > >Can anyone confirm that such a conversion is possible? I was hoping to >convert to Money 2007 but was unsuccessful. I'll live with Money 2006 >Canadian but I want to ensure the conversion is possible first. I don'...

Expression Too Complex in Access 2000
Hi, Consider the following query: SELECT crTbl.acct_1, crTbl.amount, crTbl.date FROM crTbl WHERE (((crTbl.acct_1)="Supplies") AND ((crTbl.date) Between [Forms]![crReportOptionsFrm]![startDate] And [Forms]![crReportOptionsFrm]![endDate])); The query works fine on my own computer in Access 2002. When converted to Access 2000 and used on an other computer, I get the following error: "This expression is typed incorrectly or is too complex to be evaluated. Try simplifying the expression by assigning parts of the expression to variables." The problem is with the "Between...

Don't want to automatically open email Outlook 2000
I have recently started using Outlook 2000 instead of Outlook Express. In Express - I was able to preview messages without actually opening the file. In OL2000, the file automatically opens up... is there any way of preventing this. ...

How to get only the year in the date format in Access
How to get only the year in the date format I.e in the table in need to display only year E.g 2005 - should be display " 05" automatically Custom format the cell as: yy -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "yanu" <yanu@discussions.microsoft.com> wrote in message news:14CE9F60-F7B9-467A-8C16-71088C31BEBA@microsoft.com... > How to get only the year in the date form...

groups detail section totals access 2003
Hi all, I know this can be done, but haven't figured out how yet. I have what basically is a summary report that my sql comes up with for the detail rows. I want to total these rows in the report and display immediately below the detail section. I don't really want to group anything, but want to treat the whole detail section as a group. That being said, how can I get a "group footer" on the designer so I can add my total columns. If I use "sorting and grouping", it starts grouping things and that is not what I want. I don't want to use the "page foo...

Help with conditional formatting with 2000
Any help would be greatly appreciated. I am trying to group data together into increments of 10% of th numbers and then chart them based on these groups. For example, I hav 300 data points that vary from 20 to 500 in value. I want them t appear in a chart based on the number of values that fall in the lowes 10% of numbers (ie. 20-40) then the next 10% (ie. 40-60) etc. up to th top 10% of numbers, but I do not want to manually determine what thes ranges are. I want to see a distribution of how many numbers fal within each 10% of values. I am not sure if this makes sense, please let me know...

How OMPM Scanner (offscan) Filter by Access/Modified Date ?
Hello, I have problem to inventory excel files on very big file server, but I believe there are so many documents we no longer need to maintain. I want to skip files if the access date or modified date longer than 6 month, but don't see the OMPM providing feature about it. I currently running OMPM since 2 weeks ago and running out of time for reporting to my manager. Please help me, this is my critical assignment. -- Eldi Munggaran ...

Is it possible to generate non-technical schema validation errors?
With the 1.0 Framework, I've worked out using the XmlValidatingReader. Since I'm using the validation errors as feedback to the end user, I'm hoping to get away from techy messages such as "The 'http://tempuri.org/XMLFile1.xsd:MaxDependents' element has an invalid value according to its data type. An error occurred at file:///c:/work/prodika/main/code/apps/schemavalidation/XMLFile1.xml(8, 25)." and go with a user friendly message of "Max Dependents must be between 0 and 10". I've scoured the newsgroups, MSDN and docs for creating custom valid...

How to create an "and" rule in Query Based Distribution Groups
Hi, With Exchange 2003 Query Based Distribution groups, is it possible to create an "and" rule? ie, all users who are based in "London" "and" have the first name "John"? Thanks, Curtis. -- Please reply to news group only. Thank you. Sure. (&(attribute1=blah)(attribute2=blah)) http://msdn.microsoft.com/library/en-us/adsi/adsi/search_filter_syntax.asp?frame=true -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------- "Curtis Fray" <xxx@xxx.com> wrote in message news:OjVc...

comdlg32.ocx and Access 2007
I have several Access databases that were originally written using an Access version prior to Access 2007. I am in the process of converting the databases and installing them on new machines running Win7 and Access 2007. Over the years, one of the References I commonly used was comdlg32.ocx. It does not appear that either Win7 or Access 2007 installs that particular Active X file. I can copy it from an older machine, but that seems like a strange solution. Is comdlg32.ocx a "legacy" Active X file and has it been replaced with a newer (and differently named) Activ...

Signature button greyed out and unavailable in outlook 2000 solution
Buggy fucking microsoft software, and yes, it is patched. Well this worked for me :- change the message format from rich text to html, click apply. This brought the button back from being greyed out to letting me click on it again. However i did not want html as the message format so i changed it back to rich text again and the signature button was still available. Hmm where does it say THAT in the manual? Oh and in case you don't know where to change the message type it's on the same menu that you have your greyed out signature button - just look up a bit dummy! :0) FOR THE LOVE...

Outlook admin vs individual access (VBA pulling info)
I'm not looking for a specific solution, just information on whether or not this is possible, and if so, some general leads on what I'd need to learn more about in order to develop and implement. I have a VBA macro that grabs appointment data (time/date/duration/subject/etc). As an individual user, it only pulls this information from my calendar. We are considering a project where employees would start using custom labels to document their time on different projects, and ideally we would be able to pull all of that data at once (not require each user to pull their own...

HELP! Need Access DB to show realtime changes in different location
Hi Guys, I was wondering about how access refreshes it's data within forms and it seems that it doesn't. If i have a form open in one location on a network and update fields on the form the changes are not shown on the form opened in another location. (I.e. if 2 users are in the same form at the same time and one adds a new record the other users form will not show this change). Is there anyway that Access can be setup to show real-time changes over multiple locations? Preferably without having to do a manual refresh by clicking a button or something. All i want to do is show change...

publisher 2000 photo editing
I have a bmp file inserted in to a Publisher 2000 document. I want to lighten the entire photo so it is just a background with words on the top of it. I can't find any contrast or watermark or anything like that in my software. Everything changes it to a lighter shade of just one color. I want to keep all the colors, just make the entire image really light. Did you try using a photo editor first? -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "stitchesbymindy" <stitchesbymindy@discussions.microsoft.com&g...