Help please _ Various Combo Boxes within Continuous Subform

[burgerpj]

Hi

I am struggling no for a few hours (2 days) to beat this problem I am having. I have searched everywhere and can't find an answer. Please help if you can.

I have various comboboxes within a continious subform where you select a main field, and then it should let you only select the relent fields below it.

Example:
1.Main Product (HS_level1): Live animals
2.Sub Product (HS_level2): Live sheep and goats
3.Product (HS_level3): Sheep, live
...other fields on amounts, etc.

Adding the first product to the list is easy. Query I use:

Private Sub HS_level1ID_AfterUpdate()

'make next HS levels empty and requery the list of products

Me.HS_level2ID = Null
Me.HS_level2ID.Requery
'the query that is been run selects only HS_level2 that are linked with HS_level1

Me.HS_level3ID = Null
Me.HS_level3ID.Requery

'jump to next HS_Level field
DoCmd.GoToControl ("HS_level2ID")



End Sub

But when I add the second product to the CONTINUOUS form, all hell breaks loose and the previous record's values also gets reset.

How can I ensure that VBA only runs for the selected record?

Sincere thanks for all the help. Pieter

[aytee111]

What is the record source for your subform?

[burgerpj]

Hi 'aytee111'

The use of the database is to issue permits for agricultural exports.
Permits is the Parent table
PermitsDetails is the Child table (the continuous subform)

The HS_level fields (products) is in three different tables: HS_level1, ...2, ...3

Within the subform HS_level1 is linked directly to its HS_table. HS_level2 is linked to a select query which selects the HS_level2 values with the chosen HS_level1 parent. The requery thus reruns the query when a parent category is selected.

The select query works 100% for the first product I enter, but with the second one all goes beserk.

Thanks, Pieter

[aytee111]

When trying to add records to a table, the record source for the subform needs to be that table, not a query - you can't add records to a query. You have Permits, PermitsDetails, then 3 HS_Level tables. To which are you trying to add records? Make that your subform record source, then have the combo boxes as before.

[burgerpj]

Products (HS_Codes) must be added to Permits.

Tables as follows:
1. Permits Table
---ID
---Company
---Destination
---Date

2.PermitDetails
---ID
---PermitID (linked to Permit.ID)
---HSCode1ID (lookup HSCode1.ID)
---HSCode2ID (lookup HSCode2.ID)
---HSCode3ID (lookup HSCode3.ID)
---Quantity

The PERMIT_FORM where details are entered works as follows:
---HSCode1ID: ControlSource = HS_level1ID, RowSource = HSCode1Table
---HSCode2ID: ControlSource = HS_level2ID, RowSource = HSCode2SelectQuery
---HSCode3ID: ControlSource = HS_level3ID, RowSource = HSCode3SelectQuery

The RowSource is still the normal value, but your option to select in the combobox is from the query that gets requery on the change of the above value.

I wish the VBA would not apply code to all the products currently added, and only to the one that I am currently busy with. Can you maybe give me an idea how code for .currentrecord will look?

I can also email you the database (with sensitive data removed) if it would be easier?

Thanks again, Pieter

[aytee111]

Sure, attach the database

[burgerpj]

Hi, original database still at work. This is just a previous version. Had to delete sensitive data. The form where the problem is will automatically load on startup. Thanks.

[aytee111]

I guess I don't understand your sequence of events. I go to add a new record and the combo boxes work just as they should. If I go back and change the hs1 field it resets the other combo boxes just fine. So I need more detailed explanation of what exactly happens when you say that data gets changed on other records.

[burgerpj]

I attached screen shots of what is happening in a Word file. When you (I) select the HS_Code of the second (or any other) product to the permit it runs the VBA code for ALL records. That's seems to be the main problem. If only I could tell it to run it only for the current product (exportbilldetail) record? Thanks again. Hope we can solve this one.

[aytee111]

Duh - didn't notice that, sorry - will check it out.

[aytee111]

I absolutely cannot get this right at all! I will continue trying to figure it out, but you may want to start another thread with a different subject line, maybe someone else will have the answer.

[aytee111]

I guess you're on a different time zone! Oh wow, you're in Stellenbosch. I'm from Cape Town, now in Mississippi. Have a glass on me - I miss the good stuff.

Anyway, here's all I can figure out, very convoluted. All the links are a headache. You still might want to reach out to someone else like I suggested before (while you were sleeping!). I won't be offended in the slightest.

I made the subform a single record at a time only, I made a list box for you to click on to make changes to existing records, with an Add New button to add a new one. Your form was missing a level, by the way, the table between Companies and Details, so I added that. I didn't make it pretty in the slightest, I left that to you to do the finer details.

[burgerpj]

Hi. Thanks for the response. On phone internet now, so will check file as soon as I am in office again (Friday). I also thought of adding a single record with form as you suggested. Seems great minds do think alike. I'll drink to that! Keep well and I'll let you know about my findings.

[burgerpj]

Dude!!! Just got back at office and what you did is truly awesome! It actually is much better and more user friendly than the first record. Thanks a million. I hereby declare this thread 'solved'.

[aytee111]

Lekka!

This is something else that I did: I removed all lookup's that were on the tables. For me, I like things to be under my control, i.e. on forms, reports and queries I am the one who must decide what to display and how to make it work. Putting combo's on the tables confuses the issue - where is the problem, is it the table, the form, where? So I just remove them all and - to me - it makes it easier to troubleshoot.

If you can't figure out what I was trying to achieve, you know where I am.