Write to table syntax problem

[Les]

I am using the following code which concatinates values which I want to
write to a table.

I have problems getting the code to recognise the variable "strInitials"
(See ****** ) It seems to want a string only.

Can anyone give me the correct syntax for this.

Thanks

Les


Dim rstRecips As Object
Dim strInitials As String

strInitials = ""

Set rstRecips = CurrentDb.OpenRecordset("qryUpdate_Responsibility") 'Query
Name
If rstRecips.BOF Then 'BOF=no results from query
MsgBox "Query produced no results."
Else
rstRecips.MoveFirst
Do
strEddresses = strEddresses & _
rstRecips.strResponsibility & "," 'Email Field NAme
rstRecips.MoveNext
Loop Until rstRecips.EOF

'Insert Into Table

strsql = "Insert Into tblResponsibility (numID,strResponsibility_List)Values
( " & Me!ActivityID & ", strInitials)" *******

CurrentDb.Execute strsql, dbFailOnError

End If

rstRecips.Close
Set rstRecips = Nothing

 

[Gerald Stanley]

2 things for you to consider

1. The variable strInitials does not appear to be getting
updated in the Do Loop.

2. The correct syntax is
strsql = "Insert Into tblResponsibility
(numID,strResponsibility_List)Values
( " & Me!ActivityID & ", " & strInitials & ")"

Hope That Helps
Gerald Stanley MCSD

-----Original Message-----

I am using the following code which concatinates values which I want to
write to a table.

I have problems getting the code to recognise the variable "strInitials"
(See ****** ) It seems to want a string only.

Can anyone give me the correct syntax for this.

Thanks

Les


Dim rstRecips As Object
Dim strInitials As String

strInitials = ""

Set rstRecips =

CurrentDb.OpenRecordset("qryUpdate_Responsibility") 'Query

 

[Les]

Gerald thanks for that. I have now changed the code to the following but I
am getting the message " Number of Query Values and destination fields are
not the same" When I hover the curser over the code I see the exact output
I am expecting.

Any thoughts please?

Les

Dim rstRecips As Object
Dim strInitials As String

strInitials = ""

Set rstRecips = CurrentDb.OpenRecordset("qryUpdate_Responsibility")
'Query Name
If rstRecips.BOF Then 'BOF=no results from query
MsgBox "Query produced no results."
Else
rstRecips.MoveFirst
Do
strInitials = strInitials & _
rstRecips.strResponsibility & "," 'Email Field Name
rstRecips.MoveNext
Loop Until rstRecips.EOF

'Insert Into Table

strsql = "Insert Into tblResponsibility (numID,strResponsibility_List)Values
( " & Me!ActivityID & ", " & strInitials & ")"

CurrentDb.Execute strsql, dbFailOnError

End If

rstRecips.Close
Set rstRecips = Nothing

 

[Gerald Stanley]

Les

This is probably because strInitials ends with a comma.

Hope That Helps
Gerald Stanley MCSD

 

[Les]

Gerald

Thanks for your help with this

Les

This is probably because strInitials ends with a comma.

Hope That Helps
Gerald Stanley MCSD