By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,166 Members | 1,130 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,166 IT Pros & Developers. It's quick & easy.

Enable subform based on combo box choice

AccessIdiot
100+
P: 493
I have been looking through posts on events happening based on a combo box choice but none really get at the heart of what I am trying to do.

I think this is simple but I'm a newbie so any hand holding and non-startling movements will help here. :-)

I have a form Specimen with a dropdown that displays Species by Species_Code. In the Species table I have a Yes/No field for whether or not the Species is a Fish (Yes = fish). If the user chooses a Species_Code that is a Fish I want the subform to become enabled.

Seems like this is pretty simple but I don't know VB (though I can kind of follow it when I see it written) and am not sure on which event to put this on or how to tie a field from the Species table that isn't being displayed in the dropdown to the subform. Is that confusing?

Basically --> If user chooses a Species_Code that has Fish = yes for that record then subform.visible = true.

Help!
Melissa
Feb 27 '07 #1
Share this Question
Share on Google+
26 Replies


ADezii
Expert 5K+
P: 8,638
I have been looking through posts on events happening based on a combo box choice but none really get at the heart of what I am trying to do.

I think this is simple but I'm a newbie so any hand holding and non-startling movements will help here. :-)

I have a form Specimen with a dropdown that displays Species by Species_Code. In the Species table I have a Yes/No field for whether or not the Species is a Fish (Yes = fish). If the user chooses a Species_Code that is a Fish I want the subform to become enabled.

Seems like this is pretty simple but I don't know VB (though I can kind of follow it when I see it written) and am not sure on which event to put this on or how to tie a field from the Species table that isn't being displayed in the dropdown to the subform. Is that confusing?

Basically --> If user chooses a Species_Code that has Fish = yes for that record then subform.visible = true.

Help!
Melissa
I would have liked a little more information but I'll make some basic assumptions such as:
__1 Your Combo Box is named cboSpeciesCode.
__2 Your Species Table contains the Fields [Fish] (Yes/No), [Species] (Text), and [Species_Code] (Long).
__3 Your Sub-Form name is subfSpecies.
__4 Make any other adjustments as needed.
__5 In the AfterUpdate() Event of cboSpeciesCode:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboSpeciesCode_AfterUpdate()
  2. Dim intSpeciesCode As Integer, IsItAFish As Integer
  3.  
  4. intSpeciesCode = Me![cboSpeciesCode]
  5. IsItAFish = DLookup("[Fish]", "Species", "[Species_Code]=" & intSpeciesCode)
  6.  
  7. If IsItAFish = -1 Then  'True - it is a fish
  8.   Me![subfSpecies].Visible = False
  9. Else
  10. End If
  11. End Sub
Feb 28 '07 #2

AccessIdiot
100+
P: 493
Sorry! I should have specified these things. I made appropriate changes but Species_Code is text, not a long integer. So it seems to be getting hung up on this line:
Expand|Select|Wrap|Line Numbers
  1. IsItAFish = DLookup("[Fish]", "Species", "[Species_Code]=" & intSpeciesCode)
  2.  
What do I have to change? Also, could you explain what the line does so I know how to apply it next time?

Here is what I changed it to:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Species_AfterUpdate()
  2. Dim intSpeciesCode As Integer, IsItAFish As Integer
  3.  
  4. intSpeciesCode = Me![Species]
  5. IsItAFish = DLookup("[Fish]", "Species", "[Species_Code]=" & intSpeciesCode)
  6.  
  7. If IsItAFish = -1 Then  'True - it is a fish
  8.   Me![subfrm_FishSpecimen].Visible = False
  9. Else
  10. End If
  11. End Sub
Thank you!
Feb 28 '07 #3

AccessIdiot
100+
P: 493
More information:

Combo box is called "Species", Species Table has fields [Species_ID] (autonumber, PK), [Species_Code] (text), [Common_Name] (text), [Family] (text), [Genus_ID] (autonumber, FK), [Scientific_Name] (text), [Alternate_Name] (text), [Fish] (Yes/No).

Do I need to include all these in the code?

Subform name is sbfrm_FishSpecimen

