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

[sueb]

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.

[ADezii]

@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.

[robjens]

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.

Private Sub Button1_Click()
 
me.txtField1 = ""
me.txtField2 = ""
etc
 
End Sub
 
 

Optionally you might need to save the current record depending on how your form is bound using

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

[sueb]

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.

[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:
   

   On Error Resume Next
    
   Dim ctl As Control
    
   For Each ctl In Me.Controls
     If ctl.Tag = "Clear" Then
       ctl.Value = Null
     End If
   Next

[sueb]

@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?

[ADezii]

@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.

[sueb]

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.

[ADezii]

@sueb

For Each ctl In Me.Controls

[sueb]

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!

[ADezii]

@sueb
You are quite welcome. It wasn't your misunderstanding, but my poor explanation. Good Luck.