Run-time error '3075'

  • Thread starter Thread starter Chrissy
  • Start date Start date
C

Chrissy

Syntax error (missing operator) in query expresssion '[AppInvNum] ='

I get the above message running the following code. Basically, I am
entering an invoice and then opening another form to apply a credit. This
first delete code works when the user quits the invoice entry without a
credit applied.

This is a case where the user changes mind about invoice entry and, if
credit is applied, both the invoice and the application of credit need to go
away.

'deletes current job record
strSQL = "DELETE FROM [tbl 2 Job] " & "WHERE [InvNum] = " & [txtInvNum]
CurrentDb.Execute strSQL, dbFailOnError

'deletes credit applied if record exists.
strSQL1 = "DELETE FROM [tbl 1 ClientCreditApplied] " & "WHERE [AppInvNum] =
" & [txtInvNum]
CurrentDb.Execute strSQL1, dbFailOnError

I wasn't sure if strSQL could be used for both, so I DIM'd strSQL1.

The table names and fields are correct.

I read that this could indicate a Null field...but it is not null, the
credit application record does exist correctly.
 
Chrissy,
Have you verified that there actually is a record for the credit application
before the second SQL statement executes. Try stepping through the code and
verifying that the record exists before the second statement is executed.
 
Yes you can use strSQL for both.
Error indicates [txtInvNum] contains a blank or Null. Are you sure it
contains a value?
Did you try 'Msgbox strSQL1' just before you execute the statement?
It's not a good idea to have spaces in table names or column names.

'deletes credit applied if record exists.
strSQL1 = "DELETE FROM [tbl 1 ClientCreditApplied] " & "WHERE [AppInvNum] =
" & [txtInvNum]
CurrentDb.Execute strSQL1, dbFailOnError

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
Yes, Lynn. Thanks. There is a record. It was written to the other table
prior to the user quitting.

I do, however, have a me.undo prior to these strSQLs.

Still, the record is there since it is in another table (not the query/table
the invoice form is tied to.)
--
Chrissy


Lynn Trapp said:
Chrissy,
Have you verified that there actually is a record for the credit application
before the second SQL statement executes. Try stepping through the code and
verifying that the record exists before the second statement is executed.

--
Lynn Trapp
MCP, MOS, MCAS


Chrissy said:
Syntax error (missing operator) in query expresssion '[AppInvNum] ='

I get the above message running the following code. Basically, I am
entering an invoice and then opening another form to apply a credit. This
first delete code works when the user quits the invoice entry without a
credit applied.

This is a case where the user changes mind about invoice entry and, if
credit is applied, both the invoice and the application of credit need to go
away.

'deletes current job record
strSQL = "DELETE FROM [tbl 2 Job] " & "WHERE [InvNum] = " & [txtInvNum]
CurrentDb.Execute strSQL, dbFailOnError

'deletes credit applied if record exists.
strSQL1 = "DELETE FROM [tbl 1 ClientCreditApplied] " & "WHERE [AppInvNum] =
" & [txtInvNum]
CurrentDb.Execute strSQL1, dbFailOnError

I wasn't sure if strSQL could be used for both, so I DIM'd strSQL1.

The table names and fields are correct.

I read that this could indicate a Null field...but it is not null, the
credit application record does exist correctly.
Click to expand...
Click to expand...
 
Assumption: txtInvNum is a control on your form.

Problem: When you delete the record you delete the value of the control

Solution: Build the sql strings before you execute either query.
Or assign the value of txtInvNum to a variable and use the variable to build
the SQL strings.

'=== Two string variables required for the SQL statements
strSQL = "DELETE FROM [tbl 2 Job] " & "WHERE [InvNum] = " & [txtInvNum]
strSQL1 = "DELETE FROM [tbl 1 ClientCreditApplied] " & "WHERE [AppInvNum] =
" & [txtInvNum]

'deletes current job record
CurrentDb.Execute strSQL, dbFailOnError

'deletes credit applied if record exists.
CurrentDb.Execute strSQL1, dbFailOnError

Alternative solution
Dim vInvoice as Long 'Guessing at data type here.
Dim strSQL as String

vInvoice = [txtInvNum]

'delete current job record
strSQL = "DELETE FROM [tbl 2 Job] " & "WHERE [InvNum] = " & vInvoice
CurrentDb.Execute strSQL, dbFailOnError

'delete credit applied if record exists.
strSQL = "DELETE FROM [tbl 1 ClientCreditApplied] " & "WHERE [AppInvNum]=" &
vInvoice
CurrentDb.Execute strSQL1, dbFailOnError




John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Yes I am Dorian. Thanks. I use it a lot of places prior to this code
including the immediately previous strSQL-successfully.

I know about the spaces...holdover from long ago and not worth the effort to
change now.

Thanks.
--
Chrissy


Dorian said:
Yes you can use strSQL for both.
Error indicates [txtInvNum] contains a blank or Null. Are you sure it
contains a value?
Did you try 'Msgbox strSQL1' just before you execute the statement?
It's not a good idea to have spaces in table names or column names.

