Microsoft Access 97 Lookup Tables 1

[0212]

For data Entry: I want to restrict a lookup table's choices to only a portion of the list using a link from a field in the form. Is it possible? The lookup list is too big.

 

[coco86]

Try setting the rowsource for the lookup to:

SELECT [Field] FROM LookupTable WHERE ((([LookupTable].[FilterField])=[Forms]![FormName]![Textbox]));

 

[0212]

Thanks, coco86! However, I can't seem to get it to work. My restricted lookup table is blank! Could you be more specific as to what to do? Thanks, Again!

 

[coco86]

Okay, I had that problem the first time I tested it. When it didn't work [Forms]![FormName]![Textbox] was referencing an unbound field and there was no recordsource for the form. I set the recordsource for the form and it worked.

 

[0212]

Hi, coco86! Its still not working! My form is already bound since I created the form with the form wizard. Could you check the syntax? SELECT [Core Assess].[Core Assess Code], [Core Assess].[Assessment Description], [Core Assess].[Core Type Code] FROM [Core Assess] WHERE [Core Assess].[Core Type Code]=[Forms]![Core Assessment Evaluation1]![Core Type Code];

Note: The lookup table is called Core Assess and the form is called Core Assessment Evaluation. The only other thing is that the textbox input field called Core Type Code in the form is a combo box and also comes from a lookup table (Core Type). Thanks for all your help!

 

[0212]

Hi, coco86. I'm getting closer! I was able to get the conditional lookup to work. However, it only works once when I open the table for the first time. Whichever record the table opens to is the value thats used to restrict the lookup table. When I try to add additional records from the form, the lookup table is stuck on the original value. Any thoughts? Thanks for all of your help!

 

[coco86]

Is there a combobox on the form for the field with the lookup? If so, try putting code in the oncurrent event

me.comboboxname.requery


You may need to refresh

I don't know if you can do this at the table level.

 

[coco86]

oops, just reread this and what I meant was to put the code in the gotfocus event of the combobox not the oncurrent event

Sorry

 

[0212]

Hi, coco86! Thanks for all your help. However, three things. First, which combobox should I put the code into (the lookup table field that I need to restrict or the restricting or filter field lookup table? Second, when I try it (I put it in the restricting field), an error message came up. It is looking for a Macro name "me". (The code that you gave me is me.comboboxname.requery). We're getting closer! Also, I don't know how to refresh or do I need to? Thanks.

 

[coco86]

Put it in the event for the lookup table field that you need to restrict

You need to put this code in the event procedure. Select "[event procedure]" from the dropdown box. Click the button to the right with the "..." and then when the code module comes up, put it there.

if you need to refresh you will add one of the following lines of code after the previous line

me.refresh
me.combobox.refresh

I don't think you're going to need either one, though

 

[0212]

Hi, coco86! It works!!!! I did have to use the refresh code, though. Thanks for all your help!