Subform won't load from combo box selection--need to load so can add record tho. (1 Viewer)

[Minddumps]

[Solved] Subform won't load from combo box selection--need to load so can add record.

[Solved: Thank you to CJ London for his help ]

Hello everyone and HAPPY FRIDAY!!! I haven't found any solutions that work for me so I'm seeking help from you. Anything you can offer me is much appreciated


There are 2 issues I’m trying to figure out for an Access2007 form:


1) I should first state that I am working off of 3 tables:
a) Marketers
b) Companies that belong to those marketers
c) Points of Contacts (POC) for each Company

2) In my frmMain, I have 2 combo boxes. The first loads the second and the second loads a CompanyID text field (IDc) that the subform bases for which record info to pull. How do I get the CompanyID (and subform) fields to load blank upon form open and until the 2nd combo box selection is made?

3) A company that doesn’t have any POCs entered yet won’t load its CompanyID even though it DOES have an ID number. Well the Id not being changed doesn’t change the POC info either… I’m guessing it’s because there is no info to fill in the subform yet. However, I need to be able to still pull up the blank fields so I can ADD a POC. Can someone please review my form and vba code to see why a company with no POCs yet won’t load?

 

[CJ_London]

How do I get the CompanyID (and subform) fields to load blank upon form open and until the 2nd combo box selection is made?

I would suggest the following:
1. remove the recordsource from the main form - it is not required
2. remove the allCompanies and iDc controls from the form - as far as I can see they are not required
3. on your subform control change the linkchildfields and linkmasterfields to IDc and cbo2ndCombo respectively

Can someone please review my form and vba code to see why a company with no POCs yet won’t load?

In your subform recordsource change the join to a left join (right click on the join line and select option 2) to include all companies and pocs where they exist

 

[Minddumps]

Thank you so much for your suggestions. I've tried following the first part, but I then received errors in the link with my subform as the comboboxes are unbound and the subform won't allow me to list master/child fields with unboung controls. Also, if I remove the record source from the top my VBA for setting off the subfrom recordset gives me an error... However, changing the left join to option 2 for the query info on my main form did solve the issue of being blank before making the selection. Thanks for that!


In reference to your second suggestion, I changed to the second option for the left join in my subform, but the form still doesn't pull up the required info (if there is no POC info entered already) in order to add a new POC...


Edit**I'm a dodo... when I changed the left join in the MAIN form, not just on the SUBFROM... the form opened with blank subform fields and the records pulled up even in no POCs listed. (had to leave the record source and IDc field though, I just made it not visible)

THANK YOU SO MUCH!! I appreciate the help!

 

[CJ_London]

the subform won't allow me to list master/child fields with unboung controls

It will - you just need to type them directly rather than select from a list