On Open

[Guest]

I'd like to open a form and filter based on another form's combo box.

The form and cbox is:
frmFinishedGoods.cbProfileID

The form I'd like to open based on the above form's cbox is:
frmQueryFGProcessingFacIDsLineIDs.cbNavigateProfiles

cbProfileID and cbNavigateProfiles source the same table and field. I'd like
cbNavigateProfiles to filter to the record in cbProfileID.

How can I code the On Open property of my form to achieve this?

Thanks for your help!

 

[Albert D. Kallal]

Well, where in code do you open this new form?

Just use the where clause as follows:


dim strWhere as string


strWhere = "cbProFileID = " & forms!frmFinishedGoods!cbProfileID
docmd.Openform "frmQueryFGProcessingFacIdsLindID",,,strWhere

 

[Guest]

Thanks, Albert!

I created a button that opens forms from a combobox list. I open the form
through this.

I placed the code in the form's On Open property. When I execute the button
I'm asked to enter in a parameter for cbProfileID. The debugger points to
this line:

DoCmd.OpenForm "frmQueryFGProcessingFacIDsLineIDs", , , strWhere

???

 

[Albert D. Kallal]

I placed the code in the form's On Open property. When I execute the
button
I'm asked to enter in a parameter for cbProfileID. The debugger points to
this line:

DoCmd.OpenForm "frmQueryFGProcessingFacIDsLineIDs", , , strWhere

???

Why would we place the above code in the on-open event? Try reading the code
before pasting it!!!

The docmd.OpenForm will OPEN a ms-access form.

So, common sense would seem to indicate that we want to place this code
behind the button on our first form.

So, remove the code from the forms on-open. We don't need it.

further, the "strWhere" code is critical for correct operation, and you
failed to post/include this in your question. (did you forget this by
accident, or did you not realizes that the strWhere part is the whole key
concept to make this work).

So, we don't need any code in the forms on-open event. the ONLY place we
need code is behind our button on the form.

 

[Dirk Goldgar]

I'd like to open a form and filter based on another form's combo box.

The form and cbox is:
frmFinishedGoods.cbProfileID

The form I'd like to open based on the above form's cbox is:
frmQueryFGProcessingFacIDsLineIDs.cbNavigateProfiles

cbProfileID and cbNavigateProfiles source the same table and field.
I'd like cbNavigateProfiles to filter to the record in cbProfileID.

How can I code the On Open property of my form to achieve this?

Thanks for your help!

Hi, John! Long time no hear.

Could you clarify this a bit? I'm not sure I understand what you're
trying to do. "frmQueryFGProcessingFacIDsLineIDs.cbNavigateProfiles"
looks like a reference to a combo box, not the name of a form.

 

[Guest]

I'm confused. I first placed the code behind the button but the prompt to
enter a cbProfileID appeared and I thought I had it wrong. Also, won't
placing this behind the button then limit it's function? I use the button to
execute one of any number of forms in a cbox list. I don't need them to all
filter to the cbProfileID record.

 

[Albert D. Kallal]

I'm confused. I first placed the code behind the button but the prompt to
enter a cbProfileID appeared and I thought I had it wrong. Also, won't
placing this behind the button then limit it's function? I use the button
to
execute one of any number of forms in a cbox list. I don't need them to
all
filter to the cbProfileID record.

ah..ok - this just means we are making progress!!!

Well, we could try and place some code in the form that loads, but what
happens when you want to open that form, and NOT filter the data? So, we
should likely try to get this to work in the code that OPENS THE form, not
the code in the form its self (since, as mentioned, we might want to open
that form from several different places in the application - and, perhaps
NOT always filter).

So, as a goal, lets try and get this to work with your button code:

You might have to paste what you have for your button code now, but in that
code, simply check for that particular form, and then use our code.


if "some form name" = " our form we want to filter" then

dim strWhere as string
strWhere = "cbProFileID = " & forms!frmFinishedGoods!cbProfileID
docmd.Openform "frmQueryFGProcessingFacIdsLindID",,,strWhere

else

your other code goes here

end if

