Selected data in subform disappears

[melanie]

Hoping you could help me with this problem. I have a form which has a subform and the subform has a subform of its own which is a datasheet. The problem is this:

In the last form, the datasheet, when I select an item from the 2nd combobox (its value its dependent on the 1st combobox) in the first record all is fine, but when I go to the second record and select an item in the first combobox the value in the second combobox of the first row disappears.

Your help would be much appreciated.

Melanie

 

[melanie]

Synchronizing Multiple Combo Boxes

I've managed to solve the previous problem using the MS Knowledge Base Article 289670 - Synchronizing Two Combo Boxes.

To my dismay another problem has appeared, in that there are multiple combo boxes (5) each one is dependent on the value listed in the previous one. The above mentioned solution has solved the "disappearing values" but I don't know what code to insert in order to get the other combo boxes working.

I'm new to this and this is my first "major" database.

Your help would be much appreciated (understatement).

Many thanks

Melanie

 

[Jack Cowley]

I am unclear as to what is not working for you. You have the code to do 'cascading' or 'synchronized' combo boxes so what should happen with your combo boxes that is not happening?

You can also take a look at this article as it mentions Requery which may be part of your problem.

Jack

 

[melanie]

Jack thank you for responding.

I'll give you the outline of the database and the hassle I'm having at the moment.

It is a shipping program - each consignment has a Document number and per consignment there are 1 or many containers - each container has its own Id Number. Per container there are a number of pallets. These pallets have drop down lists so the Product (Grapes) can be selected and then the Variety (Sunred Seedless or Red globe, etc. So the second list is determined by the selection in the first. The Varieties have different Packaging and the Packaging Lists the different Counts of the fruit (sizes), the counts determine the number of cartons packed on a pallet.

I have a table for the Document Information - vessel, eta. customer, etc. This is the basis for the main form.

The subform in the main form has the container details - container number, delivery note number, seal number, temp recorder number.

The above container subform then has a subform of its own which is the pallet details - this is in data sheet view.

I initially had the problem when selecting the Product from the combo list in the second and subsequent records the Variety value vanished, although it was present in the underlying tables, the "Ghost Data" of which I have read in the forums posted messages. I followed the steps given on synchronising two combos and had success with it - but i don't know how to set the code for the packaging, count and ctns per pallet fields. Each one of which is dependent on the previous selection.

I have been back and forth through the forums messages, the MS Knowledge Base as well as my text books and have had little success with how to set up Multiple Synchronised Combo's.

Any advice would be valued.

Melanie

 

[melanie]

Synchronising Multiple Combo Boxes

Jack

I have printed the Ms Knowledge base articles 98660 and 209576 and will follow the steps now and let you know the results.

Thanks again.

Melanie

 

[Jack Cowley]

Melanie -

If you can't get your 'synchronized' combos to work let us know. If you can create a small demo of that portion of your db that you can't get to work I am sure that there is someone here that can take a look and help you find a solution.

One thing that I am not clear on and that is how the combo boxes figure in with form/subform/subsubform? Selecting an item in the main form should show you the correct items in both subforms if your tables are properly joined and your forms/subforms are properly linked. Maybe there is no connection between the forms and the combo boxes, but I want to cover that area as well.

Good luck!

Jack

 

[melanie]

Multiple synchronised combos

Jack

I'm not having any luck, I've followed the various steps and am coming up with the same situation. I'll attach the zipped Db - take a look at the "Pack List" form - the subform containing the Product variety and so forth. I've been stumbling back and forth trying out different code and examples and I'm so confused by now, I'm not really sure where I am. I have tried the following code as well and then gone back to one of the posts by Lancer (the step by step on creating combos).

I have altered the pack list and left off a few items and the sub sub form is now in form view and not in datasheet and this seems to make no difference to the "ghosting".

your kindness means much to a tired, fustrated lady!!!

Melanie

 

[melanie]

Jack - oops forgot the Db

 

[melanie]

Sorry, try again, connection seems to be dwindling.

 

[Jack Cowley]

Melanie -

I have your database and after looking at it I am unsure of just what the problem is. If I select DOC NO ID2172 on the Main form then the data in the subforms works as it should. If I click in the Container Number control in the first subform the second subform shows the correct data. Could you kindly step me throught what you are doing so I can duplicate the problem? If it would be easier for you you can email me directly at jpcowley@aaahawk.com.

Jack

 

[melanie]

Synchronising combos

Jack, thank you ever so much for you quick response.

Firstly after going back to the zipped file I sent you I noticed that when I had compacted it, the DocNo relationship between the "Containers" table and the "Pallet" tables had been removed, there is some error listed to the effect. I'm not sure why it did this, but it is not really relevant anyway to the actual problem.

Ok, the problem is here:

In the packing list - the pallet details form - where the record starts with pallet no. The variety field is dependent on the selection in the product field, so say for instance I select "Pears" my choices in the varieties would be "Rosemarie" and "Forelle"
all is fine until I go to the second record and select "Grapes" for instance - then the variety listed previously disappears. Play around with reselecting the product and see how unstable the varieties coumn appears.

I'm also wanting to know how to link up my other combos as I have the instructions to Synchronise two combos, but not multiple combos each of which is dependent on the previous one's selection, but I need to get past the "ghosting" first.

I am running Access 2002.

Kindest Regards
Melanie

 

[Jack Cowley]

I only have Access2000 but I was able to import the data from your db and I am able to get it to work.

Thank you for the explanation on which combo boxes are giving you the trouble. I will look at it shortly. As for the other 'synchronized' combos what you can do with 2 combo boxes you can do with more. You just apply the same technique to the next combo box. Let me see if I can explain....

Combo box 2 is based on a query where the query gets its criteria from combo box 1. Combo box 3 would be based on a query that gets its criteria from combo box 2. So to carry it out to combo box 6 it would be based on a query that gets its criteria from combo box 5.

Your SQL for the query might look like this for combo box 2

SELECT [CONTAINER DETAILS].*, [CONTAINER DETAILS].[DOC NO]
FROM [CONTAINER DETAILS]
WHERE ((([CONTAINER DETAILS].[DOC NO])=[Forms]![MyForm]![ComboBox1]));

It may be an hour or so before I can get to your problem with the combo boxes so don't give up on me. Please feel free to email me directly.

Jack

 

[ice-9]

i have the same problem.
is there a way aroud it ?

i have 2 combo boxes on a datasheet.
combo 2 is depedent on comboxbox 1.
But when i work on futhter and click other occurenses, the values change (because of the query looking at the first combobox)

this is very annoying.
can someone pleeeeease help,
ive been trying for 2 days now

 

[ice-9]

please help, its urgent!!!!

 

[ice-9]

please help, its urgent!!!!