CurrentDB openRecordset search failed....

[SpankyTClown]

I am trying to copy a record with child records. The process is to copy
the parent and then search the child tables for record related to the
parent. When I try to search the child table for all related records I
get the following error:
Syntax error (missing operator) in query expression
'Configuration Id=28"'

I have shown the code below, any help would be appreciated.

---------------------------------------------------------------------------------------------------------------
On Error GoTo Err_cmd_Modify_Click

' Get the configuration id that is being copied
Me![sourceCI] = Me![Configuration Id]

' Copy the Configuration Item record
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste
Append

' Move to the last record in the table
DoCmd.GoToRecord , , acLast

' Update the revision number
Me![RevisionNumber] = Me![RevisionNumber] + 1

' Get the new configuration id
newCI = Me![Configuration Id]

' create a recordset with the DHS information for this CI
searchSQL = "Select * from Tbl_DHSDetails where Configuration Id =
" & newCI & """"

Set fromrec = CurrentDb.OpenRecordset(searchSQL)
fromrec.MoveFirst

Exit_cmd_Modify_Click:
Exit Sub

Err_cmd_Modify_Click:
MsgBox Err.Description
Resume Exit_cmd_Modify_Click

[Baz]

"SpankyTClown" <ch**************@gmail.comwrote in message
news:11**********************@74g2000cwt.googlegro ups.com...

I am trying to copy a record with child records. The process is to copy
the parent and then search the child tables for record related to the
parent. When I try to search the child table for all related records I
get the following error:
Syntax error (missing operator) in query expression
'Configuration Id=28"'

I have shown the code below, any help would be appreciated.

--------------------------------------------------------------------------

-------------------------------------

On Error GoTo Err_cmd_Modify_Click

' Get the configuration id that is being copied
Me![sourceCI] = Me![Configuration Id]

' Copy the Configuration Item record
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste
Append

' Move to the last record in the table
DoCmd.GoToRecord , , acLast

' Update the revision number
Me![RevisionNumber] = Me![RevisionNumber] + 1

' Get the new configuration id
newCI = Me![Configuration Id]

' create a recordset with the DHS information for this CI
searchSQL = "Select * from Tbl_DHSDetails where Configuration Id =
" & newCI & """"

Set fromrec = CurrentDb.OpenRecordset(searchSQL)
fromrec.MoveFirst

Exit_cmd_Modify_Click:
Exit Sub

Err_cmd_Modify_Click:
MsgBox Err.Description
Resume Exit_cmd_Modify_Click

There's a space in Configuration Id, put square brackets around it in the
SQL statement

[SpankyTClown]

Changed the sql statement to use
searchSQL = "Select * from Tbl_DHSDetails where [Configuration Id] = "
& newCI & """"

I get the same error.
Baz wrote:

"SpankyTClown" <ch**************@gmail.comwrote in message
news:11**********************@74g2000cwt.googlegro ups.com...

I am trying to copy a record with child records. The process is to copy
the parent and then search the child tables for record related to the
parent. When I try to search the child table for all related records I
get the following error:
Syntax error (missing operator) in query expression
'Configuration Id=28"'

I have shown the code below, any help would be appreciated.

--------------------------------------------------------------------------

-------------------------------------

On Error GoTo Err_cmd_Modify_Click

' Get the configuration id that is being copied
Me![sourceCI] = Me![Configuration Id]

' Copy the Configuration Item record
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste
Append

' Move to the last record in the table
DoCmd.GoToRecord , , acLast

' Update the revision number
Me![RevisionNumber] = Me![RevisionNumber] + 1

' Get the new configuration id
newCI = Me![Configuration Id]

' create a recordset with the DHS information for this CI
searchSQL = "Select * from Tbl_DHSDetails where Configuration Id =
" & newCI & """"

Set fromrec = CurrentDb.OpenRecordset(searchSQL)
fromrec.MoveFirst

Exit_cmd_Modify_Click:
Exit Sub

Err_cmd_Modify_Click:
MsgBox Err.Description
Resume Exit_cmd_Modify_Click

There's a space in Configuration Id, put square brackets around it in the
SQL statement

[SpankyTClown]

