Enable subform based on combo box choice

[AccessIdiot]

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

[ADezii]

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:

Private Sub cboSpeciesCode_AfterUpdate()
Dim intSpeciesCode As Integer, IsItAFish As Integer
 
intSpeciesCode = Me![cboSpeciesCode]
IsItAFish = DLookup("[Fish]", "Species", "[Species_Code]=" & intSpeciesCode)
 
If IsItAFish = -1 Then  'True - it is a fish
  Me![subfSpecies].Visible = False
Else
End If
End Sub

[AccessIdiot]

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:

IsItAFish = DLookup("[Fish]", "Species", "[Species_Code]=" & intSpeciesCode)
 

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:

Private Sub Species_AfterUpdate()
Dim intSpeciesCode As Integer, IsItAFish As Integer
 
intSpeciesCode = Me![Species]
IsItAFish = DLookup("[Fish]", "Species", "[Species_Code]=" & intSpeciesCode)
 
If IsItAFish = -1 Then  'True - it is a fish
  Me![subfrm_FishSpecimen].Visible = False
Else
End If
End Sub

Thank you!

[AccessIdiot]

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:

Private Sub Species_AfterUpdate()
Dim intSpeciesCode As Integer, IsItAFish As Integer
 
intSpeciesCode = Me![Species]
IsItAFish = DLookup("[Fish]", "Species", "[Species_Code]=" & intSpeciesCode)
 
If IsItAFish = -1 Then  'True - it is a fish
  Me![subfrm_FishSpecimen].Visible = False
Else
End If
End Sub

Thank you!

[AccessIdiot]

Anyone know how to change this if SpeciesCode is a string?

Private Sub Species_AfterUpdate()
Dim intSpeciesCode As Integer, IsItAFish As Integer
 
intSpeciesCode = Me![Species]
IsItAFish = DLookup("[Fish]", "Species", "[Species_Code]=" & intSpeciesCode)
 
If IsItAFish = -1 Then  'True - it is a fish
  Me![subfrm_FishSpecimen].Visible = False
Else
End If
End Sub
 

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!

[AccessIdiot]

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)

Private Sub cboSpecies_AfterUpdate()
    Dim SpeciesCode As String, IsItAFish As Integer
 
    SpeciesCode = Me![cboSpecies]
    If IsNull(Me.cboSpecies) = False Then
        IsItAFish = DLookup("[Fish]", "[tbl_Species]", "[Species_Code]= '" & Me.cboSpecies & "'")
    End If
 
    If IsItAFish = -1 Then  'True - it is a fish
        Me![subfrm_FishSpecimen].Visible = False
    Else
    End If
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

[AccessIdiot]

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!

[NeoPa]

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

[NeoPa]

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.

[NeoPa]

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 :

Private Sub FishControl_AfterUpdate()
  Me![subfrm_FishSpecimen].Visible = Me!FishControl
End Sub

[NeoPa]

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

[AccessIdiot]

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:

Private Sub cboSpecies_AfterUpdate()
Dim SpeciesCode As String
Dim aFish As Boolean
 
    SpeciesCode = Me.cboSpecies
    aFish = Me.cboSpecies.Column(2)
 
    If aFish
        Me.sbfrm_FishSpecimen.Visible = True
    Else
        Me.sbfrm_FishSpecimen.Visible = False
    End If
 
End Sub
Private Sub Form_Current()
 
    If Not IsNull(Me.cboSpecies) Then
        cboSpecies_AfterUpdate
    End If
 
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 :-)

[NeoPa]

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:

Private Sub cboSpecies_AfterUpdate()
Dim SpeciesCode As String
Dim aFish As Boolean
 
    SpeciesCode = Me.cboSpecies
    aFish = Me.cboSpecies.Column(2)
 
    If aFish
        Me.sbfrm_FishSpecimen.Visible = True
    Else
        Me.sbfrm_FishSpecimen.Visible = False
    End If
 
End Sub
Private Sub Form_Current()
 
    If Not IsNull(Me.cboSpecies) Then
        cboSpecies_AfterUpdate
    End If
 
End Sub

Try out this version :

Private Sub cboSpecies_AfterUpdate()
  Call CheckFish()
End Sub
 
Private Sub Form_Current()
  Call CheckFish()
End Sub
 
Private Sub CheckFish()
  Dim blnFish As Boolean
 
  'As a default blnFish starts as False
  If Not IsNull(Me.cboSpecies) Then blnFish = Me!cboSpecies.Column(2)
  Me!sbfrm_FishSpecimen.Visible = blnFish
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.

[AccessIdiot]

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!

[NeoPa]

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.

[NeoPa]

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.

[AccessIdiot]

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

[AccessIdiot]

Try out this version :

Private Sub cboSpecies_AfterUpdate()
  Call CheckFish()
End Sub
 
Private Sub Form_Current()
  Call CheckFish()
End Sub
 
Private Sub CheckFish()
  Dim blnFish As Boolean
 
  'As a default blnFish starts as False
  If Not IsNull(Me.cboSpecies) Then bnlFish = Me!cboSpecies.Column(2)
  Me!sbfrm_FishSpecimen.Visible = blnFish
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

[AccessIdiot]

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

[NeoPa]

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".

[NeoPa]

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.

[AccessIdiot]

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!

[NeoPa]

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.

[Rabbit]

I prefer using the '.' as I know right away if the object or item exists because of the pop-up menu that follows.

[NeoPa]

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

[Rabbit]

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 '!'

[NeoPa]

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.