filtering a form

[johnlute]

Access 2003 and Win2000

I'm trying to open a form [frmPackaging] via a listbox [cbMainSelect]
from another form [frmKEYSTONEeditids] and then set a control
[frmPackaging].[cbqryTypes] to the value in Column 2 of
[cbMainSelect]:

DoCmd.OpenForm "frmPackaging" Forms![frmPackaging].Form![cbqryTypes] =
Forms![KEYSTONEeditids].Form![cbMainSelect].Column(2)

This works fine. [frmPackaging] opens and [cbqryTypes] displays the
value in Column 2 of [cbMainSelect].

The challenge I can't resolve is filtering [frmPackaging] to the value
that I'm "pushing" into [cbqryTypes]. [cbqryTypes] is also used to
select values to filter [frmPackaging] using an AfterUpdate:
Forms![frmPackaging].Form.Refresh
Forms![frmPackaging].Form.Requery

Of course, the Record Source for [frmPackaging] has a criteria:
[Forms]![frmPackaging].[Form]![cbqryTypes] Or [Forms]![frmPackaging].
[Form]![cbqryTypes] Is Null

Clicking [cbMainSelect] works fine to open [frmPackaging] except that
the value that's pushed into it results in the form NOT being
filtered.

I've tried several things but nothing works. I'm not even sure that
what I'm currently doing is the best approach.

Does anyone have any ideas how to resolve?

Thanks for your time!

 

[Dale Fye]

John,

Use the WhereCondition argument of the docmd.openform method.

Something like:

Dim strCriteria as string
strCriteria = "[SomeField] = " & me.cbMainSelected
Docmd.OpenForm "frmPackaging",,,strCriteria

 

[johnlute]

Hi, Dale!

Thanks for the insight. I gave this a try:
Dim strCriteria As String
strCriteria = "Forms![frmPackaging].Form![cbqryTypes] = " &
Me.cbMainSelect.Column(2)
DoCmd.OpenForm "frmPackaging", , , strCriteria

