Create new record with current record field value

Question

I want to create a button that will create a new record for data-entry. However, one of the fields in the new record will take a value from a field of the current record. My attempt --

Option Compare Database

Public FormID As Integer
Private Sub Form_Open(Cancel As Integer)
    DoCmd.GoToRecord , , acNewRec
    FormID = Me.OpenArgs
    RespondentID = FormID
End Sub
Private Sub NextCondition_Click()
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.GoToRecord , , acNewRec
    If Me.NewRecord Then
        Me.RespondentID = FormID
    End If
End Sub

EDIT - CONTEXT: There are two relevant forms - Respondent and MedicalConditions. Since each respondent should only have 1 entry in the respondent table, I created a new table MedicalConditions that allows for multiple entrys per respondent, as a respondent can have multiple MedicalConditions. We get to the MedicalConditions form by clicking a button on the Respondent form. So, our RespondentID is already known. In fact, this is passed via OpenArgs, and, when medicalConditions form is opened, OpenArgs is saved into FormID which has public scope. Since we are only entering MedicalConditions for a specific Respondent, this should be reflected in the table by having the same RespondentID for each Medical Condition entered in this session. Thus, when the enterer clicks "Enter Another Condition", I would like to create a new record so that he/she can fill in MedicalCondition, however, since it is for the same Respondent, I would like RespondentID to be carried over into that new record. In my current implementation, I am able to pass RespondentID into the form using OpenArgs so that the first entry has the correct RespondentID. However, my problem is -- when I go to add another MedicalCondition, the RespondentID is 0 (rather than the prior value).

Is FormID in .openargs or have you taken it to the variable, not sure what you are asking? — Nathan_Sav
@Nathan_Sav I've edited my OP to contain more context to (hopefully) answer your question. — byrnesj1
It would help if you'd TAG this with the application you're using. I assume it should be ms-access, but... — Cindy Meister
@CindyMeister the application I'm using is Access, but the question isn't specific to Access — byrnesj1

jacouh jacouh · Accepted Answer · 2020-03-23T21:00:06

In your place, I would try this, by assigning a default value for RespondentID via Me.RespondentID.DefaultValue = CLng(Me.OpenArgs) in Form_Open(), as this:

'Public FormID As Integer
Private Sub Form_Open(Cancel As Integer)
    Me.RespondentID.DefaultValue = CLng(Me.OpenArgs)
    DoCmd.GoToRecord , , acNewRec
    'FormID = Me.OpenArgs
End Sub
Private Sub NextCondition_Click()
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.GoToRecord , , acNewRec
    '
    'If Me.NewRecord Then
    '    Me.RespondentID = FormID
    'End If
    '
End Sub

By the way, all NewRecords will have a RespondentID.value automatically assigned by Access VBA, with the same .Value = .DefaultValue. The public var FormID is now useless, so commented out. CLng(Me.OpenArgs) converts a string into Long.

Create new record with current record field value

1 Answers