Struggling with Update Query

[MadAtMicrosoft]

As with many others that posted similar threads on this topic, I am new to Access AND to VBA coding so what seem as simple answers or instructions by experienced users zoom right over my head.

That said I am creating a db for my Company. I've viewed training videos and purchased a more in depth training video. What seems so simple on the video isn't working for me. I keep getting the blasted "Operation must use an updateable query" error......

******************************
Here is the background

This is for an operation that has only ONE employee on duty at a time. There is a "Crossover" when the next Employee comes in and all money is accounted for to go to next Employee.

At the beginning of a "Shift" an employee will login, start a time card which will behind the scenes initiate the tracking of a "Shift"

The unique part (at least to me) to this is that the business day does not match a typical clock. The reason is that the "Business Day" starts at say 6 AM of one calendar day and ends at 4 AM of the next day. My report totals need to tie to totals provided by a govt reporting system which Business Day ends at that 4 AM time.

Thus, I have created the following tables with the following fields:

A) tblEmployees

1)EmployeeID (PK) (Auto#)
2)EmpFirstName
3)EmpLastName
​

B) tblShifts

1)ShiftID (PK) (Auto#)
2)EmployeeID (Lookup to tblEmployees)
3)DateTimeCreatedStamp (General Date) (Dflt Value = Now())
4)ShiftStartDate (ShortDate) (Dflt Value = Now())
5)ShiftStartTime (MediumTime) (Dflt Value = Now())
6)BusinessDateofShift (ShortDate) (Blank until "Update Query Can complete)
​

****************************
Queries created:
A) Select Query wherein I test the ShiftStartTime Vs. a table where I define the Default "Start Time" of a Location's Business Day. This then defines the Alias: ShiftBusinessDay

ShiftBusinessDay: IIf([tblShifts]![ShiftStartTime]>[tblBusinessDay]![StartTimeOfBusinessDay],CVDate(Format([tblShifts]![ShiftStartDate],"Short Date")),CVDate(Format(DateAdd("d",-1,[tblShifts]![ShiftStartDate]),"Short Date")))


B) THEN I created a temporary table by using "Make Table" query whose SQL stmt that got created was:

SELECT tblShifts.ShiftID, tblShifts.EmployeeID, tblShifts.DateTimeCreatedStamp, tblShifts.ShiftStartDate, tblShifts.ShiftStartTime, tblShifts.CurrentDBALocationID, qryShiftsDetails.ShiftBusinessDay INTO tblTempShiftUpdateBusinessDate
FROM (tblBusinessDay INNER JOIN tblDBALocation ON tblBusinessDay.BusinessDayID=tblDBALocation.BusinessDayStartTime) INNER JOIN (tblCurrentDBALocation INNER JOIN (tblShifts INNER JOIN qryShiftsDetails ON tblShifts.ShiftID=qryShiftsDetails.ShiftID) ON tblCurrentDBALocation.CurrentDBAID=tblShifts.CurrentDBALocationID) ON tblDBALocation.DBALocationID=tblCurrentDBALocation.CurrentDBAID;

**************************************************

After that temp table I then tried an "Update Query" to update the tblShifts.BusinessDateofShift but received the dreaded error.

AFTER reading some posts here, I gave a whirl at creating a "Temp Table" using the "Make Table Query". However, that works ONCE and only if I link that table to the tblShifts. Once I do that I get a new error when running the "Make Table Query" a second or subsequent time that tells me it can't delete a table due to its relationship to another table.

SO NOW I'M STUCK.

Sorry, for the length of the explanation! Thank you in advance for you help.

 

[JANR]

You don't use make-table query but instead create the tmpTable and insert record into it.

You need to create 3 action queries.

First a DeleteQuery that empties your tmpTbl
Then an AppendQuery that inserts the records that you'll use in your UpdateQuery.
And finally your UpdateQuery.

To run the cycle you can either create a Macro that runs the sequence or to avoid access warnings create a function in a StandardModule that runs the sequence.

Function RunBatch()
 
CurrentDb.Execute("NameOfDeleteQuery"), dbFailOnError
CurrentDb.Execute("NameOfAppendQuery"), dbFailOnError
CurrentDb.Execute("NameOfUpdateQuery"), dbFailOnError
 
End Function

You can call this function from a button on a form, in it's click event put this:

=RunBatch()

Hope this helps

JR

 

[MadAtMicrosoft]

Thanks so much! I will give that a try. Embarrassing how one misread word (make a table vs make table query) instruction can change the meaning so much.

 

[MadAtMicrosoft]

JANR

Your solution worked perfectly!!!! Thank You again.

One observation. I had to add a Select Query so I could calc the "Business Day" based upon the transaction's time and date as it compared to the "default Business Start Time".

Thank you again

 

[JANR]

No worries, glad it helped

JR