Hi,

I have two tables and one form. (table1, table2, form1)

Once the user updates info on form1 I want to check table1 to see if 3 columns = Yes. If so, I want to insert that record into table2.

I have the follwing syntax in the Form After Update Event. It runs without error but doesn't insert the data into table2.

If Forms!form1!Delete_Approval_MS = Yes _
   'And Forms!forms1!Delete_Approval_DR = Yes _
   'And Forms!forms!Delete_Approval_DK = Yes _
Then docmd.runsql sqlStr
Else
End If

I think I should be checking the table instead, but when I run this code in the Form After Update Event, I error with a "object required" message.

If table1.Delete_Approval_MS = Yes _
    And table1.Delete_Approval_DR = Yes _
    And table1.Delete_Approval_DK = Yes _
Then docmd.runsql sqlStr
Else
End If

Which code should I be using and where do I locate it?

--this is my sqlstr

sqlStr = "INSERT INTO table2 " & _
        "SELECT * FROM table1 " & _
        "WHERE (((table.Delete_Approval_MS)=Yes) " & _
        "AND ((table1.Delete_Approval_DR)=Yes) " & _
        "AND ((table1.Delete_Approval_DK)=Yes)) "

Thanks, bph

It looks like you want to do one record at a time, so what you want instead of this:

sqlStr = "INSERT INTO table2 " & _
        "SELECT * FROM table1 " & _
        "WHERE (((table.Delete_Approval_MS)=Yes) " & _
        "AND ((table1.Delete_Approval_DR)=Yes) " & _
        "AND ((table1.Delete_Approval_DK)=Yes)) "

Is:

Dim iPK As Integer

iPK = Me.PKFieldForTable1


sqlStr = "INSERT INTO table2 " & _
        "SELECT * FROM table1 " & _
        "WHERE [PKFieldForTable1] = " & iPK

Did that help?



mmcdonal

Sorry, didn't answer the question.

I would put this code on the Form's After Update, and Before Insert events.

Dim iPK As Integer
Dim sqlStr As String

If Me.Delete_Approval_MS = True AND _
   Me.Delete_Approval_DR = True AND _
   Me.Delete_Approval_DK = True Then

     iPK = Me.PKFieldForTable1
     sqlStr = "INSERT INTO table2 " & _
        "SELECT * FROM table1 " & _
        "WHERE [PKFieldForTable1] = " & iPK

     DoCmd.RunSql sqlStr
End If

The problem with this code and where it is is that if a user adds a new record then it copies the record, no problem. If the user MODIFIES an existing record, then it copies the new record, problem.

So what you will need to do is modify the code on the Before Update event to allow a user to change one of these values to yes to make them all yes, and then do the update from there.

So perhaps what you want to do is add this code to a module:

Public pMS, pDR, pDK As Boolean


Then on the form's the On Current event:

pMS = Me.Delete_Approval_MS
pDR = Me.Delete_Approval_DR
pDK = Me.Delete_Approval_DK

Then add this code to the Before Update Event (but not the Before Insert event):

Dim bMS, bDR, bDK As Boolean

bMS = Me.Delete_Approval_MS
bDR = Me.Delete_Approval_DR
bDK = Me.Delete_Approval_DK

If pMS = False Or pDR = False Or pDK = False Then
    If bMS = True And bDR = True And bDK = True Then
        'code to add record here
    End If
Else
    'No record added
        Exit Sub
End If

Did that help any?


mmcdonal

Excellent help. Went with the second way.
One piece I left out is that I then delete the record from table1 after the insert is completed.

Thanks for the response! bph

One minor addition - sorry, mmcdonal, especially as you are so helpful all the time, but I was surprised to see an "advanced member" using code like this:

Public pMS, pDR, pDK As Boolean

and this:

Dim bMS, bDR, bDK As Boolean

In both these cases, the first two variables are *not* declared as Boolean, but as a Variant type. Ok, so they will work as a Boolean when you use them like that, but the performance and memory usage will be much poorer. Not a big issue for a small program, but if you do this throughout a large application, then it will perform visibly worse than if the variables are declared separately, like this:

Dim bMS As Boolean
Dim bDR As Boolean
Dim bDK As Boolean

It's only a small amount more typing, and can reap a great benefit in performance.

Just my tuppence...

That is probably a carry over from my VBScripting, which is typeless. Normally my VBA is a Dim on each line.


mmcdonal