Connect a form & subform with aLIKE statement? (1 Viewer)

[Wayne Beck]

Can somebody please help, I've created a form which shows a subform that is linked by buyers names. The problem i have is that in one table the buyers record could contain two names ie "Joe Bloggs & A Friend". This is causing me some problems with conecting to the subform. Is there any way that i can link the main form to the subreport using the LIKE statement ie '[subformBuyer] LIKE "*Joe*"'

Sorry if im confusing people but i cant seem to explain it any better.

Thanks for any help in advance

Wayne

 

[KevinM]

Why don't you link the two tables the proper way with Primary ID and Foreign key fields?

 

[Pat Hartman]

No way to use like - this is an exact key match.

You could change the subform so that it is a stand alone form that you open by some action from the main form. That way you could use a filter to sync the forms. This also requires adding some code to the "new" form's BeforeInsert event so that key fields are properly filled in when rows are added.

However, the real solution is to fix the data. That will take quite a bit of work at this point since you would also have to correct existing data. But, it will take even more work tomorrow. The problem is twofold. First, you have not defined referential integrity between the two related tables. This allowed you to enter child records that had no parents (matching buyer values). You should have linked the tables on the buyerID rather than the name field. Second, you seem to have a one-to-many relationship that you have not defined properly. The buyerID belongs in a separate table if multiple buyers can be associated with a single subform row.

 

[Wayne Beck]

Thanks for your help both. I've decided to fix the data and change the way that my tables link.

Thanks

Wayne