Solved

MS Access - SetValue and Automation Object

Posted on 2009-04-03
11
256 Views
Last Modified: 2013-11-28
Hello all,

I am creating a form where, when an item in a dropdown box is selected, the SetValue command is used to update the value of another field.  So far, I have used this several times and have not had a problem.  However, I am now trying to use SetValue to fill a memo field and keep getting the error "The object doesn't contain the automation object "QueryName."

Can SetValue be used with memo fields?  As I mentioned, I have gotten it to work with text and number fields, but am struggling with this one.

Thanks
0
Comment
Question by:EricW_CA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24065787
You have not posted what you are trying, so this will be difficult to analyze...

Instead of using SetValue in a Macro, try using code, something like this:

Sub YourComboBox_AfterUpdate()
   YourMemoField=YourComboBox
End Sub

JeffCoachman
0
 

Author Comment

by:EricW_CA
ID: 24082085
Thank you for the comment. Now that I have read the question back, I realize that it makes no sense at all. So, let me give you a little more background.

The table which I am working off of is called "tblCauseStatement." Inside the table, there are three fields: Cause, Criteria, Effect. Cause is a text field and Criteria and Effect are both memos. There are 20+ records, each with a unique Cause, Criteria, and Effect.

There is another table (tblAuditTrack) which stores the actual records for what we are tracking. It includes such things as company name, date of analysis, etc. etc. It also contains blank fields named Cause, Criteria, Effect (text, memo, memo). In a form, which has the tblAuditTrack table set as the source, I have created a dropdown box which is populated with the values from the Cause field in tblCauseStatement. Using the setvalue command in a macro, I have been able to get Access to set the value of Cause in tblAuditTrack to whatever a user selects from the dropdown box.  However, I would also like the fields Criteria and Effect to automatically fill based on the Cause which is selected.  I have not been able to get this to work.  I am mainly trying to use macros so that, should I not work on this project in the future and someone needs to modify the database, they will be able to without a strong knowledge of programming languages (which, frankly, I also lack). However, if using code would be better for this, I am open to all suggestions.
Thanks!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24083112
OK,

LOL, well since you did not state what:
   "I have not been able to get this to work."
...means, I will presume that you mean the Criteria and Effect were being truncated to 255 characters.

Here is how it should go down.

You should not be storing the Criteria or Effect (or Cause for that matter) in tblAuditTrack.

This data should be linked in via a query.

You should add a "Short Name" for the Criteria and Effect, so that this short Name can be selected in the combobox and the "Long Name" (the memo field) can be dispayed in another textbox via the Forms source query.

This will all be done in the form.
No Coding or Macros required.
;-)

Here is a sample.

Study it "Carefully" and completely.

I am sure you will be able to adapt this technique to work in your database in no time.

;-)

JeffCoachman
Access-EEQ24293935ComboBoxFriend.mdb
0
Stressed Out?

Watch some penguins on the livecam!

 

Author Comment

by:EricW_CA
ID: 24083185
One small piece of info I left out.  Of course, it's the most important.  The reason why I need to store the Cause, Criteria, and Effect info in tblAuditTrack is because there are small pieces of information in each which needs to be customized to the individual audit.  For instance, the Effect field might have the pre-defined statement of:

"The result of the cause, specifically XXXX, has led XXXX to implement protocols which are unapproved."

In addition to filling in the XXXXs, there are also times where users will need to add information in addition to the pre-defined wording (supporting information, etc).  Because of this, I can't see a way to accomplish what I need without storing the information in the fields within tblAuditTrack.

Thanks for the help.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24083748
Where are you actually typing in the "final" text for this memo field?
0
 

Author Comment

by:EricW_CA
ID: 24083919
Once the text fills from tblCauseStatement into the Cause, Criteria and Effect fields in tblAuditTrack (hopefully), the idea is to be able to then edit the text inside of a form where not only are the Cause, Criteria and Effect fields being entered, but also all other information regarding the particular record.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24086136
So,
You wish to select an "Effect" which may be over 255 characters long.
Then you may want to edit this "Effect"
Then you want this effect to be displayed in the tblAuditTrack table.
In addition to the normal edits for the other fields in the form for tblAuditTrack.

Is this correct?

0
 

Author Comment

by:EricW_CA
ID: 24086972
Correct.  I wish I could have said it that succinctly.  Almost all predefined Criteria and Effects are over 255 characters, and all are set as memo fields in boh the tblCauseStatement and tblAuditTrack
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 24089061
Try this:

JeffCoachman
Access-EEQ24293935ComboBoxFriend.mdb
0
 

Author Closing Comment

by:EricW_CA
ID: 31566468
Awesome!  I can't tell you how much time you just saved me.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24090481
;-)
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question