VBA Syntax for an IF statement using a subform

[kgudgel]

I am not very familar with VBA syntax, so any help will be much appreciated. I am using Access 2003.

I have a subform that I would like to filter with an if statement.

I have a main form with a customerID. On the subform I have a list of the productID available. I would like to write an if statement that would exclude showing any productID that have already been purchased by the customerID.

The main form includes customerID.

The subform includes customerID and productID

[patjones]

Without knowing more about your situation (for instance, table and column names), it sounds like you're looking for a SQL query that performs the desired operation, instead of an If statement.

Pat

[kgudgel]

Okay..I think I figured out what I need to do, I just do not know how I get there.

I would like to Apply a Filter to a subform.

Main form is frmDirectoryListingsAndZips_DataEntry
SubForm is frmZip_info

The subform and the main form both contain the field OwnerID.

I would like the subform to filter out any ownerID records that match the ownerID on the main form.

[patjones]

You want only records that have the same ownerID as the main form, or records that do not have the same ownerID?

In order to proceed I would need to know what your table names are, what the pertinent fields are, what information you want in the sub-form, etc.

Pat

[NeoPa]

Ah. I think I see now. It is only those products that are not already linked (purchased) that you're interested in yes?

[kgudgel]

I want records to appear on the subform that do not have the same ownerID as the main form.

The main form: frmDirectoryListingsAndZips_DataEntry populated from table: tblDirectoryListingsAndZips

The subform: frmZip_info populated from table: tblZip_info_wActiveFloristZips

Both tables have the field OWNER_ID

These are the fields that will be visable on the subform:
ZipCode, CityName, StateAbbreviation, Select, and OWNER_ID

[nico5038]

You don't need VBA to achieve this. Just the right query will do the job.

Just use a query like:

SELECT * FROM tblProducts WHERE NOT ProductID IN (SELECT ProductID FROM tblInvoices WHERE OWNER_ID =forms!frmDirectoryListingsAndZips_DataEntry!OWNER_ID )
 

for your subform.

Getting the idea ?

Nic;o)

[NeoPa]

I would agree there is no real need for VBA as the filtering is constant.

Some alternative SQL might be :

SELECT *
FROM   [tblZip_info_wActiveFloristZips]
WHERE  [Owner_ID]<>Forms!frmDirectoryListingsAndZips_DataEntry.Owner_ID

PS. I'll take your post #6 to mean yes :) The extra info was definitely helpful mind.