Can anyone help with the following statement? I am clearly declaring
the strings incorrectly but can't work out how.


Dim StrValue1 As String
Dim StrValue2 As String

StrValue1 = "[Forms]![frmPolicy]![txtPolicyNo]"
StrValue2 = "[Forms]![frmPolicy]![txtPolicyVer]"

strSQL = "INSERT INTO ProfitShareReInsured (policy_no, version_no,
organisation_id) VALUES ('StrValue1', 'StrValue2', '809')"

DoCmd.RunSQL strSQL

FireyColin wrote:
Assuming you wish the values contained in those form controls to be assigned
to the variables, you have to remove the delimiters (the quotes).

StrValue1 = [Forms]![frmPolicy]![txtPolicyNo]
StrValue2 = [Forms]![frmPolicy]![txtPolicyVer]

'check the values of the variables - comment when
'finished debugging
msgbox strvalue1
msgbox strvalue2

In this case the problem is different: your statement is inserting the
literal strings 'StrValue1' and 'StrValue2'. You need to concatenate the
values into the sql string:

strSQL = "INSERT INTO ProfitShareReInsured " & _
"(policy_no, version_no, organisation_id) VALUES ('" & _
StrValue1 & "', '" & StrValue2 & "', '809')"

'again - check the value assigned to strSQL
'comment when finished debugging:

msgbox strSQL

Incidently, if the user enters a value containing an apostrophe into either
form control, this will fail. To foolproof it, you need to escape any
apostrophes in the strings by doubling them. You can use the replace()
function to do it, like this:

StrValue1 = [Forms]![frmPolicy]![txtPolicyNo]
StrValue1 = replace(StrValue1, "'","''")
StrValue2 = [Forms]![frmPolicy]![txtPolicyVer]
StrValue1 = replace(StrValue2, "'","''")

On Nov 25, 1:13*pm, "Bob Barrows" <reb01... (AT) NOSPAMyahoo (DOT) com> wrote:
Many Thanks Bob, I knew it would be something simple.