Here is what I changed your suggested code to:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Species_AfterUpdate()
  2. Dim intSpeciesCode As Integer, IsItAFish As Integer
  3.  
  4. intSpeciesCode = Me![Species]
  5. IsItAFish = DLookup("[Fish]", "Species", "[Species_Code]=" & intSpeciesCode)
  6.  
  7. If IsItAFish = -1 Then  'True - it is a fish
  8.   Me![subfrm_FishSpecimen].Visible = False
  9. Else
  10. End If
  11. End Sub
Thank you!
Feb 28 '07 #4

AccessIdiot
100+
P: 493
Anyone know how to change this if SpeciesCode is a string?

Expand|Select|Wrap|Line Numbers
  1. Private Sub Species_AfterUpdate()
  2. Dim intSpeciesCode As Integer, IsItAFish As Integer
  3.  
  4. intSpeciesCode = Me![Species]
  5. IsItAFish = DLookup("[Fish]", "Species", "[Species_Code]=" & intSpeciesCode)
  6.  
  7. If IsItAFish = -1 Then  'True - it is a fish
  8.   Me![subfrm_FishSpecimen].Visible = False
  9. Else
  10. End If
  11. End Sub
  12.  
Obviously Dim SpeciesCode As String but how do I change the DLookup line?

Also, why is -1 a true value? Shouldn't it be just 1?

thanks for any help!
Feb 28 '07 #5

AccessIdiot
100+
P: 493
Wow, how quickly this got buried!

Anyway, I am getting this error:

Run-time error '94':
Invalid use of Null

Here is my code now (I changed some table and form names)
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboSpecies_AfterUpdate()
  2.     Dim SpeciesCode As String, IsItAFish As Integer
  3.  
  4.     SpeciesCode = Me![cboSpecies]
  5.     If IsNull(Me.cboSpecies) = False Then
  6.         IsItAFish = DLookup("[Fish]", "[tbl_Species]", "[Species_Code]= '" & Me.cboSpecies & "'")
  7.     End If
  8.  
  9.     If IsItAFish = -1 Then  'True - it is a fish
  10.         Me![subfrm_FishSpecimen].Visible = False
  11.     Else
  12.     End If
  13. End Sub
I have tried all sorts of things with the DLookup line and I get either the Inavlid use of Null error or a data mismatch error, no matter what I put in the criteria part of the function.

Any ideas?

thanks,
melissa
Feb 28 '07 #6

AccessIdiot
100+
P: 493
Okay I got it! Finally! I was confused on the combo box and was using the value shown (text) vs the bound source (integer).

Anyway, it works beautifully.

HOWEVER, if I change the value in the dropdown the function doesn't refire. Sort of.

So if I choose a non-fish in the dropdown the subform doesn't show. If I change the dropdown to be a fish the subform does show (hurrah!). If I change the dropdown to a non fish again the subform is still there. How do I change that?

Basically, I want the subform to show only when the user chooses a fish. If they make a mistake and go back and change it to non-fish the subform needs to disappear.

Thanks!
Feb 28 '07 #7

NeoPa
Expert Mod 15k+
P: 31,494
Melissa,
Give me some time (maybe over the weekend) and I'll come back to you on this one. I'm just leaving work atm.
There are a number of questions in your posts and I'd like to cover them as they all seem to point to your better understanding the subject, which is a bonus for all of us here :)
Mar 2 '07 #8

NeoPa
Expert Mod 15k+
P: 31,494
Let's try this one at a time shall we.
I'll respond to some of your points then we'll see if we can take it from there.
True.
True is held as -1 because boolean fields are stored as numbers in a binary field. In a binary field, when all bits are set to one, the equivalent numeric value is -1. In an associated point, it is never a good idea to compare boolean fields.
IE. Never say If MyBoolean = True (or -1 even) Then... Always say If MyBoolean Then... instead.
Lack of Response.
This can happen sometimes and is usually the result of some confusion or some expert being away for a short while. You know from personal experience that it is unusual. However, if for any reason an expert is working on your thread and his notifications become reset (there is a bug in the site currently which is being looked at) then he will not be aware of your responses I'm afraid.
Knowing ADezii as I do, I'm sure he wouldn't leave you hanging like that deliberately. The Access forum is currently so busy that hunting for non-registered threads is more than most of us can manage. It doesn't look like an orphaned thread at first glance you see.
Mar 2 '07 #9

