Filtering a subform based on a combobox in the main form

[Sierras]

Hello,

I'm having trouble filtering a sub form based on a combo box in the
main form. I've tried the various suggestions here with the requery,
but I still can not get it to work. I'd appreciate any help

The database is for aircraft discrepancies.
The main form (frmMCC) has a combo box which allows users to select
the aircraft with the discrepancy. It is based on a query which
includes an autonumberid [ACNID], the serial number of the aircraft
[ACN] and the model of the aircraft [Model]

The subform (MCCdetails) allows users to select the aircraft messages
from a query which has the [msgID], the message [Msg] and the [Model]
of the aircraft it applies to.

I would like that when the users pick the aircraft serial number from
the combo box in the main form, the subform gets filtered to show only
the messages applicable to that particular model aircraft.

Thank you

 

[Allen Browne]

Use the AfterUpdate event procedure of the combo in the main form, create a
SQL string and assign it to the RowSource of the combo in the subform. Then
call the event from the main form's Current event procedure as well.

This kind of thing:

Private Sub SerialNumber_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT SomeField, AnotherField FROM SubformCombosLookupTable
WHERE SubformCombosLookupTable.SomeForeignKey = " & Nz(Me.SerialNumber, 0) &
";"


Me.[NameOfYourSubformControlHere].Form![NameOfSubformComboHere].RowSource =
strSQL
End Sub

Private Sub Form_AfterUpdate()
Call SerialNumber_AfterUpdate
End Sub

 

[Sierras]

Thanks for your response. I do not understand what is meant by
"SomeForeignKey" what is that?
Right now, I've got the following in the After Update event of the
combobox in the main form.

Private Sub ACN_AfterUpdate()
Dim strSQL As String

strSQL ="SELECT ACNID, Model FROM MCCDetailsALLQry
WHERE MCCDetailsALLQry.Model = & Nz(Me.ACN, 0) &
";"

Me.[MCCDetailALLSub]From![msgID].RowSource =
strSQL
End Sub

Then in the current event of the main form, I've got:

Private Sub Form_AfterUpdate()
Call ACN_AfterUpdate
EndSub

But it's not working. I get the error message MS Access can't find
the field "|" refered to in your expression.

 

[Allen Browne]

If I have understood you correctly, the idea is that the subform's combo
should list only the items that match the combo in the main form. That is
achieved by using the main form combo's AfterUpdate event to assign the
RowSource of the subform combo.

Currently, subform combo's RowSource will be a table (or query), and there
will be a field in that table which has to match the value of the main
form's combo. That field is what is intended by "SomeForeignKey".

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for your response. I do not understand what is meant by
"SomeForeignKey" what is that?
Right now, I've got the following in the After Update event of the
combobox in the main form.

Private Sub ACN_AfterUpdate()
Dim strSQL As String

strSQL ="SELECT ACNID, Model FROM MCCDetailsALLQry
WHERE MCCDetailsALLQry.Model = & Nz(Me.ACN, 0) &
";"

Me.[MCCDetailALLSub]From![msgID].RowSource =
strSQL
End Sub

Then in the current event of the main form, I've got:

Private Sub Form_AfterUpdate()
Call ACN_AfterUpdate
EndSub

But it's not working. I get the error message MS Access can't find
the field "|" refered to in your expression.

Use the AfterUpdate event procedure of the combo in the main form, create a
SQL string and assign it to the RowSource of the combo in the subform. Then
call the event from the main form's Current event procedure as well.

This kind of thing:

Private Sub SerialNumber_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT SomeField, AnotherField FROM SubformCombosLookupTable
WHERE SubformCombosLookupTable.SomeForeignKey = " & Nz(Me.SerialNumber, 0) &
";"


Me.[NameOfYourSubformControlHere].Form![NameOfSubformComboHere].RowSource =
strSQL
End Sub

Private Sub Form_AfterUpdate()
Call SerialNumber_AfterUpdate
End Sub

 

[Sierras]

I think it's getting better, but I'm still getting a message box
asking for a parameter. The foreignKey I guess would be the {Model]
of the aircraft. Something like "ABC-123" This is common to both
tables. I changed the data a little to try and understand what's
going on. Maybe you could direct me in the right direction...

The combobox in the main form called ACN gets its data from a table
called tblAircraft which has fields ACNID, ACSN, and Model.

The combo in subform called Messages200ID gets its data from a table
called tblMessages and has fields MsgID, Message, Model

So far, I've got this which is not working

Private Sub ACN_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT [tblmessages].[MsgID], [tblmessages].[Message],
[tblmessages].[Model] FROM tblmessages WHERE ([tblmessages].[Model]) =
" & Nz(Me.ACN.Column(3), 0) & ";"

Me.MCC_Details_Sub.Form![Messages200ID].RowSource = strSQL
End Sub


Right now, the after update gives a message box asking for the "ABC"
parameter. That would be the first 3 letters of the model.

Any ideas??
Thanks

 

[Allen Browne]

From your example, Model appears to be a Text field (not a Number field). If
so, the SQL string needs quotes around the literal value:

strSQL = "SELECT [tblmessages].[MsgID], [tblmessages].[Message],
[tblmessages].[Model] FROM tblmessages WHERE
([tblmessages].[Model]) = """ & Me.ACN.Column(3) & """;"


