Good Morning! I am trying to add sequential numbers to a table. If it does not exceed a number another table.
I am having trouble with the insert portion. this code works in a normal query in the access query gui.
I can't seem to get it to work in the code below.
Also if there is a better way to do this please let me know.
Thanks
Here is the code I have so far
I am having trouble with the insert portion. this code works in a normal query in the access query gui.
SELECT Format(Date(),"yy") & "-" & Format([Closed_File],"0000") AS test;
I can't seem to get it to work in the code below.
Also if there is a better way to do this please let me know.
Thanks
Here is the code I have so far
Code:
Private Sub Closed_File_NumbersUpdate()
Dim db As Database
Dim sqlstr As String
Dim sqlstr1 As String
Dim Closed_File As Integer
Dim ClosedNumber As Integer
Dim test As String
Dim rs As Recordset
Dim rs1 As Recordset
Set db = CurrentDb
sqlstr = "SELECT Max(Val(Right([Closed_File_Numbers]![Closed_Numbers],4))) AS Closed_File1" & _
" FROM Closed_File_Numbers " & _
" WHERE (Left([Closed_File_Numbers]![Closed_Numbers],2))=Format(Date(),""yy""); "
Set rs = db.OpenRecordset(sqlstr, dbOpenSnapshot)
Closed_File = rs!Closed_File1
Debug.Print Closed_File
sqlstr1 = "SELECT Max(Val(Right([ClosedNumbers]![ClosedNumbers],4))) AS ClosedNum" & _
" FROM ClosedNumbers " & _
" WHERE (Left([ClosedNumbers]![ClosedNumbers],2))=Format(Date(),""yy""); "
Set rs1 = db.OpenRecordset(sqlstr1)
ClosedNumber = rs1!ClosedNum
Debug.Print ClosedNumber
Do
Closed_File = Closed_File + 1
db.Execute "INSERT INTO Closed_File_Numbers(ClosedNumbers)" & _
" Select Format(Date(),""yy"")"&"-"&Format(Closed_File,0000) As test;"
Loop While Closed_File < ClosedNumber
End Sub