NeoPa
Expert Mod 15k+
P: 31,494
I'm assuming that the control [FishControl] (You need to supply the name of this control) is displayed and updated when a species is selected in the ComboBox [Species].
If so then the following should work for you :
Expand|Select|Wrap|Line Numbers
  1. Private Sub FishControl_AfterUpdate()
  2.   Me![subfrm_FishSpecimen].Visible = Me!FishControl
  3. End Sub
Mar 2 '07 #10

NeoPa
Expert Mod 15k+
P: 31,494
I hope I've covered everything, and I'm sorry if I have inadvertently requested any info that is already posted but I missed.
I've also posted 4 times in a row, so don't forget to check them all out and let me know where that leaves you.

Good luck :)
Mar 2 '07 #11

AccessIdiot
100+
P: 493
Hey thanks for getting back to me!

I understand Boolean but in my experience Boolean is always 0 or 1 so why -1? That just seems odd to me. But I hear you on ifMyBoolean vs ifMyBoolean = -1 (or whatever value) - that is smarter coding.

Mary actually helped me out with something cleaner and simpler. Here is the code in case anyone else needs it:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboSpecies_AfterUpdate()
  2. Dim SpeciesCode As String
  3. Dim aFish As Boolean
  4.  
  5.     SpeciesCode = Me.cboSpecies
  6.     aFish = Me.cboSpecies.Column(2)
  7.  
  8.     If aFish
  9.         Me.sbfrm_FishSpecimen.Visible = True
  10.     Else
  11.         Me.sbfrm_FishSpecimen.Visible = False
  12.     End If
  13.  
  14. End Sub
  15. Private Sub Form_Current()
  16.  
  17.     If Not IsNull(Me.cboSpecies) Then
  18.         cboSpecies_AfterUpdate
  19.     End If
  20.  
  21. End Sub
One thing that would be nice (but isn't totally necessary) is to have the subform set to visible = false when the user goes to a new record. So it doesn't have to wait for another choice from the dropdown to decide whether to be visible or not.

But that's not a necessity - there are bigger fish to fry at the moment!

Thanks again for your help. And for the record, I wasn't really complaining about the lack of help - rather just marveling at how fast a new post gets buried - there are a lot of users on this forum! (which is great by the way).

cheers,
melissa :-)
Mar 5 '07 #12

NeoPa
Expert Mod 15k+
P: 31,494
I understand Boolean but in my experience Boolean is always 0 or 1 so why -1? That just seems odd to me.
I don't know if you missed that part of my answer (post #9), but at a bitwise level, -1 is just all the bits set to 1. A bit like saying 'All True' if you like. -1 is just the signed integer conversion of a value which is all set to ones. I hope that makes sense.
Mary actually helped me out with something cleaner and simpler. Here is the code in case anyone else needs it:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboSpecies_AfterUpdate()
  2. Dim SpeciesCode As String
  3. Dim aFish As Boolean
  4.  
  5.     SpeciesCode = Me.cboSpecies
  6.     aFish = Me.cboSpecies.Column(2)
  7.  
  8.     If aFish
  9.         Me.sbfrm_FishSpecimen.Visible = True
  10.     Else
  11.         Me.sbfrm_FishSpecimen.Visible = False
  12.     End If
  13.  
  14. End Sub
  15. Private Sub Form_Current()
  16.  
  17.     If Not IsNull(Me.cboSpecies) Then
  18.         cboSpecies_AfterUpdate
  19.     End If
  20.  
  21. End Sub
Try out this version :
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboSpecies_AfterUpdate()
  2.   Call CheckFish()
  3. End Sub
  4.  
  5. Private Sub Form_Current()
  6.   Call CheckFish()
  7. End Sub
  8.  
  9. Private Sub CheckFish()
  10.   Dim blnFish As Boolean
  11.  
  12.   'As a default blnFish starts as False
  13.   If Not IsNull(Me.cboSpecies) Then blnFish = Me!cboSpecies.Column(2)
  14.   Me!sbfrm_FishSpecimen.Visible = blnFish
  15. End Sub
An illustration of how powerful Boolean code can be.
One thing that would be nice (but isn't totally necessary) is to have the subform set to visible = false when the user goes to a new record. So it doesn't have to wait for another choice from the dropdown to decide whether to be visible or not.
Is it a new record in the subform you're talking about or a new record in the main form? We should be able to find a way to implement this. It may mean coding an event in the form that's on your subform but we'll see.
Thanks again for your help. And for the record, I wasn't really complaining about the lack of help - rather just marvelling at how fast a new post gets buried - there are a lot of users on this forum! (which is great by the way).

cheers,
melissa :-)
Oh I see. Buried, as in a long way down the list in the Access forum.
Yes, things do move fast in here. It can be a struggle to keep up sometimes.
Anyway, Pleased to hear you're basically happy.
Mar 5 '07 #13

