Adding Records with Macro

[Chris Belcher]

The large bleeding bump on my forehead is from beating my head on the wall.

I have a form with 3 subforms.. In add mode I enter the main form
information then go to an unbound combo box and select a detail scope.
Using the OnChange event I open an "Add Scope form" that shows the
selected scope and displays 2 buttons "Add Scope" and "Cancel" Pressing
the "Add Scope button runs a macro that opens an append query that
writes the scope data to the detail table. Or at least thats how it's
suppose to work....

I get "Can't append all records" and goes on to say that there were (n)
key violations. After a head banging session I figure out that if I go
to the next record and back the append works. I'll not bore you with the
details but I've tried save, next record, previous record, requery, and
closing the form after I get the scope form up and get the necessary
values stored in unbound text boxes. Each of these result in some type
of error.

What I think is happening here is, while I'm putting the master table
information in, the data is not stored in the master table until I go to
the next record. (I get a pencil on the record selector until then).
[AI Key] is the primary key and is a calculated field set with a default
value that is in the "default value" property for that field. Until I go
to the Next record the Master table records don't exist except on the
form. The Append query is attempting to add detail, using a FK that does
not yet have a PK, so it sets the Key violations.

Am I correct? If so, Is there a way to get Access to push that data into
the Master Table before I start adding the detail.

What am I missing?

Any Help would be appreciated.

 

[Steve Schapel]

Chris,

If your diagnosis is correct, you can try putting a DoCommand/SaveRecord
action in the macro to save the new record to the "master table".
However, this seems odd, as I would expect this to be saved as soon as
the focus moves to the secondary form.

Obviously I don't know all the details of what you are doing. But on
the face of it, you seem to be making life unnecessarily difficult for
yourself. Why don't you use a bound subform for the Scope?

 

[Chris Belcher]

Thanks Steve! Just the ticket. My macro goes out and builds packages of
records to append to a detail table. The problem I have now is that once
it returns you don't see the update until its saved. Sounds like another
DoCommand/SaveRecord or requery would do it but I don't know the command
to make an open form active. I would have thought you would "goto
control" but I'm missing something cause it bombs. It says control not
available or something like that.

To put your previous answer in context.
I have a bound subform for the detail [AIDetail] but was not able to
figure out how to add the scope any other way. It's a similar problem
that you would have if you were a retailer that sold parts, and kits
made up of those parts, except I want to enter the kits as if only the
parts that make up the kits were purchased. It looks something like this:

Form
-------------------------------
| |
|AIMaster |
| |
| [scope] (unbound) |
| |
| ------------------------ |
| | AIDetail | |
| | | |
| | | |
| | | |
| | | |
| | | |
| ------------------------ |
--------------------------------

AI master has a number of bound fields and one unbound, named scope,
that is a combo box populated by a "Select Distinct" of [ScopeID] from
the scope table.

The "scope table" contains "canned" packages of RECORDS each having the
folowing fields:
[ScopeID],[assigneeShort],[Element],[Type]. These packages are then
appended to the AIDetail table. There are multiple records for each of
the Scopes. For instance, if I select "5ESS" as a scope it appends 3
records into the AIDetail Table. If I select "beachland" one record is
added. Many times the user will select more than one scope for a given
AIMaster record. In short, the scope table is used to build the detail
for AIMaster w/o having to build each detail item. The only other field
added to each of these records is AIKey (the FK in AIDetail for AIMaster) .

Thanks for your help.