Can I convert a SQL type concatenate function into VB code 1

[Johnnycat1]

I have a project where I need to concatenate data (employee names) from a field in a table where the field for cost codes in the table match the field for costs codes an open form. I have used the following sql in a query based on a module that PHV and MRemou helped me with years ago.

'This is the concatenate of the Employee Names and Hours per Cost Code
Concatenate("SELECT[Employees] & ': ' & [TotalSTHrs] & ', ' & [TotalOTHrs] & ', ' & [TotalDTHrs] WHERE [TblTimeSheetsDailyLogEmployees].[WEID]='" & [TblTimeSheetsDailyLogNotes].[WEID] & "'", Chr(13) & Chr(10))

This is what I am trying to convert it to in the Event Procedure in the form:

Dim strConcat As String 'build return string
Set rstTblTimeSheetsDailyLogEmployees = CurrentDb.OpenRecordset("TblTimeSheetsDailyLogEmployees", dbOpenDynaset)
With rstTblTimeSheetsDailyLogEmployees
If Not .EOF Then
.FindFirst "[WEID]='" & strWEIDCode & "'"
Do While Not .EOF
.MoveFirst
strConcat = strConcat & _
![Employees]
.MoveNext
Loop
End If
.Close
End With

I want to use the strConcat in the following procedure:

Set rstTblTimeSheetsDailyLogNotes = CurrentDb.OpenRecordset("TblTimeSheetsDailyLogNotes", dbOpenDynaset)

With rstTblTimeSheetsDailyLogNotes
.FindFirst "[WEID]='" & strWEIDCode & "'"
If .NoMatch Then Exit Sub Else .Edit
![EmployeesPerCode] = strConcat
.Update

End With


Any help here would be greatly appreciated!

 

[dhookom]

I don't know which Concatenate() function you are using but I think you can try an update query like:

UPDATE tblTimeSheetsdailyLogNotes
SET EmployeesPerCode = Concatenate("SELECT [Employees] FROM tblTimeSheetsDailyLogEmployees WHERE [WEID]='" & [WEID] & "'", Chr(13) & Chr(10))

This uses the Concatenate() function from the FAQs and make some assumptions about your tables, fields, data, and desires.

Duane
Hook'D on Access
MS Access MVP

 

[Johnnycat1]

Duane,

I was using this in a make table query which was very slow. The update query is much better.

Thanks for your help.

Johnnycat