Only Copy certain fields of a record and other fields from a form into a new record.

[Rujahin]

I have a form (frmEditForm) with 2 subforms (frmMarkMaintenanceSubform, and frmInspectionHistory).

The main form has location information for traffic marks on the roadway. It also has 4 text boxes called txtMaintDate, txtBy, cmbMaterial and txtInspector.

The frmMarkMaintenanceSubform has the maintenance history for each record of the main form. The fields here are: LocationID, MaintInstallDate, InstalledBy, cmbMaterialID, Marking, Type and cmbMarkTypeID.

The frmInspectionHistory has the fields: locationID, txtInspectionDate, txtInspector and txtCondition. This records the history of Inspections this particular mark has had.

So here’s my issue: When I get to a record in the main form that has recently had maintenance performed on it, I press (alt+g) which sets the focus to the first record of frmMarkMaintenanceSubform. Then I want it to copy LocationID, Marking, Type and cmbMarkTypeID from the first record of the subform and txtMaintDate, txtBy and cmbMaterial from the text boxes on the main form, then paste it all into a new record on the subform.

Then send the focus over to frmInspectionHistory, go to a new record and paste the txtMaintDate and txtInspector from the main form into txtInspectionDate and txtInspector respectively. Then fill in txtCondition with “100”.



Lastly, send the focus back to the record on the main form. Oh, did I mention I need it all coded to a button?

I’ve gotten various parts of this to work at different times using variations of paste append and duplicate event procedures but never all of it at once. Any help would be greatly appreciated.

[June7]

Sounds suspiciously like duplication of data. Why would LocationID, MaintDate, By, Material be in both the main table/form and the dependent table/form?

Was the first subform record just entered during the current session since form was opened? Are main form and subform synchronized by Master/Child links properties?

[Rujahin]

main form and sub are linked by LocationID. MaintDate, By, and Material are only stored in the subform. They are only text boxes on the main form that I fill in as I go (they will stay the same for a couple dozen records, then one or all of them will need to be changed). The first subform record is just the most recent maintenance record available, could be anywhere from 1-15 years old.

[June7]

The LocationID will automatically save if Master/Child links properties of the subform container control are set to the PK/FK LocationID fields.

You want to use unbound textboxes on main form for entry of MaintDate, By, Material into new records of subform? This requires VBA code in some event to populate the fields. The trick is figuring out what event. Try the Current event of each of the subforms.

If IsNull(Me.MaintDate) Then Me.MaintDate = Forms!frmEditForm!txtMaintDate

[Rujahin]

I'm trying to code it to a button on the subform. I think your code will work for the text boxes, but I need to copy Marking, Type, and MarkTypeID from the first record of the Subform into to the new record also.

[June7]

So if there are already several related records when the form opens you want the values from the most recent? That's even trickier, especially if this is a multi-user db. Why do you want these same values? Under what circumstances will they change?

Review http://allenbrowne.com/ser-24.html

[Rujahin]

Technically this will be a multi-user database, although rarely will anyone else be using it. The values in those 3 fields will almost never change for each individual mark, but if they do I have to be able to record that. Each of the 15,000 marks is assigned to one of 55 different mark types (eg. Marking: Stopbar, Type: Standard, MarkTypeID: 0 or Marking: Crosswalk, Type: Blocked, MarkTypeID: 5). For some marks the values will never change, but others like crosswalks will all be changed at some point.

[June7]

Why save the MarkTypeID and the descriptive Type? Isn't the MarkTypeID the ID for the Type?

I am a little lost on the data structure. If you want to replicate all this data for each new record in subform, what makes the record unique?

If you want to provide db for analysis, follow instructions at bottom of my post.

[Rujahin]

Here ya go. Keeping those values in the subform just makes it easier for everyone.

[June7]

Displaying related data in subform is not the same as saving into record. There are techniques to display related info without saving to table. If you save the MarkTypeID there is no reason to save the descriptive Type, and vice versa. Having now seen your db I do see that you are not saving both ID and descriptor into MarkMaintenance.

You should set textboxes in subform as Locked Yes and TabStop No: LocationID, type, marking.

The copy/paste code is causing duplication of unique index. Don't use it. Just move to new record then populate fields.

Dim intID As Integer
intID = Me.MarkTypeID
DoCmd.RunCommand acCmdRecordsGoToNew
Me.InstalledBy = Forms!frmeditform!cmbBy
Me.cmbmaterialID = Forms!frmeditform!cmbDefaultMaterial
Me.MaintInstallDate = Forms!frmeditform!MaintDate
Me.MarkTypeID = intID
With Forms!frmeditform!frmInspectionHistory
    .SetFocus
    DoCmd.GoToRecord , , acNewRec
    !InspectionDate = Forms!frmeditform!MaintDate
    !Inspector = Forms!frmeditform!txtInspector
    !Condition = 100
End With

[Rujahin]

You ROCK!
Works Fantastic.
Thanks SOOOO much.