SQL / query syntax, and code about inserting records

[Mark Kubicki]

(just can't seem to get this one right)

I am adding to a table [tblInstallationNotes] all of the records in a query
[qryInstallationNotes ] where the query fields [Manufacturer ] and
[CatalogNumber] match fields on the form

The query combines 2 tables: [tblMfrCatNoInstallationNotes] and
[tblInstallationNotes] by linking a field [NoteNumber]

table [tblMfrCatNoInstallationNotes] includes fields: [Manufacturer],
[CatalogNumber], and [NoteNumber]

table [tblInstallationNotes] includes fields: [NoteNumber], [NoteTitle],
and [NoteFullText]

the code I have written is:
Public Sub DoSQLAddBaseInstallationNotes(frm As Access.Form)
Dim sSQL As String
sSQL = "INSERT INTO tblInstallationNotes(Type,
PrintInstallationNote, BaseInstallationNote, InstallationNote) " & _
"SELECT '" & frm.Type.Value & "', True, True, NoteFullText" & _
"from qryInstallationNotes " & _
"WHERE Manufacturer = ('" & frm.Manufacturer & _
"') and (CatalogNumber = '" & frm.CatalogNo & "');"

DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True
End Sub

the code is inserting what would be the correct number of records; however,
it is prompting me for a value for: NoteFullText which it then provides as
the value for each of the entries (!?) (not the correct NoteFullText that
wouldbe associated with each record)

as always, thanks in advance,
mark

 

[Dale_Fye via AccessMonster.com]

First, replace these three lines:

DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True

with: Currentdb.Execute strsql, dbfailonerror

This statement avoids the warnings associated with an action query, but by
adding the "dbFailOnError" option, allows you to generate, trap, and handle
errors when they are encountered.

The next thing I would do is take a look at qryInstallationNotes. It appears
that you think it is returning a field [NoteFullText] which may be misspelled.
Make sure that field is spelled correctly. I would also wrap that field name
in brackets [ ] for good measure.

Then I would confirm that [Manufacturer] and [CatalogNumber] are text fields,
and that there is no chance that the value in [Manufacturer] or [CatalogNo]
contains a single quote. If it does, then it will mess up the entire SQL
string.

sSQL = "INSERT INTO tblInstallationNotes(Type, PrintInstallationNote,
BaseInstallationNote, InstallationNote) " _
& "SELECT '" & frm.Type.Value & "', True, True, [NoteFullText] " _
& "FROM qryInstallationNotes " _
& "WHERE [Manufacturer] = '" & frm.Manufacturer & "' "_
& " AND [CatalogNumber] = '" & frm.CatalogNo & "' "

HTH
Dale

(just can't seem to get this one right)

I am adding to a table [tblInstallationNotes] all of the records in a query
[qryInstallationNotes ] where the query fields [Manufacturer ] and
[CatalogNumber] match fields on the form

The query combines 2 tables: [tblMfrCatNoInstallationNotes] and
[tblInstallationNotes] by linking a field [NoteNumber]

table [tblMfrCatNoInstallationNotes] includes fields: [Manufacturer],
[CatalogNumber], and [NoteNumber]

table [tblInstallationNotes] includes fields: [NoteNumber], [NoteTitle],
and [NoteFullText]

the code I have written is:
Public Sub DoSQLAddBaseInstallationNotes(frm As Access.Form)
Dim sSQL As String
sSQL = "INSERT INTO tblInstallationNotes(Type,
PrintInstallationNote, BaseInstallationNote, InstallationNote) " & _
"SELECT '" & frm.Type.Value & "', True, True, NoteFullText" & _
"from qryInstallationNotes " & _
"WHERE Manufacturer = ('" & frm.Manufacturer & _
"') and (CatalogNumber = '" & frm.CatalogNo & "');"

DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True
End Sub

the code is inserting what would be the correct number of records; however,
it is prompting me for a value for: NoteFullText which it then provides as
the value for each of the entries (!?) (not the correct NoteFullText that
wouldbe associated with each record)

as always, thanks in advance,
mark