syntax error 3075


G

Guest

Novice needs help please.

I am attempting to use code I found here (surrounded by messages on/off) to
delete the current record at subform and the related ones (in other, sibling
subforms.) The error is:

-- syntax error (missing operator) in query expression '[tbl 2 Job].InvNum
=' --

My code is:
' run a delete query to delete the related records from the subform
DoCmd.RunSQL "DELETE [tbl 2 Job].* FROM [tbl 2 Job] Where [tbl 2 _
Job].InvNum = " & Me.[InvNum]

'line continuation is only for newsgroup.

' Delete current record on viewed form
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
 
Ad

Advertisements

D

Douglas J. Steele

You sure Me.[InvNum] is returning a value?

If it is, is the InvNum field text? If so, you need quotes:

DoCmd.RunSQL "DELETE [tbl 2 Job].* FROM [tbl 2 Job] " & _
"Where [tbl 2 Job].InvNum = '" & Me.[InvNum] & "'"

Exagerated for clarity, that second line is

"Where [tbl 2 Job].InvNum = ' " & Me.[InvNum] & " ' "
 
G

Guest

Thanks for responding.

Yes, the field is number, currency type. I'm Access 2007.

When I hover over the VBA code that is highlighted, it shows value of
Me.InvNum is null. My command button to delete is on a form and the subform
shows InvNum.

I tried setting the focus to the subform first -- same error.
I also tried Me.dirty=false -- same error.
I also tried setting above focus then Me.dirty=false -- same error.

Any ideas?

--
Thanks for your help,
Chris


Douglas J. Steele said:
You sure Me.[InvNum] is returning a value?

If it is, is the InvNum field text? If so, you need quotes:

DoCmd.RunSQL "DELETE [tbl 2 Job].* FROM [tbl 2 Job] " & _
"Where [tbl 2 Job].InvNum = '" & Me.[InvNum] & "'"

Exagerated for clarity, that second line is

"Where [tbl 2 Job].InvNum = ' " & Me.[InvNum] & " ' "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Chris said:
Novice needs help please.

I am attempting to use code I found here (surrounded by messages on/off)
to
delete the current record at subform and the related ones (in other,
sibling
subforms.) The error is:

-- syntax error (missing operator) in query expression '[tbl 2 Job].InvNum
=' --

My code is:
' run a delete query to delete the related records from the subform
DoCmd.RunSQL "DELETE [tbl 2 Job].* FROM [tbl 2 Job] Where [tbl 2 _
Job].InvNum = " & Me.[InvNum]

'line continuation is only for newsgroup.

' Delete current record on viewed form
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
 
P

Pieter Wijnen

If Not VBA.IsNull(Me.InvNum.Value) Then
Access.CurrentDb.Execute "DELETE From [tbl 2 Job] WHERE InvNum=" &
Me.InvNum.Value, DAO.DbSeeChanges Or DAO.DbFailOnError
End If

HtH

Pieter

Chris said:
Thanks for responding.

Yes, the field is number, currency type. I'm Access 2007.

When I hover over the VBA code that is highlighted, it shows value of
Me.InvNum is null. My command button to delete is on a form and the
subform
shows InvNum.

I tried setting the focus to the subform first -- same error.
I also tried Me.dirty=false -- same error.
I also tried setting above focus then Me.dirty=false -- same error.

Any ideas?

--
Thanks for your help,
Chris


Douglas J. Steele said:
You sure Me.[InvNum] is returning a value?

If it is, is the InvNum field text? If so, you need quotes:

DoCmd.RunSQL "DELETE [tbl 2 Job].* FROM [tbl 2 Job] " & _
"Where [tbl 2 Job].InvNum = '" & Me.[InvNum] & "'"

Exagerated for clarity, that second line is

"Where [tbl 2 Job].InvNum = ' " & Me.[InvNum] & " ' "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Chris said:
Novice needs help please.

I am attempting to use code I found here (surrounded by messages
on/off)
to
delete the current record at subform and the related ones (in other,
sibling
subforms.) The error is:

-- syntax error (missing operator) in query expression '[tbl 2
Job].InvNum
=' --

My code is:
' run a delete query to delete the related records from the subform
DoCmd.RunSQL "DELETE [tbl 2 Job].* FROM [tbl 2 Job] Where [tbl 2 _
Job].InvNum = " & Me.[InvNum]

'line continuation is only for newsgroup.

' Delete current record on viewed form
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
 
G

Guest

Thanks, Pieter, but you have me confused with a pro. I don't follow that at
all!!!

But, I did insert the code to my delete button as a test. Nothing happens
-- I should say nothing visible happens and the record remains un-deleted.

--
Thanks for your help,
Chris


Pieter Wijnen said:
If Not VBA.IsNull(Me.InvNum.Value) Then
Access.CurrentDb.Execute "DELETE From [tbl 2 Job] WHERE InvNum=" &
Me.InvNum.Value, DAO.DbSeeChanges Or DAO.DbFailOnError
End If

HtH

Pieter

Chris said:
Thanks for responding.

Yes, the field is number, currency type. I'm Access 2007.

When I hover over the VBA code that is highlighted, it shows value of
Me.InvNum is null. My command button to delete is on a form and the
subform
shows InvNum.

I tried setting the focus to the subform first -- same error.
I also tried Me.dirty=false -- same error.
I also tried setting above focus then Me.dirty=false -- same error.

Any ideas?

--
Thanks for your help,
Chris


Douglas J. Steele said:
You sure Me.[InvNum] is returning a value?

If it is, is the InvNum field text? If so, you need quotes:

DoCmd.RunSQL "DELETE [tbl 2 Job].* FROM [tbl 2 Job] " & _
"Where [tbl 2 Job].InvNum = '" & Me.[InvNum] & "'"

Exagerated for clarity, that second line is

"Where [tbl 2 Job].InvNum = ' " & Me.[InvNum] & " ' "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Novice needs help please.

I am attempting to use code I found here (surrounded by messages
on/off)
to
delete the current record at subform and the related ones (in other,
sibling
subforms.) The error is:

-- syntax error (missing operator) in query expression '[tbl 2
Job].InvNum
=' --

My code is:
' run a delete query to delete the related records from the subform
DoCmd.RunSQL "DELETE [tbl 2 Job].* FROM [tbl 2 Job] Where [tbl 2 _
Job].InvNum = " & Me.[InvNum]

'line continuation is only for newsgroup.

' Delete current record on viewed form
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
 
D

Douglas J. Steele

To refer to a control on a subform, you need to use

Me!NameOfSubformControl.Form!InvNum

Note that depending on how you added the subform to the parent form, the
name of the subform control may be different than the name of the form being
used as the subform: it's the name of the control on the parent form that
you need to use.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Chris said:
Thanks for responding.

Yes, the field is number, currency type. I'm Access 2007.

When I hover over the VBA code that is highlighted, it shows value of
Me.InvNum is null. My command button to delete is on a form and the
subform
shows InvNum.

I tried setting the focus to the subform first -- same error.
I also tried Me.dirty=false -- same error.
I also tried setting above focus then Me.dirty=false -- same error.

Any ideas?

--
Thanks for your help,
Chris


Douglas J. Steele said:
You sure Me.[InvNum] is returning a value?

If it is, is the InvNum field text? If so, you need quotes:

DoCmd.RunSQL "DELETE [tbl 2 Job].* FROM [tbl 2 Job] " & _
"Where [tbl 2 Job].InvNum = '" & Me.[InvNum] & "'"

Exagerated for clarity, that second line is

"Where [tbl 2 Job].InvNum = ' " & Me.[InvNum] & " ' "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Chris said:
Novice needs help please.

I am attempting to use code I found here (surrounded by messages
on/off)
to
delete the current record at subform and the related ones (in other,
sibling
subforms.) The error is:

-- syntax error (missing operator) in query expression '[tbl 2
Job].InvNum
=' --

My code is:
' run a delete query to delete the related records from the subform
DoCmd.RunSQL "DELETE [tbl 2 Job].* FROM [tbl 2 Job] Where [tbl 2 _
Job].InvNum = " & Me.[InvNum]