If I hard code the SQL statement it works
Set fromrec = CurrentDb.OpenRecordset("Select * from Tbl_DHSDetails
where [Configuration Id] = 17;")

So its something to do with the quotes used in the statement.
SpankyTClown wrote:

Changed the sql statement to use
searchSQL = "Select * from Tbl_DHSDetails where [Configuration Id] = "
& newCI & """"

I get the same error.
Baz wrote:

"SpankyTClown" <ch**************@gmail.comwrote in message
news:11**********************@74g2000cwt.googlegro ups.com...

I am trying to copy a record with child records. The process is to copy
the parent and then search the child tables for record related to the
parent. When I try to search the child table for all related records I
get the following error:
Syntax error (missing operator) in query expression
'Configuration Id=28"'
>
I have shown the code below, any help would be appreciated.
>
--------------------------------------------------------------------------

-------------------------------------

On Error GoTo Err_cmd_Modify_Click
>
' Get the configuration id that is being copied
Me![sourceCI] = Me![Configuration Id]
>
' Copy the Configuration Item record
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste
Append
>
' Move to the last record in the table
DoCmd.GoToRecord , , acLast
>
' Update the revision number
Me![RevisionNumber] = Me![RevisionNumber] + 1
>
' Get the new configuration id
newCI = Me![Configuration Id]
>
' create a recordset with the DHS information for this CI
searchSQL = "Select * from Tbl_DHSDetails where Configuration Id =
" & newCI & """"
>
Set fromrec = CurrentDb.OpenRecordset(searchSQL)
fromrec.MoveFirst
>
Exit_cmd_Modify_Click:
Exit Sub
>
Err_cmd_Modify_Click:
MsgBox Err.Description
Resume Exit_cmd_Modify_Click
>

There's a space in Configuration Id, put square brackets around it in the
SQL statement

[Baz]

"SpankyTClown" <ch**************@gmail.comwrote in message
news:11**********************@i3g2000cwc.googlegro ups.com...

If I hard code the SQL statement it works
Set fromrec = CurrentDb.OpenRecordset("Select * from Tbl_DHSDetails
where [Configuration Id] = 17;")

So its something to do with the quotes used in the statement.

Correct, you've got too many of them! This should do the trick:

searchSQL = "Select * from Tbl_DHSDetails where [Configuration Id] = " &
newCI

[Allen Browne]

There are no quotes in the example that works, so [Configuration Id] must be
a Number type (not a Text field.)

Therefore:
searchSQL = "Select * from Tbl_DHSDetails where [Configuration Id] = "
newCI & ";"
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"SpankyTClown" <ch**************@gmail.comwrote in message
news:11**********************@i3g2000cwc.googlegro ups.com...

If I hard code the SQL statement it works
Set fromrec = CurrentDb.OpenRecordset("Select * from Tbl_DHSDetails
where [Configuration Id] = 17;")

So its something to do with the quotes used in the statement.
SpankyTClown wrote:

>Changed the sql statement to use
searchSQL = "Select * from Tbl_DHSDetails where [Configuration Id] = "
& newCI & """"

I get the same error.
Baz wrote:

"SpankyTClown" <ch**************@gmail.comwrote in message
news:11**********************@74g2000cwt.googlegro ups.com...
I am trying to copy a record with child records. The process is to
copy
the parent and then search the child tables for record related to the
parent. When I try to search the child table for all related records
I
get the following error:
Syntax error (missing operator) in query expression
'Configuration Id=28"'

I have shown the code below, any help would be appreciated.

--------------------------------------------------------------------------
-------------------------------------
On Error GoTo Err_cmd_Modify_Click

' Get the configuration id that is being copied
Me![sourceCI] = Me![Configuration Id]

' Copy the Configuration Item record
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste
Append

' Move to the last record in the table
DoCmd.GoToRecord , , acLast

' Update the revision number
Me![RevisionNumber] = Me![RevisionNumber] + 1

' Get the new configuration id
newCI = Me![Configuration Id]

' create a recordset with the DHS information for this CI
searchSQL = "Select * from Tbl_DHSDetails where Configuration Id
=
" & newCI & """"

Set fromrec = CurrentDb.OpenRecordset(searchSQL)
fromrec.MoveFirst

Exit_cmd_Modify_Click:
Exit Sub

Err_cmd_Modify_Click:
MsgBox Err.Description
Resume Exit_cmd_Modify_Click
There's a space in Configuration Id, put square brackets around it in
the
SQL statement

[SpankyTClown]

Removing the extra quotes did the trick.

Thanks for all the help.
Allen Browne wrote:

There are no quotes in the example that works, so [Configuration Id] must be
a Number type (not a Text field.)

Therefore:
searchSQL = "Select * from Tbl_DHSDetails where [Configuration Id] = "
newCI & ";"
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"SpankyTClown" <ch**************@gmail.comwrote in message
news:11**********************@i3g2000cwc.googlegro ups.com...

If I hard code the SQL statement it works
Set fromrec = CurrentDb.OpenRecordset("Select * from Tbl_DHSDetails
where [Configuration Id] = 17;")

So its something to do with the quotes used in the statement.
SpankyTClown wrote:

Changed the sql statement to use
searchSQL = "Select * from Tbl_DHSDetails where [Configuration Id] = "
& newCI & """"

I get the same error.
Baz wrote:
"SpankyTClown" <ch**************@gmail.comwrote in message
news:11**********************@74g2000cwt.googlegro ups.com...
I am trying to copy a record with child records. The process is to
copy
the parent and then search the child tables for record related to the
parent. When I try to search the child table for all related records
I
get the following error:
Syntax error (missing operator) in query expression
'Configuration Id=28"'
>
I have shown the code below, any help would be appreciated.
>
--------------------------------------------------------------------------
-------------------------------------
On Error GoTo Err_cmd_Modify_Click
>
' Get the configuration id that is being copied
Me![sourceCI] = Me![Configuration Id]
>
' Copy the Configuration Item record
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste
Append
>
' Move to the last record in the table
DoCmd.GoToRecord , , acLast
>
' Update the revision number
Me![RevisionNumber] = Me![RevisionNumber] + 1
>
' Get the new configuration id
newCI = Me![Configuration Id]
>
' create a recordset with the DHS information for this CI
searchSQL = "Select * from Tbl_DHSDetails where Configuration Id
=
" & newCI & """"
>
Set fromrec = CurrentDb.OpenRecordset(searchSQL)
fromrec.MoveFirst
>
Exit_cmd_Modify_Click:
Exit Sub
>
Err_cmd_Modify_Click:
MsgBox Err.Description
Resume Exit_cmd_Modify_Click
>

There's a space in Configuration Id, put square brackets around it in
the
SQL statement