'deletes credit applied if record exists.
strSQL1 = "DELETE FROM [tbl 1 ClientCreditApplied] " & "WHERE [AppInvNum] =
" & [txtInvNum]
CurrentDb.Execute strSQL1, dbFailOnError

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


Chrissy said:
Syntax error (missing operator) in query expresssion '[AppInvNum] ='

I get the above message running the following code. Basically, I am
entering an invoice and then opening another form to apply a credit. This
first delete code works when the user quits the invoice entry without a
credit applied.

This is a case where the user changes mind about invoice entry and, if
credit is applied, both the invoice and the application of credit need to go
away.

'deletes current job record
strSQL = "DELETE FROM [tbl 2 Job] " & "WHERE [InvNum] = " & [txtInvNum]
CurrentDb.Execute strSQL, dbFailOnError

'deletes credit applied if record exists.
strSQL1 = "DELETE FROM [tbl 1 ClientCreditApplied] " & "WHERE [AppInvNum] =
" & [txtInvNum]
CurrentDb.Execute strSQL1, dbFailOnError

I wasn't sure if strSQL could be used for both, so I DIM'd strSQL1.

The table names and fields are correct.

I read that this could indicate a Null field...but it is not null, the
credit application record does exist correctly.
Click to expand...
Click to expand...
 
Thanks, J. Moving me.undo to after deletes results in "could not update
currently locked.'

I moved it back.
--
Chrissy


J_Goddard via AccessMonster.com said:
Hi -
I do, however, have a me.undo prior to these strSQLs.
Click to expand...

That may be the source of the problem. Me.undo undoes all the changes to the
current record. If that record is a new record, then all the entered data is
cleared - this will include the control [txtInvNum], so that when the strSQL
string is created, it has the syntax error "WHERE [AppInvNum] = ".

Try moving the me.Undo after the delete queries.

HTH

John

Yes, Lynn. Thanks. There is a record. It was written to the other table
prior to the user quitting.

I do, however, have a me.undo prior to these strSQLs.

Still, the record is there since it is in another table (not the query/table
the invoice form is tied to.)
Chrissy,
Have you verified that there actually is a record for the credit application
Click to expand...
[quoted text clipped - 27 lines]
I read that this could indicate a Null field...but it is not null, the
credit application record does exist correctly.
Click to expand...
Click to expand...

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com


.
Click to expand...
 
John Spencer to my rescue. Thank you!

I made an unbound text box giving it the InvNum value then called it in the
strSQLs. Works fine.

Thanks so much,
--
Chrissy


John Spencer said:
Assumption: txtInvNum is a control on your form.

Problem: When you delete the record you delete the value of the control

Solution: Build the sql strings before you execute either query.
Or assign the value of txtInvNum to a variable and use the variable to build
the SQL strings.

'=== Two string variables required for the SQL statements
strSQL = "DELETE FROM [tbl 2 Job] " & "WHERE [InvNum] = " & [txtInvNum]
strSQL1 = "DELETE FROM [tbl 1 ClientCreditApplied] " & "WHERE [AppInvNum] =
" & [txtInvNum]

'deletes current job record
CurrentDb.Execute strSQL, dbFailOnError

'deletes credit applied if record exists.
CurrentDb.Execute strSQL1, dbFailOnError

Alternative solution
Dim vInvoice as Long 'Guessing at data type here.
Dim strSQL as String

vInvoice = [txtInvNum]

'delete current job record
strSQL = "DELETE FROM [tbl 2 Job] " & "WHERE [InvNum] = " & vInvoice
CurrentDb.Execute strSQL, dbFailOnError

'delete credit applied if record exists.
strSQL = "DELETE FROM [tbl 1 ClientCreditApplied] " & "WHERE [AppInvNum]=" &
vInvoice
CurrentDb.Execute strSQL1, dbFailOnError




John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Syntax error (missing operator) in query expresssion '[AppInvNum] ='

I get the above message running the following code. Basically, I am
entering an invoice and then opening another form to apply a credit. This
first delete code works when the user quits the invoice entry without a
credit applied.

This is a case where the user changes mind about invoice entry and, if
credit is applied, both the invoice and the application of credit need to go
away.

'deletes current job record
strSQL = "DELETE FROM [tbl 2 Job] " & "WHERE [InvNum] = " & [txtInvNum]
CurrentDb.Execute strSQL, dbFailOnError

'deletes credit applied if record exists.
strSQL1 = "DELETE FROM [tbl 1 ClientCreditApplied] " & "WHERE [AppInvNum] =
" & [txtInvNum]
CurrentDb.Execute strSQL1, dbFailOnError

I wasn't sure if strSQL could be used for both, so I DIM'd strSQL1.

The table names and fields are correct.

I read that this could indicate a Null field...but it is not null, the
credit application record does exist correctly.
Click to expand...
.
Click to expand...
 
Thanks, J. Moving me.undo to after deletes results in "could not update
currently locked.'

I moved it back.
Click to expand...

In that case... stash the value of txtAppInvNum in a variable, Undo, and then
concatenate the variable into the SQL string (rather than the now empty
textbox reference).
 
Back
Top