Filtering on a field in a subform

G

Guest

With these tables: tblShipments (main), tblInvoices (sub)
and an unbound combo in the header: cboShowShip

I'm using the following code, adapted from Allen Browne's example for this
problem:

Private Sub cboShowShip_AfterUpdate()

Dim strSQL As String
If IsNull(Me.cboShowShip) Then
' If the combo is Null, use the whole table as the RecordSource.
Me.RecordSource = "tblShipments"
Else
strSQL = "SELECT DISTINCTROW tblShipments.* FROM tblShipments " & _
"INNER JOIN tblInvoices ON " & _
"tblShipments.ShipmentID = tblInvoices.InvoiceID " & _
"WHERE tblInvoices.InvoiceID = " & Me.cboShowShip & ";"
Me.RecordSource = strSQL
End If

End Sub


Using a test invoice number in the subform, which appears in two different
shipments, I enter the number, but the result is a blank record, filter count
1. I want to see the two records for which that invoice number appears in
the subform of course. Any ideas here for me?

tia
 
S

Steve Schapel

Ricter,

You have more than one Shipment with the same InvoiceID? The SQL you
have constructed is confused and confusing, and I think it is because
the relationships here are a but topsy-turvy. Seems to me that
tblInvoices table should be "main" and tblShipments table should be
"sub". Seeing the two records on the main form corresponding with a
record on the subform is not normally the way she is done.
 
G

Guest

I have more than one Shipment with the same invoice number, and this occurs
in cases of backorders. First shipment is a partial, but the invoice number
is entered. A second shipment occurs, with the backordered items, and the
invoice number is entered again. This is done separately because, for
customs purposes, I have to treat each shipment separately.

So yeah, a shipment can have many invoice numbers, and occasionally an
invoice number will appear in a couple of shipments. I simply want to be
able to filter my main form by an invoice number (which are displayed in the
subform).
 
S

Steve Schapel

Ricter,

Thanks for the further clarification.

Well, I think you will need to adjust your table structure to reflect
the reality of your data relationships. If I understand you correctly,
at the moment an invoice "knows" which shipment it is related to by
virtue of the ShipmentID foreign key field in the tblInvoices table.
Right? And each Invoice in the tblInvoices table is unique, with a
unique InvoiceID, right? So your table structure at the moment does not
allow for the possibilty of more than one shipment being associated with
any given invoice. You will need an additional table, for the
ShipmentInvoice, which will contain a ShipmentID and InvoiceID fields,
to relate to each of the existing tables, and remove the ShipmentID
field from the Invoices table. This article may be of interest to you...
http://accesstips.datamanagementsolutions.biz/many.htm
You will need to leave a consideration of the form design until after
the tables are set up correctly.
 
G

Guest

You're absolutely right, it is a m:m relationship and I should have created a
link table for the two entities. But I didn't. But even though my design is
not perfectly normalized I should still be able to make the filter work.
 
S

Steve Schapel

Ricter,

Regarding "I should still be able to make the filter work", how would
this happen? I can't see at the moment how this would be possible. Can
you let us know some details of your table design at the moment, with
some examples related to this question? Thanks.
 
G

Guest

Ok Steve,

tblShipments, primary key ShipmentID
tblInvoices, primary key InvoiceID, foreign key ShipmentID

Record 1
Shipment No. 11111
subInvoices:
INV12345
INV67890
INV34567

Record 2
Shipment No. 11112
subInvoices:
INV09876
INV87654

Record 3
Shipment No. 11113
subInvoices:
INV65432
INV12345

So of course, I want to apply a filter on INV12345 and so only have Record 1
and Record 3 in my filterset...
 
S

Steve Schapel

Ricter,

I am very sorry if I am being obtuse here. Yes, I understood (I think)
what you are trying to achieve. What I can't get my head around, in
terms of your example, is this... In the tblInvoices table, there is
only one record for INV12345. Am I right? And in this table, there is
only one foreign key ShipmentID field. Right? So there is only
provision in your database for one Shipment to be associated with any
given Invoice. Right? So how can INV12345 be an invoice for Shipment
11111 *and* Shipment 11113? Where is this information recorded, and
retrievable from? Sorry, I can't see this from your example. I can see
how any given Shipmement can be associated with multiple Invoices, but I
can't figure how the data example is possible with the table structure
you showed us.
 
G

Guest