If I click on the [[cbMainSelect] value that has "CG" in Column(2) I
get a parameter prompt that has "CG" and a parameter box. I don't want
that prompt so when I bypass it then the form opens without any value
appearing in [cbqrytypes] and the form is opened to a new record with
no value in [cbqryTypes].

Any thoughts?

Thanks!

John,

Use the WhereCondition argument of the docmd.openform method.

Something like:

Dim strCriteria as string
strCriteria = "[SomeField] = " & me.cbMainSelected
Docmd.OpenForm "frmPackaging",,,strCriteria

----
HTH
Dale

Access 2003 and Win2000

I'm trying to open a form [frmPackaging] via a listbox [cbMainSelect]
from another form [frmKEYSTONEeditids] and then set a control
[frmPackaging].[cbqryTypes] to the value in Column 2 of
[cbMainSelect]:

DoCmd.OpenForm "frmPackaging" Forms![frmPackaging].Form![cbqryTypes] =
Forms![KEYSTONEeditids].Form![cbMainSelect].Column(2)

This works fine. [frmPackaging] opens and [cbqryTypes] displays the
value in Column 2 of [cbMainSelect].

The challenge I can't resolve is filtering [frmPackaging] to the value
that I'm "pushing" into [cbqryTypes]. [cbqryTypes] is also used to
select values to filter [frmPackaging] using an AfterUpdate:
Forms![frmPackaging].Form.Refresh
Forms![frmPackaging].Form.Requery

Of course, the Record Source for [frmPackaging] has a criteria:
[Forms]![frmPackaging].[Form]![cbqryTypes] Or [Forms]![frmPackaging].
[Form]![cbqryTypes] Is Null

Clicking [cbMainSelect] works fine to open [frmPackaging] except that
the value that's pushed into it results in the form NOT being
filtered.

I've tried several things but nothing works. I'm not even sure that
what I'm currently doing is the best approach.

Does anyone have any ideas how to resolve?

Thanks for your time!
.- Hide quoted text -

- Show quoted text -

 

[johnlute]

Hi, Dale.

You know something? I had a brain cramp. I'm in the middle of a major
re-design and got lost in what I was doing. This is a little more
complicated. I forgot that I used [cbMainSelect] to open forms via a
lookup:

Private Sub cbMainSelect_AfterUpdate()
Dim strFormToOpen As String

With Me!cbMainSelect
'If no selection has been made, we can't do anything useful.
If IsNull(.Value) Then
Exit Sub
End If

'Here a selection has been made, so open the appropriate form
'for the selection.

'Lookup the form, returning a null string if none is
specified.
strFormToOpen = vbNullString & _
DLookup("FormToOpen", "tblProfileTypes", _
"txtProfileType=""" & .Column(2) & """")
DoCmd.OpenForm strFormToOpen, acNormal

End With

End Sub

So now the trick really is to open [frmPackaging] IF it's the one
found in the lookup and then filter it to the value in
cbMainSelect].Column(2) by "pushing" that value into [frmPackaging].
[cbqrytypes]

I know this is a bit much for this forum so I won't feel bad if you
bail!

I'll keep poking at it...

 

[johnlute]

<GAK> The following results in all selections opening EXCEPT the ones
with "PK" in Column(1).

Private Sub cbMainSelect_AfterUpdate()
Dim strFormToOpen As String
Dim strCriteria As String
strCriteria = "Forms![frmPackaging].Form![cbqryTypes] = Forms!
[KEYSTONEeditids].Form![cbMainSelect].Column(2)"

With Me!cbMainSelect
'If no selection has been made, we can't do anything useful.
If IsNull(.Value) Then
Exit Sub
End If

'Here a selection has been made, so open the appropriate form
'for the selection.

'Lookup the form, returning a null string if none is
specified.
strFormToOpen = vbNullString & _
DLookup("FormToOpen", "tblProfileTypes", _
"txtProfileType=""" & .Column(2) & """")
If Me.cbMainSelect.Column(1) = "PK" Then
DoCmd.OpenForm "frmPackaging", , , strCriteria
End If
DoCmd.OpenForm strFormToOpen, acNormal

End With

End Sub

 

[johnlute]

A little more tweaking and everything opens fine but I just can't seem
to get [frmPackaging] to filter to the value in [cbMainSelect].Column
(2)

Private Sub cbMainSelect_AfterUpdate()
Dim strFormToOpen As String
Dim strCriteria As String
strCriteria = "Forms![frmPackaging].Form![cbqryTypes] = Forms!
[KEYSTONEeditids].Form![cbMainSelect].Column(2)"

With Me!cbMainSelect
'If no selection has been made, we can't do anything useful.
If IsNull(.Value) Then
Exit Sub
End If

'Here a selection has been made, so open the appropriate form
'for the selection.

'Lookup the form, returning a null string if none is
specified.
strFormToOpen = vbNullString & _
DLookup("FormToOpen", "tblProfileTypes", _
"txtProfileType=""" & .Column(2) & """")
If Me.cbMainSelect.Column(1) = "PK" Then
DoCmd.OpenForm strFormToOpen, acNormal,
strCriteria
End If
DoCmd.OpenForm strFormToOpen, acNormal

End With

End Sub

 

[Dirk Goldgar]

A little more tweaking and everything opens fine but I just can't seem
to get [frmPackaging] to filter to the value in [cbMainSelect].Column
(2)

Private Sub cbMainSelect_AfterUpdate()
Dim strFormToOpen As String
Dim strCriteria As String
strCriteria = "Forms![frmPackaging].Form![cbqryTypes] = Forms!
[KEYSTONEeditids].Form![cbMainSelect].Column(2)"

With Me!cbMainSelect
'If no selection has been made, we can't do anything useful.
If IsNull(.Value) Then
Exit Sub
End If

'Here a selection has been made, so open the appropriate form
'for the selection.

'Lookup the form, returning a null string if none is
specified.
strFormToOpen = vbNullString & _
DLookup("FormToOpen", "tblProfileTypes", _
"txtProfileType=""" & .Column(2) & """")
If Me.cbMainSelect.Column(1) = "PK" Then
DoCmd.OpenForm strFormToOpen, acNormal,
strCriteria
End If
DoCmd.OpenForm strFormToOpen, acNormal

End With

End Sub

John, I haven't read through this thread very carefully, but you might try
this:

'------ start of suggested code ------
Private Sub cbMainSelect_AfterUpdate()

Dim strFormToOpen As String
Dim strProfileType As String

With Me!cbMainSelect

'If no selection has been made, we can't do anything useful.
If IsNull(.Value) Then
' Do nothing
Else

'Here a selection has been made, so open the appropriate form
'for the selection.

'Lookup the form, returning a null string if none is specified.

strProfileType = .Column(2)

strFormToOpen = vbNullString & _
DLookup("FormToOpen", "tblProfileTypes", _
"txtProfileType=""" & strProfileType & """")

'If we have a form to open, do that.

If Len(strFormToOpen) > 0 Then

DoCmd.OpenForm strFormToOpen, acNormal

'If this is the packaging form, set it up to show the
'desired profile type.
If strFormToOpen = "frmPackaging" Then
With Forms!frmPackaging
!cbqryTypes = strProfileType
.Requery
End With
End If

End If

End If

End With

End Sub
'------ end of suggested code ------

It may well be possible to filter the opened form by criteria specified in
the call to OpenForm, and that would be more efficient, but I don't know the
full recordsource of that form, and I'd need to in order to supply an
appropriate where-condition. If I understood what I read in your preceding
messages, I believe the above approach ought to at least work.

 

[johnlute]

Hi, Dirk!

Why is it that when you post "you might TRY" that it seems to work on
the very first try?!

I actually re-posted this problem with a little more clarity (so I
thought) and finally resolved it however your solution is much more
sleek!

Thanks so much!

A little more tweaking and everything opens fine but I just can't seem
to get [frmPackaging] to filter to the value in [cbMainSelect].Column
(2)

Private Sub cbMainSelect_AfterUpdate()
Dim strFormToOpen As String
Dim strCriteria As String
strCriteria = "Forms![frmPackaging].Form![cbqryTypes] = Forms!
[KEYSTONEeditids].Form![cbMainSelect].Column(2)"

   With Me!cbMainSelect
       'If no selection has been made, we can't do anything useful.
       If IsNull(.Value) Then
           Exit Sub
       End If

       'Here a selection has been made, so open the appropriateform
       'for the selection.

       'Lookup the form, returning a null string if none is
specified.
       strFormToOpen = vbNullString & _
           DLookup("FormToOpen", "tblProfileTypes", _
                   "txtProfileType=""" & .Column(2) & """")
                   If Me.cbMainSelect.Column(1) ="PK" Then
                       DoCmd.OpenForm strFormToOpen, acNormal,
strCriteria
                   End If
           DoCmd.OpenForm strFormToOpen, acNormal

   End With

End Sub

John, I haven't read through this thread very carefully, but you might try
this:

'------ start of suggested code ------
Private Sub cbMainSelect_AfterUpdate()

    Dim strFormToOpen As String
    Dim strProfileType As String

    With Me!cbMainSelect

        'If no selection has been made, we can't do anything useful.
        If IsNull(.Value) Then
                ' Do nothing
        Else

            'Here a selection has been made, so open the appropriate form
            'for the selection.

            'Lookup the form, returning a null string if noneis specified.

            strProfileType = .Column(2)

            strFormToOpen = vbNullString & _
                DLookup("FormToOpen", "tblProfileTypes", _
                            "txtProfileType=""" & strProfileType & """")

            'If we have a form to open, do that.

            If Len(strFormToOpen) > 0 Then

                DoCmd.OpenForm strFormToOpen, acNormal

                'If this is the packaging form, set it upto show the
                'desired profile type.
                If strFormToOpen = "frmPackaging" Then
                    With Forms!frmPackaging
                        !cbqryTypes = strProfileType
                        .Requery
                    End With
                End If

            End If

        End If

    End With

End Sub
'------ end of suggested code ------

It may well be possible to filter the opened form by criteria specified in
the call to OpenForm, and that would be more efficient, but I don't know the
full recordsource of that form, and I'd need to in order to supply an
appropriate where-condition.  If I understood what I read in your preceding
messages, I believe the above approach ought to at least work.

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -