Record Copying Woes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Record Copying Woes

Author

Topic

CPEPhantom
Starting Member

6 Posts

Posted - 2004-06-15 : 23:45:53

Greetings,

I am having a few issues (read problems) with my access 97 database. The database is designed to track purchase requests. The users enter the data using a form as an interface. My problems are these; I need to be able to copy the purchase requests since there are a lot of periodic duplicate orders. however the copying code only works if the .dbm has been opened using the pulldown menu (file->open). this is annoying. Also, even if the file is opened this way, the copied records do not show up unless the form is restarted. This happens even though the form is refreshed in the VB code. any help would be greatly appreciated.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-16 : 00:10:21

I'm confused:

You mention that you're trying to copy a newly created record - and if so why? Where is the code you're trying to run located?
You also mention that the code fails to run after opening what I assume to be the database. This makes even less sense.

Can you clarify exactly what you're trying to do?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-06-16 : 04:47:46

Sounds to me like you have all your code on either the wrong events or on only one of two events...

Sounds like the "pull down menu" is something created by your app... You may need to fire off the code behind this event if you open the form elsewhere...

Without more information, no-one here can tell you exactly what to do...

CPEPhantom
Starting Member

6 Posts

Posted - 2004-06-16 : 14:41:51

quote:
Originally posted by timmy

I'm confused:

You mention that you're trying to copy a newly created record - and if so why? Where is the code you're trying to run located?
You also mention that the code fails to run after opening what I assume to be the database. This makes even less sense.

Can you clarify exactly what you're trying to do?

Okay, here goes nothing....
I am not trying to copy an newly created record specifically, any record in the database should be copyable once it has been created. This database was put together because my boss got tired of flipping through stacks of old purchase requests to find an old order that he wanted to make again.

On the opening issue; if I open the database by double clicking on the icon or from the recently used file list in access I get the error "cannot find file 'abcxx.dbm'" when I try to use the copy button I put on the form. I don't get this error if I open access and then go file->open to open the file. I suppose I could open the file explicitly in the code(full path, etc.) but I would really like to avoid that.

Code location: The code is located in the form's "copy record" button. It should be noted that the database is comprised of 2 tables linked by a request number. That was the easiest way to have multiple items per request. The code itself is fired of when the button is clicked.

The code itself does the following: copies the purchase request info (date, vendor, etc.) into a new record in the first table. It then copies the ordered items into new records in the second table using the new purchase request number from the first table so that they remain properly linked. It then makes the form do a requery (supposedly) so that the new records will be immediately visible to the user who can then adjust the purchase request however it needs to be adjusted.

To summarize, the things I need help with are (a) how can I fix the opening file problem, (b) how can I make the form requery the tables so that I can see the new records that are there (they are there because if I do another copy, the previously copied records show up. Go figure.).

I hope this helps clarify the problem.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-16 : 19:16:59

First problem:
The recently opened file list is (I think) stored in the registry, probably in the HKCU branch. Check the full path, as it may be referring to an unknown path (e.g. if you had a share drive mapped as a different letter).
Second Problem:
When you copy the record contents, find out what the PK of the new record is (although this may depend on exactly how you're copying the records). Once you know this, you can simply change the form's RecordSource to "SELECT fields FROM orders WHERE keyfield = newPK"

CPEPhantom
Starting Member

6 Posts

Posted - 2004-06-16 : 20:46:43

quote:
Originally posted by timmy

First problem:
The recently opened file list is (I think) stored in the registry, probably in the HKCU branch. Check the full path, as it may be referring to an unknown path (e.g. if you had a share drive mapped as a different letter).
Second Problem:
When you copy the record contents, find out what the PK of the new record is (although this may depend on exactly how you're copying the records). Once you know this, you can simply change the form's RecordSource to "SELECT fields FROM orders WHERE keyfield = newPK"

Thanks for the info. Could you elaborate on the solutions you suggested? I am a bit of a newbie as far as access goes and am kind of feeling my way through this thing.
Thanks a bunch.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-16 : 22:05:49

As for copying the records, I would do it using a recordset:

On the P/O form, create a OrderCopy function that:
Creates a new recordset from the orders table
Creates a new record in the recordset and populates with values from the form
Update the recordset, which would give you the ID of the newly created record
Create another recordset on the order items.
Create new records on this, using the new p/o ID and the items from the sub-form

Once this is done, you can set the form recordsource as "SELECT * FROM orders WHERE idfield = newID"

HTH,

Tim

ps - I think Access has a 'copy record' button in the button wizards. You might be able to adapt this

CPEPhantom
Starting Member

6 Posts

Posted - 2004-06-21 : 11:02:36

quote:
Originally posted by timmy

As for copying the records, I would do it using a recordset:

On the P/O form, create a OrderCopy function that:
Creates a new recordset from the orders table
Creates a new record in the recordset and populates with values from the form
Update the recordset, which would give you the ID of the newly created record
Create another recordset on the order items.
Create new records on this, using the new p/o ID and the items from the sub-form

Once this is done, you can set the form recordsource as "SELECT * FROM orders WHERE idfield = newID"

HTH,

Tim

ps - I think Access has a 'copy record' button in the button wizards. You might be able to adapt this

Thanks for the Clarification. How would you create the recordset? Do I have to open the current file in the code or is it implicit that I want to use the file that the form is based on? In my code, I open the file and then create the recordsets. This may be the problem. Also, Yes, Access has a copy function. That was the first thing I tried but it didn't work in a way that was adaptable to what I want to do.

Thanks for the help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources