New query record disappears after saving

[JL2309]

When I add a new record to an existing query and save, the underlying tables populate - but when I close and re-open the query the record is gone. So it is now showing up across the (8) connected tables but not in the query results or the connected report. Any help much appreciated!

[orange]

How exactly do you add a new record to a query? Can you show us?

[June7]

How do you use one query to input new records into 8 tables?

If you want to provide db for analysis, follow instructions at bottom of my post.

[JL2309]

Screenshot attached...I added the new record on the bottom line and filled in the relevant fields going across. The tables populated with the correct information but upon re-opening the query the new record was gone. I also tried populating the individual tables in the database on an earlier try, which then do not show up on the query.

[JL2309]

I am not trying to populate 8 tables...I am saying that 8 tables are included in the query, which seem to populate with the information just fine, but I am trying to add a new record to my report. This query is the report record source.

[June7]

Data entry/edit should be done through forms. One form for one table.

Post the query SQL statement. If it uses INNER JOIN that might explain why your new record does not show. INNER JOIN requires related records in both tables.

[JL2309]

Query SQL statement:

SELECT tbl_ID_Names.FEMA_NASNo, tbl_ID_Names.FEMAName, tbl_ID_Nos.CASNo, tbl_Synonyms.LatinName, tbl_Synonyms.BotanicalName, tbl_Synonyms.Synonyms, tbl_ID_Nos.FDANo, tbl_ID_Nos.COE, tbl_Isolation_Purification.GeographicalSource, tbl_Isolation_Purification.SourceMaterial, tbl_Isolation_Purification.Isolation_Manufacture, tbl_Isolation_Purification.PercentYield, tbl_Analysis_Exposure.Appearance, tbl_Analysis_Exposure.AlcoholSolubility, tbl_Analysis_Exposure.WaterSolubility, tbl_Analysis_Exposure.Solubility_OtherSolvents, tbl_Analysis_Exposure.MajorMarkerConstituents, tbl_Analysis_Exposure.[PerCapitaIntake_μg/person/day], tbl_HistoryOfUse.HistoryofUse, tbl_HistoryOfUse.FirstKnownDate, tbl_HistoryOfUse.IndustryGeneralUse, tbl_PhysicalData.RIFM_OpticalRot20degC, tbl_PhysicalData.RIFM_SpGravity20degC, tbl_PhysicalData.RIFM_RefIndex20degC, tbl_PhysicalData.FCC_OpticalRot20degC, tbl_PhysicalData.FCC_SpGravity20degC, tbl_PhysicalData.FCC_RefIndex20degC, tbl_PhysicalData.IOFIMIS_OpticalRot20degC, tbl_PhysicalData.IOFIMIS_SpGravity20degC, tbl_PhysicalData.IOFIMIS_RefIndex20degC, tbl_PhysicalData.OTHER_OpticalRot20degC, tbl_PhysicalData.OTHER_SpGravity20degC, tbl_PhysicalData.OTHER_RefIndex20degC, tbl_PhysicalData.SourceNotes, tbl_ID_Nos.ISO, tbl_PhysicalData.ISO_OpticalRot20degC, tbl_PhysicalData.ISO_SpGravity20degC, tbl_PhysicalData.ISO_RefIndex20degC, tbl_Poundage.[1995Poundage_lbs], tbl_Poundage.[2005Poundage_lbs], tbl_Poundage.[2010Poundage_lbs], tbl_Analysis_Exposure.SPET_mg_person_day, tbl_PhysicalData.Industry_OpticalRot20degC, tbl_PhysicalData.Industry_SpGravity20degC, tbl_PhysicalData.Industry_RefIndex20degC, tbl_Analysis_Exposure.FEMAmarkercompounds
FROM ((((((tbl_ID_Names INNER JOIN tbl_ID_Nos ON tbl_ID_Names.ID = tbl_ID_Nos.ID) INNER JOIN tbl_Synonyms ON tbl_ID_Names.ID = tbl_Synonyms.ID) INNER JOIN tbl_Isolation_Purification ON tbl_ID_Names.ID = tbl_Isolation_Purification.ID) INNER JOIN tbl_Analysis_Exposure ON tbl_ID_Names.ID = tbl_Analysis_Exposure.ID) INNER JOIN tbl_HistoryOfUse ON tbl_ID_Names.ID = tbl_HistoryOfUse.ID) INNER JOIN tbl_PhysicalData ON tbl_ID_Names.ID = tbl_PhysicalData.ID) INNER JOIN tbl_Poundage ON tbl_ID_Names.ID = tbl_Poundage.ID;



I'm the only one who uses this database and so would prefer to enter data on my tables directly rather than building entire forms just for me. However, when I enter the data in the tables it is not showing up in the query.

Thanks again, this is for work and greatly appreciated.

[June7]

Yep, I see INNER JOIN used for all links. Change to an outer (LEFT or RIGHT) JOIN: "Include all records from tbl_ID_Names and only those from {other table} that match"

[JL2309]

Sorry, I have never used VBA...I am vaguely familiar with joins but don't understand where to make changes. Where does this text go? What should the full SQL statement look like to bring in the new record?

[June7]

This is not VBA, it is query design.

Open query in design view, click on the line that links the tables, choose appropriate join type.

[JL2309]

Thank you so much!! Sorry for misunderstanding, I see what you mean now for next time.