Combo Box - for Dirk or anyone who can help

[Guest]

I've tried this but get a "can't find the field 'Profile' referred to in your expression". It might be easier if I give more complete details

SubForm name: sfrmProfilesAndAssociation
Record Source: tblProfilesAndAssociation
Combo Box name: Combo1
Combo Box Control Source: AssociatedProfile
Combo Box Row Source: SELECT tblProfiles.txtProfileID, tblProfiles.Description, tblProfiles.Type FROM tblProfiles

Again, I need to be able to make a selection in the combo box and then double-click it to open a form based on the selection's Type. For example, I select something with a Type "CG" and then double-click it to open a form frmPKCorrugated

Sorry for any confusion

Sorry - thanks for sticking with me!
TYPES of records. The primary key is txtProfileID. The different
TYPES of records are: CG, FG, BG, LA, PL, etc.
fields in the tblProfiles. I need to be able to make a selection in
the combo box and double click it to go to its form.

If the name of the form can be determined from the value of the txtType
column of the combo box, then it's not a problem. For example:

Select Case Me!cboProfile.Column(2) ' the third column
Case "CG"
DoCmd.OpenForm "Form1"
Case "FG"
DoCmd.OpenForm "Form2"
Case "BG"
DoCmd.OpenForm "Form3"
' ... and so on ...
End Select

Or, if the names of the forms are constructed from the types, you might
even do something like this:

DoCmd.OpenForm "Form" & Me!cboProfile.Column(2)

to open "FormCG", "FormFG", "FormBG", etc.

 

[Dirk Goldgar]

I've tried this but get a "can't find the field 'Profile' referred to
in your expression". It might be easier if I give more complete
details.

SubForm name: sfrmProfilesAndAssociations
Record Source: tblProfilesAndAssociations
Combo Box name: Combo10
Combo Box Control Source: AssociatedProfiles
Combo Box Row Source: SELECT tblProfiles.txtProfileID,
tblProfiles.Description, tblProfiles.Type FROM tblProfiles;

Again, I need to be able to make a selection in the combo box and
then double-click it to open a form based on the selection's Type.
For example, I select something with a Type "CG" and then
double-click it to open a form frmPKCorrugated.

Sorry for any confusion!

The error is raised when you double-click the combo box? Let's see the
code you have in the combo box's DblClick event procedure.

By the way, it would have been better just to post your message as a
reply in the original thread, rather than starting a whole new thread.
Most of us "regulars" keep an eye on discussion threads in which we're
participating, but may overlook a new thread started on the same
subject. You're lucky I keep an eye out for messages with my name in
the subject, or I might well have missed this one.

 

[Guest]

Thanks, Dirk! The error is raised when I double-click the combo box in the subform. I used the exact code you gave me

Select Case Me!cboProfile.Column(2) ' the third column
Case "CG"
DoCmd.OpenForm "frmPKCorrugated"
End Select

Rather than listing all forms I simply started with this one.


----- Dirk Goldgar wrote: ----

I've tried this but get a "can't find the field 'Profile' referred t
in your expression". It might be easier if I give more complet
details
Record Source: tblProfilesAndAssociation
Combo Box name: Combo1
Combo Box Control Source: AssociatedProfile
Combo Box Row Source: SELECT tblProfiles.txtProfileID
tblProfiles.Description, tblProfiles.Type FROM tblProfiles
then double-click it to open a form based on the selection's Type
For example, I select something with a Type "CG" and the
double-click it to open a form frmPKCorrugated

The error is raised when you double-click the combo box? Let's see th
code you have in the combo box's DblClick event procedure

By the way, it would have been better just to post your message as
reply in the original thread, rather than starting a whole new thread
Most of us "regulars" keep an eye on discussion threads in which we'r
participating, but may overlook a new thread started on the sam
subject. You're lucky I keep an eye out for messages with my name i
the subject, or I might well have missed this one

 

[Dirk Goldgar]

Since you tell me your combo box is named "Combo10", the code should
read

Select Case Me!Combo10.Column(2) ' the third column

Note also that the combo box must have its Column Count property set to
3.

 

[Guest]

