How to use VBA to Update a Query with many one-to-many relations?

[calmypal]

ERROR MESSAGE
The field cannot be updated because another user or process has locked the corresponding record or table. (Error 3164)


Public Sub CopyTasks()
On Error GoTo Err_CopyTasks

Set TaskDB = CurrentDb
Set TaskDB2 = CurrentDb
strSQL = "SELECT * FROM qryTasks WHERE [ActYear] = " & PYear & " and [ActWeek] = " & PWeek & ""
strSQL2 = "SELECT * FROM qryTasks2"
Set TaskRec = TaskDB.OpenRecordset(strSQL, dbOpenDynaset)
Set TaskRec2 = TaskDB2.OpenRecordset(strSQL2, dbOpenDynaset)
'DoCmd.Hourglass True
'Copy Tasks
Do While Not (TaskRec.EOF)
With TaskRec
TaskRec2.AddNew
TaskRec2![ActYear] = PYear
TaskRec2![ActWeek] = ![ActWeek] + 1
'TaskRec2![Foreman] = ![Foreman]
'TaskRec2![DayofWeek] = ![DayofWeek]
'TaskRec2![JobPriority] = ![JobPriority]
'TaskRec2![ClientName] = ![ClientName]
'TaskRec2![Address] = ![Address]
'TaskRec2![City] = ![City]
'TaskRec2![State] = ![State]
'TaskRec2![PostalCode] = ![PostalCode]
'TaskRec2![TelNo] = ![TelNo]
'TaskRec2![EQ] = ![EQ]
'TaskRec2![PPS] = ![PPS]
'TaskRec2![Comm_Res] = ![Comm_Res]
'TaskRec2![InitialCleanup] = ![InitialCleanup]
'TaskRec2![Mowing] = ![Mowing]
'TaskRec2![Pre-] = ![Pre-]
'TaskRec2![Fertilzer 1Q] = ![Fertilzer 1Q]
'TaskRec2![Fertilzer 2Q] = ![Fertilzer 2Q]
'TaskRec2![Fertilzer 3Q] = ![Fertilzer 3Q]
'TaskRec2![Fertilzer 4Q] = ![Fertilzer 4Q]
'TaskRec2![SprinklerOn] = ![SprinklerOn]
'TaskRec2![SprinklerOff] = ![SprinklerOff]
'TaskRec2![SprinklerPurge] = ![SprinklerPurge]
'TaskRec2![Aeration] = ![Aeration]
'TaskRec2![Weeding] = ![Weeding]
'TaskRec2![SpringManicure] = ![SpringManicure]
'TaskRec2![Inter Prune] = ![Inter Prune]
'TaskRec2![Tree & Shrub Fert] = ![Tree & Shrub Fert]
'TaskRec2![Fall Cleanup] = ![Fall Cleanup]
'TaskRec2![Fall Prune] = ![Fall Prune]
'TaskRec2![Start] = ![Start]
'TaskRec2![Finish] = ![Finish]
'TaskRec2![Areas To Maintain] = ![Areas To Maintain]
'TaskRec2![Comments] = ![Comments]
'TaskRec2![Service Time] = ![Service Time]
'TaskRec2![Pond Start] = ![Pond Start]
'TaskRec2![Pond Stop] = ![Pond Stop]
'TaskRec2![Pond Mtce] = ![Pond Mtce]
'TaskRec2![Pest Spray] = ![Pest Spray]
TaskRec2.Update

TaskRec.MoveNext
End With
Loop
TaskRec.Close
TaskRec2.Close
Exit_CopyTasks:
DoCmd.Hourglass False
Exit Sub
Err_CopyTasks:
MsgBox "Problem Copying Tasks!!! " & Err.Description
Resume Exit_CopyTasks
End Sub

 

[boblarson]

Your problem is that you are opening the same table(s) twice (even though they are different query names I am assuming that they both have the same table in them). So, what is the SQL for qryTasks and qryTasks2?

 

[boblarson]

Oh, and this is unrelated but you don't need the double quotes at the end:
"SELECT * FROM qryTasks WHERE [ActYear] = " & PYear & " and [ActWeek] = " & PWeek & ""

you can leave off like:

"SELECT * FROM qryTasks WHERE [ActYear] = " & PYear & " and [ActWeek] = " & PWeek

there is no need for them.

 

[calmypal]

I'm trying to copy specific records selected by Year and Week from the original qryTASKS.

Year 2011 Week 1 all records copied to week 2

The double quotes are added by access not me. ??

 