'line continuation is only for newsgroup.

' Delete current record on viewed form
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
 
Ad

Advertisements

G

Guest

Thanks, Doug. I missed that reference and when I correct it the record is
deleted. However, for each related table I get an error that I cannot change
or add because one is required in a related table.

I tried a requery of the subform control (Invoicefrm) after I turn back on
the messages and I still get the above message.

How do I eliminate the message about EACH related table?

--
Thanks for your help,
Chris


Douglas J. Steele said:
To refer to a control on a subform, you need to use

Me!NameOfSubformControl.Form!InvNum

Note that depending on how you added the subform to the parent form, the
name of the subform control may be different than the name of the form being
used as the subform: it's the name of the control on the parent form that
you need to use.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Chris said:
Thanks for responding.

Yes, the field is number, currency type. I'm Access 2007.

When I hover over the VBA code that is highlighted, it shows value of
Me.InvNum is null. My command button to delete is on a form and the
subform
shows InvNum.

I tried setting the focus to the subform first -- same error.
I also tried Me.dirty=false -- same error.
I also tried setting above focus then Me.dirty=false -- same error.

Any ideas?

--
Thanks for your help,
Chris


Douglas J. Steele said:
You sure Me.[InvNum] is returning a value?

If it is, is the InvNum field text? If so, you need quotes:

DoCmd.RunSQL "DELETE [tbl 2 Job].* FROM [tbl 2 Job] " & _
"Where [tbl 2 Job].InvNum = '" & Me.[InvNum] & "'"

Exagerated for clarity, that second line is

"Where [tbl 2 Job].InvNum = ' " & Me.[InvNum] & " ' "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Novice needs help please.

I am attempting to use code I found here (surrounded by messages
on/off)
to
delete the current record at subform and the related ones (in other,
sibling
subforms.) The error is:

-- syntax error (missing operator) in query expression '[tbl 2
Job].InvNum
=' --

My code is:
' run a delete query to delete the related records from the subform
DoCmd.RunSQL "DELETE [tbl 2 Job].* FROM [tbl 2 Job] Where [tbl 2 _
Job].InvNum = " & Me.[InvNum]

'line continuation is only for newsgroup.

' Delete current record on viewed form
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
 
D

Douglas J. Steele

Sorry, you're not giving enough details.

What the record source of the parent form and of the subform? Do you have
Referential Integrity set between the tables? If you've got a parent-child
relationship for the tables that make up the record source of the form and
subform (as is usual when using subforms), are you trying to delete from the
parent table or the child table?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Chris said:
Thanks, Doug. I missed that reference and when I correct it the record is
deleted. However, for each related table I get an error that I cannot
change
or add because one is required in a related table.

I tried a requery of the subform control (Invoicefrm) after I turn back on
the messages and I still get the above message.

How do I eliminate the message about EACH related table?

--
Thanks for your help,
Chris


Douglas J. Steele said:
To refer to a control on a subform, you need to use

Me!NameOfSubformControl.Form!InvNum

Note that depending on how you added the subform to the parent form, the
name of the subform control may be different than the name of the form
being
used as the subform: it's the name of the control on the parent form that
you need to use.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Chris said:
Thanks for responding.

Yes, the field is number, currency type. I'm Access 2007.

When I hover over the VBA code that is highlighted, it shows value of
Me.InvNum is null. My command button to delete is on a form and the
subform
shows InvNum.

I tried setting the focus to the subform first -- same error.
I also tried Me.dirty=false -- same error.
I also tried setting above focus then Me.dirty=false -- same error.

Any ideas?

--
Thanks for your help,
Chris


:

You sure Me.[InvNum] is returning a value?

If it is, is the InvNum field text? If so, you need quotes:

DoCmd.RunSQL "DELETE [tbl 2 Job].* FROM [tbl 2 Job] " & _
"Where [tbl 2 Job].InvNum = '" & Me.[InvNum] & "'"

Exagerated for clarity, that second line is

