Filter from either or two values

  • Thread starter Thread starter Nathan Guill
  • Start date Start date
N

Nathan Guill

I have a table that handles all my drawings. In this table, I list the part
number that this drawing pertains to as well as the reference part number
that is similar. Both of these part number values are stored as long
integers that correspond to the ID of a record in the Part Number table.

Now, I have a form that shows me one part number at a time. On this form is
a subform that I want to show me all drawings that have the part number
(pnID) or reference part number (REFpnID) IDs match the ID of the current
record. I can get this to work for either one field (pnID or REFpnID), but
not both. Does anyone know how to do this?
 
Nathan, could you use two subforms: one to show the related part numbers,
and the other that shows the related reference part numbers? On two pages of
a tab control, perhaps?

If that is not suitable, you could leave the subform contorl's
LinkMasterFields/LinkChildFields blank, and reassign its RecordSource in the
Current event of the main:

Private Sub Form_Current()
Dim strSQL As String
If Me.NewRecord Then
strSQL = "SELECT * FROM MyTable WHERE (False);"
Else
strSQL = "SELECT * FROM MyTable WHERE (MyTable.pnID = " & Me.pnID &
") OR (MyTable.REFpnID = " & Me.pnID & ");"
End If
Me.[NameOfYourSubformControl].Form.RecordSource = strSQL
End Sub
 
-----Original Message-----
I have a table that handles all my drawings. In this table, I list the part
number that this drawing pertains to as well as the reference part number
that is similar. Both of these part number values are stored as long
integers that correspond to the ID of a record in the Part Number table.

Now, I have a form that shows me one part number at a time. On this form is
a subform that I want to show me all drawings that have the part number
(pnID) or reference part number (REFpnID) IDs match the ID of the current
record. I can get this to work for either one field (pnID or REFpnID), but
not both. Does anyone know how to do this?
Click to expand...
Hi Nathan,

you could use a union query as the subform record source
or
in the query design view add the drawing table twice. the
second table with an alias. Each drawing table can link
using a different field to the Part Number table.

Luck
Jonathan
 
Back
Top