Edit Combo Box Event Code?

[gomikem]

I have a drop down list in a form that's the first of a cascading set of drop down lists. It loads the list data from a table that lists "Shirt Type." There are some shirt types that are no longer active, so I would like for them to not show in the list on the form. I don't want to delete them, however, because we need the data that they're related to.

I've been able to do this in a simple drop down list by saying SELECT Yard.YardName FROM Yard; WHEN Yard.Active = "Yes" in the Row Source.

With the cascading drop down lists, it looks like it uses events to pull the info it needs. Here is the Got Focus event code:

Private Sub cboType_GotFocus()
'If cboType.Text = "" Then
LoadShirtType
'End If
End Sub

Here is the On Click event code:

Private Sub cboType_Click()
'LoadShirtType
strShirtType = cboType.Text
LoadShirtStyle


End Sub

Can I, and if so, how? Can I modify the code to say something like LoadShirtType WHEN ShirtType.Active = "Yes"?

Any help is appreciated. Thanks!

 

[rainman89]

what about.

If shirtype.active="yes" then 
Loadshirtype
else
loadshirtstyle
end if

 

[gomikem]

what about.

If shirtype.active="yes" then 
Loadshirtype
else
loadshirtstyle
end if

This works great... but how would I define ShirtType.Active as a variable in the code? It works, but gives me an error saying that ShirtType.Active is not defined as a variable.

Would I do it here?

Dim strShirtType As String
Dim strShirtType.Active As String
Dim strShirtStyle As String
Dim strShirtSize As String

 

[rainman89]

itd be a boolean since its yes/no

 

[gomikem]

I'm pretty new to VB. How do I declare that variable as a boolean? I tried to, but it gives me more errors.

 

[rainman89]

is active a colum in your table?

 

[gomikem]

Yes, Active is a field in the ShirtType table.

 

[rainman89]

ok then

Dim strShirtType As String
strShirtype=dlookup("active","tablename","[whateverfield]=" blah blah)
if strshirtype="yes" then
loadshirtype
else
blah blah

you can find a dlookup reference here

 

[gomikem]

where do I put:

strShirtType = DLookup("[Type]", "ShirtType", "[Active] = Yes")

?

The code has the following at the top:

Option Compare Database
Option Explicit

Dim strShirtType As String
Dim strShirtStyle As String
Dim strShirtSize As String

Then I've got this-

Private Sub cboType_GotFocus()
'If cboType.Text = "" Then
'If strShirtType = "Yes" Then
LoadShirtType
'End If
'End If
End Sub


Which seems ok?

Thanks for all your help guys!

 

[rainman89]

Option Compare Database
Option Explicit

Private Sub cboType_GotFocus()

Dim strShirtType As String
Dim strShirtStyle As String
Dim strShirtSize As String

strShirtType = DLookup("[Active]", "ShirtType", "[Type] =" & me.cbotype) [COLOR="Red"]<- this should return yes/no based on your comobo box value
[/COLOR]

if strshirtype="yes" then
loadshirtype
else

End Sub

 

[gomikem]

After making the changes- When I open the form, it gives me an error:

Compile Error: Invalid Outside Procedure

And it highlights the bolded section in the following:

strShirtType = DLookup("[Active]", "ShirtType", "[Type] =" & Me.cboType)

Active is a Field in the ShirtType table and it's a yes/no data type.

Also, if I switch Active and Type, it will highlight Type as the error. So whatever is in that position gives me an error.

Am I doing something wrong?