AccessIdiot
100+
P: 493
Are you kidding? I'm incredibly happy. I'm ecstatic. I'm giddy with excitement. I'm actually getting something to work that has no business working because I don't know the code and am basically diving in with lead boots. But thanks to a wonderful community of developers and the internet I'm actually accomplishing something.

I'm going to stop gushing now and try the code you suggested. :-)

Oh and to answer your question its when the main form advances to a new form. Basically when you choose a species from the drop down the subform shows if the species you chose is a fish (I've set up a control so that you can't advance to a new record in the subform, only one subform per main form allowed). When you advance to a new record in the main form the subform is still there, albeit on a new clean record. I'd like it so that the subform disappears when you advance to a new record, sort of starting with a clean plate.

However, after talking with the other person on this project I think we are going to really dumb it down for the users. We're going to hide all the Access built in form buttons (the arrow at the bottom that go to first, last, next, previous and new records) and build in our own with big letters so it is super obvious and they can't accidentally screw things up. :-)

I'm sure when we build the buttons to go to a new record I can program the subform to be not visible again. That would be pretty easy right?

thanks again!
Mar 5 '07 #14

NeoPa
Expert Mod 15k+
P: 31,494
This all sounds very good, except I am no nearer understanding the answer to my question than I was when I asked it (I'm sure you're trying to be as helpful as you can). Your answer only explains things relative to other things I don't know about. I'm afraid it doesn't take me forward.
Can you try rephrasing your answer in the terms defined by the question (It was formulated specially with that in mind).
I guess that you somehow trigger the opening of another form, but which form and how they're related, is not explained anywhere as far as I can see (I mean in Access terms not from the perspective of the operator, which may have been explained to a certain extent).
Before I can get an idea of what's needed I need a very precise understanding of what's going on.
Is it a separate, unrelated, form (that is opened)?
What are the Record Sources for all the forms and subforms that will pertain in this situation?
Why is the selecting of an item from cboSpecies not an adequate trigger mechanism considering all else that you've explained (which would lead me to conclude that it would be)? Please help me to make more sense of this.
Mar 6 '07 #15

NeoPa
Expert Mod 15k+
P: 31,494
In case this is not clear, I'm not trying to be grumpy (that just comes naturally), nor am I upset. Simply a bit confused.
Mar 6 '07 #16

AccessIdiot
100+
P: 493
Hi NeoPa,

I really appreciate your help and direct questions. I am sorry my answers have been so convoluted. I am learning by fumbling my way through and it is sometimes difficult to comprehend how everything is connected in Access (tables, queries, forms, controls) while teaching myself code that is unfamiliar.

SO - we are going to try a different approach. We are going to remove the standard buttons that Access has on the bottom of all the forms for first/last record, new record, etc and make our own buttons. That way I can put additional code on the button events and that might solve the problem I'm having here.

I'm sure you'll hear from me if I can't get it to work!

thanks again, truly,
melissa :-)
Mar 7 '07 #17

AccessIdiot
100+
P: 493
Try out this version :
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboSpecies_AfterUpdate()
  2.   Call CheckFish()
  3. End Sub
  4.  
  5. Private Sub Form_Current()
  6.   Call CheckFish()
  7. End Sub
  8.  
  9. Private Sub CheckFish()
  10.   Dim blnFish As Boolean
  11.  
  12.   'As a default blnFish starts as False
  13.   If Not IsNull(Me.cboSpecies) Then bnlFish = Me!cboSpecies.Column(2)
  14.   Me!sbfrm_FishSpecimen.Visible = blnFish
  15. End Sub
An illustration of how powerful Boolean code can be.
Hey NeoPa I just tried this version and unfortunately it doesn't work. No matter what I choose in the dropdown the subform doesn't show. Maybe I'm not doing something right? Can you explain what it is doing exactly? Maybe I can find the problem.

Also, can you explain to me the difference between using "." and "!" as I see both in different bits of code. As in Me.sbfrm_FishSpecimen.Visible = False and Me!sbfrm_FishSpecimen.Visible = blnFish

