The Recordset is not updateable (1 Viewer)

[NSAMSA]

All:

I've been trying to figure out what the issue is with a query of mine that is not updateable.

I have a training database and my query is pulling from 2 tables. One table is the records table with employee number, name, course, department, date completed, etc. The parameters of Name, Department and Course are linked to combo boxes in my opening form. The second table is the schedule. This has the month, year and courses assigned. The courses on this table are relational to the courses in the records table. The month parameter is from a combo box in the opening form.

The goal is to be able to put the month in along with the other parameters and update the records based on things like department and month of assignment. If I take out the schedule parameters, it works, but when I include the schedule table it becomes non-updateable.

Below is the code for the query

SELECT [04-RecordEntryMaster].[EE Number], [04-RecordEntryMaster].[EE Name], [04-RecordEntryMaster].[Course Name], [04-RecordEntryMaster].[Department Cost Center], [04-RecordEntryMaster].Frequency, [04-RecordEntryMaster].[Last Date], [04-RecordEntryMaster].[Next Date], [04-RecordEntryMaster].Pass, [04-RecordEntryMaster].Status, [07-ScheduleMaster].ScheduleMonth, [07-ScheduleMaster].Type
FROM [07-ScheduleMaster] INNER JOIN [04-RecordEntryMaster] ON [07-ScheduleMaster].ScheduleCourse.Value = [04-RecordEntryMaster].[Course Name]
WHERE ((([04-RecordEntryMaster].[EE Name]) Like "*" & [Forms]![frmRecordEntry]![cboName] & "*") AND (([04-RecordEntryMaster].[Course Name]) Like "*" & [Forms]![frmRecordEntry]![cboCourse] & "*") AND (([04-RecordEntryMaster].[Department Cost Center]) Like "*" & [Forms]![frmRecordEntry]![cboDepartment] & "*") AND (([07-ScheduleMaster].ScheduleMonth) Like "*" & [Forms]![frmRecordEntry]![cboMonth] & "*") AND (([07-ScheduleMaster].Type)="Training"));


Regards

 

[arnelgp]

Change the INNER JOIN to Left Join

 

[NSAMSA]

Change the INNER JOIN to Left Join

I did this and am still getting the same result. The suggestion does make sense, and I'm actually surprised its still not yielding an updateable form.

 

[arnelgp]

you make the field on one table as PK or unique (no duplicate).

 

[NSAMSA]

you make the field on one table as PK or unique (no duplicate).

I put the unique record in the query to "yes" if that is what you are suggesting. Otherwise I do have keys on both tables.

 

[arnelgp]

if it can't be done, then i think you can create a Master/Child form.
RecordMaster being on the master form.
while ScheduleMaster on a subform.
therefore you have two separate query/table that can be linked on Course Name.

 

[CJ_London]

sometimes you can make an non-updateable query updateable by changing the recordset type property from dynaset to dynaset - inconsistent updates. Open query in design view in the query grid, if you can't see properties click on the properties button on the ribbon. Now click on an empty area in the top half of the query window (where the tables and links are displayed)

 

[NSAMSA]

I've managed to get the dataset updateable in my query. However, it still will not update on my subform. Is there some setting that I'm missing? Before adding the schedule element, I had no issue with updates on the subform.

Regards

 

[bastanu]

Try to also change the subform's recordset type to Dynaset-Inconsistent Updates.

 

[Pat Hartman]

Please post your table relationships so we can see if you have correct joins.