Office & Productivity Software forum

General discussion

Confirm data entered in a form in Access 2003

I am setting up a database and I'd really like to make it as professional as possible..I have created two tables with Auto ID fields and a third table which uses a new auto ID and then allows the user to input an ID from the two other tables. I want a way of getting Access to notify you if one of the IDs you entered in the third table does NOT exist in the first two tables...any way of doing that?

Thanks alot
Luke

Discussion is locked
You are posting a reply to: Confirm data entered in a form in Access 2003
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: Confirm data entered in a form in Access 2003
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
Re: Access

In reply to: Confirm data entered in a form in Access 2003

Two ways to do it. Both assume you're using a form to enter the data. It's not really professional to have users enter data into a table directly. If that assumption is a problem, please post back.

1. Use a dropdown-box (combobox) for the field in table #3. Set it's property "Only list" to true.
2. Code a BeforeUpdate event procedure on the field using a dcount, for example, to check if the entered value is present in table #1 or #2.

Hope this helps.


Kees

Collapse -
re:re:

In reply to: Re: Access

I am using a form yes Happy I think I'll go for the 2nd option coz using a combo box would mean having to list down all the options available. What exactly do you type in to get it to check that the value exists? coz that's exactly what I want..

Thanks alot Happy

Collapse -
Re: checking that a value exists

In reply to: re:re:

Sorry, I don't supply free VBA-code.

The function to use is dcount (with the right parameters); if that's zero give an error message using msgbox and set the predefined parameter cancel to true (or -1).
I use the Me-object to reference the current form, and the current field just shows up in it's properties.

Hope this helps.


Kees

Collapse -
And what's wrong with a combo-box?

In reply to: re:re:

It can reference tables #1 and #2 to show the exact current list, and the meaning of the code as defined in the "description" field in that table (you can even sort on that). Nothing to setup. Always current, easy to use.
Much better than have the user type in a meaningless number without even giving back what it really means and only confirming it exists.

Maybe study a book about professional user interface design?

Kees

Collapse -
no coz

In reply to: And what's wrong with a combo-box?

its coz the numbers to be typed in are automatically generated ID numbers and there are loads so it's better if you simply type it in and make sure it exists than choose it from a list coz that would take ages and alot of scrolling.

thx alot for ur help Happy
Luke

Collapse -
In a combobox ...

In reply to: no coz

you can type the value you want (no need to use the mouse or even choose 1) and Access will automatically check if it exists and give an error message if i doesn't.

Your choice to code it yourself in VBA. Maybe even better, if it's a large list. That's OK with me, I'd do the same. But I know VBA. And it seems you don't at the moment. That will be a useful learning experience.

Kees

Collapse -
it's ok

In reply to: In a combobox ...

it's ok i've managed it Happy thanks ALOT for your help hehe

Luke

Collapse -
Good job.

In reply to: it's ok

Glad to see you managed it all by yourself.

Kees

Popular Forums

icon
Computer Newbies 10,686 discussions
icon
Computer Help 54,365 discussions
icon
Laptops 21,181 discussions
icon
Networking & Wireless 16,313 discussions
icon
Phones 17,137 discussions
icon
Security 31,287 discussions
icon
TVs & Home Theaters 22,101 discussions
icon
Windows 7 8,164 discussions
icon
Windows 10 2,657 discussions

SMART HOME

This one tip will help you sleep better tonight

A few seconds are all you need to get a better night's rest.