EXCELLENT! Thanks, Dirk!!! Works just fine. How do I adjust this code for other subforms? For example, I have another subform with a combo box with 3 queried fields and would like to open a form based off the first column (a primary key field in the related table)

Here's what I've done but don't quite understand the Case part of the code

Select Case Me!Combo6.Column(1) ' the first colum
Case "???
DoCmd.OpenForm "frmSuppliers
End Selec

----- Dirk Goldgar wrote: ----

Since you tell me your combo box is named "Combo10", the code shoul
rea

Select Case Me!Combo10.Column(2) ' the third colum

Note also that the combo box must have its Column Count property set t
3

 

[Dirk Goldgar]

EXCELLENT! Thanks, Dirk!!! Works just fine. How do I adjust this code
for other subforms? For example, I have another subform with a combo
box with 3 queried fields and would like to open a form based off the
first column (a primary key field in the related table).

Here's what I've done but don't quite understand the Case part of the
code.

Select Case Me!Combo6.Column(1) ' the first column
Case "???"
DoCmd.OpenForm "frmSuppliers"
End Select

I'm not sure whether you're trying to do the same sort of thing or
something different. If you really want to open a different form
depending on the value in the first column of the combo -- which
incidentally is Column(0), not Column(1) -- then I can't possibly know
what values should be used in the Case statements of your Select
structure. Only you could know that.

However, it may be that what you want to do is always open
"frmSuppliers", but filtered to show the record for the supplier in the
first column of the Combo6. That's different from what we did before.
If that's what you're after, then suppose that the combo box's column
comes from the field named "SupplierID". The code might then look
something like this:

Private Sub Combo6_DblClick(Cancel As Integer)

If IsNull(Me!Combo6) Then
' no supplier selected; show all ...
DoCmd.OpenForm "frmSuppliers"
Else
' show selected supplier ...
DoCmd.OpenForm "frmSuppliers", _
WhereCondition:="SupplierID=" & _
Me!Combo6.Column(0)
End If

End Sub

That assumes that SupplierID is a numeric field, not a text field.
Also, if that column is the bound column of the combo box, you don't
need to use ".Column(0)" to refer to it; you can just write

DoCmd.OpenForm "frmSuppliers", _
WhereCondition:="SupplierID=" Me!Combo6

 

[Guest]

Yes - this is what I'm trying to do:

However, it may be that what you want to do is always open
"frmSuppliers", but filtered to show the record for the supplier in the
first column of the Combo6. That's different from what we did before.
If that's what you're after, then suppose that the combo box's column
comes from the field named "SupplierID". The code might then look
something like this:

But my Supplier ID is actually "txtSupplierID" so what do I change in the code?

----- Dirk Goldgar wrote: -----

EXCELLENT! Thanks, Dirk!!! Works just fine. How do I adjust this code
for other subforms? For example, I have another subform with a combo
box with 3 queried fields and would like to open a form based off the
first column (a primary key field in the related table).
Case "???"
DoCmd.OpenForm "frmSuppliers"
End Select

I'm not sure whether you're trying to do the same sort of thing or
something different. If you really want to open a different form
depending on the value in the first column of the combo -- which
incidentally is Column(0), not Column(1) -- then I can't possibly know
what values should be used in the Case statements of your Select
structure. Only you could know that.

However, it may be that what you want to do is always open
"frmSuppliers", but filtered to show the record for the supplier in the
first column of the Combo6. That's different from what we did before.
If that's what you're after, then suppose that the combo box's column
comes from the field named "SupplierID". The code might then look
something like this:

Private Sub Combo6_DblClick(Cancel As Integer)

If IsNull(Me!Combo6) Then
' no supplier selected; show all ...
DoCmd.OpenForm "frmSuppliers"
Else
' show selected supplier ...
DoCmd.OpenForm "frmSuppliers", _
WhereCondition:="SupplierID=" & _
Me!Combo6.Column(0)
End If

End Sub

That assumes that SupplierID is a numeric field, not a text field.
Also, if that column is the bound column of the combo box, you don't
need to use ".Column(0)" to refer to it; you can just write

DoCmd.OpenForm "frmSuppliers", _
WhereCondition:="SupplierID=" Me!Combo6

 

