VBA Experssion Error with linked table

[colintis]

Hi guys,

I just takeover to continue on a database project with a bug, the code seems all good, but it didn't pass through an execute SQL statement. Here the part of code that stucked:

        If main.UserAction = main.UserSelected Then
 
            myDb.Execute "INSERT INTO ArchiveEmployees SELECT * FROM Employees WHERE ID=" & [txtID]                    
 
            If myDb.RecordsAffected = 1 Then
 
                DeactiveDate = main.g_FmtDate
                Comment = main.g_Str
 
                myDb.Execute " UPDATE ArchiveEmployees SET [Date Terminated] = " & DeactiveDate & ", " & _
                "  Comments ='" & Comment & "'" & _
                " WHERE ID = " & [txtID]
 
                myDb.Execute " Delete * FROM Employees WHERE ID=" & [txtID]
 
                MsgBox " Archive Success !", vbInformation + vbOKOnly, " Operation complete"
            Else
                MsgBox " Cannot copy records to the Archive Table", _
                    vbOKOnly + vbCritical, " Operation Failed"
            End If
 
            Me.Requery
            Me.Refresh
End If

The expression is trying to move any leaving staff from one table to another, both tables are linked from another access mdb. The line in bold is where the process stops, but this whole expression in previous versions did work where its exactly the same. I had suspected that its because the code was supported in Access 97 but not in Access 2003, but as the file format is compatable to both version I don't think such fact matters. So how can I fix this error? Thanks.

Colin

[NeoPa]

A good start would be to report the error message that you get when you attempt to run this code.

I'm assuming the variable MyDB is already set up correctly, as this code isn't included, but maybe it would be a good idea to post that as well.

[NeoPa]

Actually, check these out before posting too. You may find your problem goes away after some of these basics. If not, at least we know we're not wasting time on the them.

When posting any code on here please :

1. Ensure you have Option Explicit set (See Require Variable Declaration).
2. Try to compile it. If it doesn't compile for any reason please explain that clearly - including the error message and which line of your code it appears on. Compilation is done from the Visual Basic Editor menu - Debug \ Compile Project (Where Project is the actual name of your project).
3. Copy your code (using the Clipboard - Cut / Copy / Paste) from your project directly into your post. Typing in code is not appreciated as it is likely to introduce typos which cause members to waste their time unnecessarily.
4. Ensure that the code in your post is enveloped within CODE tags (For more on this see BB Code List). The hash (#) button in the posting page helps with this. Simply select your code and click on the hash button to have it enveloped automatically.

If all these points are covered then all members will be better able to understand, and therefore attempt to answer, your question.

[colintis]

Thanks for the reply and corrections NeoPa, after setting the Variable Declaration and clicked compile, there's nothing happened and the compile button is disabled. Does it means the codes were fine?

MyDB from the upper part of the code is simply defined with:

Private myDb As Database
....
Private Sub cmdArchive_Click()
Dim DeactiveDate As String
Dim Comment As String
 
    Set myDb = CurrentDb
....
 

The attachment below is the error message I've been received, the error message was received when the code executed from .mde file, while executed in .mdb file there were no actions occured, stopping on the INSERT SQL line.

[NeoPa]

@colintis
Generally, when the compile option is greyed out it means the code is already successfully compiled. This is a good status.

Otherwise, the error message seems to be pointing towards an issue with the configuration of the form - in that it doesn't like the procedure you linked to to handle the event. This may be due to security settings on an MDE. I'm not really sure.

When the code runs and stops on the myDB.Execute line, there should be an error message displayed there too. We're a bit stuck without that info to be fair. There is nothing obviously wrong I can see, but then I don't know your database. It could be something that cannot work within your database specifically.

[colintis]

So in that case, the error is located somewhere outside the codes? but settings for both mdb and mde are using the default settings which its been working in previous versions(by other programmers before me) as well.

What other info would give the help that I can provide? I'll take a check again on the tables to see if there's any problems.

[colintis]

Yes the problem is found on the target table with a column missing, i checked it with running the SQL in the query and it showed an error message about it. Thanks for your advise NeoPa

[NeoPa]

@colintis
A pleasure to help Colin.

You probably understand better how hard it is to help in a more specific way when the error was in an area we had no information about.

Of course, it's often hard to know what information to include too, when you don't understand the problem. It's what makes it all so much fun :D

Anyway, I'm pleased you managed to find the solution :)