MS Access VBA Filter

Larry Brister
Larry Brister used Ask the Experts™
on
I have a subform which is linked on ClientID = CLientID parent to child

There is another field visitID in both parent and child (visitID)

I need a link on parent and subform where the parent visitID = child.visitID OR a NULL value.

Heres the reason...
The child table is a contacts history table and can have replicated contact's because they will on multiple visits over the years
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
You can't really link with a NULL value, at least not directly with the Master/Child linking mechanism. Note you CAN include more than one field in the Master/Child links ... to do that, click on the build button in the Master Linkfield or the Child linkfield in the subform object's Property view. You can then select multiple fields to link on.

If you mean that you need to link on both (ClientID=ClientID and VisitID=VisitID) as well as ONLY (ClientID=ClientID), then I think you'll probably have to build the recordset for that yourself and set your subform's Recordsource directly, instead of relying on the builtin link mechanism. To do that, just use the Master form's Current event  to set the subform's Recordsource, somethnig like:

Me.YourSubformOBJECT.Form.Recordsource = "SELECT * FROM SomeTable WHERE (ClientID=" & Me.ClientID & " AND VisitID=VisitID) OR (ClientID=" & Me.ClientID & ")"
Larry Bristersr. Developer

Author

Commented:
Thanks
Watch for another question

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial