need a little help fixing an SQL error...

[markjkubicki]

I have an SQL statement, which I presume has "something' wrong with it; and could use someone's help in determining what that "something" is...

Here's what the statement ought to do:
It shold add records to a table [tbeAdditionalPages] based on an update to a control [frmSpec.CatalogNo] on the current form [frmSpec].

The problem is:
As soon as the code tries to execute when matching records are found... nothing... it locks the user in the current form's control (until a choice with no corresponding recond in FixtureCatalogsPages is found)...

I've checked all the spellings... could it possibly be something with the field [catalogSheetLink] which is a hyperfield?

In the table tbeAdditionalPages, the fields are:
Type text
PrintCatalogShhet Y/N
BaseCatalogSheet Y/N
Catalogsheet hyperlink
PrintOrder number
IsMountingDetail Y/N
The corresponding fields in table FixtureCatalogsPages have the same data type
Both [frmSpec.manufacturer] and [frmSpec.CatalogNo] are text

Many thanks in advance,
mark

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


Public Sub DoSQLAddCatalogBaseSheets(frm As Access.Form)
Dim sSQL As String
sSQL = "INSERT INTO tbeAdditionalPages (type, printCatalogSheet, BaseCatalogSheet, CatalogSheetLink, PrintOrder, IsMountingDetail) " & _
"SELECT '" & Forms![frmSpec].[Type] & "', true, true, CatalogSheetLink, 0, false " & _
"FROM FixtureCatalogsPages " & _
"WHERE Manufacturer = '" & Forms![frmSpec].Manufacturer.Value & _


"' and CatalogNumber = '" & Forms![frmSpec].CatalogNo.Value & "';"
CurrentDb().Execute sSQL, dbFailOnError
End Sub

[NTC]

you state that "it locks the user in the current form's control "; this tends to indicate that your issue is trigger timing, and not necessarily the sql itself - - although there may be sql issues.

as a test; trigger that sql after the record is totally completed - when the focus has left that record (the pencil icon to the left of the record is no longer there). Because first you need to resolve any sql issues, then address the most elegant and appropriate point to trigger the query. If you trigger it while still in the record - then the record is 'dirty' or still open and will not be included in the query as it isn't yet written to the table. You must complete the record, or force it via dirty=false preceeding the query.

[markjkubicki]

(thanks I'll try your suggestion)

BUT, I'm not so confident that's problem...
If I delete the hyperlink field, everything works fine
(of course, the catch is that I need to include that filed !)

thoughts?
--------------
the code would be changed to...

sSQL = "INSERT INTO tbeAdditionalPages (type, printCatalogSheet, BaseCatalogSheet, PrintOrder, IsMountingDetail) " & _
"SELECT '" & Forms![frmSpec].[Type] & "', true, true, 0, false " & _
"FROM FixtureCatalogsPages " & _
"WHERE Manufacturer = '" & Forms![frmSpec].Manufacturer.Value & _
"' and CatalogNumber = '" & Forms![frmSpec].CatalogNo.Value & "';"

[markjkubicki]

the problerm was a simple syntax error:
the # was missing before the inserted hyperlink [CatalogSheetLink]...

sSQL = "INSERT INTO tbeAdditionalPages (type, printCatalogSheet, BaseCatalogSheet, CatalogSheetLink, PrintOrder, IsMountingDetail) " & _
"SELECT '" & Forms![frmSpec].[Type] & "', true, true, '#' & CatalogSheetLink, 0, false " & _
"FROM FixtureCatalogsPages " & _
"WHERE Manufacturer = '" & Forms![frmSpec].Manufacturer.Value & _
"' and CatalogNumber = '" & Forms![frmSpec].CatalogNo.Value & "';"