How to copy records for a Continuous form input.

[Jeroen3131]

Hello,

I'm developing a database where workinstruction s (which consist of Steps) can be created and edited. I'v already completed the "Create" form and it works flawless. Now I'm stuck on the Edit part. The user must be able to select a Step and the data from the already created Step must appear on the Edit form. When the user is finished with editting, the data must be saved in a new record. The old data may not be overwritten because the old data always has to be accessible.

I have a bound form (EditStep) with:

Form: EditStep
RecordSource: tblSteps

Textbox: txtStepName
CtrlSource: StepName

Textbox: subStepBox
CtrlSource: Unbound

Button: cmdEditStep

Button: cmdSelectStep3
(If this button is pressed, the dialog form "SelectStep " will be opened.)

Form: SelectStep
Record Source: Unbound

This Form (SelectStep) has also a button "Select Step" and a List (selectionList) . After the user selects a list item and presses the Select Step button, the StepID value from the list is set to the textbox "subStepBox ".

Button: cmdSelectStep

List: selectionList
Row Source: SELECT tblSteps.StepID FROM tblSteps

The main form also has a continuous form:

Continuous subform: subForm1
Link Master Field: subStepBox
Link Child Field: StepID

The subform (subForm1) consists of two Controls:

Textbox: Qty
CtrlSource: Qty

Combobox: cboPart
Control Source: PartID
RowSource: SELECT tblParts.partID , tblParts.PartDe scription FROM tblParts ORDER BY tblParts.PartDe scription

In my database the following tables exist:

Table: tblParts
PartID (Primary Key)
PartDescription (text)

Table: tblSteps
StepID (Primary Key)
StepName (text)

Table: tblStepParts
StepPartID (Primary Key)
StepID (number)
PartID (number)
Qty (number)

This is my working code for setting the EditStep form to the selected record:

Private Sub cmdSelectStep_Click()

    On Error Resume Next
    Dim rst As Object
    Set rst = Forms!EditStep.Form.RecordsetClone
    rst.FindFirst "StepID = " & Me.selectionList.Value
    Forms!EditStep.Form.Bookmark = rst.Bookmark
	
	DoCmd.Close
	
	RunCommand acCmdRecordsGoToNew
	
End Sub

The code for the Edit Step button is simple:

    On Error Resume Next
    If (Form.Dirty) Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    DoCmd.GoToRecord , "", acNewRec
    MsgBox "Step created Succesfully!", vbInformation, ""

If the User selects a step the data is correctly shown in the Controls on the forms. If the user edits something on the mainform (EditStep) for instance, the StepName, this edit is saved succesfully under a new record with a new StepID. However, if the User edits something on the continious subform (Qty or PartID) and then clicks the Edit Step button, the data Qty and/or PartID from the selected (original) step is adjusted. These data isn't saved to a new record.

I need something to duplicate the values in tblStepParts to new records in the same table but with the StepID adjusted to the current record of the main form.

I hope someone can chime in and provide some help.

Kind regards,

Jeroen

[twinnyfo]

Jeroen,

Perhaps, when they select the Step to Edit, before they have a chance to change anything, copy that record, go to a new record and then paste that record into the new record. If, after they have reviewed or made changes, those changes will automatically be saved in that new record. You could also include a "Discard Changes" button, which would delete the record just created.

About the only other option would be to use unbound forms/controls, which would be populated programmaticall y, then, any changes would be inserted after the fact to a new record.

Just a couple options. It just depends on how often a person edits the steps or discards the changes they make.

[Jeroen3131]

Hi Twinnyfo,

Changes will be made freqeuntly so I want to automatically copy the selected records and add the values to new records.

Your idea of copying the values to a new record before they can make any changes is good but I dont know how to exactly do this with VBA.

I'm trying to use SQL INSERT INTO but the SELECT statement can only accept Fieldnames or a specific value if you type it in between "".

Also the Discard button was the next item on my to do list, added it by using the standard macro "Undo Record" and it seems to work.

[TheSmileyCoder]

Hi Jeroen
If you look at the sample I have posted here:

http://thesmileycoder.com/highlightchanges/

After download, select the "Open Test Form" option, and that will display the method of copying the entire "old" record to a history table, right before changes are saved. Thats the method I use most frequently.

If you still need the old step to be available in the table, and not just recorded for historic purposes, another possibility could be to open the same form twice. Imagine your "display" form as the main form, and when you hit edit, it will open another instance of the form, in Add record mode, and set each controls defaultvalue equal to the current value of the record you started from. It would look and feel like a edit, but it is in fact a new record.

[jforbes]

Hey Jeroen,

Hopefully I'm not getting too far off topic for you, I'm having trouble understanding the big picture for this project. There are a few ways to make the mechanics you need to work and Twinnyfo and TheSmileyCoder have got you pointed in a good direction, but again, I'm getting hung up on the purpose of copying all these records. If you are building a Template for an Order Workflow that your users can select from to start a new Order (with work instructions ) and then add Parts and possibly Labor against, then I think you are on the right track of clicking a button to copy the Template and then either committing it or rolling back as the user sees fit.

But if you are looking to create a history for each WorkInstruction , by copying it and it's linked parts, then you might want to re-think this a little bit before going on, as there are better ways of doing this. Ways to create better traceability and better normalization and with less data that will cause you less trouble further on in your project.

I'm just curios of what your overall goal is with this project.

[Jeroen3131]

Hey Jforbes,

I'll create a new Topic to discuss the database layout of my Project. I will link it in this topic so others can see it too.

TheSmileyCoder, thanks for the example database. It has usefull parts but I need to study it (I'm new to DAO databases) and i need to figure out how to adapt it to my database. But first I will create a new topic to elaborate the main goal of my project.

[Jeroen3131]

Here's the link to the thread which explains the purpose of my database: http://bytes.com/topic/access/answer...ns#post3783547