Cascading combobox with tempvar check (1 Viewer)

hugomadail

Registered User.
Local time
Today, 00:31
Joined
Aug 31, 2018
Messages
25
Hello, I have a form with two cascading comboboxes - cbA e cbB, where cbB is filtered with cbA choice (all works well here).

BUT the problem is because I'm trying to make a messagebox to warn when cbA is changed, so I used cbA before update event to make a tempvar about old record in cbA, then a cbA after update event to check if new record is the same as tempvar or not, also checks if cbB has record or is null. the code I'm using:

Code:
Private Sub cbA_BeforeUpdate(Cancel As Integer)
TempVars!FrOld = Me.cbA.Value
End Sub

Private Sub cbA_AfterUpdate()

If [cbA].Value <> TempVars("FrOld") Then

    If Not IsNull(cbB) Then

        If MsgBox("you chose to change cbA value to another and theres already a record on cbB, do you validate changes and clear cbB?", vbYesNo + vbQuestion, "Warning") = vbYes Then
        [cbB].Value = Null
        End If
    End If
End If
End Sub

So "Private Sub cbA_AfterUpdate()" should be checking if new cbA value is different than FrOld (old cbA value) and also cbB cannot be null! I think problem is with "If [cbA].Value <> TempVars("FrOld") Then" part because if I use a number (ex: 19) instead of tempvar, the code works. the tempvar seems to be well defined because I placed a independent checkbox on form to display tempvar value and it shows the value!

I'm not a programmer so I did this code by trial and error, but now I don't know what I'm doing wrong.

Can someone help me with a solution?
 
Last edited by a moderator:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 00:31
Joined
Jul 9, 2003
Messages
16,245
I've held off responding to your post for a while because I thought someone else might see exactly what the issue is, and respond.

I haven't seen anyone attempt to do what you're doing before. That would indicate to me that maybe there's a different way of achieving what you want to achieve.

Normally the first combobox will filter the selections provided by the second combobox. This would mean the first combobox would be unbound in other words the value is not recorded, as you don't need the value from the first, combobox as it's just allowing you to zoom in on a specific set of values. This leads to another problem, if your second combobox has had its record source Changed by the first combobox then when you move through the records, the second combobox won't display the correct record because you need to reset its record source to show all records.

Anyway the point is I think you need to explain better what you are doing.

Incidentally, don't let me put you off experimenting with the code, this is the best way to learn.
 

isladogs

MVP / VIP
Local time
Today, 00:31
Joined
Jan 14, 2017
Messages
18,186
I also held off responding as I can't see the point of this.

Are your combo boxes row sources bound to a table/query?
If so, the code appears to be completely unnecessary as the combos will only select values already in those row sources

If you are using a value list for one or more combos which form part of a cascading combo setup, I would question whether that is sensible or even feasible.

Perhaps more detail and/or screenshots would help clarify this
 

hugomadail

Registered User.
Local time
Today, 00:31
Joined
Aug 31, 2018
Messages
25
Uncle Gizmo, thanks for the post edit, looks better that way.

As requested, here is a (hopefully better ?) explanation of what I'm trying to do:

What I have:
- I have a form with 2 comboboxes - cbA and cbB
- Selecting a cbA record filters options available on cbB (example cbA choose fruit instead of furniture, then cbB will show apple, pear)
- I cannot use cbB alone because options available are always filtered by cbA, cbA is always used first
- For new records comboboxes will be empty
- For existing records comboboxes already have been filled with something

What I want:
- For existing records (comboboxes already are showing previously selected information), when a user changes cbA record to something else, I want a messagebox appear to validate changes. The code I provided was my attempt to make this work.

I still need help because did not found a solution yet.
If this code is a dead end, I apreciate any other solution.

Edit: isladogs maybe this explanation will clarify what I want, my code does not relate with rowsource. Comboboxes work well, this is only about creating a messagebox to validate changes on combobox
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 00:31
Joined
Jul 9, 2003
Messages
16,245
The explanation of what you want is quite clear, and really you haven't added much to what you've already said.

I draw your attention to Colin's comment:-

the code appears to be completely unnecessary as the combos will only select values already in those row sources

I'm thinking the same, there's something about what you are doing that doesn't makes sense.

So really that's what you need to address, you need to educate us, not to say "I want it to do this that and the other" but to explain the issue that you are trying to fix. Really, answer Colin's observation that what you are trying to do is unnecessary.

If you can convince us that it is necessary, then we can move forward..
 

isladogs

MVP / VIP
Local time
Today, 00:31
Joined
Jan 14, 2017
Messages
18,186
I agree cascading combos work well. I use them regularly. Here is an example from one of my apps. All 5 combos shown are based on tables



Sorry but your extra information doesn't tell me anything of significance that wasn't in the original explanation
I still don't know if you are using a table/query or a value list for each combo.

You might find this example useful. Its from the FMS website: http://www.fmsinc.com/microsoftaccess/Forms/combo-boxes/cascading.html

Good luck
 