[boblarson]

I'm trying to copy specific records selected by Year and Week from the original qryTASKS.

Year 2011 Week 1 all records copied to week 2

You need to post the SQL from qryTasks and qryTasks2.

The double quotes are added by access not me. ??

Umm, it didn't add the ampersand (&) so if that wasn't there then it wouldn't need to add the quotes. Delete the last ampersand and two sets of double quotes.

 

[calmypal]

strSQL = "SELECT * FROM qryTasks WHERE [ActYear] = " & PYear & " and [ActWeek] = " & PWeek & ""
strSQL2 = "SELECT * FROM qryTasks2"

What do I use instead of currentDb for the other database?

 

[calmypal]

FORM frmCopyTasks

Option Compare Database
Option Explicit

Private Sub cboYear_AfterUpdate()
PYear = Me.cboYear
End Sub

Private Sub cboWeek_AfterUpdate()
PWeek = Me.cboWeek
On Error GoTo Err_cboFRMN_Click
Dim FoundIt As Boolean

FoundIt = False
'DoCmd.Hourglass True

strSQL = "SELECT * FROM qryTasks WHERE [ActYear] = " & PYear & " and [ActWeek] = " & PWeek & ""
Set TaskDB = CurrentDb
Set TaskRec = TaskDB.OpenRecordset(strSQL)
If TaskRec.RecordCount <> 0 Then 'Check if There is a least one task
FoundIt = True
TaskRec.Close
>> CopyTasks
Else
MsgBox "SELECTED TASK NOT ASSIGNED!!! "
Me.cboYear.SetFocus
TaskRec.Close
End If


'Close this form
DoCmd.Close acForm, "frmCopyTask"


Exit_cboFRMN_Click:
DoCmd.Hourglass False
Exit Sub
Err_cboFRMN_Click:
MsgBox "Problem Copying Tasks!!! " & Err.Description
Resume Exit_cboFRMN_Click



End Sub
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
DoCmd.Close
Exit_cmdClose_Click:
Exit Sub
Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click

End Sub

 

[boblarson]

strSQL = "SELECT * FROM qryTasks WHERE [ActYear] = " & PYear & " and [ActWeek] = " & PWeek & ""
strSQL2 = "SELECT * FROM qryTasks2"

What do I use instead of currentDb for the other database?

That isn't the SQL for qryTasks and qryTasks 2. You need to open qryTasks (and qryTasks2) in Design view and then View > SQL View and copy and paste that SQL here.

 

[calmypal]

Can I just send you the application zipped?

 

[boblarson]

Can I just send you the application zipped?

What version is it? I only have 2003 here at work.

 

[calmypal]

2003 Access

 

[calmypal]

ASSL1 Application attached

 

[boblarson]

Thanks for posting that. It does answer a few questions. But it also generates a bunch more. The first being - is this in production right now or are you just creating it? The reason I ask is that I think we need to work through the structure of it first. The current structure is, shall we say, in need of adjustment. The structure is key to having a very good database which you can pull information from as well as putting it in.

So, are you willing to work through the structure issues first and then, by the time we get that taken care of, the current problem will likely not exist.

 

[calmypal]

I'm building it as we communicate.
I don't use queries a lot so I'm kind of in the dark.
Thanks Cal

 

[calmypal]

I'm building it right now.
I;m knida in the dark with queries.
Thanks Cal

 

[boblarson]

So let's start out with a general explanation of your business and what it is you are trying to accomplish. My initial understanding, from looking at what you currently have, (and correct me if I am wrong) is you are wanting to be able to assign and track groups of landscapers, their foreman, on tasks for different clients. These tasks must be able be be recurring but also could include tasks which are just single tasks that won't be done again for that client.

Is that correct? Please add any additional information that I might not have included.

 

[boblarson]

I apologize for not being able to get back with you. I just have not even been able to get on the forum since before New Years. I've been swamped at work and other issues at home to deal with.

This is where I left off. This is the model I currently have:

 

[jdraw]

cal,

Here is the best advice I can offer - And it ties in what Bob has been doing (creating a picture of tables and relationships)...

Underlying a database are a series of concepts and steps for getting the proper tables identified and related. Here is a list of free video tutorials that go through those steps and processes with an example. Understanding these concepts will help you with Access.

If you google for videos "normalization langer" you will find a series of free videos by Dr.Art Langer. These are quite good for learning by Watching/Listening
rather than reading.

I have listed the videos in sequence

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

You can go back and watch these as often as you want.

Good Luck