I'd like to use it though - short and sweet!

thanks much
Mar 7 '07 #18

AccessIdiot
100+
P: 493
Never mind! I fixed it - there was a typo with blnFish. I think I understand the code too. Basically, create an integer variable that checks the value of the yes/no column then sets the visibility of the form to that value.

I'd still love to hear an explanation of "." vs "!" though if you have a free moment!

thanks :-)
Mar 7 '07 #19

NeoPa
Expert Mod 15k+
P: 31,494
Never mind! I fixed it - there was a typo with blnFish. I think I understand the code too. Basically, create an integer variable that checks the value of the yes/no column then sets the visibility of the form to that value.

I'd still love to hear an explanation of "." vs "!" though if you have a free moment!

thanks :-)
For you Melissa - any time :)
Your top explanation is pretty well there :
  1. Check that the cboSpecies control actually has something selected.
  2. Only if so, set the boolean variable blnFish to reflect this from the boolean value in column 2 of cboSpecies.
  3. Set the .Visible property (Also boolean) to the value of blnFish.
'.' Versus '!'
'.' indicates that a (built-in) Property follows.
'!' indicates that a named item in a collection follows.
Object!NameExample is technically a shortcut for writing Object.Collection("NameExample"). This, in itself is a way of referencing Object.Collection(n) where Object.Collection(n).Name = "NameExample".

I'm not sure about controls on a form.
I've recently been treating them as named items in a Controls collection, but it occurs to me that, while they may not be properties of a Form object exactly, they may well be defined properties of the customised form object that a running form is an instance of.
EG. If you have a form called frmItems then you have created a class object called Form_frmItems in your database. Any instances of this object would have defined (built-in) properties for each of the controls on the form.
If none of this makes sense then ignore it and just stop after ... = "NameExample".
Mar 7 '07 #20

NeoPa
Expert Mod 15k+
P: 31,494
Never mind! I fixed it - there was a typo with blnFish. I think I understand the code too. Basically, create an integer variable that checks the value of the yes/no column then sets the visibility of the form to that value.

I'd still love to hear an explanation of "." vs "!" though if you have a free moment!

thanks :-)
Thanks for highlighting the typo in bnlFish that I had (but have now fixed) in post #13.
As a follow on from my last about using '!', it can also be used to denote other shortcuts when referencing objects. Basically it denotes a shortcut. There are a number of shortcuts used in VBA but I don't know them all. There are a whole bunch specially for accessing subforms and controls thereon.
Mar 7 '07 #21

AccessIdiot
100+
P: 493
Thanks for the explanation - I can't claim that it makes perfect sense, especially as there are cases where I see both used and it works just fine but I guess the lesson is if one way fails try the other way! lol . . .

thanks again!
Mar 7 '07 #22

NeoPa
Expert Mod 15k+
P: 31,494
The compiler certainly is very forgiving, so will recognise what is required unless it can refer to two different items.
Me!ControlName, for instance, would be unambiguous as it can only really be one thing. I can't think of anything, at the moment, to illustrate an ambiguous reference. If you think of the '!' as more of a general shortcut, rather than specifically referring to items in collections, that may help.
Mar 7 '07 #23

Rabbit
Expert Mod 10K+
P: 12,366
I prefer using the '.' as I know right away if the object or item exists because of the pop-up menu that follows.
Mar 7 '07 #24

NeoPa
Expert Mod 15k+
P: 31,494
Unfortunately, not all properties are necessarily shown in that pop-up.
Consider the Caption property of a Label control.
I too prefer using the '.' where it works though. I don't like for the compiler to have to fix my mistakes :(
Mar 7 '07 #25

Rabbit
Expert Mod 10K+
P: 12,366
Unfortunately, not all properties are necessarily shown in that pop-up.
Consider the Caption property of a Label control.
I too prefer using the '.' where it works though. I don't like for the compiler to have to fix my mistakes :(
Indeed, but it's better than shooting in the dark with '!'
Mar 7 '07 #26

NeoPa
Expert Mod 15k+
P: 31,494
Oh, absolutely.
I wasn't trying to imply your method wasn't a good one (I use it mostly myself), just that it can let you down sometimes. It's a pretty reliable way of getting the code right first time - which is good in my book.
Mar 7 '07 #27

Post your reply

Sign in to post your reply or Sign up for a free account.