Form doesn't input to table

[Thompyt]

I cannot seem to get the form to input data. I can change current records data. What am I missing?

Thanks
Wayne

[ranman256]

is the form connected to a single table? (some joined queries cannot add recs)
is the form set to dynaset?
is the form set to allow additions?

[June7]

Normally, a form enters/edits data for only one table. Which table do you want to edit with this form?

INNER JOIN requires associated records in each table for any data to show. This may be why cannot enter new records.

Relationships are set as 1-to-1 - is this what you want?

tblRental and tblRcost are linking on autonumber fields in Relationships builder. This will not work.

Appears to be circular referencing with tblMain, tblCosts, tblRental, tblRCost. https://www.codeproject.com/articles...atabase-design

[orange]

Wayne,

In simple terms, what are you trying/wanting to do?

[mike60smart]

Hi Wayne

You need to read up on tables and Relationships.

You should Enforce Referential Integrity between related Tables.

You should NOT use Lookup Fields in tables.

The screenshot attached shows your Data Input Form based solely on tblMain.

Some of the Calculations will not work because I am unable to relate your "tblRCost" with a related Table?

Please explain the purpose of this table and how it relates to other tables?

[Thompyt]

All, Thanks for the replies.

The lookups in tblMain will disappear once I get the data to work as I want. Its easier to read then a number reference to another table. I have moved the two date fields from tblDates to tblMain. The issue I am having is the circular reference tblMain.location to tblcosts.costID to tblRcost.locID/tblRcostRTID to tblRental.RentID to tblmain.Rental. If I break the loop I end up with 60 records, vice with the loop I get the intended 12 records.

My intent is to have the choice of a rental vehicle for the traveler: Compact, Standard, Full, SUV or None. If two travelers are going to the destination, then there is only 1 rental, thus one will be None. Since there are multiple destinations, there will be a different rental cost for each rental platform. Fuel costs remain the same for each rental no matter the destination.

tblRental has the rental type and gas. these will not change. The rental cost is linked to tblCost for location. rental Cost s separate from from table cost as there are 4 records to each line in tblCost.

[June7]

Change qryMain to:

SELECT TblMain.MainID, [TDYend]-[TDYStart] AS Days, [First] & " " & [Last] AS Name, ([RCost]+[Gas])*[Days] AS Rentals, ([Lodge]+[LodgeTax])*[Days] AS Lodging, (([MIE]*2)*0.75)+([MIE]*([Days]-2)) AS Meals, [Flight]+[Bag]+((2*[H2AirMiles])*0.52) AS Air, ([Lodging]+[Meals]+[Air]+[Rentals])*0.09 AS 9Perc, [Rentals]+[Lodging]+[Meals]+[Air]+[9Perc] AS GTotal, [Rentals]+[Lodging]+[Meals]+[Air]+[9Perc] AS GP, tbleExercise.ExerciseName, tblEvent.Event, tblDates.TDYStart, tblDates.TDYEnd, tblCosts.TDYLocation, tblCosts.TDYAirport, tblCosts.Lodge, tblCosts.LodgeTax, tblCosts.MIE, tblCosts.Flight, tblCosts.Bag, tbleNames.Last, tbleNames.First, tbleNames.H2AirMiles, IIf([RType]="None","",[RType]) AS Rent, IIf([RType]="None","",[Gas]) AS Gass, IIf([RType]="None","",[RCost]) AS RRCost, tblDates.FiscalYear, tblCosts.LocationLong, tbleExercise.ExLong, tbleExercise.EID
FROM (tblCosts INNER JOIN (tblRcost INNER JOIN tblRental ON tblRcost.RTID = tblRental.RentID) ON tblCosts.CostID = tblRcost.LOCID) INNER JOIN (tbleExercise INNER JOIN (tblEvent INNER JOIN (tbleNames INNER JOIN (tblDates INNER JOIN TblMain ON tblDates.DatesID = TblMain.[StartDay]) ON tbleNames.NameID = TblMain.Names) ON tblEvent.EVID = TblMain.EV) ON tbleExercise.EID = TblMain.Ex) ON (tblRcost.LOCID = TblMain.Location) AND (tblRcost.RTID = TblMain.Rental);


This removes links from TblMain to tblRental and tblCosts and adds a compound link between TblMain and tblRcost. Save RCostID into TblMain and compound link would not be needed.

Fix the Relationships builder links accordingly.

[Thompyt]

All,
Thanks for the pointers. Got rid of the circular relationship. I have been able to update the form and add records with all but the tblRCost.RCost field. Whenever I add RCost into the forms query and set it up so that the correct records are shown, I can see the records, but am unable to edit.

Mike, I think we are looking at the same issue in relating the Rcost to the main table. Rcost requires two relationships to get to the right data. Location and Rental Type. I am thinking of a Dlookup, but that slows the db considerably if it got too big.

Thanks
Wayne

[June7]

The form RecordSource uses the original table links I advised not to use.

tblRCost is essentially built from tblCost and tblRental data. What is purpose of tblRCost? What is its relationship to TblMain? Can each tblRCost record associate with multiple TblMain records? If yes, then tblRCost is a lookup table and should not edit lookup tables in same form as table they feed data into.

All of these tables related to TblMain are set up as lookup tables. Again, lookup tables should not be edited in same form as table they feed data into. Only reason to include lookup tables in form RecordSource is to display related data (don't use INNER JOIN). Do not allow edits to lookup table fields. Better way to display related data is for textboxes to reference columns of comboboxes.

Bind form to TblMain only.

[Thompyt]

June7,
I'll use a sub form for all but the tblMain data. tblCost is used to pull up a rental vehicle for the cost by Rental Car Size by Location of TDY Airport. It was my intent to be able to edit the non tblMain fields on the fly. Thinking on it, this would cause historic data to be changed and skew the total.

I did try to add a RentalCost (Number) field to tblMain.

[June7]

Using subforms for lookup tables does not really make sense. If you want to add records to lookup tables 'on-the-fly' during data entry, use combobox and listbox NotInList events.

https://blueclawdatabase.com/notinlist-event-code/