Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-12-2018, 02:55 AM   #1
hugomadail
Newly Registered User
 
Join Date: Aug 2018
Posts: 15
Thanks: 2
Thanked 0 Times in 0 Posts
hugomadail is on a distinguished road
Cascading combobox with tempvar check

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 Uncle Gizmo; 10-12-2018 at 04:13 AM. Reason: added code tags - OP - look at it in Edit view to see how to use code tags
hugomadail is offline   Reply With Quote
Old 10-12-2018, 05:34 AM   #2
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 9,458
Thanks: 358
Thanked 773 Times in 738 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Cascading combobox with tempvar check

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.
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is online now   Reply With Quote
Old 10-12-2018, 05:43 AM   #3
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,552
Thanks: 88
Thanked 1,609 Times in 1,499 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Cascading combobox with tempvar check

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

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Web links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders

Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 10-12-2018, 05:52 AM   #4
hugomadail
Newly Registered User
 
Join Date: Aug 2018
Posts: 15
Thanks: 2
Thanked 0 Times in 0 Posts
hugomadail is on a distinguished road
Re: Cascading combobox with tempvar check

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 by hugomadail; 10-12-2018 at 06:01 AM.
hugomadail is offline   Reply With Quote
Old 10-12-2018, 07:41 AM   #5
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 9,458
Thanks: 358
Thanked 773 Times in 738 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Cascading combobox with tempvar check

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:-

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..
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is online now   Reply With Quote
Old 10-12-2018, 08:31 AM   #6
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,552
Thanks: 88
Thanked 1,609 Times in 1,499 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Cascading combobox with tempvar check

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/microsoftacces...cascading.html

Good luck
Attached Images
File Type: png PostcodeBuilder.PNG (74.4 KB, 73 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Web links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders

Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 10-12-2018, 10:36 AM   #7
hugomadail
Newly Registered User
 
Join Date: Aug 2018
Posts: 15
Thanks: 2
Thanked 0 Times in 0 Posts
hugomadail is on a distinguished road
Re: Cascading combobox with tempvar check

Quote:
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 ?

hugomadail is offline   Reply With Quote
Old 10-12-2018, 11:37 AM   #8
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,552
Thanks: 88
Thanked 1,609 Times in 1,499 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Cascading combobox with tempvar check

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"
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Web links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders

Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 10-12-2018, 11:58 AM   #9
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,398
Thanks: 291
Thanked 374 Times in 359 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Cascading combobox with tempvar check

Have a look at the .OldValue property.

https://docs.microsoft.com/en-us/off...xtbox.oldvalue

Test for NewRecord in the AfterUpdate event to determine if new record or not.

HTH
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 10-12-2018, 12:55 PM   #10
hugomadail
Newly Registered User
 
Join Date: Aug 2018
Posts: 15
Thanks: 2
Thanked 0 Times in 0 Posts
hugomadail is on a distinguished road
Re: Cascading combobox with tempvar check

isladogs:

Quote:
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.

Quote:
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.

Quote:
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.

Quote:
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

Quote:
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

Quote:
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.
hugomadail is offline   Reply With Quote
Old 10-12-2018, 06:54 PM   #11
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,352
Thanks: 2
Thanked 1,971 Times in 1,927 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Cascading combobox with tempvar check

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.
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is online now   Reply With Quote
Old Today, 12:22 AM   #12
hugomadail
Newly Registered User
 
Join Date: Aug 2018
Posts: 15
Thanks: 2
Thanked 0 Times in 0 Posts
hugomadail is on a distinguished road
Re: Cascading combobox with tempvar check

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

hugomadail is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Cascading Combobox warriorkid1 Forms 11 08-05-2014 08:11 AM
Need Help on a cascading combobox pikoy Forms 2 12-25-2013 07:24 AM
Cascading Combobox DMerchen Forms 14 11-17-2008 12:16 PM
cascading combobox help twychopen22 Queries 0 08-14-2006 01:13 PM
Cascading combobox Jeff_Ford Forms 3 12-14-2003 05:56 AM




All times are GMT -8. The time now is 04:53 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World