Attachments

  • PostcodeBuilder.PNG
    PostcodeBuilder.PNG
    74.4 KB · Views: 242

hugomadail

Registered User.
Local time
Today, 00:31
Joined
Aug 31, 2018
Messages
25
I draw your attention to Colin's comment:-

Quote:
Originally Posted by isladogs View Post
the code appears to be completely unnecessary as the combos will only select values already in those row sources
I'm thinking the same, there's something about what you are doing that doesn't makes sense.

So really that's what you need to address, you need to educate us, not to say "I want it to do this that and the other" but to explain the issue that you are trying to fix. Really, answer Colin's observation that what you are trying to do is unnecessary.

If you can convince us that it is necessary, then we can move forward..


Thank you for your time Uncle Gizmo and isladogs. I thought I didnt explained myself right in the first post, but I realized just now why does it not make sense to you:

Yes, comboboxes only select values on the defined rowsources, but the message-box its suposed to warn when a change is made between the default values and not when something different from rowsource data is added. The idea is to only make the combo change value if the user click yes on msgbox to confirm (Edit: to prevent unintended changes on the combo!)

For example: I'm seeing an existing record in form, comboA shows fruits, comboB shows pear. I click the drop-down-arrow from comboA and select meals which will replace fruit value (and also filter comboB to show all meal options). But I want a messagebox appear to validate this change on comboA (both came from rowsource, no new value added).

If I had fruit value on comboA, messagebox would not appear if I select fruit again.
But if I select meal on comboA (or to another option) it would ask me if I want to make the change. Also in this case, it would only ask if comboB is empty (this last one would be for not trigger msgbox on newrecords).

Was this the missing thing ? :)
 

isladogs

MVP / VIP
Local time
Today, 00:31
Joined
Jan 14, 2017
Messages
18,186
1. I'll try asking for the third time. Are your combos based on a table/query or a value list?

2. Recommend you remove the default values. That will force the user to explicitly update the combo to select anything. Then the validation message box no longer has any purpose in my opinion. Normally these are used where the user is about to delete/update/append record. You're doing none of those.
You are just filtering records. There are no new records

3. If you want fruit in combo A twice in a row, you don't select it a second time as it's already selected.

4. Why would combo B be empty? Is that because you haven't populated all possible outcome? Or because the user hasn't selected from combo B?

5. If it helps you can add 'default text' on the combos like you see on websites e.g. "Please select an item from the drop down box"
 

hugomadail

Registered User.
Local time
Today, 00:31
Joined
Aug 31, 2018
Messages
25
isladogs:

1. I'll try asking for the third time. Are your combos based on a table/query or a value list?

Combos are based on tables! on both first column is a name, second column is ID. cbA (the fruit one) is independant, still displays values based on table. cbB (the pear one) is not independant, displays values from table. cbA is independant but required to filter cbB.

2. Recommend you remove the default values. That will force the user to explicitly update the combo to select anything. Then the validation message box no longer has any purpose in my opinion. Normally these are used where the user is about to delete/update/append record. You're doing none of those. You are just filtering records. There are no new records

Should not have used default word, it misleads. I used it to say all values are from rowsource always because you cant add more. validation message is important for user dont accidentaly change value because that would mean it had to search for a paper process to find the correct value to introduce if he didnt memorized it (I used fruits and pears only to simplificate, everything here is classified at area 51 LOL ups XD). I want to protect that info from change, unless its intended, that would happen with a validation message or something else.

3. If you want fruit in combo A twice in a row, you don't select it a second time as it's already selected.

but if I do want to change fruit to something else but accidentaly press fruit again, message box it is not suposed to appear because fruit is already there.

4. Why would combo B be empty? Is that because you haven't populated all possible outcome? Or because the user hasn't selected from combo B?

for new records both combos are empty! using default word was misleading as explained, sorry

5. If it helps you can add 'default text' on the combos like you see on websites e.g. "Please select an item from the drop do

that is a good idea, will do.

I hope I've answered everything to make the code understandable and to find why t doesnt work :)

Gasman

Have a look at the .OldValue property.

I'm not familiar with that property, will check it later to see if it can help. I'm not a programmer I hope to understand it. :)
 

JHB

Have been here a while
Local time
Today, 01:31
Joined
Jun 17, 2012
Messages
7,732
The event Before Update isn't the right place because the value is the same (equal to) in the Before Update and the After Update. It's logical otherwise you can't use the Before Update event to test if a new value is valid and that's what you normally use the event for.
So I would suggest you to put the code you've in the Before Update event, to the Enter event.
 

hugomadail

Registered User.
Local time
Today, 00:31
Joined
Aug 31, 2018
Messages
25
After some attempts with Gasman and JHB ideas, I could not make it work (that might be my fault and not the ideas LOL)

I was still convinced that it was something wrong with tempvar so I changed the code to make a different check (on cbB) since they were related:

Code:
If [cbA].Value <> [cbB].column(1) Then

Now it works! :)

Thank you all for your time dedicated on this problem :)
 

Users who are viewing this thread

Top Bottom