Hi Every body

I have a query that outputs students names on certain criteria
I want to collect these names in a single textbox in a form(separated by space or dash)
the number of records varies each time.

what can I do to achieve that

Thanks very much for your help...

Dr. Mohammad El-Nesr
Irrigation Systems Design Specialist

Hi,

   You can do this by stacking the names in a text string. You would do it like this:

Put the code on the Form Detail On Format event
Create Connection
Create Recordset
Open Connection
Open Recordset on list of student names

If rs.Recordcount <> 0 Then
   rs.MoveFirst
   Do Until rs.EOF
     sNameList = sNameList & " - " & rs("NameField")
   rs.MoveNext
   Loop
End If

Me.Textbox = sNameList

Set your text box to Can Grow = Yes

Does that help?


mmcdonal

Dear mmcdonal
Thank you for your help,
It surely seems to be helpful but unfortunately I did not understand the
first four lines in your code,

Create Connection
Create Recordset
Open Connection
Open Recordset on list of student names

I know that these lines reflect some abbreviations to some code
but how can I write it down,

can you,kindly, explain it through a Northwind example, or any possible example
thank you very much for your helps



Dr. Mohammad El-Nesr
Irrigation Systems Design Specialist

Here is code that I use on the Form's On Current Event - (not the detail section's On Format event) - to do what you want:

'Declare variables
Dim sSQL, sRisk As String
Dim iControlActID, iLen As Integer
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

'Check PK, if exists, take it for SQL String
If IsNull(Me.ControlActID) Or Me.ControlActID = "" Then
    Exit Sub
    Else
    iControlActID = Me.ControlActID
'================================================= ======
'Risk text box 113
'-------------------------------------------------------
    'This is my query to get the list of items to concatenate
    sSQL = "SELECT * FROM tblrisk WHERE [ControlActID] = " & iControlActID

    'Open Connection to SQL Server
    Set cn = New ADODB.Connection
    With cn
        .ConnectionString = CurrentProject.Connection
        .CursorLocation = adUseClient
    End With
    cn.Open "Provider=SQLOLEDB;Data Source=SAW2K3DB01;Initial Catalog=icmt;UID=" & sUID & ";PWD=" & sPWD

    'Open recordset on table where I am getting the
    'string values to concatenate
    Set rs = New ADODB.Recordset
    rs.CursorType = adOpenDynamic
    rs.LockType = adLockOptimistic
    rs.Open sSQL, cn

    'Check for values. If yes, then loop through...
    If rs.RecordCount <> 0 Then
        rs.MoveFirst
        Do Until rs.EOF
            'Stack up list in one string
            sRisk = sRisk & ", " & rs("Risk")
        rs.MoveNext
        Loop
    End If
    'If the list is longer than "" then remove the ", "
    'from the beginning of the string
    If sRisk <> "" Then
        iLen = Len(sRisk) - 2
        sRisk = Right(sRisk, iLen)
    End If
    'Put the value in the text bax
    Me.Text113 = sRisk
    'Close the recordset
    rs.Close
    'reset the string length since I do this with 4 text boxes
    'on the same form
    iLen = 0
'
'

You will need to change the connection to the local database, or whereever your dbms is, and your SQL statement, of course.

Does this help? As you can see, I am using the same technique you are attempting.



mmcdonal

Dear Mcdonald
Thank you for your detailed answer and speedy reply

One more question,
Is it necessary to declare these variables while using the code in the MS-Access VBA? Isn't the database connection is open by default?
If yes.. please why?
I thought in first impression that this code is written to VB.net or VB6 not for the direct VBA language..
Best regards, thank you for your care.
Dr. M. B. El-Nesr

Dr. Mohammad El-Nesr
Irrigation Systems Design Specialist

Hi,

 I use ADO and work with databases in SQL mostly. So many of my applications are not connected to the DBMS, like you would find in Access .mdb databases.

   If you are using Access for your tables, then you can even use DAO instead of ADO to connect. In that case, some declarations do not have to be made, as you suggest. I do not use DAO much, but if you are interested, you can open VBA, then do a search for DAO Connection, and DAO Recordset.

HTH


mmcdonal