Rsults of a query to a textbox

[Moss100]

Hello,

I have a continuous form called CONFIRM which has a textbox called NUMBERS (as is the control source of the text box)

The form usually contains around 20 or so records.

I have a textbox in the footer of the form call ALL_NUMBERS

I would like the ALL_NUMBERS textbox to contain all the NUMBERS from the form, with each being seperated by a semi colon


To be clear the ALL_NUMBERS textbox would display something like 07711545454; 078815487899; 07896465214 etc...

Many thanks for anyone who can help me with this.

Regards


Mark

 

[Moss100]

help!!

 

[MajP]

Public Function ConcatRecords(frmName As String, fieldName As String, Optional delimeter As String = "") As String
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Set rs = Forms(frmName).RecordsetClone
  Set fld = rs.Fields(fieldName)
  rs.MoveFirst
  Do While Not rs.EOF
    If ConcatRecords = "" Then
      ConcatRecords = fld.Value
    Else
      ConcatRecords = ConcatRecords & delimeter & " " & fld.Value
    End If
    rs.MoveNext
  Loop
End Function

from the control ALL_NUMBERS
control source: = concateRecords("Confrm","Numbers")

 

[Moss100]

Thank you very much for your help - unfortunatley i have not got it working.

I have created a module called concateRecords and pasted your code into this.

I have then pasted into my textbox ALL_NUMBERS control source: = concateRecords("Confrm","Numbers")

The text box ALL_NUMBERS just displays #Name? when I open the form.

Any ideas?

Thanks Mark

 

[Moss100]

I have re looked and it was just a typo - the code works a treat.

Many thanks Mark


PS is there an easy way to add a ";" after each record except for the last record?

Thanks again

 

[Andrzejek]

One of the parameters you pass to the Function is: delimeter.
So when you call this Function, you may want to pass ";"

Have fun.

---- Andy

 

[MajP]

as Andy stated, it would look like
control source: = concateRecords("Confrm","Numbers",";")
or whatever character you want to pass in

 

[Moss100]

Thank you very much -

Is it possible to further modify it so that should there be a record without a number in, it skips this and doesn't insert a ';' - otherwise I might get a number of ;'s together.

Really, many thanks

Mark

 

[MajP]

Public Function ConcatRecords(frmName As String, fieldName As String, Optional delimeter As String = "") As String
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Set rs = Forms(frmName).RecordsetClone
  Set fld = rs.Fields(fieldName)
  rs.MoveFirst
  Do While Not rs.EOF
    if not trim(fld.value & " ") = "" then 'Verify that the record is not null or empty
      If ConcatRecords = "" Then
        ConcatRecords = fld.Value
      Else
        ConcatRecords = ConcatRecords & delimeter & " " & fld.Value
      End If
    end if
    rs.MoveNext
  Loop
End Function

also you may need to add this to the form to ensure you screen updates.

Private Sub Form_Current()
Me.Recalc
End Sub

 

[Moss100]

Thank you very much.

My project is taking great shape.

 

[MajP]

I have created a module called concateRecords and pasted your code into this

FYI you cannot name a module and have functions within the module with the same name. Nor can you name a module the same as the database. The code will not compile because it does not know which object you are referring to. Normally I name my modules with a "mdl" prefix. So I could have mdlConcatRecords and then the function ConcatRecords without conflict. You can unfortunately have a form, report, table, query, control, field all with the same name without conflict. Also it is pretty standard to name access objects with three/four letter prefixes. It makes debugging and readibility a lot easier and avoids any possible conflicts.
frmSomeForm
qrySomeQuery
tblSomeTable
txtBxSomeTextBox
cboSomeCombo
etc.

 

[Moss100]

I am very grateful for all the help.

On the form I am using it with there is a field called SMS

I would like it to only put numbers in when this field is set to YES

Much appreciated if someone can help me here - I am sure I will eventually start to read code a little better!

Regards


Mark

 

[MajP]

The original code was written so it could work with almost any generic form. Now it is specific to just yours

Public Function ConcatRecords(frmName As String, fieldName As String, Optional delimeter As String = "") As String
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Set rs = Forms(frmName).RecordsetClone
  Set fld = rs.Fields(fieldName)
  rs.MoveFirst
  Do While Not rs.EOF
    if not trim(fld.value & " ") = "" [b] and RS.fields("SMS") = True [/b] then 
     'Verify that the record is not null or empty, and SMS = true
      If ConcatRecords = "" Then
        ConcatRecords = fld.Value
      Else
        ConcatRecords = ConcatRecords & delimeter & " " & fld.Value
      End If
    end if
    rs.MoveNext
  Loop
End Function

 

[Moss100]

Much appreciated -

If I wanted to keep the flexibility of the public function could I use the code attached to a form privately and keep the original function public?

Thanks Mark

 

[MajP]

In theory you could do that, but that would be a bad coding practice. I would give the function specific to the form a different name, like ConcatNumbers because it concatenates numbers. If you made it specific you could shorten the code a little by putting in the actual object names. No reason to pass in names. Place the code in the form's module

Public Function ConcatNumbers(Optional delimeter As String = "") As String
  Dim rs As DAO.recordset
  Dim fld As DAO.Field
  Set rs = me.recordsetClone
  Set fld = rs.Fields("NUMBERS")
  rs.MoveFirst
  Do While Not rs.EOF
    if not trim(fld.value & " ") = ""  and RS.fields("SMS") = True  then 
     'Verify that the record is not null or empty, and SMS = true
      If ConcatNumbers = "" Then
        ConcatNumbers = fld.Value
      Else
        ConcatNumbers = ConcatNumbers & delimeter & " " & fld.Value
      End If
    end if
    rs.MoveNext
  Loop
End Function

 

[dhookom]

MajP's code is the most efficient since it uses the recordset already created by the form. There is also a generic function faq701-4233 which is similar but doesn't use the form's record source.

Duane
Hook'D on Access
MS Access MVP