No worries Steve,
tblInvoices may have multiple records of INV12345, associated with different
ShipmentID's. I know, it's not fully normalized, and that was my error. My
only consolation is that it's not a common occurence.

Only one foreign key, ShipmentID, yes.

The info from an invoice, and its association with one or more shipments, is
in tblInvoices. It's possible simply because the invoice number can repeat
in that table.

Thanks a ton for sticking with me on this.

Rick
 
S

Steve Schapel

Rick,

Ok, I had assumed INV12345 was the entry in the InvoiceID field, which
is Primary Key. So... it is InvoiceID, and InvoiceID is not primary
key? Or, InvoiceID is primary key, but INV12345 is an another field? I
will assume the former.

Ok, so let's go back to your original question. Seems to me it should
be like this...

strSQL = "SELECT DISTINCTROW tblShipments.* FROM tblShipments " & _
"INNER JOIN tblInvoices ON " & _
"tblShipments.ShipmentID = tblInvoices.ShipmentID " & _
"WHERE tblInvoices.InvoiceID = " & Me.cboShowShip
(assuming InvoiceID is number)
 
G

Guest

Actually, it's the latter, Steve. InvoiceID is the pk, autonumber.
InvoiceNum is where "INV12345" would be found...
 
S

Steve Schapel

Rick,

Ok, so I guess InvoiceNum is Text, and also it is InvoiceNum which is
the Bound Column of cboShowShip. Yes? No?

Well, how does this go...
strSQL = "SELECT DISTINCTROW tblShipments.* FROM tblShipments " & _
"INNER JOIN tblInvoices ON " & _
"tblShipments.ShipmentID = tblInvoices.ShipmentID " & _
"WHERE tblInvoices.InvoiceNum = '" & Me.cboShowShip & "'"
 
G

Guest

Steve,
Had to change the bound column of cboShowShip to '3', which is InvoiceNum.
The filter is working, w00t!

However.... I can't "turn it off". When I delete the contents of
cboShowShip, I get a popup called "Enter parameter value", asking for
ShipmentID.

Here's the current code (note the correction of one field, InvoiceNum is
actually VendorInvNum):

Private Sub cboShowShip_AfterUpdate()

Dim strSQL As String
If IsNull(Me.cboShowShip) Then
Me.RecordSource = tblShipments
Else
strSQL = "SELECT DISTINCTROW tblShipments.* FROM tblShipments " & _
"INNER JOIN tblInvoices ON " & _
"tblShipments.ShipmentID = tblInvoices.ShipmentID " & _
"WHERE tblInvoices.VendorInvNum = '" & Me.cboShowShip & "'"
Me.RecordSource = strSQL
End If
End Sub
 
G

Guest

It's no longer giving me the ShipmentID popup, but now it's hanging up on the
Form's On_Current event. Says it can't find fields specified in that event.
Hmm...
 
G

Guest

Run-time error '2424':
"The expression you entered has a field, control, or property name that
Microsoft Access can't find.

The debugger then points to this line in the form's On_Current event:
If TransType = "Export" Or TransType = "Amend" Or TransType = "Refund" Then
 
S

Steve Schapel

So, what is TransType? If it's a field name, is the field included in
the query that the form is bsed on? If so, is there a control on the
form bound to this field? If so, is the control also named TransType?
If you type this in the VBE...
Me.Tr
.... does the Intellisense auto-complete with TransType?
 
S

Steve Schapel

... what is the Record Source of the form prior to the running of the
cboShowShip code?
 
G

Guest

TransType is Transaction Type, which is basically a shipment type. A
shipment can have only one transaction type.

The form, if I understand you, is not based on a query. It's recordsource
is simply "tblShipments".

There is a combobox on the form, called TransType, bound to the field. It's
rowsource type is a value list.

Intellisense (so that's what you call it) does indeed auto-complete "Me.tr"
to "Me.TransType"
 
S

Steve Schapel

Rick,

Hmmm... clutching at straws here...

Can you replace the TransType in the line of code you quoted with
Me.TransType

Also, what is the Row Source of the TransType combobox?

Maybe you can copy/paste the entire Current event procedure into your
reply here.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Allen Browne's "Filtering on a field in the Subform" 3
Form's new SQL recordsource returns nothing 5
The expression you entered as a query parameter produced this erro 2
AfterUpdate event and Form Filtering 4
Double-click on a subform record to open that record in another fo 1
Filter Form by Subform 4
Prevent sequential number being used if record not saved 7
Combo box with a subform 1

Top