Go Back   Access World Forums > Microsoft Access Discussion > Forms

Thread Tools Rate Thread Display Modes
Old 04-14-2011, 07:13 AM   #1
Newly Registered User
Join Date: Jul 2010
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Abdulkawi is on a distinguished road
Exclamation Duplicate Specific Fields Only with Subform too

Hi there,

I hope you can help me, below is the structure of my form which has a subform.

Table: tbl_assets
Main Form: frm_assets
Field: AssetNo (Primary Key) - Manually Created
Field: Company
Field: Purchased_Date

Table: tbl_extensions
Sub-Form: frm_assets_subform
Field: AssetNo (Primary-Foreign Key)
Field: Password
I want to be able to duplicate the CURRENT record to a NEW record. I only want specific fields to be duplicated, whilst the rest will be left as default/blank.

I'd like the Company and Purchased Date to be duplicated - whilst the AssetNo (Primary Key - Manually Created) is not. AND I'd like the ExtName and Password to be duplicated in the subform too.

However, here is the tricky part (or at-least for me it is). The Subform with the ExtName and Password has a primary/foreign key which is AssetNo, so the subform record wont be created until the record is created in the main form.

Heres what I need done but I dont know how to do =/
  1. When the "Duplicate Record" (button) is clicked. It will take you to a new form that has a field "AssetNo" - from there you will enter the NEW AssetNo - since this is the primary key.
  2. Once "Continue" is clicked, It will then take you back to the main form and create the record on the AssetNo you just entered AND then it should automatically pull the specific fields data from the record that you was just on to the new asset you just created.
  3. The subform should also automatically use the AssetNo you created and duplicate the ExtName and Password field. But it should only place the AssetNo if any data exists in those two fields from the previous record.
I really dont know much VBA, and I tried doing this with a macro and failed miserably. I would really appreciate any help at all you can give me.

Thank you.

Abdulkawi is offline   Reply With Quote
Old 04-14-2011, 07:25 AM   #2
Surfing while working...
Join Date: Nov 2010
Posts: 1,012
Thanks: 4
Thanked 141 Times in 140 Posts
CBrighton is on a distinguished road
Re: Duplicate Specific Fields Only with Subform too

VBA is certainly the way I would do it, especially as this gives me full control over what is created and which order (I could ensure the main tabbe is populated before the sub so there is a corresponding AssetNo to link to).

Everything you want to do is completely possible.

Step 1 - You should be able to do this yourself, it's a form with a single textbox for the user to imput the new AssetNo and a command button to save / close.

Step 2 - I don't know your form or control names, so I will bold them all so you can easily see what needs replacing. This is some example code to create a new record in a table based on values from 2 forms:

dim db as dao.database
dim rst as dao.recordset
set db = currentdb
dim rst as db.openrecordset("SELECT * FROM MainTable")
rst!Company = forms!frm_assets!CompanyControlName
rst!PurchasedDate = forms!frm_assets!PurchasedDateControlName
rst!AssetNo = forms!NewForm!NewAssetNoControlName
Step 3 - Similar code will be used here, the only difference being the table loaded in the recordset, the fields which are in that table and the form you are refering to to get the data to duplicate.


See if you can work out the final part of code based on the earlier part.

If not I can post more. Better to learn it than be told it though.
Attention: Any advice I give is based on Access 2003 as that is what I use. Any changes to Access since then are unknown to me!

Please take any code I post as examples, not code to immediately copy & paste into your own. I take no responsibility for errors/issues caused by using my code without checking / modifying to suit your database.
CBrighton is offline   Reply With Quote

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy specific fields from recordset to excel specific columns teasugar Modules & VBA 13 06-08-2012 09:57 AM
import specific csv columns to specific fields in table shaunh1 Modules & VBA 1 07-06-2010 12:41 AM
Duplicate Specific Records - Please help Kenln Modules & VBA 1 10-24-2007 03:55 AM
[SOLVED] counting records on a subform that have a specific value in a subform field tolgainci Forms 2 12-09-2002 12:16 AM
How to duplicate a specific record skelley Forms 1 09-09-2000 07:11 PM

All times are GMT -8. The time now is 09:12 AM.

Microsoft Access Help
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post

Sponsored Links

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World