Solved

MS Access - SetValue and Automation Object

Posted on 2009-04-03
11
237 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
 

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now