Do feell free to post your buttion code that you have now, and we should be
able to figire out how to add the abve code....

 

[Guest]

Hi, Dirk! Hope all is well. I was actually searching for your name in
particular today to see if I could find something of yours regarding this.

We must be on the same telepathic wavelength.

How awesome to have you and Albert - two mighty MVP's helping me out with
this!

Here is my button code:

Private Sub cmdGOrpt_Click()
On Error GoTo Err_cmdGOrpt_Click

Dim stDocName As String

Dim strWhere As String

strWhere = "cbProfileID = " & Forms!frmFinishedGoods!cbProfileID
DoCmd.OpenForm "frmQueryFGProcessingFacIDsLineIDs", , , strWhere

DoCmd.OpenForm Me!cbSelectReportRQ, acNormal

Exit_cmdGOrpt_Click:
Exit Sub

Err_cmdGOrpt_Click:
MsgBox Err.Description
Resume Exit_cmdGOrpt_Click

End Sub

I know it's screwy but my brain is mush from dealing with this and some
other pretty hairy stuff today.

I'm simply trying to open frmQueryFGProcesingFacIDsLineIDs and have
cbNavigateProfiles filter to the record from frmFinishedGoods.cbProfileID.

Maybe you or Albert can see where I've gone off track?

 

[Dirk Goldgar]

Hi, Dirk! Hope all is well. I was actually searching for your name in
particular today to see if I could find something of yours regarding
this.

We must be on the same telepathic wavelength.

How awesome to have you and Albert - two mighty MVP's helping me out
with this!

LOL Maybe Albert's mighty.

Here is my button code:

Private Sub cmdGOrpt_Click()
On Error GoTo Err_cmdGOrpt_Click

Dim stDocName As String

Dim strWhere As String

strWhere = "cbProfileID = " & Forms!frmFinishedGoods!cbProfileID
DoCmd.OpenForm "frmQueryFGProcessingFacIDsLineIDs", , , strWhere

DoCmd.OpenForm Me!cbSelectReportRQ, acNormal

Exit_cmdGOrpt_Click:
Exit Sub

Err_cmdGOrpt_Click:
MsgBox Err.Description
Resume Exit_cmdGOrpt_Click

End Sub

I know it's screwy but my brain is mush from dealing with this and
some other pretty hairy stuff today.

I'm simply trying to open frmQueryFGProcesingFacIDsLineIDs and have
cbNavigateProfiles filter to the record from
frmFinishedGoods.cbProfileID.

Maybe you or Albert can see where I've gone off track?

Bear with me for another question or two. What do you mean when you say
you want to "have cbNavigateProfiles filter to the record from
frmFinishedGoods.cbProfileID"? Presumably, cbNavigateProfiles is a
combo box that shows ProfileIDs. Do you mean that you want this combo
box's dropdown list to show only the ProfileID from
frmFinishedGoods.cbProfileID? Or do you mean that you want to
"automatically select" that profile in cbNavigateProfiles? Or something
else?

You can certainly stuff the current value of
frmFinishedGoods.cbProfileID into
frmQueryFGProcessingFacIDsLineIDs.cbNavigateProfiles, if that's what you
intend (and if it's in the combo's list). You could do it like this:

DoCmd.OpenForm "frmQueryFGProcessingFacIDsLineIDs"

Forms!frmQueryFGProcessingFacIDsLineIDs!cbProfileID = _
Forms!frmFinishedGoods!cbProfileID

But I don't know if that's what you have in mind or not. If it is, is
anything supposed to happen automatically when you do it? Setting the
value this way won't fire the control's AfterUpdate event, so if there's
code that's supposed to run when the value is changed, you have to call
it yourself.

 

[Guest]

Bear with me for another question or two. What do you mean when you say
you want to "have cbNavigateProfiles filter to the record from
frmFinishedGoods.cbProfileID"? Presumably, cbNavigateProfiles is a
combo box that shows ProfileIDs. Do you mean that you want this combo
box's dropdown list to show only the ProfileID from
frmFinishedGoods.cbProfileID?

