Missing parameter?

[PayeDoc]

Hello All

A simple problem here, I think (compared to the last one!).

In this code:

Dim missinginfo As String
If IsNull([DOB]) Then missinginfo = " date of birth."
If IsNull([NI number]) Then missinginfo = " National Insurance number."
If IsNull([NI number]) And IsNull([DOB]) Then missinginfo = " date of birth
or National Insurance number."

MsgBox ("We do not have a record of this employee's" & [missinginfo])
DoCmd.RunSQL "INSERT into employee_submissions (employee,submission_type)
Values([Forms]![staffs subform new]![employee_name],[missinginfo])"


.... the MsgBox action runs fine, and displays the expected value of
[missinginfo].
But the INSERT statement won't recognise [missinginfo] - access asks me for
this 'parameter value'. The record does get added to table
'employee_submissions', with the expected value of the 'employee', but not
with the [missinginfo] value.

I have tried removing the square brackets round 'missinginfo', but to no
avail.
Whats have I done wrong?

Thanks for any help.
Leslie Isaacs

 

[Jack Leach]

the Variable missinginfo should be contencated into the sql string.

try this:

DoCmd.RunSQL "INSERT INTO employee_submissions " & _
"(employee, submission_type) " & _
"VALUES ([Forms]![staffs subform new]![employee_name], """ & _
missinginfo & """)"



because missinginfo is a variable in the code, rather than part of a form,
you need to "manually" enter it into the sql, rather than using the
expression service.


hth

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[PayeDoc]

Jack
OK - got it.
Many thanks (again!).
Les

the Variable missinginfo should be contencated into the sql string.

try this:

DoCmd.RunSQL "INSERT INTO employee_submissions " & _
"(employee, submission_type) " & _
"VALUES ([Forms]![staffs subform new]![employee_name], """ & _
missinginfo & """)"



because missinginfo is a variable in the code, rather than part of a form,
you need to "manually" enter it into the sql, rather than using the
expression service.


hth

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Hello All

A simple problem here, I think (compared to the last one!).

In this code:

Dim missinginfo As String
If IsNull([DOB]) Then missinginfo = " date of birth."
If IsNull([NI number]) Then missinginfo = " National Insurance number."
If IsNull([NI number]) And IsNull([DOB]) Then missinginfo = " date of birth
or National Insurance number."

MsgBox ("We do not have a record of this employee's" & [missinginfo])
DoCmd.RunSQL "INSERT into employee_submissions (employee,submission_type)
Values([Forms]![staffs subform new]![employee_name],[missinginfo])"


.... the MsgBox action runs fine, and displays the expected value of
[missinginfo].
But the INSERT statement won't recognise [missinginfo] - access asks me for
this 'parameter value'. The record does get added to table
'employee_submissions', with the expected value of the 'employee', but not
with the [missinginfo] value.

I have tried removing the square brackets round 'missinginfo', but to no
avail.
Whats have I done wrong?

Thanks for any help.
Leslie Isaacs