Sequence of events when duplicating a record

[night_writer]

I have a form thet displays a record with about 65 fields, 50 of which
are checkboxes for individual states. Based on the OnCurrent event, I
check the status of each checkbox and color code the label depending
on whether or not the box is checked. This subroutine is initiated by
the OnCurrent event, which I chose because it seems to be the only
event that fires as you flip through multiple records while paging
through the form.

I want to allow my users to duplicate this record, so I used the
wizard to create a button to duplicate it. The problem arises in that
apparently the OnCurrent event is being triggered even though the
window isn't "active." (I get 50 error messages that each say: "The
expression you entered requires the control to be in the active
window.") After I answer "OK" to each mesage, all of the data appears
in the form, and it even appears that the fields are colored properly
for the state of the check box, but this might be some kind of
artifact from the originating form.

Here is the code that the Wizard generated for me:

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste
Append

When I try to figure it out from the help file, it refers me to menus
from Access 95 for an interpretation of the numbers. I'm using 2003
but creating my data base in 2000 format. I have no idea what the code
is actually doing.

I think what I need is a way to create the record and then move to it,
so that the form is active before the OnCurrent event occurs (????).
Perhaps I could insert the new record with an SQL statement, or do
something with recordsets...I'm not very practiced with either of
those. I would appreciate any advice on a approch to this problem.


Alice

 

[John W. Vinson]

I have a form thet displays a record with about 65 fields, 50 of which
are checkboxes for individual states.

Then your table structure IS WRONG. "Fields are expensive, records are cheap"
- you'll do much better to use a one to many relationship to a second table,
with one record for each state.

Based on the OnCurrent event, I
check the status of each checkbox and color code the label depending
on whether or not the box is checked. This subroutine is initiated by
the OnCurrent event, which I chose because it seems to be the only
event that fires as you flip through multiple records while paging
through the form.

I'd suggest (if you stick with this table design) that you instead use a
Textbox bound to each field. Set its Format to "Yes/no" so it displays the
word rather than -1/0; and use Conditional Formatting to change its color.

I want to allow my users to duplicate this record, so I used the
wizard to create a button to duplicate it. The problem arises in that
apparently the OnCurrent event is being triggered even though the
window isn't "active." (I get 50 error messages that each say: "The
expression you entered requires the control to be in the active
window.") After I answer "OK" to each mesage, all of the data appears
in the form, and it even appears that the fields are colored properly
for the state of the check box, but this might be some kind of
artifact from the originating form.

Here is the code that the Wizard generated for me:

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste
Append

When I try to figure it out from the help file, it refers me to menus
from Access 95 for an interpretation of the numbers. I'm using 2003
but creating my data base in 2000 format. I have no idea what the code
is actually doing.

The Wizards are old, creaky and doddering and they don't know how to do things
well...

I think what I need is a way to create the record and then move to it,
so that the form is active before the OnCurrent event occurs (????).
Perhaps I could insert the new record with an SQL statement, or do
something with recordsets...I'm not very practiced with either of
those. I would appreciate any advice on a approch to this problem.

If you really want to do this I'd have the button run an Append query
appending the current record to the table upon which the form is based, and
then navigate to the new record. If you'll post some details of the structure
of your table (the Form's Recordsource query SQL might be helpful) someone
should be able to help write the code for you.

 

[night_writer]

Then your table structure IS WRONG. "Fields are expensive, records are cheap"
- you'll do much better to use a one to many relationship to a second table,
with one record for each state.


I'd suggest (if you stick with this table design) that you instead use a
Textbox bound to each field. Set its Format to "Yes/no" so it displays the
word rather than -1/0; and use Conditional Formatting to change its color..









The Wizards are old, creaky and doddering and they don't know how to do things
well...


If you really want to do this I'd have the button run an Append query
appending the current record to the table upon which the form is based, and
then navigate to the new record. If you'll post some details of the structure
of your table (the Form's Recordsource query SQL might be helpful) someone
should be able to help write the code for you.
--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

Alas, I know my table structure for this is wrong, wrong, wrong, and I
knew it when I designed the thing. I also knew that I just wasn't
adept enough at manipulating recordsets through code to do some of the
things my users want and need. I've learned a lot on this project, but
I'm so close to having something usable at this point, that I feel I
have to plow through and get my users a finished product soon. I will
have to save the normalization for the rewrite.

I have managed to do an insert query by creating an SQL statement in
VBA, and I can probably figure out an Append query. I was just hoping
there would be some way I could make the form be active before the
OnCurrent event fired.

One option I was considering was to set some variable when entering
the "duplicate record" subroutine, then test that variable in the
OnCurrent event and cancel the state checking subroutine. Then I could
return to the "duplicate record" subroutine adn set the focus on a
field to activate the window before calling the state checking
routine. Or something like that.

Anyway, thanks for the advice. Hopefully I can get something to work!

 

[John W. Vinson]

I have managed to do an insert query by creating an SQL statement in
VBA, and I can probably figure out an Append query. I was just hoping
there would be some way I could make the form be active before the
OnCurrent event fired.

ummm... Insert Query and Append Query are two names for the same thing!

Please post your current code. This is fixable.

 

[night_writer]

ummm... Insert Query and Append Query are two names for the same thing!

Please post your current code. This is fixable.

Got sidetracked for a few days trying to fix this myself, and I just
can't figure it out. Here is what I have for code:

~~~~
'variable applies at modular level
blnDuplicate = True 'I DON'T RUN AN ONCURRENT EVENT SUBROUTINE IF
TRUE

Me.ProjectID.Enabled = True 'I NEED TO ENABLE THIS FIELD,
OTHERWISE IT DOESN'T SEEM TO COPY

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend

Me.strCircularNumber.SetFocus ' THIS IS WHERE IT BOMBS (SEE BELOW)

blnDuplicate = False
~~~~

What I need to do is make the screen with the new record active so I
can move to strCircularNumber field, and then set ProjectID.Enable to
False and a few other things. The error I'm getting is: "MS Office
Access can't move the control to strCircularNumber."

I've tried all sorts of things, and it appears that even though my
screen changes and appears to make the newly duplicated record the
current record, it skips the "activate" step. According to the help
file: The Activate event doesn't occur when a form receives focus back
from a dialog box, popup, or another form. I'm guessing this is what's
happening, but the help file also says: You can make a form or report
active ... by using the SetFocus method in Visual Basic (for forms
only). However, I am getting the error message above when I try to use
hte SetFocus method. I've also tried being more explicit, as in: Forms!
frmCirculars!strCircularNumber.SetFocus, but I still get the error
message.

I am thinking there must be something very simple that I'm missing. I
just want to get control of this form again after I've appended the
record and I haven't been able to figure out how to do it!

Any help or advice would be greately appreciated.

Alice