It appears that your BE is SQL Server (or equivalent)?
So I cannot test any of this.....
how do i write the where clause of the update to be circularid = current recordset circularid?
You need to concatenate "!circularid". You have
Code:
CurrentDb.Execute ("UPDATE circularT SET duedate=#" & Format(n, "mm/dd/yyyy") & "# WHERE circularid=!circularid ")
Should be
Code:
CurrentDb.Execute ("UPDATE circularT SET duedate=#" & Format(n, "mm/dd/yyyy") & "# WHERE circularid= " & !circularid )
Why are you using "Format(n, "mm/dd/yyyy")"? This converts the date to a string. Don't you want a Date??
second problem so far is that my variable "x" that is picking up the new PK to insert as a FK in the workquecraft table is not resetting. all my records going into workquecraft are getting the same FK even though the cursor has moved onto a new record. can it be reset at each loop?
I think there are two problems here.
1) You have declared "x" to be an Integer. An ACCESS integer. Not the same and an SQL INT.
I found this:
SQL
Int -2,147,483,648 to 2,147,483,647
Access
Integer -32,768 and 32,767 (Allows whole numbers between )
Long -2,147,483,648 and 2,147,483,647 (Allows whole numbers between)
So I changed the code to
And I found this
Code:
Dim query As String
Dim newRow As Long ' note change of data type
Dim db As DAO.Database
query = "INSERT INTO InvoiceNumbers (date) VALUES (" & NOW() & ");"
Set db = CurrentDB
db.Execute(query)
newRow = db.OpenRecordset("SELECT @@IDENTITY")(0)
Set db = Nothing
Then I think the line to get the new PK should be moved up to the first INSERT query. You have it after the UPDATE query.
Now I have a question.
In both Insert queries, why is every value delimited with quotes??? I would have thought that at least the ID fields would be numeric.
Here is the code as I have modified it. Remember, I have not tested these changes
Code:
Private Sub circular()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset '''circular table'''
Dim rs2 As DAO.Recordset '''circular craft table'''
Dim sqlrs1 As String '''circular sql'''
Dim sqlrs2 As String '''circular craft sql'''
Dim sSQL As String
Dim NewIdent As Long ' < was - Dim x As Integer
Dim dNewDueDt As Date
sqlrs1 = "SELECT * FROM circularT" '''get circular table'''
sqlrs2 = "SELECT * FROM circularcraftT" '''get circular craft table'''
Set db = CurrentDb
Set rs1 = db.OpenRecordset(sqlrs1)
Set rs2 = db.OpenRecordset(sqlrs2)
With rs1 '''loop thru circulart'''
If Not .BOF And Not .EOF Then '''check to see if on a record'''
.MoveFirst
Do Until .EOF
dNewDueDt = Empty 'clear new due date
NewIdent = 0 'reset new identity
If (!DueDate - Date) <= 0 Then '''check due dates that are due today or past'''
'''insert main job into record'''
sSQL = "INSERT INTO workqueT(circularid, locid, deptid, systemid, assetid, componentid,workid,summary,detail,statusid)"
sSQL = sSQL & " VALUES(""" & !circularid & """,""" & !LocID & """,""" & !DeptID & """,""" & !SystemID & """,""" & !AssetID & ""","""
sSQL = sSQL & !ComponentID & """,""" & !WorkID & """,""" & !Summary & """,""" & !detail & """,'" & 1 & "')"
' Debug.Print sSQL 'for debugging
db.Execute sSQL, dbFailOnError
NewIdent = CurrentDb.OpenRecordset("select @@identity")(0) '''get unique ID'''
dNewDueDt = !DueDate + 5 '''set new due date'''
sSQL = "UPDATE circularT SET duedate=#" & Format(dNewDueDt, "mm/dd/yyyy") & "# WHERE circularid = " & !circularid
' Debug.Print sSQL 'for debugging
db.Execute sSQL, dbFailOnError
With rs2 '''loop thru circularcraftt'''
If Not .BOF And Not .EOF Then
.MoveFirst
Do Until .EOF
'''insert the crafts for the main jobs'''
sSQL = "INSERT INTO WorkquecraftT(workqueID,craftid,hours,craftnotes,statusid)"
sSQL = sSQL & " VALUES (""" & NewIdent & """,""" & !CraftID & """,""" & !Hours & """,""" & !CraftNotes & """,'" & 5 & "')"
' Debug.Print sSQL 'for debugging
db.Execute sSQL, dbFailOnError
.MoveNext
Loop
.Close
End If
End With
End If
.MoveNext
Loop
.Close
End If
End With
Set rs2 = Nothing
Set rs1 = Nothing
Set db = Nothing
End Sub
I added the Debug statements because I like to look at the SQL to see if it is properly formed. (spaces, delimiters)
I changed "x" and "n" because.... well , they are poor names/not descriptive.