Duplicate Specific Fields Only with Subform too (1 Viewer)

Abdulkawi

Registered User.
Local time
Today, 01:55
Joined
Jul 30, 2010
Messages
13
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:
ExtName
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.
 

CBrighton

Surfing while working...
Local time
Today, 08:55
Joined
Nov 9, 2010
Messages
1,012
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:

Code:
dim db as dao.database
dim rst as dao.recordset
set db = currentdb
dim rst as db.openrecordset("SELECT * FROM [B]MainTable[/B]")
 
rst.addnew
rst!Company = forms!frm_assets![B]CompanyControlName[/B]
rst!PurchasedDate = forms!frm_assets![B]PurchasedDateControlName[/B]
rst!AssetNo = forms![B]NewForm[/B]![B]NewAssetNoControlName[/B]
rst.update

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.

:edit:

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

Users who are viewing this thread

Top Bottom