Issue with displaying all records in linked tables using combo box and subform

[400Lifer]

Hi!



I have a main database with a linked (one to many) table [ PROJECTS and Service_Issues ] respectively.

I have a combo box on my main form to select the record. The form populates (including pulling data from other tables.) I have a subform which displays current records (Service_Issues) connected to my main table (PROJECTS) with linked parent and child fields.

My issue comes up when there are no records in the associated Service_Issues table - the form will not display the selected PROJECTS record and reverts to the previous record data from both the PROJECTS and Service_Issues tables.

I'd like to be able to show a blank subform of the Service_Issues if there are no records, but also show the main PROJECTS record.

Can someone help me out on this - I'm stymied! I've attached a zipped sample of my database.

Thanks!

[winterh]

I have a table and form set up a bit like your, I used the instructions below the only thing I did dif to them was add within my child table a column call "clientID" and when I defind the field for the relationship I used the PK in my main table and link it to "clientID".

And it will show blank within the sub form within your main table and show data when in your sub form.

If you need any more help let me know


http://office.microsoft.com/en-us/ac...010098674.aspx

[400Lifer]

Winterh,

the information on your link is how I setup the subform originally - however, I think there is an issue with how I've set up the relationship between my two main tables. If I have records in my linked table (linked to the PK of Table 1) the subform operates as expected. If there are no records in that table, then the main form doesn't reflect the record change, and the subform stays the same.

Table 1 will always have records, and table 2 may or may not have records. Related as one-to-many.

What is causing my form to populate incorrectly? Perhaps I've changed a property inadvertently that is causing this???

[TG_W]

How are your Join Proprties set up? Try to join them where you include all records fom Table 1 and only those from Table 2 where the joined fields are equal.

Hope that helps.

[winterh]

Hum...I will have a look at the prob in my form as mine works like the way you want yours to.

Are both fields the same formate rg numbers?

Have you defined your own 121 or did access do it for you?

[400Lifer]

This is my SELECT statement for this form - It's a biggie, and I'm not sure of the format for including an IsNull Command, or should it be an iif() ???

SELECT Project.*, ProvState.ProvStateID, ProvState.ProvStateAbb, ProvState.Country, Customers.CustID AS CustID_Customers, Customers.Company, Customers.Contact, Customers.Address, Customers.City, Customers.Prov, Customers.PC, Customers.Country AS Country_Customers, Customers.Phone, Customers.Mobile, Customers.Fax, Customers.Email, Contractors.ContID AS ContID_Contractors, Contractors.Company AS Company_Contractors, Contractors.Contact AS Contact_Contractors, Contractors.Address AS Address_Contractors, Contractors.City AS City_Contractors, Contractors.Prov AS Prov_Contractors, Contractors.PC AS PC_Contractors, Contractors.Country AS Country_Contractors, Contractors.Phone AS Phone_Contractors, Contractors.Mobile AS Mobile_Contractors, Contractors.Fax AS Fax_Contractors, Contractors.Email AS Email_Contractors, Contractors.Notes AS Notes_Contractors, Architects.ArchID AS ArchID_Architects, Architects.Company AS Company_Architects, Architects.Contact AS Contact_Architects, Architects.Address AS Address_Architects, Architects.City AS City_Architects, Architects.Prov AS Prov_Architects, Architects.PC AS PC_Architects, Architects.Country AS Country_Architects, Architects.Phone AS Phone_Architects, Architects.Mobile AS Mobile_Architects, Architects.Fax AS Fax_Architects, Architects.Email AS Email_Architects, Installers.InstallerID, Installers.[Last Name], Installers.[First Name], ServiceIssues.ServiceID FROM (Installers INNER JOIN (Architects INNER JOIN (Contractors INNER JOIN (Customers INNER JOIN (ProvState INNER JOIN Project ON ProvState.ProvStateID = Project.JobProv) ON Customers.CustID = Project.CustID) ON Contractors.ContID = Project.ContID) ON Architects.ArchID = Project.ArchID) ON Installers.InstallerID = Project.InstallerAssigned) INNER JOIN ServiceIssues ON Project.ProjectID = ServiceIssues.ProjectID;


I've played with different syntax but I'm not getting anywhere...this is what happens when you know a little bit and jump in too deep...hahah

[400Lifer]

Are both fields the same formate rg numbers?

Have you defined your own 121 or did access do it for you?

rg numbers? 121? lost me.... LOL

[TG_W]

Is it possible for you to post the project for further analysis?

[400Lifer]

I've changed the last INNER JOIN to LEFT OUTER JOIN, and this gives me the correct result! (Yay) Thanks for the input !! What a relief!

Now where I still have an issue is on my selection of the combo on the main form - I cannot cycle past the first four records. I know it's something simple, but I just can't figure it out...looking too closely I think....

[400Lifer]

Sure! I posted it on my original thread...but here it is again....any help I can get would be wonderful!!!

[TG_W]

Thanks. I figured you might have made some changes since the OP.

[TG_W]

Dagnabbit -it won't open. Can you save it to .mdb and post that way? Stupid 2007 not being compatible with 2010

[400Lifer]

Access2010 won't let me save in an earlier format. And the .mdb is too large to upload here...