CODEWITHSUNDEEP
vbams-accessrecordset
Richard D
Richard D

Reputation: 142

Microsoft Access VBA add data from form to tables unbound form

How can I use the data generated by the code and the user input to add the records to the deploymentStep table and to the deploymentPlan table?

I have table like the following

PillarTeamDeploymentSteps

pillarTeam pillarTeamStep other info
team 1 step 1
team 1 step 2
team 1 step 3
team 1 step 4
team 2 step 1
team 2 step 2
team 2 step 3
team 2 step 4
team 2 step 5
team 2 step 6

And tables with the structure

deploymentStep

pillarTeam pillarTeamStep deployment endDate

deploymentPlan

pillarTeam deployment startDate endDate

Using a form I would like users to be able to select their team and have it autopopulate text fields with the corresponding pillarTeamSteps. This can be seen in the screenshots. enter image description here

enter image description here

After the user has selected a team and the steps have autopopulated they would manually input data in the date boxes. From here i'd like a button press to add the records. I am using the following code to update the forms visibile fields and values. There will be only a max steps of ten per team.

How can I use the data generated by the code and the user input to add the records to the deploymentStep table and to the deploymentPlan table?

Private Sub pillarTeam_AfterUpdate()
Dim db As DAO.Database
Dim RS As DAO.Recordset
Dim deploymentSteps As DAO.Recordset

Set db = CurrentDb

strSQL = "SELECT PillarTeamDeploymentSteps.pillarTeam, PillarTeamDeploymentSteps.pillarTeamStep, PillarTeamDeploymentSteps.deploymentType FROM PillarTeamDeploymentSteps WHERE (((PillarTeamDeploymentSteps.pillarTeam)=" & Me.pillarTeam & "))"

Debug.Print (strSQL)

Set RS = db.OpenRecordset(strSQL)
If Not (RS.EOF) Then
    RS.MoveLast
    RS.MoveFirst
End If



'setting visible controls
Dim ctl As Control
For Each ctl In Me.Controls
    If ctl.Tag < RS.RecordCount + 1 Or (ctl.Tag < RS.RecordCount + 21 And ctl.Tag >= 20) Then
        ctl.Visible = True
    Else
        ctl.Visible = False
    End If
Next ctl


With RS
    If Not .BOF And Not .EOF Then
    .MoveLast
    .MoveFirst
    While (Not .EOF)
        Debug.Print (RS.AbsolutePosition)
'populating deployment steps
        For Each ctl In Me.Controls
            If ctl.Tag = RS.AbsolutePosition Then
                ctl.Value = RS.Fields("pillarTeamStep")
            End If
            
         Next ctl
          
        .MoveNext
    Wend
        
        
    End If
    .Close
End With
    

    Set RS = Nothing
    Set db = Nothing

Upvotes: 1

Views: 1007

Answers (1)

June7
June7

Reputation: 21370

Suggest binding form to deploymentPlan and have a subform bound to deploymentStep.

To 'batch' create records, use an INSERT SELECT action SQL.

CurrentDb.Execute "INSERT INTO deploymentStep(pillarTeam, pillarTeamStep, deployment) " & _
                   "SELECT " & Me.tbxTeam & " AS T, pillarTeamStep, " & Me.tbxDeploymentID & " AS D " & _
                   "FROM PillarTeamDeploymentSteps " & _
                   "WHERE pillarTeam=" & Me.tbxTeam

This assumes pillarTeam and deployment are numeric identifiers such as Autonumber type. If not, then use apostrophe delimiters.

Trick here is first saving 'parent' deploymentPlan record and grabbing that record identifier to save as foreign key in deploymentStep.

Upvotes: 1

Related Questions