The Nz() is not needed for strings: if the value is blank, the value becomes
a zero-length string.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I think it's getting better, but I'm still getting a message box
asking for a parameter. The foreignKey I guess would be the {Model]
of the aircraft. Something like "ABC-123" This is common to both
tables. I changed the data a little to try and understand what's
going on. Maybe you could direct me in the right direction...

The combobox in the main form called ACN gets its data from a table
called tblAircraft which has fields ACNID, ACSN, and Model.

The combo in subform called Messages200ID gets its data from a table
called tblMessages and has fields MsgID, Message, Model

So far, I've got this which is not working

Private Sub ACN_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT [tblmessages].[MsgID], [tblmessages].[Message],
[tblmessages].[Model] FROM tblmessages WHERE ([tblmessages].[Model]) =
" & Nz(Me.ACN.Column(3), 0) & ";"

Me.MCC_Details_Sub.Form![Messages200ID].RowSource = strSQL
End Sub


Right now, the after update gives a message box asking for the "ABC"
parameter. That would be the first 3 letters of the model.

Any ideas??
Thanks

If I have understood you correctly, the idea is that the subform's combo
should list only the items that match the combo in the main form. That is
achieved by using the main form combo's AfterUpdate event to assign the
RowSource of the subform combo.

Currently, subform combo's RowSource will be a table (or query), and there
will be a field in that table which has to match the value of the main
form's combo. That field is what is intended by "SomeForeignKey".

 

[Sierras]

THANKYOU THANKYOU THANKYOU!!
This is working great. At least the subform is filtered for new
records. The only problem now is when I just want to scroll through
the records in the main form.

The way it's working, the subform shows the correct message when the
previous record was the same aircraft model.
However, when the previous record was a different model, the combo box
shows blank and the only way to get it to appear is to click the combo
box or click next record and then back (but that only works if the
next record is the same model as well)

Any way to automate this? I have the Call ACN_AfterUpdate in the main
forms current event. Maybe I have to put that somewhere else as
well??

 

[Allen Browne]

If the bound column of the combo is zero-width, what you are seeing is
probably a bug in Access, discussed in the later part of this article:
http://allenbrowne.com/bug-06.html

It has been like that for at least the last 6 versions, so don't hold your
breath for Microsoft to fix this one.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

THANKYOU THANKYOU THANKYOU!!
This is working great. At least the subform is filtered for new
records. The only problem now is when I just want to scroll through
the records in the main form.

The way it's working, the subform shows the correct message when the
previous record was the same aircraft model.
However, when the previous record was a different model, the combo box
shows blank and the only way to get it to appear is to click the combo
box or click next record and then back (but that only works if the
next record is the same model as well)

Any way to automate this? I have the Call ACN_AfterUpdate in the main
forms current event. Maybe I have to put that somewhere else as
well??

From your example, Model appears to be a Text field (not a Number field). If
so, the SQL string needs quotes around the literal value:

strSQL = "SELECT [tblmessages].[MsgID], [tblmessages].[Message],
[tblmessages].[Model] FROM tblmessages WHERE
([tblmessages].[Model]) = """ & Me.ACN.Column(3) & """;"


The Nz() is not needed for strings: if the value is blank, the value becomes
a zero-length string.

 

[Sierras]

I'm not sure if this is the problem as the records show up fine when
the previous record had the same model.

It's like the subform needs to be refreshed or requeried or something

How can I tell if this access problem is what is actually happening
here? What is meant by "If the bound column of the combo is
zero-width" I did a search in the Access help window and could not
find anything on this. As far as I know, this is happening whether
the bound column in the combo has data in it or not.

 

[Sierras]

One more thing, if I press the refresh button from the records menu,
then the records show up. Maybe I could enter this automaticaly
somewhere?

 

[Sierras]

OK I think I got it.
I put in a refresh statement in the on current event of the main form.
So now I've ogt:

Private Sub Form_AfterUpdate()
Call ACN_AfterUpdate
Me.MCCDetailALLSub.From.Refresh
EndSub

This looks like it's doing what it's supposed to do. Can't find
anything wrong for now unless you think of something.

Thanks Allen for all your help

 

[Sierras]

One more thing on this which is working great.
What do I have to add to this to get it to sort it by [Message]

Thanks

 

[Allen Browne]

Access is really good at helping you create your SQL statements.

1. Create a query into tblMessages.
2. Drag MsgIID, Message, and Model into the grid.
3. Type some value in the Criteria row under Model.
4. Choose Ascending in the Sorting row under Message.
5. Switch to SQL View (View menu).

You can now see what you need to produce to achieve the sorting, and where
the ORDER BY clause has to go in your string.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

One more thing on this which is working great.
What do I have to add to this to get it to sort it by [Message]

Thanks

From your example, Model appears to be a Text field (not a Number field). If
so, the SQL string needs quotes around the literal value:

strSQL = "SELECT [tblmessages].[MsgID], [tblmessages].[Message],
[tblmessages].[Model] FROM tblmessages WHERE
([tblmessages].[Model]) = """ & Me.ACN.Column(3) & """;"


The Nz() is not needed for strings: if the value is blank, the value becomes
a zero-length string.

 

[Sierras]

Thanks Allen - that works very well.
Actually, I like this better than you just giving me the right code to
write. Takes a little longer, but I actually learned something today.
Guess I could go to bed now

Thanks again!!