"Where [tbl 2 Job].InvNum = ' " & Me.[InvNum] & " ' "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Novice needs help please.

I am attempting to use code I found here (surrounded by messages
on/off)
to
delete the current record at subform and the related ones (in other,
sibling
subforms.) The error is:

-- syntax error (missing operator) in query expression '[tbl 2
Job].InvNum
=' --

My code is:
' run a delete query to delete the related records from the subform
DoCmd.RunSQL "DELETE [tbl 2 Job].* FROM [tbl 2 Job] Where [tbl 2 _
Job].InvNum = " & Me.[InvNum]

'line continuation is only for newsgroup.

' Delete current record on viewed form
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
 
G

Guest

Thanks. I'll give some details.

RIntegrity, One-one, cascade updates/deletes.

The delete button is on a form (Jobfrm) with a query as RSource. (Its place
on tab, but I think that's not an issue, right?)

The invoice form (a subform on tab0, beside the delete button) has an RS of
a table.

The related subforms are on other tabs with RS of table or query.

I have understood when tabs are used, they are transparent as to reference.
That each of my subforms though on a tab are really on the parent form --
JobFrm. Correct?

If so, and since all related with RIntegrity and cascade deletes, and the
related records are IN FACT DELETED, why do I get the previous mentioned
messaged -- counting one for each of the related subforms?

In short, all works fine except those pesky messages. And they don't appear
unless I try to close the form.


--
Thanks for your help,
Chris


Douglas J. Steele said:
Sorry, you're not giving enough details.

What the record source of the parent form and of the subform? Do you have
Referential Integrity set between the tables? If you've got a parent-child
relationship for the tables that make up the record source of the form and
subform (as is usual when using subforms), are you trying to delete from the
parent table or the child table?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Chris said:
Thanks, Doug. I missed that reference and when I correct it the record is
deleted. However, for each related table I get an error that I cannot
change
or add because one is required in a related table.

I tried a requery of the subform control (Invoicefrm) after I turn back on
the messages and I still get the above message.

How do I eliminate the message about EACH related table?

--
Thanks for your help,
Chris


Douglas J. Steele said:
To refer to a control on a subform, you need to use

Me!NameOfSubformControl.Form!InvNum

Note that depending on how you added the subform to the parent form, the
name of the subform control may be different than the name of the form
being
used as the subform: it's the name of the control on the parent form that
you need to use.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks for responding.

Yes, the field is number, currency type. I'm Access 2007.

When I hover over the VBA code that is highlighted, it shows value of
Me.InvNum is null. My command button to delete is on a form and the
subform
shows InvNum.

I tried setting the focus to the subform first -- same error.
I also tried Me.dirty=false -- same error.
I also tried setting above focus then Me.dirty=false -- same error.

Any ideas?

--
Thanks for your help,
Chris


:

You sure Me.[InvNum] is returning a value?

If it is, is the InvNum field text? If so, you need quotes:

DoCmd.RunSQL "DELETE [tbl 2 Job].* FROM [tbl 2 Job] " & _
"Where [tbl 2 Job].InvNum = '" & Me.[InvNum] & "'"

Exagerated for clarity, that second line is

"Where [tbl 2 Job].InvNum = ' " & Me.[InvNum] & " ' "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Novice needs help please.

I am attempting to use code I found here (surrounded by messages
on/off)
to
delete the current record at subform and the related ones (in other,
sibling
subforms.) The error is:

-- syntax error (missing operator) in query expression '[tbl 2
Job].InvNum
=' --

My code is:
' run a delete query to delete the related records from the subform
DoCmd.RunSQL "DELETE [tbl 2 Job].* FROM [tbl 2 Job] Where [tbl 2 _
Job].InvNum = " & Me.[InvNum]

'line continuation is only for newsgroup.

' Delete current record on viewed form
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
 
D

Douglas J. Steele

Sorry, I'm still not following.

Yes, the fact that a subform is on a tab doesn't matter: you're simply going
to refer to the subform control on the tab, with the name of the table not
entering into it.

