Synchronise Main/Subform

[jmentor]

Have searched everywhere. I need some help please
on how to synchronise a combo box criteria with a
datasheet subform.

The combo box on the mainform has this statement


[VBA]
SELECT ClientType, ClientTypeDescription FROM tblClientTypes
UNION SELECT "ALL" as ClientType, "All Types" as ClientTypeDescription
FROM tblClientTypes ORDER BY ClientTypeDescription;[/VBA]


The datasheet subform is based on tblClients.
The child and master links are ClientType/ClientType
When selecting any criteria in the combo box other than
"All Types" the selection works correctly. But not when you
click "All Types", the subform doesn't show any records

I attach the mdb so you can see exactly what I have so far
Any help appreciated

[jadedata]

I use the "OnCurrent" event of subforms to requery associated controls or other subforms when the simple Master/Child linkages won't cut it.

I build the function that does the "re-sync" separate from the event so that I can fire it whenever it is convenient,... but mostly when the current record in the subform changes.

This code could completely rewrite the sql for the combobox rowsource based on some very complex logic. The combobox could be made to change drastically as a result,....something not available with a simple Master/Child refresh...

[jmentor]

jadedata

Perhaps you could let me have your code for the
OnCurrent event so I can synchronise the datasheet
subform to correspond to (All) in the combo box.

Thank you

[jadedata]

By "All in the combobox" I presume you mean an Sql with a where statement something like "SELECT * FROM TableName WHERE ([FieldName] is not null or [Fieldname] is null)"

that is "all"

[jmentor]

hi jadedata

Everything I have is in the attached zip file.
Look at my initial post.

Thanks

[jadedata]

I don't need to see your project. You need to remove any filter or where statement that restrict records in the subform when the combobox is parked on the all row.

you can launch this also from the combobox_AfterUpdate.

Your code will include an if/else/endif that looks for the combobox condition and sends the key value of the combo to the subform recordsource when the cbo has a select "non-all" value,... and removes said filter when the cbo say "All"

You have a go at some code and we'll dial it in for your needs

[xCav8r]

jmentor,

WB!

Some friendly words of advice: you might save yourself some trouble in the future by not focusing so narrowly on a particular means to accomplish an end. I d/led and looked at your database. Ultimately, you want a report to display data based on a user-selected criterion or lackthereof. If you had framed your question with this in mind, you might have gotten different, if not better, advice.

Anyway, I?ll help you to fix what you?ve already done so that it will work the way that you want it. Then, I?ll give you some sample code and talk about Jadedata?s recommendation, which is the approach?I think?that more seasoned Access users would take, because it?s more versatile.

Okay, first, what you?ve made works fine except when a user selects All Types. The data in the bound column for All Types is ALL, but there are no records in tblClients with ALL for ClientType; therefore, the subform will never display data when a user chooses All Types. This is no good, obviously, since you want your users to see the type of data that will go into the report.

To fix it you need to do two things. First, you need to change the SQL statement of your combo box. Instead of ALL, which will return no records, you should use the wildcard character for Access, which is an asterisk. Below is what you want:

SELECT ClientType, ClientTypeDescription FROM tblClientTypes UNION SELECT '*' as ClientType, 'All Types' as ClientTypeDescription FROM tblClientTypes
ORDER BY ClientTypeDescription;

Second, you need to change the SQL of your query so that it?ll return all records whenever a user selects All Types. Below is what you want:

SELECT tblClients.ClientCode, tblClients.ClientName, tblClientTypes.ClientType, tblClientTypes.ClientTypeDescription
FROM tblClientTypes INNER JOIN tblClients ON tblClientTypes.ClientType = tblClients.ClientType
WHERE (((tblClientTypes.ClientType) Like [forms]![frmClientsByType]![ClientType]))
ORDER BY tblClients.ClientName;

If you want to filter out blanks, then you should use this instead...

SELECT tblClients.ClientCode, tblClients.ClientName, tblClientTypes.ClientType, tblClientTypes.ClientTypeDescription, tblClientTypes.ClientType
FROM tblClientTypes INNER JOIN tblClients ON tblClientTypes.ClientType = tblClients.ClientType
WHERE (((tblClientTypes.ClientType) Like [forms]![frmClientsByType]![ClientType]) AND ((tblClientTypes.ClientType) Is Not Null))
ORDER BY tblClients.ClientName;

These changes mean that when a user selects All Types, the criterion will be ClientType LIKE '*', which returns all records. If you execute the query or the report based on the query, you?ll see that both display all records. Unfortunately, this isn?t the case when it comes to the subform. It will not show any data. The reason for this is because when you use Master/Child fields, Access looks for a literal match. In other words, Access will only display records on the subform where ClientType = '*'. In this case, Access doesn?t treat the asterisk as a wildcard; instead, it literally looks for records with an asterisk in the ClientType field. Obviously, that?s not what you want.

[I?ll finish this post later.]