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_FishSpeci men

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