Force Auto Add in Subform upon entering Main form 1

[niteraven]

Hi All

I have a main form that has a sub form on it. I want to have the user enter the main form and when they enter main form or sub form, automatically add 5 new records in the sub form with one of the fields filled out. Would it be better to add when entering the main form or wait till the user tabs to the sub form?

Basically I have to auto insert into sub form. I am wondering what would be the most efficient way to do this. Which way would be best for performance?


I was using validation to make the user go back and fill out any missing categories from the 5 mandatory categories upon saving, now the request is that it auto insert all 5 categories upon entering main form or sub form in a specific order every time.

I am hoping this can be done with an insert into statement on sub form gotfocus event?


Any help is greatly appreciated.
Thanks
Raven

 

[TheAceMan1]

How are ya niteraven . . .

You could append the five records to the underlying table of the subform then requery the subform. This is just the basic idea. [purple]How could you be sure none of these records would be orphaned?[/purple]

You'll also need a method to go to the 1st appended record of the group after the requery.

Post the form/subforms names and the [blue]recordsource[/blue] of the subform.

[blue]Your Thoughts? . . .[/blue]

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[dhookom]

I would probably use the after insert event of the main form to run an append query. The query would be based on a table with the unique "5 mandatory categories" and a reference to the "linking" field from the main form. The query would also rely on either a left join or a unique index on the target table to prevent duplicates.

Duane
Hook'D on Access
MS Access MVP

 

[niteraven]

TheAceman1:
Main Form name: frmECNOE
Sub Form Name: subformECNOE
sub form recordsource: SELECT ActionID, ECNNO, DeptsImpact, ActionItem, ImpactYN, DeptPerson
FROM tblActionItems;

DeptsImpact is the field that needs 9 Departments inserted.
ActionID is the primary key to the table tblActionItems (Sub form table) = autonumber
ECNNO is the foreign key from tblecn (Main form table)

Duane:
I have tried this and have not yet been successful in making it work. After update event in the last Main form field before the sub form.

Still working on making it work.

Thanks to you both for your prompt replies! It is greatly appreciated!

Raven

 

[dhookom]

niteraven,
What SQL would you use to select the 9 DeptsImpact values? I would expect something like "SELECT DeptID FROM tblDepartments WHERE ...."

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

[blue]DeptsImpact is the field that needs [purple]9 Departments inserted[/purple].[/blue]

So ... are we talking an insertion of 9 records now, instead of the origional 5?
And what are the the [purple]names of the departments?[/purple]

[blue]Would it be better to add when entering the main form or wait till the user tabs to the sub form?[/blue]

I think it would be better to wait until the user sets focus to the new record line of the subform. This at least shows intent. And to enforce the intent I would use the subforms [purple]DoubleClick[/purple] event and check that the new record reecordselector is being double clicked. This would like:

[blue]Private Sub Form_DblClick(Cancel As Integer)
   If Me.NewRecord Then Call [purple][b]AppendRoutineName[/b][/purple]
End Sub[/blue]

Another issue here could be sorting. If we append 9 records and the recordset of the subform is sorted, this could throw the added records out of sequence when finally viewed in the subform. [blue]You need to apprise us of any sort order![/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[TheAceMan1]

I see there's [purple]no sort order[/purple] in the subforms SQL.

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[TheAceMan1]

niteraven . . .

I intended on using an append query, but that would require [blue]requery of the subform[/blue] and take you to the top of the subform listing. As such we have no reference for setting the current record to one just appended and updated (we can't pull the pk out of the append query during run time). So I had to switch to recordset.

So ... copy/paste the following to the code module of the subform:

[blue]Public Function AppendDepts(Depts As String)
   Dim rst As DAO.Recordset, hld1stID As Long, Ary, x As Integer
   
   Set rst = Me.RecordsetClone
   Ary = Split(Depts, ";")
   
   For x = LBound(Ary) To UBound(Ary)
      rst.AddNew
      rst![purple][b]DeptsImpact[/b][/purple] = Ary(x)
      
      [green]'hold 1st PK appended[/green]
      If hld1stID = 0 Then hld1stID = rst![purple][b]ActionID[/b][/purple]
      rst.Update
   Next
   
   Set rst = Nothing
   
   Me.Requery
   [green]'goto 1st record appended[/green]
   Me.Recordset.FindFirst "[[purple][b]ActionID[/b][/purple]] = " & hld1stID
   
End Function[/blue]

Note: the Depts to append are passed to [blue]AppendDepts[/blue] are passed as a [blue]semicolon delimited string[/blue]. So you can append as many as you like. As an example of triggering and passing the depts ... in the [blue]On Dbl Click[/blue] event of the subform, copy/paste the following ([blue]you![/blue] substitute proper departments in [purple]purple[/purple]):

[blue]   If Me.NewRecord Then AppendDepts "[purple][b]Dept1[/b][/purple];[purple][b]Dept2[/b][/purple];[purple][b]Dept3[/b][/purple];" & _
                                    "[purple][b]Dept4[/b][/purple];[purple][b]Dept5[/b][/purple];[purple][b]Dept6[/b][/purple];" & _
                                    "[purple][b]Dept7[/b][/purple];[purple][b]Dept8[/b][/purple];[purple][b]Dept9[/b][/purple]"[/blue]

Don't forget ... to trigger double-click the new record line [purple]record selector![/purple] Give it a shot and let me know.

[blue]Your Thoughts? . . .[/blue]


See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[niteraven]

Hey Aceman - so sorry it took so long to reply back this code worked like a charm. here is the finalized code I used.

Code thanks to Aceman! Thanks!

Public Function AppendDepts(Depts As String)

    
   Dim rst As DAO.Recordset, hld1stID As Long, Ary, x, PsEcn As Integer
   
   Set rst = Me.RecordsetClone
   Ary = Split(Depts, ";")
   
   
   If IsNull(Forms!frmDesignChangeOE![DCNumber]) Then
        Dim ADL As String
    
        ADL = vbNewLine & vbNewLine
    
        msg = "INVALID: NO DESIGN CHANGE NUMBER HAS BEEN ENTERED!!" & ADL & _
            "YOU MUST ENTER AN DESIGN CHANGE NUMBER IN ORDER TO USE THIS FEATURE" & ADL & _
            "PLEASE ENTER AN DESIGN CHANGE NUMBER. . ."
            
        Style = vbInformation + vbOKOnly
        Title = "YOU MUST ENTER AN DESIGN CHANGE NUMBER!!!"
    
        MsgBox msg, Style, Title
    
        Set rst = Nothing
        Response = acDataErrContinue
        Forms!frmDesignChangeOE!DCNumber.SetFocus
        
        
        
   
   Else
     PsEcn = Forms!frmDesignChangeOE!DCID
   
    For x = LBound(Ary) To UBound(Ary)
      rst.AddNew
      rst!DeptsImpact = Ary(x)
      rst!DCID = PsEcn
      
      
      'hold 1st PK appended
      If hld1stID = 0 Then hld1stID = rst!ActionID
      rst.Update
     Next
   
    Set rst = Nothing
   
    Me.Requery
   'go to 1st record appended
    Me.Recordset.FindFirst "[ActionID] = " & hld1stID
   
   End If
   
End Function