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

Macro error: "The object doesn't contain the automation object [database_name]"

sueb
100+
P: 379
I've done a lot of database work, but only scratched the surface with Access. I'm trying to write a macro (executable on-click from a button on a form) that will clear a subset of the current record's fields.

My macro is pretty simple-minded: It has eight actions (all SetValue), each one associated with one of eight fields ("items", right?--selected off the table's field list), and each target expression is simply "null".

I've associated the macro with the button on the form, but when I click the button, I get the following pop-up error:

--------------------------------------------------------------

The object doesn't contain the Automation object 'my_database.'

You tried to run a Visual Basic procedure to set a property or method for an object. However, the component doesn't make the property or method available for Automation operations.

Check the component's documentation for information on the properties and methods it makes available for Automation operations.

--------------------------------------------------------------

Is the "component" my database? My macro? I've been unable to find any a property associated with anything that speaks to "Automation-ability."

Since the macro dies at the first call, I changed the order of the fields, just on the off-chance that something just happened to be wrong with that first call. Same thing.
Apr 24 '10 #1

✓ answered by ADezii

@sueb
I was trying to figure out the easiest way to accomplish your Task, and this is what I came up with:
  1. For each Control on your Form that you may wish to have cleared, set its Tag Property to Clear. You can accomplish this by:
    • Right Click on the Control(s)
    • Select Properties
    • Select Other Tab
    • Tab will be the last Property listed
    • Enter Clear as the Value for this Property
  2. Execute the following code wherever you deem necessary:
    Expand|Select|Wrap|Line Numbers
    1. On Error Resume Next
    2.  
    3. Dim ctl As Control
    4.  
    5. For Each ctl In Me.Controls
    6.   If ctl.Tag = "Clear" Then
    7.     ctl.Value = Null
    8.   End If
    9. Next

Share this Question
Share on Google+
10 Replies


ADezii
Expert 5K+
P: 8,638
@sueb
There are several ways to approach this scenario, the least desirable of which would be via a Macro. Provide us with complete Details such as: Field Names, Table Names, Form Name, Criteria for setting these Fields to NULL, etc., and one of us will be glad to assist you.
Apr 24 '10 #2

P: 37
Well you picked the most impossible way to do that :) Setting null values can be bit tricky also. Easiest way is (assume they are textboxes but goes for any control) is to just clear the controls. They are bound remember so tied directly to the database, it's like working in the table itself.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Button1_Click()
  2.  
  3. me.txtField1 = ""
  4. me.txtField2 = ""
  5. etc
  6.  
  7. End Sub
  8.  
  9.  
Optionally you might need to save the current record depending on how your form is bound using

Expand|Select|Wrap|Line Numbers
  1. DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Apr 24 '10 #3

sueb
100+
P: 379
Thanks for the quick replies!

It's actually comforting that I'm going about this the wrong way, since Access's response to what seemed so simple was so puzzling!

I’m almost embarrassed to display the structure of this database, but I take a little comfort in the fact that, even though I haven’t “fixed” it, I most certainly did not create it like this.

Here is a field list (the starred fields are the ones I want to clear, and everything not specifically defined is a text field of some length):

Chart #
Date of Birth (Date/Time)
Family Name
Given Name
Other Name
* PDF (Hyperlink)
* Date (Date/Time)
* Requestor
* Priority (Yes/No)
* Procedure
* Category
* Payor
* Bucket Notes
* Status Date (Date/Time)
* Status
* Status Note
* TAR #
* Submission Date (Date/Time)
* ICD-9
* CPTs
* Inpatient (Yes/No)
* TAR Status Date (Date/Time)
* TAR Status
* TAR Status Note
(2 more sets of the starred fields, named the same as above but appended with “(2)” and “(3)”. All 3 sets of event data are displayed on the form. With the database structured like this, I’ll be needing a total of 3 identical procedures, with each procedure targeting a different “set”.)

The way this database is used is that only the latest 3 events (the 3 sets mentioned above) are retained in each record. New events are captured by erasing the data in one of the existing sets and entering the new event’s data in that set. The database is typically open by 5 users at a time, but the users don’t seem to run into each other much.

The goal of this procedure is to enable a user to quickly clear a set in preparation for entering the new event. I’d like to have a button next to each set that does that.

Any help will be greatly appreciated.
Apr 26 '10 #4

ADezii
Expert 5K+
P: 8,638
@sueb
I was trying to figure out the easiest way to accomplish your Task, and this is what I came up with:
  1. For each Control on your Form that you may wish to have cleared, set its Tag Property to Clear. You can accomplish this by:
    • Right Click on the Control(s)
    • Select Properties
    • Select Other Tab
    • Tab will be the last Property listed
    • Enter Clear as the Value for this Property
  2. Execute the following code wherever you deem necessary:
    Expand|Select|Wrap|Line Numbers
    1. On Error Resume Next
    2.  
    3. Dim ctl As Control
    4.  
    5. For Each ctl In Me.Controls
    6.   If ctl.Tag = "Clear" Then
    7.     ctl.Value = Null
    8.   End If
    9. Next
Apr 26 '10 #5

sueb
100+
P: 379
@ADezii
So in this example, I'm assuming that "Me" would be replaced by my database's name (or is that a keyword?). Also, as I mentioned earlier, the ways of Access are still murky to me, so, since it's already been made clear that I would NOT use a macro, would this be a module, that I would then attach to my form's button?
Apr 26 '10 #6

ADezii
Expert 5K+
P: 8,638
@sueb
  1. Me, in this case, would refer to the Form where the code is currently executing.
  2. The code could go in the Click() Event of a Command Button, among other places.
  3. Download the Attachment for a Visual to see how this can be done.
Attached Files
File Type: zip Clear Controls.zip (13.8 KB, 257 views)
Apr 26 '10 #7

sueb
100+
P: 379
I think I'm getting closer. But now I'm getting an "Object Required" error message. Here's my module:

Private Sub CLEAR_IUR_1_Click()
On Error GoTo Err_CLEAR_IUR_1_Click

Dim ctl As Control

For Each ctl In frm_IURStatusNotes.Controls
If ctl.Tag = "Clear" Then
ctl.Value = Null
End If
Next

Exit_CLEAR_IUR_1_Click:
Exit Sub

Err_CLEAR_IUR_1_Click:
MsgBox Err.Description
Resume Exit_CLEAR_IUR_1_Click

End Sub


I added the exit and error handler because when I ran it without them, only some of the fields marked with "CLEAR" got cleared.
Apr 26 '10 #8

ADezii
Expert 5K+
P: 8,638
@sueb
Expand|Select|Wrap|Line Numbers
  1. For Each ctl In Me.Controls
Apr 26 '10 #9

sueb
100+
P: 379
That seemed to do it! Apparently, I misunderstood what you meant about the "form on which I was working", but now it's all good! Thanks so extremely much!
Apr 26 '10 #10

ADezii
Expert 5K+
P: 8,638
@sueb
You are quite welcome. It wasn't your misunderstanding, but my poor explanation. Good Luck.
Apr 27 '10 #11

Post your reply

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

Browse more Microsoft Access / VBA Questions on Bytes