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

Need Assistance Checking the Untraced Error in my VBA and SQL Codes

P: 1
Greetings
I would appreciate your assistance to advise me which error I did in encoding SQL and VBA codes in MS Access (20)10 append table and Form.

I have two tables DataEntry and GeneralJournal. I created SQL criteria in an append query with code copied below to update Credit field in table GeneralJournal with vale of field TransactionValue if value of field TransactionTypeCode in DataEntry is 1100.

This code turns no error but zero record can be copied
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO GeneralJournal ( Credit )
  2. SELECT      DataEntry.TransactionValue
  3. FROM        DataEntry
  4. WHERE       DataEntry.TransactionTypeCode = 1100;
Then I tried to initiate a form to do the same using Afterupdate in the field TransactionValue.

Also this turns the error copied below.
Expand|Select|Wrap|Line Numbers
  1. Private Sub TransactionValue_AfterUpdate()
  2.   If DataEntry.TransactionTypeCode = 1100 Then
  3.     GeneralJournal.Credit = DataEntry.TransactionValue
  4.   Else
  5.     GeneralJournal.Credit = 0
  6.   End If
  7. End Sub
Error:
Problem in connecting MS Access with OLE server or ActiveX control

Please Assist to have it fixed.

Thanks
Sep 28 '17 #1

✓ answered by NeoPa

Your APPEND query seems fine - syntactically at least. If that's not behaving as you expect then something entirely unrelated to anything you've told us is the cause. Usually you get more information from running the query manually as part of a QueryDef than running it from code.

The code is not even a starting point. Sorry, but you cannot simply reference table fields in VBA as if they were an object. Here's something for that (Access VBA DAO Recordset Loop Using Two Recordsets), but I suspect that's not where your focus needs to be just yet.

TBH, I suspect you need to focus on the SQL side. At least first.

Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,494
Your APPEND query seems fine - syntactically at least. If that's not behaving as you expect then something entirely unrelated to anything you've told us is the cause. Usually you get more information from running the query manually as part of a QueryDef than running it from code.

The code is not even a starting point. Sorry, but you cannot simply reference table fields in VBA as if they were an object. Here's something for that (Access VBA DAO Recordset Loop Using Two Recordsets), but I suspect that's not where your focus needs to be just yet.

TBH, I suspect you need to focus on the SQL side. At least first.
Sep 30 '17 #2

Post your reply

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