need some ideals and input

[ssanfu]

Learned something new.



When using an append query in VBA, I've always written a function to retrieve the new PK number.
When I use the ".AddNew" syntax, I'll grab the PK number before the ".Update" command.
I'll have to try it. Maybe I can cut down on some code.

Thanks, Paul

[vicsaccess]

i believe it was this article from Allen Browne that i first found and started using @@identity. https://bytes.com/topic/access/answe...ng-insert-into
as for my where condition, any more thoughts? i did find something that works but i don't think i like it just because it looks messy. i moved my sqlrs2 SQL and set rs2 inside the first loop just under the execute command along with declaring a variable that pulls the circularid from rs1 and then use that variable inside a where clause for my sqlrs2 SQL. not sure if this is making sence so i'll add the code again.

Private Function circular1()
    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 x As Integer
    Dim n As Date
    Dim c As Integer
    
    sqlrs1 = "SELECT * FROM circularT" '''get circular table'''
    
    
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset(sqlrs1)
    
    
    With rs1 '''loop thru circulart'''
        If Not .BOF And Not .EOF Then '''check to see if on a record'''
            .MoveFirst
                Do Until .EOF
                If (!DueDate - Date) <= 0 Then '''check due dates that are due today or past'''
                    '''insert main job into record'''
                    CurrentDb.Execute ("INSERT INTO workqueT(circularid, locid, deptid, systemid, assetid, componentid,workid,summary,detail,statusid) " & _
                    "VALUES(" & !circularid & "," & !LocID & "," & !DeptID & "," & !SystemID & "," & !AssetID & "," & _
                     !ComponentID & "," & !WorkID & ",""" & !Summary & """,""" & !detail & """," & 1 & ") ")
                    c = !circularid
                    n = !DueDate + 5 '''set new due date'''
                    CurrentDb.Execute ("UPDATE circularT SET duedate=#" & Format(n, "mm/dd/yyyy") & "#  WHERE circularid=" & !circularid & " ")
                    
                    x = CurrentDb.OpenRecordset("select @@identity")(0) '''get unique ID'''
                        sqlrs2 = "SELECT * FROM circularcraftT WHERE circularid=" & c '''get circular craft table'''"
                        Set rs2 = db.OpenRecordset(sqlrs2)
                        With rs2 '''loop thru circularcraftt'''
                            If Not .BOF And Not .EOF Then
                                .MoveFirst
                                Do Until .EOF
                                '''insert the crafts for the main jobs'''
                                CurrentDb.Execute ("INSERT INTO WorkquecraftT(workqueID,craftid,hours,craftnotes,statusid) " & _
                                "VALUES (" & x & "," & !CraftID & ",'" & !Hours & "',""" & !CraftNotes & """,'" & 5 & "')  ")
                                '''WHERE CircularID=" & c & "
                        
                                .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 Function

so far it works but not sure if its be best way. thanks

[pbaldy]

No problemo.

Heads up, if you switch to SQL Server you'll want to grab the PK after the .Update. It won't work before. Note in Allen's code he does it after, which will work in either environment.

[ssanfu]

i moved my sqlrs2 SQL and set rs2 inside the first loop just under the execute command along with declaring a variable that pulls the circularid from rs1 and then use that variable inside a where clause for my sqlrs2 SQL.

You are absolutely correct. This is the proper place for those two lines.
-------------------------------------------------------


You have "Set db = CurrentDb" but then use

CurrentDb.Execute ("UPDATE circularT SET duedate=#" & Format(n, "mm/dd/yyyy") & "#  WHERE circularid=" & !circularid & " ")

So why have "Set db = CurrentDb"??
Maybe change "CurrentDb.Execute...." to "db.Execute...."??? (4 places in the code)

When using "Execute", you should also use ", dbFailOnError"
In the above update query, you could use "c" in the WHERE clause instead of "!circularid"
-------------------------------------------------------


Also, you have programmed in a couple of bombs in your code.
Let me ask you: "What data type is "!circularid"?
Answer:In the tables, it is an Autonumber or a Long Integer.

However, in code, you have:

    Dim x As Integer
    Dim c As Integer
.
.
.
.
    c = !circularid
    x = CurrentDb.OpenRecordset("select @@identity")(0)    '''get unique ID'''

Here, you have defined "c" and "x" as Integers.
Access is very forgiving and automatically casts the Long Integers as Integers.
But what happens when the numbers that are assigned to "c" or "x" exceeds 32,767 (the max for an Integer)?
Boom!! The code blows up.

You should have:

   Dim x As Long   ' Dim x As Integer
   Dim c As Long  ' Dim c As Integer

--------------------------------------------------------
I don't know if you understand why I changed to SQL lines.
Which would be easier to debug the SQL statements (queries)?

   CurrentDb.Execute ("UPDATE circularT SET duedate=#" & Format(n, "mm/dd/yyyy") & "#  WHERE circularid=" & !circularid & " ")

or

         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

-------------------------------------------------------

As long as the code does what you want, you can write it any way you want.

And thanks for the info about the @@identity code.

[vicsaccess]

hey i did it correct, yea i'm starting to learn I don't have a good answer for using currentdb, will change, not used to using the shorthand. circularid is a number, its my PK in circular and FK in circularcraftT. good catch on the integer, i hadn't thought that far in the future but i'm sure it will get to the long. i also still need to change the "c" and "x" to a better description. as for the SQL's i thought that was only because you thought it was a SQLserver. yes it does make it easier, i just do it the other way again out of early habits. thanks for all your help. i'll get these changes done in the morning.

[vicsaccess]

So far its working great, at least until i change the update duedate part with a real section of code to allow different lengths of reoccurrence. but that would be a new post. Thanks everyone for helping