MS Access VBA Continuous Form OnLoad

Question

First, I'm a beginner in this area. I have a continuous form tied to a query that displays only active records pulled from Table1. Within the form is a textbox that i'd like to use to display information (if it exists) relevant to each active record shown in the continuous form. The query/form displays 15 records. If i add Table2 to query (Table2 is the 'Many' relationship to Table1) and set the relationship such that all records in Table1 are required along with the info in Table 2 (Table2 info is applicable to only 3 Table 1 records) my query/form now displays 18 records...

For example, there are 3 entries in Table 2 that are related to Table 1, Record 2. Current query containing both tables yields three consecutive Table 1, Record 2 entries and if i bind the text box the Table2 data, i have a single item in each text box.

What I'm trying to do is have a single entry for each Table1 record and populate the corresponding text box (or list box) with Table 2 Data.

My code (is ugly) makes a recordsetclone of the form (query) and loads data from Table2. Then two for loops...(not optimal) outer runs through the clone, inner goes through all Table2 records. If a match is found between the compared values, then i make a string from the actual Table2 data I want in the text box. Once inner loop done, if string > 0 then populate text box...

Right now, it will simply put the last string into every text box... Sample code below...be gentle...i know it's ugly

Private Sub Form_Load()
Dim db As Database
Dim rst1, rst2 As Recordset
Dim ftpString As String
Dim frmTemp As Form_sfrmTOLlist
Dim sfrmTOLlistcnt As Integer

Set db = CurrentDb
Set frmTemp = Me.Form
Set rst1 = Me.RecordsetClone
Set rst2 = db.OpenRecordset("tbl_jnc-Tol_ftp")

For x = 1 To .RecordCount
    For y = 1 To rst2.RecordCount
        If rst2.Fields(0) = .Fields(0) Then
            ftpString = ftpString & rst2.Fields(TOL_ID).Value & " "
        End If
        rst2.MoveNext
    Next y
    If Len(ftpString) > 0 Then
        [Text42].Value = ftpString
        Debug.Print (ftpString)
    End If
    ftpString = ""
    rst2.MoveFirst
    .MoveNext
Next x

End Sub

Answer 1

You could do this by concatenating the rows from Table2, separating each row with a carriage return/line feed. This is illustrated in a report in Concat.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

In this little demo file the option to 'concatenate values from a related table' opens such a report, using Northwind data as its example, in which products ordered by each customer are listed in a text box whose ControlSource property is an expression calling a GetList function which uses the highly efficient GetString property of an ADO recordset object to concatenate the values into a single string expression.

Exactly the same technique could be applied to a bound form in continuous forms view, as illustrated below, in which the same expression is used for the ControlSource property of the text box to the right of the form:

Answer 2

Thanks, but i was unable to access the .zip...checking settings but would it be possible to shoot me the code snippet?

Answer 3

This is the GetList function which concatenates the values:

Public Function GetList(strTable As String, strColumn As String, strSortColumn As String, strDelim As String, Optional strFilter As String = "True") As String

Const NOCURRENTRECORD = 3021  
Dim rst As ADODB.Recordset  
Dim strSQL As String  
Dim strList As String  
  
strSQL = "SELECT " & strColumn & " FROM " & strTable & " WHERE " & strFilter & " ORDER BY " & strSortColumn  
 
Set rst = New ADODB.Recordset  
  
With rst  
    Set .ActiveConnection = CurrentProject.Connection  
    .Open _  
        Source:=strSQL, _  
        CursorType:=adOpenForwardOnly, _  
        Options:=adCmdText  
      
    On Error Resume Next  
    strList = .GetString(adClipString, , strDelim, strDelim)  
    .Close  
    Select Case Err.Number  
        Case 0  
        ' no error so remove trailing delimiter  
        ' and return string  
        GetList = Left(strList, Len(strList) - Len(strDelim))  
        Case NOCURRENTRECORD  
        ' no rows in table so return  
        ' zero length string  
        Case Else  
        ' unknown error  
        GetList = "Error"  
    End Select  
End With  
  

End Function

This the opening page of the report which explains how it's called: