I have a listbox based on a table view ,tables address(id,GroupNumber,fname,… 20 records) and a lookup table (id,title 7 records). No matter which join I try Group ID(Lkup table) to GroupNumber(data table), when clicking the listbox I get All titles from the Address Table(Data tableT, 3/4 "Family2/3 Business, etc…? How can I control the list to only the values in the lookup Table(7). Tried changing field positions in the source view with no change. Any ideas ?
When using a list box, text box, etc., in order to fill the table, a process is needed to retrieve the results. The method to use is either macros or what is known as filtering. Filtering is non programming and the easiest. For the best explanation and numerous samples see this post: click here.
Edit:
That certainly wasn’t clear from your post. I’ve attached a sample. Look at Bike2 form and examine the properties (Data tab). This is basically a query. The other site may be of value to you for filtering (also used in the sample attached).
After looking at your image I believe your problem lies in a missing subform. Look at the structure of the sample in the Form Navigator. Only the list box is on the main form and all the rest are on the subform. Also look at the subform properties to see the connection in the Data tab relating back to the main form.
Thats a lot to read and I will, but, I am able to update the address table its the list box dropdown thats the issue. While I’m reading here are the images.
Also look at the properties of the main form and list box to see how it’s filled.
Answered, tried to explain it but… I tried master slave as you suggest. Had nothing but troubles. Will examine your demo and get back to you. Thanks, Patrick
Bottom line. Main form is set to Filter table. List box data is tied to Filter table but fills from query result. List box selection is saved in Filter table. When button is pushed, subform uses filter to retrieve specific results.
The other problem you may be having is duplicate entries in the list. This can be solved with an SQL change. You can use SELECT DISTINCT... see this site click here.
I’ve got it working OK with a “TableControl” subform. I can also do it with a full field(normal) subform except even with navigation bar selected on the properties, no navigation bar appears meaning that I can only review one entry from a multiple record group.Any way to add a record selector to the subform. When I try to add the control to the subform it does not display or it wipes out an existing field on the subform. Tablecontrol sub is to small ,have to scroll it. Hope I’m making sense.
Added second sample to original answer. Just need to add a Navigation Bar to the subform. Nothing special. Never had problems as you describe when adding one.
It Works.!!! Thank you so much. Patrick…FYI I tried a Select Distinct from that link you gave. It didn’t work. Image.
Ratslinger, At the risk of being a pest, I just wanted to thank you for your help. Considering the depth of your knowledge I really appreciate your willingness to put up with us newbee’s. Now the pest, Thought you might like to see the fruits of your help and 2, Is there a way to get rid of the blank entry in the dropbox and can you remove the main menu from the screen ? Image I will work on my question syntax in future.
Glad to hear it’s working. Blank entry - never figured out a way to remove without using macros. Menu removal - not without macro. See my answer on this post: click here.. Actually your DISTINCT did work. Since you made it with 2 fields combination is distinct. In my second sample, DISTINCT is used for the Color list box.
Understand, I’ll check it out.