By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,646 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

VBA Experssion Error with linked table

100+
P: 255
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:
Expand|Select|Wrap|Line Numbers
  1.         If main.UserAction = main.UserSelected Then
  2.  
  3.             myDb.Execute "INSERT INTO ArchiveEmployees SELECT * FROM Employees WHERE ID=" & [txtID]                    
  4.  
  5.             If myDb.RecordsAffected = 1 Then
  6.  
  7.                 DeactiveDate = main.g_FmtDate
  8.                 Comment = main.g_Str
  9.  
  10.                 myDb.Execute " UPDATE ArchiveEmployees SET [Date Terminated] = " & DeactiveDate & ", " & _
  11.                 "  Comments ='" & Comment & "'" & _
  12.                 " WHERE ID = " & [txtID]
  13.  
  14.                 myDb.Execute " Delete * FROM Employees WHERE ID=" & [txtID]
  15.  
  16.                 MsgBox " Archive Success !", vbInformation + vbOKOnly, " Operation complete"
  17.             Else
  18.                 MsgBox " Cannot copy records to the Archive Table", _
  19.                     vbOKOnly + vbCritical, " Operation Failed"
  20.             End If
  21.  
  22.             Me.Requery
  23.             Me.Refresh
  24. 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
Mar 26 '10 #1

✓ answered by 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.

Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,273
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.
Mar 26 '10 #2

NeoPa
Expert Mod 15k+
P: 31,273
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.
Mar 26 '10 #3

100+
P: 255
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:

Expand|Select|Wrap|Line Numbers
  1. Private myDb As Database
  2. ....
  3. Private Sub cmdArchive_Click()
  4. Dim DeactiveDate As String
  5. Dim Comment As String
  6.  
  7.     Set myDb = CurrentDb
  8. ....
  9.  
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.
Attached Images
File Type: jpg error.jpg (26.3 KB, 256 views)
Mar 29 '10 #4

NeoPa
Expert Mod 15k+
P: 31,273
@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.
Mar 30 '10 #5

100+
P: 255
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.
Mar 31 '10 #6

100+
P: 255
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
Apr 1 '10 #7

NeoPa
Expert Mod 15k+
P: 31,273
@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 :)
Apr 8 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.