Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

MS Access - SetValue and Automation Object

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
EricW_CA
Asked:
EricW_CA
  • 6
  • 5
1 Solution
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
EricW_CAAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
EricW_CAAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
Where are you actually typing in the "final" text for this memo field?
0
 
EricW_CAAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
EricW_CAAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
Try this:

JeffCoachman
Access-EEQ24293935ComboBoxFriend.mdb
0
 
EricW_CAAuthor Commented:
Awesome!  I can't tell you how much time you just saved me.
0
 
Jeffrey CoachmanMIS LiasonCommented:
;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now