simple ? of SQL syntax

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark J Kubicki - 11 Feb 2007 19:03 GMT

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

Reply to this Message

jimbeard - 11 Feb 2007 20:02 GMT

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

> I and adding to a table data which contains the following:
>
[quoted text clipped - 22 lines]
> thanks in advance,
> mark

Reply to this Message

Mark J Kubicki - 11 Feb 2007 20:47 GMT

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,
>
[quoted text clipped - 44 lines]
>> thanks in advance,
>> mark

Reply to this Message

Douglas J. Steele - 11 Feb 2007 21:45 GMT

Use True, not vbTrue.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

>I and adding to a table data which contains the following:
>
[quoted text clipped - 22 lines]
> thanks in advance,
> mark

Reply to this Message