Copy prior record into the current record

[BobV]

Group:

I have an input form with fields that are bound to a table, and I want to
develop a VBA subroutine that will copy the contents of the prior record
(all fields) into the current record. Can anyone give me a hint as to the
VBA code that I will need in order to accomplish this? I have several books
on Access VBA programming, but I haven't found anything that talks about
duplicating the prior record into the current record. Would I use a query to
do this? Any help with a code sample will be greatly appreciated.

Thank you,
BobV

 

[Allen Browne]

The article:
Carry data over to new record
at:
http://members.iinet.net.au/~allenbrowne/ser-24.html
explains how to duplicate the values from the last record when you begin to
create a new one.

 

[BobV]

Allen:

Thank you for your help. This is what I need.

Thanks,
BobV

 

[BobV]

Allen:

The code in your article regarding carrying data over to a new record works
perfectly. However, after it is used, my application cannot exit and close
down Access. My application disappears, but a blank Access window remains
open and cannot be closed. I have to restart my computer. Any ideas as to
what could be causing this?

Thanks,
BobV

 

[Allen Browne]

What version of Access is this?

There was a problem like that back in the Access 97 days.
There were 2 possible causes:
1) Objects (particularly recordsets) that were not dereferenced;
2) Boolean controls (check box, option button, toggle button) referenced
like this:
If Me.MyCheckbox Then ...

If you have any boolean fields in the form, make sure your code includes the
line:
Set rst = Nothing

Try replacing the line:
ctl = rst(ctl.Name)
with:
ctl.Value = rst(ctl.Name)

 

[BobV]

Allen:

I made the change in the code to ctl.value=rst(ctl.name), but that did not
help. And I do have the line, "Set rst=Nothing", in my code. Before setting
rst to nothing, I also tried closing the recordset (rst.close). That didn't
help either.

I found that everything works fine if I only have the code copy the ComboBox
values -- I can then exit normally. But when I execute the code that copies
the TextBoxes, that's when my application has a problem closing. The form I
am working with is a multi-tabbed form, so maybe that is causing a problem.

It's wierd because the new record that I am inserting does carryover the
values from the last record, but after executing the code, my application
does not want to close down.

I am using Access XP. My operating system is Windows XP.

Thanks,
BobV

 

[Allen Browne]

Bob, let's make certain this is not a fixed bug or a corruption.

1. Make sure you have at least SP2 for Access 2002 (prefereably SP3), and
SP8 for JET 4. The Access SP shows up under Help | About. To get your JET
SP, locate the file msjet40.dll (typically in windows\system32), right-click
and look at the version. The minor number should start with 8, i.e.
4.0.8xxx.0.

2. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect

3. Decompile a copy of the database by entering something like this at the
command prompt while Access is not running. It is all one line, and include
the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Compact your database:
Tools | Database Utilities | Compact

5. Press Ctrl+G to open the code window, and compile:
Debug | Compile.

Is the problem still there? If so:
6. Create a new (blank) database:
File | New

7. In the new database, uncheck the boxes under:
Tools | Options | General | Name AutoCorrect

8. Import the just the objects you need to get this problem to show. That
might be just one table and one form. If the form is based on a query, you
may need that also, or if it calls other code, you may need that also.

9. Press Ctrl+G to open the code window, and choose minimal references:
Tools | References
More info:
http://members.iinet.net.au/~allenbrowne/ser-38.html

If the problem is reproducable in this database, email me.