Subform Headache!

[zqi]

Hi,

What i am trying to do is allow users to process multiple records in a subform at one time. Is that possible to highlight(select) any multiple records in a subform? if so, how can i retrieve one by one.

I try dim the subform as a control ctlfrmSub, but ctlfrmSub.itemselected doesn't works. Is there any way to go through each selected record in a subform, when the user highlight all records?

You suggestions are appreciated!

 

[RichD]

You could add a checkbox (Yes/No) field to the subform's underlying table. Make the default false.

The user then highlights the records by checking the appropriate box.

An update query could clear all the check boxes when your procedure is finished.

HTH
Rich

Lead Developer

http://www.blsoftware.co.uk

 

[VBslammer]

You can get at the number of selected items in datasheet view, the only problem is when you try to click a button or move the focus off the subform, the records are de-selected except for the last one that had focus.

You can, however, use a popup menu or a toolbar to call a macro, which will leave the focus on the subform.

See if you can play with this and achieve what you want:

Function ShowSelected()
On Error GoTo ErrHandler

  Dim lngNumRows As Long
  Dim lngNumColumns As Long
  Dim lngTopRow As Long
  Dim lngLeftColumn As Long
  Dim strForm As String
  Dim strMsg As String
  Dim frm As Form
  Dim ctl As Control
  
  Set frm = Screen.ActiveForm
  
  On Error Resume Next
  For Each ctl In frm.Controls
    If TypeOf ctl Is SubForm Then
      Set frm = ctl.Form
    End If
  Next
  
  On Error GoTo ErrHandler
  
  If frm.CurrentView = 2 Then               'Form is in Datasheet view.
    lngNumRows = frm.SelHeight              'Number of rows selected.
    lngNumColumns = frm.SelWidth            'Number of columns selected.
    lngTopRow = frm.SelTop                  'Topmost row selected.
    lngLeftColumn = frm.SelLeft             'Leftmost column selected.
                                            
    strForm = frm.Name
    strMsg = "Form: " & strForm & vbCrLf
    strMsg = strMsg & "Number of rows: " & lngNumRows & vbCrLf
    strMsg = strMsg & "Number of columns: " & lngNumColumns & vbCrLf
    strMsg = strMsg & "Top row: " & lngTopRow & vbCrLf
    strMsg = strMsg & "Left column: " & lngLeftColumn
    
    MsgBox strMsg
    
  End If

ExitHere:
  Exit Function
ErrHandler:
  Debug.Print "Error: " & Err & " - " & Err.Description
  Resume ExitHere
End Function

VBSlammer

Unemployed in Houston, Texas

 

[zqi]

thanks Rich and VB.

I finally create a Local table with a Yes/No field to store the datasheet data, and manually process after user finishes.

Zhong