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

[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_FishSp ecimen.Visible = False and Me!sbfrm_FishSp ecimen.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!NameExam ple is technically a shortcut for writing Object.Collecti on("NameExample "). This, in itself is a way of referencing Object.Collecti on(n) where Object.Collecti on(n).Name = "NameExampl e".

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 ... = "NameExampl e".