Solved

MS Access - SetValue and Automation Object

Posted on 2009-04-03
11
247 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
  • 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

808 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