Sequence of events when duplicating a record

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
0
night_writer
1/22/2010 4:30:56 PM
access.formscoding 7493 articles. 0 followers. Follow

4 Replies
646 Views

Similar Articles

[PageSpeed] 48

On Fri, 22 Jan 2010 08:30:56 -0800 (PST), night_writer <night_writer@att.net>
wrote:

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

             John W. Vinson [MVP]
0
John
1/22/2010 5:40:03 PM
On Jan 22, 11:40=A0am, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On Fri, 22 Jan 2010 08:30:56 -0800 (PST), night_writer <night_wri...@att.=
net>
> wrote:
>
> >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 ch=
eap"
> - you'll do much better to use a one to many relationship to a second tab=
le,
> 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 th=
e
> 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:
>
> > =A0 =A0DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
> > =A0 =A0DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
> > =A0 =A0DoCmd.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 t=
hings
> 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, a=
nd
> then navigate to the new record. If you'll post some details of the struc=
ture
> of your table (the Form's Recordsource query SQL might be helpful) someon=
e
> should be able to help write the code for you.
> --
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0John 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!
0
night_writer
1/22/2010 6:32:44 PM
On Fri, 22 Jan 2010 10:32:44 -0800 (PST), night_writer <night_writer@att.net>
wrote:

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

             John W. Vinson [MVP]
0
John
1/22/2010 7:04:39 PM
On Jan 22, 1:04=A0pm, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On Fri, 22 Jan 2010 10:32:44 -0800 (PST), night_writer <night_wri...@att.=
net>
> wrote:
>
> >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.
> --
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0John W. Vinson [MVP]


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 =3D True  'I DON'T RUN AN ONCURRENT EVENT SUBROUTINE IF
TRUE

    Me.ProjectID.Enabled =3D 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 =3D 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
0
night_writer
1/25/2010 9:01:21 PM
Reply:

Similar Artilces:

How to autofill a sequence
How can I autofill a sequence like the following. I would start with: 1 1 2 2 3 3 ..... and it would contunue to autofill.... 4 4 5 5 6 6 ..... Can this be done without vba coding? Greatly appreciate any help. Best Regards - Randy Randy 1 Select the relevant range you that already contains the series. 2 With the range selected, point your mouse over the bottom right corner of the last cell in the range. 3 Right click your mouse and drag down as far as you need. 4 Release the right mouse button and you should be presented with a number of options - select "Fill Series" -- XL200...

Fewer records after UNION?
I am using Union to bring two tables together. Before the union, one query has 1500 results and the other 97. After the union I have only 1350 results. I should have 1597. What could be the reasons behind receiving fewer records after a union than what each of the union members have before the union? Sean M. Severson UINION eliminates duplicates. Try using UNION ALL instead, and see whether you get 1597. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "NerdRunner" <sseverson@2sts.biz> wrote in message news:1187955413.049914....

validating xml defined by DTD
I want to bypass the dtd validation that child elements must appear in a certain order ... In a DTD child elements that are defined and seperated by commas they must appear in the same sequence in an xml file in order for the xml to be valid ... eg if the dtd defines an element Parent with two child nodes <!ELEMENT Parent (a, b)> Then the following xml is valid <Parent> <a>data</a> <b>data</b> </Parent> but the following xml <Parent> <b>data</b> <a>data</a> </Parent> is invalid ... Does anyone know of any wa...

Event ID MSExchangeSA 9097
Anyone shed any light on the following event i've been having every 5 minutes for the past few MONTHS!! Event Type: Error Event Source: MSExchangeSA Event Category: Monitoring Event ID: 9097 Date: 31/12/2003 Time: 09:27:15 User: N/A Computer: MAILSERVER Description: The MAD Monitoring thread was unable to connect to WMI, error '0x80041006'. For more information, click http://www.microsoft.com/contentredirect.asp. this link might help http://www.eventid.net/display.asp?eventid=9097&source=MSExchangeSA Dirk van Mierlo "Dan Williams" <dan_williams@newcross-nu...

Please help! Only Recording Sound Missing- wtf?
Ok so I can hear everything - except what's being recorded. Not the most technically with-it so bear with me. OS is Vista home basic and sound card is the preinstalled VIA. System sounds are fine, all playback is fine. When I record I can see it being monitored - I can save it, and play it back without problems. However - I can't hear it when it's BEING recorded. Checked in device properties via control panel and nothing wrong - can see the sound register fine. I had installed and uninstalled a number of sound-recording and related software over the last 6 months or so and...

Times out of sequence
In Access I can use the DateDiff function to calculate the difference of time between two times and it will also show negative time! in excel I have only been able use if(time1<time2,1+time1-time2,time1-time2). Could I somehow use automation to access that function... ? or is there an easieer way in excel to show negative time! Thanks Hi If you use the 1904 date system, Tools>Options>Calculation>1904 system, then you can display negative times. Be aware that there is a difference of 1432 days between the 2 system's base dates, so do not mix dates in the same workbo...

Offline Address List Error
one Exchange 2003 Sp2 server This is a hosting environment with several companies. Source: MSExchangeSA Category: OAL Generator Event ID: 9337 Here is the Event Id Description: OALGen did not find any recipients in address list '\"Name of address list"'. This offline address list will not be generated. - OAB_"name of address list" As a result of this there is no way of logging inn from an outlook 2003 client if you are a member of the address list metion in the event id. The error from the user side is: "The name could not be matched to a name in the ad...

opening an excel file opens a duplicate file of the same file
i have an excel file that when i open it it opens another duplicate file of the same name with a (2) at the end of it.. if i edit one it updates the second.. how can i tell it to not open another duplicate file? It's actually opening a second window into the single workbook. Select the window you don't want to see and hit ctrl-w. Then save the workbook (or you'll be doing it again when you reopen the workbook). skm wrote: > > i have an excel file that when i open it it opens another duplicate file of > the same name with a (2) at the end of it.. if i edit one it upd...

Activity sequence in CRM 3.0
Hi evereyone, I have got a problem in CRM Cases, I want the following procedure occures when a case is created until it resolves: 1- Support-1 create new case and fill primary field and problem description. 2- After filling General field in Case information, this fields must be read only ! 3- Support-1 should try to resolve this case otherwise he assigns it to Support-2. 4- Support 1 and 2 must register all activities pursuant to a troubleshooting rule. 5- Each step in this rule must be registered in CRM as an activity but these activities have sequence and serialization. Now, my proble...

Event ID 1000
I am getting to following error when I open Outlook XP: Event Type: Error Event Source: Microsoft Office 10 Event Category: None Event ID: 1000 Date: 11/27/2003 Time: 11:13:16 PM User: N/A Computer: ANGELA_LAPTOP Description: Faulting application outlook.exe, version 10.0.2627.1, faulting module mso.dll, version 10.0.2625.0, fault address 0x006d3fe5. For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. Data: 0000: 41 70 70 6c 69 63 61 74 Applicat 0008: 69 6f 6e 20 46 61 69 6c ion Fail 0010: 75 72 65 20 20 6f 75 74 ure out 0018: 6c 6f 6f...

All Day Event
I have more than 11 items scheduled as All day events. I can only view eleven of them in the Calendar View. How can I view more than eleven items - is there a setting? What is your screen resolution, upping it to 1024 x 768 should allow for 16 appointments <anonymous@discussions.microsoft.com> wrote in message news:00a901c3c33d$7e43cda0$a501280a@phx.gbl... > I have more than 11 items scheduled as All day events. > I can only view eleven of them in the Calendar View. > How can I view more than eleven items - is there a > setting? That worked - Thanks >-----Original Mes...

Using email to update Table records
Wasn't sure exactly where to post this so here goes: One of my employees is trying to utilize the functionality in Access 07 where she can create an email that allows the recipient to make updates to a tables record then emails the new info back and that updates the databases' table accordingly. The problem is when she creates the email (using the Create Email button on the External Data tab) the email includes every record in the table, she only wants to send out one particular record in the email and doesn't want the recipient to be able to see all the other record...

event id 4879 MSDTC Client 2
Hi ! I have a problem with an application on a windows server 2008 r2. This application works ok on all clients running windows x32. But when I run it on my server it crashes with event id 4879 MSDTC Client 2 encoured a problem contacting the server itself. I've tried the binacle to set permissions to network service also change and tried almost every settings under dtc in dcomcnfg. I'm no expert at sql and get no help from the developers of the program. It can be some new securitything in R2 server or is it that it's x64? Glad for help and tips asap. Best rega...

record replication ?
Hello, I have a little problem that has been bugging me for quite some time now... I have got a table of retail stores and their addresses of with theses fields : ID (numeric) Name Street No Min No Max Parity and some other less important here So if a store only occupies one adress (say 25, Main street) his adress number will be in NoMin field but if a store occupies more than one continuous adress (say from 25 to 31, Main street) it will have 25 in the max field and 30 in the min field. The parity field says if the interval contains only even number, odd number or both. Now... what i wa...

How do I create one record using multiple relationship tables?
I'm trying to create a query using different tables to create one summary table for a mail merge. But when I did the relationship I got 6 record for the same person and I want to send just one letter with all the information for each person. ...

Add events in 2003
Added events but only added to the year 2007. None for 2008 and beyond. See http://www.outlook-tips.net/howto/missinghol.htm -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM Poll: What version of Outlook do you use? http://forums.slipstick.com/showthread.php?t=27072 "Phippsy&quo...

Recording Soundtrack in slide show
I am using PP 2002 and have put together a 25 slide, slideshow. I have been able to add a soundtrack to it and it works great on my computer. Went I send it, it goes without the soundtrack. How do I get PP 2002 to include, embed or transfer the sound track I thought I had recorded. It appears as thought the sound icon is just a link to the file on my computer. Any help would be appreciated. I have received PP slideshows with the back ground music or a soundtrack and they have played on my computer without andy root sound file. Hi If you have a wav format sound file you w...

Prevent entry of duplicates
Hi all, I would like to prevent the entry of duplicate records in a table. Sounds simple enough, but here's the catch: 1. The duplicates I'm trying to avoid are a combination of 5 fields 2. This is database with a SQL backend I have tried to create a unique index based on these 5 fields; however, when I use the upsizing wizard, I receive an error and this index does not make it to the SQL server. I know that I should try to keep things like this at the lowest level possible (i.e. table), but was wondering if anyone had any suggestions about how to do this from the form. For...

how to prevent duplicate message in a PST for Two Different Computers?
I tried to synchronize my email through copying a PST from my desktop to my laptop. but when I opened the PST in outlook2003 on my laptop and connected to internet, outlook always downloaded all the message again even if PST has all these messages. so I have to delete all the duplicate message. it is a pain to do it manually everyday. I think one way is to delete the message on server once it is downloaded to local computer. but I can not do it since I need to keep some important messages on server. any comment is highly appreciated. John Kerry <fangchun@mchsi.com> wrote: &g...

Event 9320
After I transfer Offline Address List from Exchange 2k3 to Exchange 2k7, I got a warnning Event 9320 on Exchange 2k7. It seems the limitation is too low to some objects, it's easy to change OAL Generator to Medium on Exchange 2k3 by System Manager, but how can I do it on Exchange 2k7? Definitely, System Manager doesn't work on it, and I check Get-OfflineAddressBook, there is no setting about OAL Generator. Any suggestion? Thanks. -- Ray MCSE+Internet, MCDBA, MCP This is a multi-part message in MIME format. ------=_NextPart_000_0009_01C7655C.FCEA7950 Content-Type: text/plain...

Sharing Record
Hi, Our client has requested that for CEO, he/she should be able to see the records that have been assigned and owned by particular users. How to accomodate this request beside asking the users to log on using their username?? Thanks take a look at accounts for example you could create a report that gives you a list of users and when you choose a user you will get a report of the accounts they own. Or you could simply have a report that lists all reports and shows the owninguser etc. Its late so there may be a better way :-) but this is an easy solution of course if its a one of reques...

Sequence with schema designer
I designed a schema in VS C# 2005 Express and am wondering why it chooses to place a sequence tag around the elements of a table. And additionally, if I move the elements around within the table, the sequences in the *.xsd are not rearranged, but are left as is(in the order in which the elements were created in the table). Being a noob to xml, I'm just wondering: 1. Why is the sequence tag automatically added? 2. Why is it not updated if I change the ordering of elements in the designer? This is the xsd file: <?xml version="1.0" encoding="utf-8"?> <xs:schem...

Moving to last record in a subform.
Greetings, On my main form after the last combo box is populated I display my subform, and I want to move the last record. After searching the web I found the following code and tried it, Even though the subform is populated (subform is based on a query matching values on main form) it never prints the debug message. If i REMOVE the if statement i.e. not checking the number of records within the subform it throws an error "3021 : No current record" this happens once then the program can continue to run and display rows in the subform but still never moving to the last row. ...

how to synchronize OnReceive Event of CSocket object,
helloo ... I need to synchronize OnReceive Event of CSocket object, in my SMTPGateway Windows service... any sample/url..........on this... thanks in advance Arun Devadiga David... thanks for ur interest in solving my problem.... I will tell u the exact scenario.... void CClientSocket::OnReceive(int nErrorCode) { const TCHAR THIS_FUNCTION[] = _T( "OnReceive" ); START_ERROR_LOOKUP if( bInProcess ) { bIsOnReceive = true; return; } bInProcess = true; pSMTPKernel->ProcessPendingRead(this); bInProcess = false; ...

"Find an event" feature in the Windows Live Mail/Calendar
In the Windows Live mail it is quite simple to "Find a message" as we type something in the search box and enter. But, I do not see any search box with the Calendar. Are there any ways to "Find" something in the Windows Live Calendar? Looking forward to hear from someone. Thank you. ...