[Dirk Goldgar]

Yes - this is what I'm trying to do:

However, it may be that what you want to do is always open
"frmSuppliers", but filtered to show the record for the supplier
in the first column of the Combo6. That's different from what
we did before. If that's what you're after, then suppose that
the combo box's column comes from the field named "SupplierID".
The code might then look something like this:

But my Supplier ID is actually "txtSupplierID" so what do I change in
the code?

John, you've got to work with me on this. If your field is named
"txtSupplierID", and not "SupplierID" as in my example, then you have to
change the code accordingly:

Private Sub Combo6_DblClick(Cancel As Integer)

If IsNull(Me!Combo6) Then
' no supplier selected; show all ...
DoCmd.OpenForm "frmSuppliers"
Else
' show selected supplier ...
DoCmd.OpenForm "frmSuppliers", _
WhereCondition:="txtSupplierID=" & _
Me!Combo6.Column(0)
End If

End Sub

That assumes that txtSupplierID is a numeric field, not a text
field. Also, if that column is the bound column of the combo
box, you don't need to use ".Column(0)" to refer to it; you can
just write

DoCmd.OpenForm "frmSuppliers", _
WhereCondition:="txtSupplierID=" Me!Combo6

 

[Guest]

Common sense told me that it's as simple as adding a txt prefix, however, when I did it I got a runtime error (the OpenForm action was cancelled). The debugger is pointing to this part of the code:

DoCmd.OpenForm "frmSuppliers", _
WhereCondition:="txtSupplierID=" & _
Me!Combo6.Column(0)

It seems straightforward - my form name is exactly frmSuppliers and the field is txtSupplierID.

----- Dirk Goldgar wrote: -----

Yes - this is what I'm trying to do:
"frmSuppliers", but filtered to show the record for the supplier
in the first column of the Combo6. That's different from what
we did before. If that's what you're after, then suppose that
the combo box's column comes from the field named "SupplierID".
The code might then look something like this:
the code?

John, you've got to work with me on this. If your field is named
"txtSupplierID", and not "SupplierID" as in my example, then you have to
change the code accordingly:

Private Sub Combo6_DblClick(Cancel As Integer)

If IsNull(Me!Combo6) Then
' no supplier selected; show all ...
DoCmd.OpenForm "frmSuppliers"
Else
' show selected supplier ...
DoCmd.OpenForm "frmSuppliers", _
WhereCondition:="txtSupplierID=" & _
Me!Combo6.Column(0)
End If

End Sub

That assumes that txtSupplierID is a numeric field, not a text
field. Also, if that column is the bound column of the combo
box, you don't need to use ".Column(0)" to refer to it; you can
just write

DoCmd.OpenForm "frmSuppliers", _
WhereCondition:="txtSupplierID=" Me!Combo6

 

[Dirk Goldgar]

Does the "txt" in "txtSupplierID mean that the field is a text field?
If so, then you have to put quotes -- single or double -- into the
WhereCondition string, around the value you're giving for txtSupplierID:

DoCmd.OpenForm "frmSuppliers", _
WhereCondition:="txtSupplierID='" & _
Me!Combo6.Column(0) & "'"

