Duplicate Specific Fields Only with Subform too

[Abdulkawi]

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.

[timmy]

Not attempting to answer your question here, i'm sure someone else will, but i just wanted to point out a couple of things:

Field: AssetNo (Primary Key) - Manually Created

Manually setting a primary key is a bit risky imo, im sure you have your reasons but i'de suggest having a primary key AND an assetNo that you assign yourself. Primary keys are for relational use within the DB and not for the end user.

I would suggest this layout:

Table: tbl_assets
Main Form: frm_assets
Field: AssetID (PK)
Field: AssetNo
Field: Company
Field: Purchased_Date

Table: tbl_extensions
Sub-Form: frm_assets_subform
Field: ExtensionID (PK)
Field: AssetID (FK)
Field: ExtName
Field: Password

On the frm_assets_subform you would grab the asset information using the foreign key, rather than the assetNo.

Also reading further into your post what i have suggested above would make solving your questions easier. I don't have time to answer in more detail. I'm sure one of the experts here could help you better anyway .