SQL issue

[grahamvb]

Hello Access-Programmers,

SQL statements were created using the built in Access SQL builder.

In the attached database I created a split form named frmContact that uses the first sql statement below, in its RecordSource property.
This causes the subform frmContacts_subform, imbedded in the form header, to go blank.

SELECT tblContact.ContactID, tblContact.CBusinessID, tblContact.CPosition, tblContact.CFirstName, tblContact.CLastName, tblContact.CCalcName, 
tblContact.CPhone, tblContact.CPhoneExt, tblContact.CFax, tblContact.CCell, tblContact.CEmail, tblContact.CImage, tblContact.CActive
FROM tblContact
[COLOR=red]WHERE (((tblContact.CBusinessID)=[Forms]![frmContact]![frmContact subform].[Form]![BusinessID]) AND ((tblContact.CActive)=True))[/COLOR]
ORDER BY tblContact.CCalcName;

If I remove part of the WHERE statement, that limits the split forms data sheet to those records where tblContact.CBusinessID matches the
subform's field BusinessID, the form displays all the records. (As it should without the WHERE limitation).

SELECT tblContact.ContactID, tblContact.CBusinessID, tblContact.CPosition, tblContact.CFirstName, tblContact.CLastName, tblContact.CCalcName, 
tblContact.CPhone, tblContact.CPhoneExt, tblContact.CFax, tblContact.CCell, tblContact.CEmail, tblContact.CImage, tblContact.CActive
FROM tblContact
[COLOR=red]WHERE (((tblContact.CActive)=True))[/COLOR]
ORDER BY tblContact.CCalcName;

I am at a loss as to why this is happening. It seems the sql limitation should work.
Any thoughts?

Thank you in advance for looking into this.

 

[JHB]

This causes the subform frmContacts_subform, imbedded in the form header, to go blank.

Yes it does, because the main form and the sub form are linked together with the "Link Master Fields" and "Link Child Fields". With there "Where" clause you send the forms in a "cycle", where the main form wait for a value from sub form and versa visa.
Linking the main form and sub form together with the "Link Master Fields" and "Link Child Fields", is the correct way to link 2 forms together!
I can't image what you are trying to do, can you explain it?
I the main form you've a list of contact persons/employers, and the sub form shows correct which company the person are linked to.
If you want the opposite, then turn it around, company on the main form and contact persons/employers in the sub form.

 

[grahamvb]

Thank you JHB. That was in fact the issue, the contact records can be manipulated by the parent/child fields or the sql statement in the RecordSource property, not both as I had it.

I set the subform parent/child properties to nothing and allowed the sql statement to do its thing. What I am doing is manipulating the contact records displayed on the datasheet with sql statements. The sql statements allow for multiple WHERE criteria. I am making a form that appears to a programmer as backwards but, when it works, allows Access to do most of the work. Notice how little vba is involved, and I think some of that was added in an attempt to correct the issue you identified. I believe this may end up being a lightweight way to handle a common company/contact part of typical databases.