How to force someone to use a value in listbox in datasheet view?

[DeMarcus]

How to force someone to use a value in listbox in datasheet view? [NEW Problem]

This question seems so simple, I feel like the answer is right in front of me. However, I have no idea how to solve this problem.

Essentially, I have a field in a table (or data entry form in datasheet view) that looks up values from a different table. I want the user to only be able to use one of those values, not type in whatever he wants. How do I accomplish this?

 

[CraigDolphin]

In your form, use a combo box as your control for that field, and set the Limit to List property to yes.

 

[DeMarcus]

In your form, use a combo box as your control for that field, and set the Limit to List property to yes.

..oh gees. Thank so much!

 

[DeMarcus]

Okay, I've got a related problem.

While limiting it to the list works if the person tries to type in something, if someone paste appends records (i.e. from an Excel file), it still allows items that are not valid values in the listbox.

How can I remedy this situation? This is important as the functionality of the data input depends on a user dragging and dropping in stuff from Excel.

 

[CraigDolphin]

You could try the following ...
In your table's design view, click in the field. at the bottom of the view there're two tabs: one general and the other lookup. Click on lookup. Set the control type to combo box and define your rowsource as appropriate. Then set limit to list to yes.

I'm not really familiar with drag and drop data input from excel though, sorry.

 

[DeMarcus]

Okay, I'm thinking I may just have it check on Form_UnLoad to see if the value in the field is also present in the lookup table.

Can anyone help me with this?

 

[DeMarcus]

Okay, I'm thinking I may just have it check on Form_UnLoad to see if the value in the field is also present in the lookup table.

Can anyone help me with this?

For example, the data entry form uses the table TableSales.

TABLESALES
Product Sales
A 2000
B 4000
C 3000
Df 2000
E 7000

I want it to check the Product field to make sure it exists in the table TableProducts

TABLEPRODUCT
Product Description
A Apples
B Bananas
C Cherries
D Dairy
E Eggs


As you can see, the person who was inputing into Table made a typo and put Df instead of D. Through VBA (whether it be the form unloading or a button), I want it to check the validity of the field in each record and then tell me that Df is not valid. Any help?

Thank so much in advance.

 

[CraigDolphin]

This may not be the way you want to go, but why not do this check the values as they input that data in excel rather than after the fact in access?

 

[jkl0]

You can use the Validation Rule & Validation Text properties of your text box to trap the invalid input.