SQL Problem

[DS]

I have the following SQL statement. The problem is that whatever I
enter into the TxtName field (Unbound) on Form5 a box pops up asking for
the parameter...at the top of the box is whatever I typed into TxtName.
Is this a Syntax problem?
Thanks
DS

Me.TxtTaxID = Nz(DMax("[TaxID]", "Tax"), 0) + 1
Dim TaxSQL As String
DoCmd.SetWarnings False
TaxSQL = "INSERT INTO Tax (TaxID,TaxName,TaxAmount,Active) " & _
"Values(" & Forms!Form5!TxtTaxID & "," & Forms!Form5!TxtName & "," &
Forms!Form5!TxtAmount & ","" & Forms!Form5![ChkActive] & "")"
DoCmd.RunSQL (TaxSQL)
DoCmd.SetWarnings True

 

[Delordson Kallon]

You need to reformat your taxSQL string so that the Forms!Form5!TxtName
portion of it returns a string. If you are sure txtName will never contain a
name with an apostrophy (e.g. O'Grady) then use:

TaxSQL = "INSERT INTO Tax (TaxID,TaxName,TaxAmount,Active) " & _
"Values(" & Forms!Form5!TxtTaxID & ",'" & Forms!Form5!TxtName & "'," &
Forms!Form5!TxtAmount & ","" & Forms!Form5![ChkActive] & "")"

otherwise use

TaxSQL = "INSERT INTO Tax (TaxID,TaxName,TaxAmount,Active) " & _
"Values(" & Forms!Form5!TxtTaxID & ",'" & Replace(Forms!Form5!TxtName,"'",
"''") & "'," &
Forms!Form5!TxtAmount & ","" & Forms!Form5![ChkActive] & "")"

In the first example you are placing a single quotation mark in front of and
after " & Forms!Form5!TxtName & "

In the second example you are doing the same but also using the replace
function to replace any single quotation marks (') in the name with a pair of
quotation marks (''), both enclosed in double quotation marks.

Delordson Kallon
www.instantsoftwaretraining.com

 

[DS]

You need to reformat your taxSQL string so that the Forms!Form5!TxtName
portion of it returns a string. If you are sure txtName will never contain a
name with an apostrophy (e.g. O'Grady) then use:

TaxSQL = "INSERT INTO Tax (TaxID,TaxName,TaxAmount,Active) " & _
"Values(" & Forms!Form5!TxtTaxID & ",'" & Forms!Form5!TxtName & "'," &
Forms!Form5!TxtAmount & ","" & Forms!Form5![ChkActive] & "")"

otherwise use

TaxSQL = "INSERT INTO Tax (TaxID,TaxName,TaxAmount,Active) " & _
"Values(" & Forms!Form5!TxtTaxID & ",'" & Replace(Forms!Form5!TxtName,"'",
"''") & "'," &
Forms!Form5!TxtAmount & ","" & Forms!Form5![ChkActive] & "")"

In the first example you are placing a single quotation mark in front of and
after " & Forms!Form5!TxtName & "

In the second example you are doing the same but also using the replace
function to replace any single quotation marks (') in the name with a pair of
quotation marks (''), both enclosed in double quotation marks.

Delordson Kallon
www.instantsoftwaretraining.com

:

I have the following SQL statement. The problem is that whatever I
enter into the TxtName field (Unbound) on Form5 a box pops up asking for
the parameter...at the top of the box is whatever I typed into TxtName.
Is this a Syntax problem?
Thanks
DS

Me.TxtTaxID = Nz(DMax("[TaxID]", "Tax"), 0) + 1
Dim TaxSQL As String
DoCmd.SetWarnings False
TaxSQL = "INSERT INTO Tax (TaxID,TaxName,TaxAmount,Active) " & _
"Values(" & Forms!Form5!TxtTaxID & "," & Forms!Form5!TxtName & "," &
Forms!Form5!TxtAmount & ","" & Forms!Form5![ChkActive] & "")"
DoCmd.RunSQL (TaxSQL)
DoCmd.SetWarnings True

Thanks! That did the trick!
DS