Docmd.runsql

[jl2886]

Hello. I have two questions:

 Private Sub Form_Load()
Dim assID As Long
   assID = DMax("CLng(Right([LSI_Case_Number],3))", "Master_Log")
   Me.LSI_Case_Number = Month(Date) & Format(Year(Date), "yy") & "-" & Format(assID + 1, "000")
End Sub 
 

It reads an indentifier cursor error. In my table LSI_Case_Number is a text variable

Next question:
I have a form that has all the variables from my main table: Master_Log, and one of the variables is Disposition.
Disposition is automatically filled in upon hitting enter(it depends on many of the variables in the form).
If the disposition says "rejected", I would like the case to be sent to the "Rejected" table(which is just an empty copy of the Master_Log), and deleted from the Master_Log.

I think I am supposed to use Docmd.runsql, but does it matter if try to use that command in an after_update Disposition. I think it does matter, it needs to be a DoSql sub.
Here is my poor attempt:

Private Sub DoSql()
Dim SQL As String
If Me.dispostion = "Reject" Then
SQL = "APPEND Rejected"
End If
DoCmd.RunSQL SQL
End Sub
 

[Rabbit]

Hello. I have two questions:

 Private Sub Form_Load()
Dim assID As Long
   assID = DMax("CLng(Right([LSI_Case_Number],3))", "Master_Log")
   Me.LSI_Case_Number = Month(Date) & Format(Year(Date), "yy") & "-" & Format(assID + 1, "000")
End Sub 
 

It reads an indentifier cursor error. In my table LSI_Case_Number is a text variable

Next question:
I have a form that has all the variables from my main table: Master_Log, and one of the variables is Disposition.
Disposition is automatically filled in upon hitting enter(it depends on many of the variables in the form).
If the disposition says "rejected", I would like the case to be sent to the "Rejected" table(which is just an empty copy of the Master_Log), and deleted from the Master_Log.

I think I am supposed to use Docmd.runsql, but does it matter if try to use that command in an after_update Disposition. I think it does matter, it needs to be a DoSql sub.
Here is my poor attempt:

Private Sub DoSql()
Dim SQL As String
If Me.dispostion = "Reject" Then
SQL = "APPEND Rejected"
End If
DoCmd.RunSQL SQL
End Sub
 

Well, I tested it and it works. Are you sure the last 3 characters will always be numbers?

An Append SQL is contructed like this:

INSERT INTO TableName1
SELECT Fields
FROM TableName2;
 

[jl2886]

So what would my code look like for the append and delete look like for an after update of my dispostion variable.
The assign id works but only when i load the form. Can I do a click statement that assigns the lsi_case_number. and for some reason the year is 05.

[Rabbit]

So what would my code look like for the append and delete look like for an after update of my dispostion variable.
The assign id works but only when i load the form. Can I do a click statement that assigns the lsi_case_number. and for some reason the year is 05.

I don't know what your code would look like, I don't know anything about how table structure.

Yes, you can put the code in your on load in the on click event of a control.