MS Access VBA Continuous Form OnLoad

Question

question

hwkifan-0709 asked May 4, '22 KenSheridan-7466 answered May 6, '22

MS Access VBA Continuous Form OnLoad

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

office-vba-dev office-access-dev

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TvanStiphout · May 04 at 10:59 PM

Right now, it will simply put the last string into every text box

That is because the control is unbound.

0 ·

Answer 1 · 2022-05-05T21:42:19.757Z

KenSheridan-7466 answered May 5, '22

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:

image.png (33.1 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2022-05-06T20:56:23.917Z

hwkifan-0709 answered May 6, '22

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

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 3 · 2022-05-06T23:09:43.147Z

KenSheridan-7466 answered May 6, '22

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:

image.png (47.1 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question