By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,654 Members | 1,842 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,654 IT Pros & Developers. It's quick & easy.

CurrentDB openRecordset search failed....

P: n/a
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

Aug 17 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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
Aug 17 '06 #2

P: n/a
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
Aug 17 '06 #3

P: n/a
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
Aug 17 '06 #4

P: n/a
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


Aug 17 '06 #5

P: n/a
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

Aug 17 '06 #6

P: n/a
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
Aug 17 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.