The above assumes that we can trust the field not to contain the
single-quote character ('). If we can't trust that, but can trust that
it won't contain the double-quote character ("), we can use this:

DoCmd.OpenForm "frmSuppliers", _
WhereCondition:="txtSupplierID=" & _
Chr(34) & Me!Combo6.Column(0) & Chr(34)

Chr(34) is the double-quote character.

If you can't trust either of these assumptions, things get slightly more
complicated. Then you have to use code to "double up" any quotes that
may be embedded in the value, like this:


DoCmd.OpenForm "frmSuppliers", _
WhereCondition:="txtSupplierID=" & _
Chr(34) & _
Replace(Me!Combo6.Column(0), """", """""") & _
Chr(34)

 

[Dirk Goldgar]

More thanks, Dirk! That got it for me! I was able to use your code
for two subforms that open different forms. This really makes the
database sparkle!

Now I see where I may have made an error on my first request:

[Dirk Goldgar wrote:]
If the name of the form can be determined from the value of the
txtType
column of the combo box, then it's not a problem. For example:

Select Case Me!cboProfile.Column(2) ' the third column
Case "CG"
DoCmd.OpenForm "Form1"
Case "FG"
DoCmd.OpenForm "Form2"
Case "BG"
DoCmd.OpenForm "Form3"
' ... and so on ...
End Select

This works fine but it doesn't filter to open the record that was
selected by the combo box. How can I filter to do this?

Okay, let's check assumptions. I believe we established previously that
the combo box is actually named "Combo10", so I'll use that in my
example. I'm guessing from the "txt" prefix on txtProfileID that this
is a text field, and I'll assume that it is known by the same name in
the recordsources of the forms the code may open. For simplicity, I'll
also assume that the Combo10's bound column is the first column,
txtProfileID, and I'll further assume that txtProfileID will never
contain the single-quote character (').

With those assumptions in mind, the code above might be revised as
follows:

Dim strWhere As String

strWhere = "txtProfileID='" & Me!Combo10 & "'"

Select Case Me!Combo10.Column(2) ' the third column
Case "CG"
DoCmd.OpenForm "Form1", WhereCondition:=strWhere
Case "FG"
DoCmd.OpenForm "Form2", WhereCondition:=strWhere
Case "BG"
DoCmd.OpenForm "Form3", WhereCondition:=strWhere
' ... and so on ...
End Select

Obviously, you'll have to change the form names to match those in your
application.

Thanks for your patience and guidance.

You're welcome.

BTW: I noticed your business name references Gnosticism. Are you a
student of Gnosis?

No, it's just my little joke. I figure those who get it will find it
amusing and so remember the name, and those who don't get it will puzzle
over it long enough to remember the name.

 

[Guest]

You are Da Man! I was always told to never assume but your assumptions were 100% on the dot. My database is now singing like one of the Three Tenors

I really appreciate all your help

Smart thinking about your business name - I know it worked for me

----- Dirk Goldgar wrote: ----

More thanks, Dirk! That got it for me! I was able to use your cod
for two subforms that open different forms. This really makes th
database sparkle

[Dirk Goldgar wrote:
If the name of the form can be determined from the value of th
txtTyp
column of the combo box, then it's not a problem. For example

Select Case Me!cboProfile.Column(2) ' the third colum

Case "CG
DoCmd.OpenForm "Form1
Case "FG
DoCmd.OpenForm "Form2
Case "BG
DoCmd.OpenForm "Form3
' ... and so on ..
End Selec

This works fine but it doesn't filter to open the record that wa

selected by the combo box. How can I filter to do this

Okay, let's check assumptions. I believe we established previously tha
the combo box is actually named "Combo10", so I'll use that in m
example. I'm guessing from the "txt" prefix on txtProfileID that thi
is a text field, and I'll assume that it is known by the same name i
the recordsources of the forms the code may open. For simplicity, I'l
also assume that the Combo10's bound column is the first column
txtProfileID, and I'll further assume that txtProfileID will neve
contain the single-quote character (')

With those assumptions in mind, the code above might be revised a
follows

Dim strWhere As Strin

strWhere = "txtProfileID='" & Me!Combo10 & "'

Select Case Me!Combo10.Column(2) ' the third colum
Case "CG
DoCmd.OpenForm "Form1", WhereCondition:=strWher
Case "FG
DoCmd.OpenForm "Form2", WhereCondition:=strWher
Case "BG
DoCmd.OpenForm "Form3", WhereCondition:=strWher
' ... and so on ..
End Selec

Obviously, you'll have to change the form names to match those in you
application

Thanks for your patience and guidance

You're welcome

BTW: I noticed your business name references Gnosticism. Are you
student of Gnosis

No, it's just my little joke. I figure those who get it will find i
amusing and so remember the name, and those who don't get it will puzzl
over it long enough to remember the name

 

[Dirk Goldgar]

You are Da Man! I was always told to never assume but your
assumptions were 100% on the dot. My database is now singing like one
of the Three Tenors!

I really appreciate all your help.

You're welcome.

Smart thinking about your business name - I know it worked for me!

;-)