Yes! I have a record selected in frmFinishedGoods.cbProfileID and when I
click my "Go!" button I want
frmQueryFGProcessingFacIDsLineIDs.cbNavigateProfiles to open to that same
record.

 

[Dirk Goldgar]

Yes! I have a record selected in frmFinishedGoods.cbProfileID and
when I click my "Go!" button I want
frmQueryFGProcessingFacIDsLineIDs.cbNavigateProfiles to open to that
same record.

That's still not making a lot of sense to me, sorry. Combo boxes don't
"open", the way I think about them. Please verify that the code I
posted doesn't do what you're after, as it would make sense to me for
that to be what you want. If not, I'll have to delve deeper.

 

[Guest]

I placed the code in the button's click event:

Private Sub cmdGOrpt_Click()
On Error GoTo Err_cmdGOrpt_Click

Dim stDocName As String

DoCmd.OpenForm "frmQueryFGProcessingFacIDsLineIDs"

Forms!frmQueryFGProcessingFacIDsLineIDs!cbProfileID = _
Forms!frmFinishedGoods!cbProfileID

DoCmd.OpenForm Me!cbSelectReportRQ, acNormal

Exit_cmdGOrpt_Click:
Exit Sub

Err_cmdGOrpt_Click:
MsgBox Err.Description
Resume Exit_cmdGOrpt_Click

End Sub

I hope I've understood properly. When I click the button the form opens
however I get the error dialog:
MS Access can't find the field 'cbProfileID' referred to in your expression.

I click OK to close the dialog and the form is open but NOT filtered to the
correct record.

frmFinishedGoods.cbProfileID is most definitely correct. Not sure why it
wouldn't be found.

 

[Dirk Goldgar]

I placed the code in the button's click event:

Private Sub cmdGOrpt_Click()
On Error GoTo Err_cmdGOrpt_Click

Dim stDocName As String

DoCmd.OpenForm "frmQueryFGProcessingFacIDsLineIDs"

Forms!frmQueryFGProcessingFacIDsLineIDs!cbProfileID = _
Forms!frmFinishedGoods!cbProfileID

DoCmd.OpenForm Me!cbSelectReportRQ, acNormal

Exit_cmdGOrpt_Click:
Exit Sub

Err_cmdGOrpt_Click:
MsgBox Err.Description
Resume Exit_cmdGOrpt_Click

End Sub

I hope I've understood properly. When I click the button the form
opens however I get the error dialog:
MS Access can't find the field 'cbProfileID' referred to in your
expression.

I click OK to close the dialog and the form is open but NOT filtered
to the correct record.

This is the first time you've said you want to filter the *form*. Up to
this point, you've said repeatedly that you want to filter the combo box
(which hasn't made a lot of sense to me). Which is it you want to
filter, the form or the combo box?

I do see that I made a mistake in the code I posted; I posted this:

Forms!frmQueryFGProcessingFacIDsLineIDs!cbProfileID = _

Forms!frmFinishedGoods!cbProfileID

when I meant to post this:

Forms!frmQueryFGProcessingFacIDsLineIDs!cbNavigateProfiles = _
Forms!frmFinishedGoods!cbProfileID

I'm not sure now whether that's what you want or not, but please fix
that and try it again.

If it doesn't give you what you want, then I'm guessing you really want
to filter the *form*. In that case, please post the recordsource of
form "frmQueryFGProcessingFacIDsLineIDs". If it's a table, please give
the name of the field that holds the ProfileID value you want to filter
by; if it's a query, please post the SQL of the query.

 

[Guest]

PERFECT! That works! Sorry - I didn't recognize the typo, either and should
have.

As always - Thanks!!! I hope others find this useful.

 

[Dirk Goldgar]

PERFECT! That works! Sorry - I didn't recognize the typo, either and
should have.

Ah, very good. I think Albert and I were both thinking you needed
something more complicated.

As always - Thanks!!! I hope others find this useful.

You're welcome.

 

[Guest]

I tend to come across as complicated despite the fact that I'm rather
incapable of being so!

I thought I described it accurately but I see now where it wasn't.

Thanks for your patience and help, guys!