SQL Problem

D

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
 
D

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
 
D

DS

Delordson said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top