Is the issue that you're deleting from the underlying recordsource of the
forms being used as subforms on the other tabs but the rows are still
showing on those subforms? (Since the rows are still on the form, Access
will try to add them, and you'll get the reported error due to RI issues) If
so, perhaps all you need is a requery of each of the subforms after your
deletion.

Me!NameOfSubformControl1.Form.Requery
Me!NameOfSubformControl2.Form.Requery

etc.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Chris said:
Thanks. I'll give some details.

RIntegrity, One-one, cascade updates/deletes.

The delete button is on a form (Jobfrm) with a query as RSource. (Its
place
on tab, but I think that's not an issue, right?)

The invoice form (a subform on tab0, beside the delete button) has an RS
of
a table.

The related subforms are on other tabs with RS of table or query.

I have understood when tabs are used, they are transparent as to
reference.
That each of my subforms though on a tab are really on the parent form --
JobFrm. Correct?

If so, and since all related with RIntegrity and cascade deletes, and the
related records are IN FACT DELETED, why do I get the previous mentioned
messaged -- counting one for each of the related subforms?

In short, all works fine except those pesky messages. And they don't
appear
unless I try to close the form.


--
Thanks for your help,
Chris


Douglas J. Steele said:
Sorry, you're not giving enough details.

What the record source of the parent form and of the subform? Do you have
Referential Integrity set between the tables? If you've got a
parent-child
relationship for the tables that make up the record source of the form
and
subform (as is usual when using subforms), are you trying to delete from
the
parent table or the child table?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Chris said:
Thanks, Doug. I missed that reference and when I correct it the record
is
deleted. However, for each related table I get an error that I cannot
change
or add because one is required in a related table.

I tried a requery of the subform control (Invoicefrm) after I turn back
on
the messages and I still get the above message.

How do I eliminate the message about EACH related table?

--
Thanks for your help,
Chris


:

To refer to a control on a subform, you need to use

Me!NameOfSubformControl.Form!InvNum

Note that depending on how you added the subform to the parent form,
the
name of the subform control may be different than the name of the form
being
used as the subform: it's the name of the control on the parent form
that
you need to use.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks for responding.

Yes, the field is number, currency type. I'm Access 2007.

When I hover over the VBA code that is highlighted, it shows value
of
Me.InvNum is null. My command button to delete is on a form and the
subform
shows InvNum.

I tried setting the focus to the subform first -- same error.
I also tried Me.dirty=false -- same error.
I also tried setting above focus then Me.dirty=false -- same error.

Any ideas?

--
Thanks for your help,
Chris


:

You sure Me.[InvNum] is returning a value?

If it is, is the InvNum field text? If so, you need quotes:

DoCmd.RunSQL "DELETE [tbl 2 Job].* FROM [tbl 2 Job] " & _
"Where [tbl 2 Job].InvNum = '" & Me.[InvNum] & "'"

Exagerated for clarity, that second line is

"Where [tbl 2 Job].InvNum = ' " & Me.[InvNum] & " ' "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Novice needs help please.

I am attempting to use code I found here (surrounded by messages
on/off)
to
delete the current record at subform and the related ones (in
other,
sibling
subforms.) The error is:

-- syntax error (missing operator) in query expression '[tbl 2
Job].InvNum
=' --

My code is:
' run a delete query to delete the related records from the
subform
DoCmd.RunSQL "DELETE [tbl 2 Job].* FROM [tbl 2 Job] Where [tbl 2
_
Job].InvNum = " & Me.[InvNum]

'line continuation is only for newsgroup.

' Delete current record on viewed form
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
 
Ad

Advertisements

G

Guest

The record being deleted does not show on sibling subforms.

The code works fine except when closing the form -- which is only done at
shutdown of Access. If I requery the sibling subform as you suggest, I am
returned a 3075 error. I am only requerying Invoicefrm, not the parent and
not sibling subforms.

I'd like to know why those messages occur after (seemingly) successfull
cleanup of related tables, and how to remedy. But if not, I can live with it
the way it is!

Thanks for your help,
Chris
 

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

Top