simple ? of SQL syntax

[Mark J Kubicki]

I and adding to a table data which contains the following:

- a text field on the calling form
- the value for (2) checkboxes
- an hyperlink entry from another table, based on the value of (2) fields
on the form

my hunch is that the syntax I'm using for the (2) checkboxes is wrong (?)
could you take a quick look and correct the grammar (if that is indeed the
problem)


Public Sub DoSQL(frm As Access.Form)
Dim sSQL As String
sSQL = "INSERT INTO additionalPages (type, printCatalogSheet,
BaseCatalogSheet, CatalogSheetLink) " & _
"SELECT '" & frm.Type.Value & "', vbTrue, vbTrue,
CatalogSheetLink " & _
"from FixtureCatalogsPages " & _
"WHERE Manufacturer = '" & frm.Manufacturer.Value & _
"' and CatalogNumber = '" & frm.CatalogNo.Value & "';"

CurrentDb().Execute sSQL, dbFailOnError
End Sub


thanks in advance,
mark

 

[Guest]

Mark,

More data may make a helpful answer easier to come by. For example, which
of the controls have which names (i.e., which one is of which type)? What
kind of error or defect are you getting when you try to run the query? (If
an error message, the exact text can be helpful.)

Having said that, I do note that you appear to be putting a field name in
single quotes (. . ."SELECT '" & frm.Type.Value & "', vbTrue, vbTrue,
CatalogSheetLink " . . .). I would expect better luck putting it in square
brackets ("SELECT [" & frm.Type.Value & "]", vbTrue, ...). If the field name
is guaranteed to have no spaces in it, you wouldn't need to enclose it at all
("SELECT " & frm.Type.Value & ", vbTrue, ...).

Jim Beard

 

[Mark J Kubicki]

Jim

of the controls on the form, there are (3) I reference:
Type - string
Manufacturer - string
CatalogNo - string

of the fields on the table I am adding to:
Type - string
printCatalogSheet - Boolean (should be set to vbTrue for each record
added)
BaseCatalogSheet - Boolean (should be set to vbTrue for each record
added)
CatalogSheetLink - hyperlink

of the fields on the table I am getting data from:
Type - string
Manufacturer - string
CatalogSheetLink - hyperlink

additionally, when I revise the code to a previous version (see below), I
get this error:
set 2 fields to Null due to a type conversion failure

revised code:

sSQL = "INSERT INTO additionalPages (type, printCatalogSheet,
BaseCatalogSheet, CatalogSheetLink) " & _
"SELECT '" & frm.Type.Value & "', 'vbTrue', 'vbTrue',
CatalogSheetLink " & _
"from FixtureCatalogsPages " & _
"WHERE Manufacturer = '" & frm.Manufacturer.Value & _
"' and CatalogNumber = '" & frm.CatalogNo.Value & "';"


thanks in advance again...
-mark

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

Mark,

More data may make a helpful answer easier to come by. For example, which
of the controls have which names (i.e., which one is of which type)? What
kind of error or defect are yodu getting when you try to run the query?
(If
an error message, the exact text can be helpful.)

Having said that, I do note that you appear to be putting a field name in
single quotes (. . ."SELECT '" & frm.Type.Value & "', vbTrue, vbTrue,
CatalogSheetLink " . . .). I would expect better luck putting it in
square
brackets ("SELECT [" & frm.Type.Value & "]", vbTrue, ...). If the field
name
is guaranteed to have no spaces in it, you wouldn't need to enclose it at
all
("SELECT " & frm.Type.Value & ", vbTrue, ...).

Jim Beard

I and adding to a table data which contains the following:

- a text field on the calling form
- the value for (2) checkboxes
- an hyperlink entry from another table, based on the value of (2)
fields
on the form

my hunch is that the syntax I'm using for the (2) checkboxes is wrong (?)
could you take a quick look and correct the grammar (if that is indeed
the
problem)


Public Sub DoSQL(frm As Access.Form)
Dim sSQL As String
sSQL = "INSERT INTO additionalPages (type, printCatalogSheet,
BaseCatalogSheet, CatalogSheetLink) " & _
"SELECT '" & frm.Type.Value & "', vbTrue, vbTrue,
CatalogSheetLink " & _
"from FixtureCatalogsPages " & _
"WHERE Manufacturer = '" & frm.Manufacturer.Value & _
"' and CatalogNumber = '" & frm.CatalogNo.Value & "';"

CurrentDb().Execute sSQL, dbFailOnError
End Sub


thanks in advance,
mark

 

[Douglas J. Steele]

Use True, not vbTrue.