MS Access VBA Continuous Form OnLoad

hwkifan 1 Reputation point
2022-05-04T15:11:00.123+00:00

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

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
852 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Ken Sheridan 2,756 Reputation points
    2022-05-05T21:42:19.757+00:00

    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:

    199379-image.png

    0 comments No comments

  2. hwkifan 1 Reputation point
    2022-05-06T20:56:23.917+00:00

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

    0 comments No comments

  3. Ken Sheridan 2,756 Reputation points
    2022-05-06T23:09:43.147+00:00

    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:

    199795-image.png

    0 comments No comments