Write to table syntax problem

  • Thread starter Thread starter Les
  • Start date Start date
L

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
 
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 =
Click to expand...
CurrentDb.OpenRecordset("qryUpdate_Responsibility") 'Query
 
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
 
Les

This is probably because strInitials ends with a comma.

Hope That Helps
Gerald Stanley MCSD
 
Back
Top