Delete lesson help

[Stewart]

I'm trying to to delete a single record.
Using the following:

----code-------
SaleNo As Long
strID As String
SaleNo = 4
strID = 23

Dim db As Database, RS As Recordset
Set db = DBEngine.Workspaces(0).Databases(0)
Set RS = db.OpenRecordset("tblTestdata")
RS.FindFirst "[SaleID] = ' SaleNo" And "[VendorID] = ' strID"
'RS.FindFirst"[SaleID] = 4 AND "[VendorID] = 23 'TM error also
'RS![BitID] = SaleNo
'RS![RecipientID] = strID
RS.Edit
RS.Delete
RS.Update
RS.Close
DoEvents
DoCmd.Requery "lstList41"

---end code----

But i'm getting a "Type mismatch" error.
The data does exist in the table, only 6 records.

Any suggestions appreciated

 

[John Spencer]

Probably with a recordset, your code should be more like

SaleNo As Long
strID As String
SaleNo = 4
strID = 23

Dim db As Database, RS As Recordset
Set db = DBEngine.Workspaces(0).Databases(0)
Set RS = db.OpenRecordset("tblTestdata")
RS.FindFirst "[SaleID] = " & SaleNo & " And [VendorID] = '" & strID & "'"
If RS.NoMatch = False then

RS.Delete
End IF

RS.Close
DoEvents
DoCmd.Requery "lstList41"

---end code----


But why are you doing this with a recordset when you could use a delete
query and a simpler construction.

Dim StrSQL as String
Dim db as Database
Set db = DBEngine.Workspaces(0).Databases(0)

StrSQL = "DELETE * FROM TblTestData WHERE [SaleID] = " & SaleNo & " And
[VendorID] = '" & strID & "'"
db.Execute StrSQL, DbFailOnError

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Allen Browne]

Instead of opening a recordset, how about executing a Delete query to get
rid of the record?

Dim strSql as String
SaleNo As Long
strID As String
SaleNo = 4
strID = "23"
strSql = "DELETE FROM tblTestData WHERE ([SaleID] = " & _
SaleNo & ") AND ([VendorID] = """ & strID & """);"
dbEngine(0)(0).Execute strSql, dbFailOnError
'Debug.Print dbEngine(0)(0).RecordsAffected & " deleted."

The example assumes that if you open tblTestData in design view, SaleID is a
Number field, and VendorID is a Text field. If they are both Number fields,
drop the extra quotes. If they are both Text fields, add the extra quotes
for SaleID as well. The extra quotes are explained here:
http://allenbrowne.com/casu-17.html

 

[Dirk Goldgar]

In

I'm trying to to delete a single record.
Using the following:

----code-------
SaleNo As Long
strID As String
SaleNo = 4
strID = 23

Dim db As Database, RS As Recordset
Set db = DBEngine.Workspaces(0).Databases(0)
Set RS = db.OpenRecordset("tblTestdata")
RS.FindFirst "[SaleID] = ' SaleNo" And "[VendorID] = ' strID"
'RS.FindFirst"[SaleID] = 4 AND "[VendorID] = 23 'TM error also
'RS![BitID] = SaleNo
'RS![RecipientID] = strID
RS.Edit
RS.Delete
RS.Update
RS.Close
DoEvents
DoCmd.Requery "lstList41"

---end code----

But i'm getting a "Type mismatch" error.
The data does exist in the table, only 6 records.

Any suggestions appreciated

I agree with the suggestions of John Spencer and Allen Browne, that
executing a delete query would be simpler and faster than using a
recordset. If using a recordset, though, John's revised recordset code
looks better than your original. However, depending on your Access
version you may still need to specify that it's a DAO recordset, not an
ADO recordset, by declaring it with

Dim db As Database, RS As DAO.Recordset

If you have both the DAO and ADODB libraries referenced, you need to
make sure which type of recordset is being declared, or you're likely to
get a type mismatch error.

 

[Stewart]

Ok

I'm trying the Delete Query, much simpler. (I thought I tried a delete query
in the past and it didn't remove the empty row and ID key).

It is giving me a "Cannot find table or input query" error now though.
That's more than I had before.

I had to add the:
Set db = OpenDatabase("C:\Accounts\TestData.mdb")
To your example, sound correct?

Probably with a recordset, your code should be more like

SaleNo As Long
strID As String
SaleNo = 4
strID = 23

Dim db As Database, RS As Recordset
Set db = DBEngine.Workspaces(0).Databases(0)
Set RS = db.OpenRecordset("tblTestdata")
RS.FindFirst "[SaleID] = " & SaleNo & " And [VendorID] = '" & strID & "'"
If RS.NoMatch = False then

RS.Delete
End IF

RS.Close
DoEvents
DoCmd.Requery "lstList41"

---end code----


But why are you doing this with a recordset when you could use a delete
query and a simpler construction.

Dim StrSQL as String
Dim db as Database
Set db = DBEngine.Workspaces(0).Databases(0)

StrSQL = "DELETE * FROM TblTestData WHERE [SaleID] = " & SaleNo & " And
[VendorID] = '" & strID & "'"
db.Execute StrSQL, DbFailOnError

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I'm trying to to delete a single record.
Using the following:

----code-------
SaleNo As Long
strID As String
SaleNo = 4
strID = 23

Dim db As Database, RS As Recordset
Set db = DBEngine.Workspaces(0).Databases(0)
Set RS = db.OpenRecordset("tblTestdata")
RS.FindFirst "[SaleID] = ' SaleNo" And "[VendorID] = ' strID"
'RS.FindFirst"[SaleID] = 4 AND "[VendorID] = 23 'TM error also
'RS![BitID] = SaleNo
'RS![RecipientID] = strID
RS.Edit
RS.Delete
RS.Update
RS.Close
DoEvents
DoCmd.Requery "lstList41"

---end code----

But i'm getting a "Type mismatch" error.
The data does exist in the table, only 6 records.

Any suggestions appreciated

 

[Stewart]

In

I'm trying to to delete a single record.
Using the following:

----code-------
SaleNo As Long
strID As String
SaleNo = 4
strID = 23

Dim db As Database, RS As Recordset
Set db = DBEngine.Workspaces(0).Databases(0)
Set RS = db.OpenRecordset("tblTestdata")
RS.FindFirst "[SaleID] = ' SaleNo" And "[VendorID] = ' strID"
'RS.FindFirst"[SaleID] = 4 AND "[VendorID] = 23 'TM error also
'RS![BitID] = SaleNo
'RS![RecipientID] = strID
RS.Edit
RS.Delete
RS.Update
RS.Close
DoEvents
DoCmd.Requery "lstList41"

---end code----

But i'm getting a "Type mismatch" error.
The data does exist in the table, only 6 records.

Any suggestions appreciated

I agree with the suggestions of John Spencer and Allen Browne, that
executing a delete query would be simpler and faster than using a
recordset. If using a recordset, though, John's revised recordset code
looks better than your original. However, depending on your Access
version you may still need to specify that it's a DAO recordset, not an
ADO recordset, by declaring it with

Dim db As Database, RS As DAO.Recordset

If you have both the DAO and ADODB libraries referenced, you need to
make sure which type of recordset is being declared, or you're likely to
get a type mismatch error.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Wow

Its working, with numbers (4, 5, etc) in place of my variables - no error.

With variables error:
"The Microsoft Jet database engine cannot find the input table or query
'False'. Make sure it exists and the name is spelled correctly."

One of my variables is a Long and one is a String - i suspect this is a
problem.

 

[Stewart]

In

I'm trying to to delete a single record.
Using the following:

----code-------
SaleNo As Long
strID As String
SaleNo = 4
strID = 23

Dim db As Database, RS As Recordset
Set db = DBEngine.Workspaces(0).Databases(0)
Set RS = db.OpenRecordset("tblTestdata")
RS.FindFirst "[SaleID] = ' SaleNo" And "[VendorID] = ' strID"
'RS.FindFirst"[SaleID] = 4 AND "[VendorID] = 23 'TM error also
'RS![BitID] = SaleNo
'RS![RecipientID] = strID
RS.Edit
RS.Delete
RS.Update
RS.Close
DoEvents
DoCmd.Requery "lstList41"

---end code----

But i'm getting a "Type mismatch" error.
The data does exist in the table, only 6 records.

Any suggestions appreciated

I agree with the suggestions of John Spencer and Allen Browne, that
executing a delete query would be simpler and faster than using a
recordset. If using a recordset, though, John's revised recordset code
looks better than your original. However, depending on your Access
version you may still need to specify that it's a DAO recordset, not an
ADO recordset, by declaring it with

Dim db As Database, RS As DAO.Recordset

If you have both the DAO and ADODB libraries referenced, you need to
make sure which type of recordset is being declared, or you're likely to
get a type mismatch error.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Wow

Its working, with numbers (4, 5, etc) in place of my variables - no error.

With variables error:
"The Microsoft Jet database engine cannot find the input table or query
'False'. Make sure it exists and the name is spelled correctly."

One of my variables is a Long and one is a String - i suspect this is a
problem.

Its fully working with variables, it was just a syntax issue:

strSQL = "DELETE * FROM tblTestdata WHERE [SaleID] = " & SaleNo & " And
[VendorID] = " & strID & ""

Thanks for your time and shared talents

 

[Stewart]

In I'm trying to to delete a single record.
Using the following:

----code-------
SaleNo As Long
strID As String
SaleNo = 4
strID = 23

Dim db As Database, RS As Recordset
Set db = DBEngine.Workspaces(0).Databases(0)
Set RS = db.OpenRecordset("tblTestdata")
RS.FindFirst "[SaleID] = ' SaleNo" And "[VendorID] = ' strID"
'RS.FindFirst"[SaleID] = 4 AND "[VendorID] = 23 'TM error also
'RS![BitID] = SaleNo
'RS![RecipientID] = strID
RS.Edit
RS.Delete
RS.Update
RS.Close
DoEvents
DoCmd.Requery "lstList41"

---end code----

But i'm getting a "Type mismatch" error.
The data does exist in the table, only 6 records.

Any suggestions appreciated

I agree with the suggestions of John Spencer and Allen Browne, that
executing a delete query would be simpler and faster than using a
recordset. If using a recordset, though, John's revised recordset code
looks better than your original. However, depending on your Access
version you may still need to specify that it's a DAO recordset, not an
ADO recordset, by declaring it with

Dim db As Database, RS As DAO.Recordset

If you have both the DAO and ADODB libraries referenced, you need to
make sure which type of recordset is being declared, or you're likely to
get a type mismatch error.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Wow

Its working, with numbers (4, 5, etc) in place of my variables - no error.

With variables error:
"The Microsoft Jet database engine cannot find the input table or query
'False'. Make sure it exists and the name is spelled correctly."

One of my variables is a Long and one is a String - i suspect this is a
problem.

Its fully working with variables, it was just a syntax issue:

strSQL = "DELETE * FROM tblTestdata WHERE [SaleID] = " & SaleNo & " And
[VendorID] = " & strID & ""

Thanks for your time and shared talents

Can you make a suggestion on on syntax relating to this?

Using a string variable named RecipID
strSQL = "DELETE * FROM tblPageLog WHERE [RecipID] = '4'" 'Works perfect!

But I just can't get the syntax correct to use the variable -- that I really
need.

Any suggestions.

 

[Allen Browne]

Assuming RecipID is a Text field (not a Number field when you open the table
in design view):

strSQL = "DELETE * FROM tblPageLog WHERE [RecipID] = """ & RecipID & """;"

 

[Stewart]

Assuming RecipID is a Text field (not a Number field when you open the table
in design view):

strSQL = "DELETE * FROM tblPageLog WHERE [RecipID] = """ & RecipID & """;"

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Can you make a suggestion on on syntax relating to this?

Using a string variable named RecipID
strSQL = "DELETE * FROM tblPageLog WHERE [RecipID] = '4'" 'Works perfect!

But I just can't get the syntax correct to use the variable -- that I
really
need.

[RecipID] is the field in the table. strIN is a variable i'm using. So I'm
going to try:
strSQL = "DELETE * FROM tblPageLog WHERE [RecipID] = """ & strIN& """;"

It Worked!!!!

Thank you again.

I believe this is traditionally why I have avoided using sql query strings,
because the syntax is so nebulous to me. I can't find any guide, or
critical instructional info on syntax & variables. Any suggestions?

 

[Allen Browne]

The core idea is simple. Use:
- # as a delimiter around literal dates in the query, e.g.:
WHERE [MyDate] = #1/1/2007#
- quote marks as the delimiter around literal text in the query, e.g.:
WHERE [City] = "New York"

Do not use any delimiter around literal values applied against a Number
field, e.g.:
WHERE [Amount] = 999

The tricky part comes when you try to put quotes inside quotes. For an
explanation of how to do this, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

If you live in a country that uses non-US date formats, there's a couple of
tricks to getting the dates right too. Details in:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Assuming RecipID is a Text field (not a Number field when you open the table
in design view):

strSQL = "DELETE * FROM tblPageLog WHERE [RecipID] = """ & RecipID &
""";"

Can you make a suggestion on on syntax relating to this?

Using a string variable named RecipID
strSQL = "DELETE * FROM tblPageLog WHERE [RecipID] = '4'" 'Works perfect!

But I just can't get the syntax correct to use the variable -- that I
really
need.

[RecipID] is the field in the table. strIN is a variable i'm using. So
I'm
going to try:
strSQL = "DELETE * FROM tblPageLog WHERE [RecipID] = """ & strIN& """;"

It Worked!!!!

Thank you again.

I believe this is traditionally why I have avoided using sql query
strings,
because the syntax is so nebulous to me. I can't find any guide, or
critical instructional info on syntax & variables. Any suggestions?

 

[Stewart]

Thank you Allen.

The core idea is simple. Use:
- # as a delimiter around literal dates in the query, e.g.:
WHERE [MyDate] = #1/1/2007#
- quote marks as the delimiter around literal text in the query, e.g.:
WHERE [City] = "New York"

Do not use any delimiter around literal values applied against a Number
field, e.g.:
WHERE [Amount] = 999

The tricky part comes when you try to put quotes inside quotes. For an
explanation of how to do this, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

If you live in a country that uses non-US date formats, there's a couple of
tricks to getting the dates right too. Details in:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Assuming RecipID is a Text field (not a Number field when you open the table
in design view):

strSQL = "DELETE * FROM tblPageLog WHERE [RecipID] = """ & RecipID &
""";"

Can you make a suggestion on on syntax relating to this?

Using a string variable named RecipID
strSQL = "DELETE * FROM tblPageLog WHERE [RecipID] = '4'" 'Works perfect!

But I just can't get the syntax correct to use the variable -- that I
really
need.

[RecipID] is the field in the table. strIN is a variable i'm using. So
I'm
going to try:
strSQL = "DELETE * FROM tblPageLog WHERE [RecipID] = """ & strIN& """;"

It Worked!!!!

Thank you again.

I believe this is traditionally why I have avoided using sql query
strings,
because the syntax is so nebulous to me. I can't find any guide, or
critical instructional info on syntax & variables. Any suggestions?

 

[Stewart]

One more on this same thread...

I had to change a Text field of a table to Numeric Long,(and form variable
to Long)now I get invalid data type when using this syntax::
(Dim RecID As Long)
strSQL = "DELETE * FROM tblPageLog WHERE [RecipID] = """ & RecID & """;"

But,
strSQL = "DELETE * FROM tblPageLog WHERE [RecipID] = 7
Works fine.

When I view the first incorrect syntax in a MsgBox, the 0ne-digit value of
RecID has quotes, "7". I can't get rid of the quotes.

Do you have any links for variable syntax in sql criteria?

The core idea is simple. Use:
- # as a delimiter around literal dates in the query, e.g.:
WHERE [MyDate] = #1/1/2007#
- quote marks as the delimiter around literal text in the query, e.g.:
WHERE [City] = "New York"

Do not use any delimiter around literal values applied against a Number
field, e.g.:
WHERE [Amount] = 999

The tricky part comes when you try to put quotes inside quotes. For an
explanation of how to do this, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

If you live in a country that uses non-US date formats, there's a couple of
tricks to getting the dates right too. Details in:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Assuming RecipID is a Text field (not a Number field when you open the table
in design view):

strSQL = "DELETE * FROM tblPageLog WHERE [RecipID] = """ & RecipID &
""";"

Can you make a suggestion on on syntax relating to this?

Using a string variable named RecipID
strSQL = "DELETE * FROM tblPageLog WHERE [RecipID] = '4'" 'Works perfect!

But I just can't get the syntax correct to use the variable -- that I
really
need.

[RecipID] is the field in the table. strIN is a variable i'm using. So
I'm
going to try:
strSQL = "DELETE * FROM tblPageLog WHERE [RecipID] = """ & strIN& """;"

It Worked!!!!

Thank you again.

I believe this is traditionally why I have avoided using sql query
strings,
because the syntax is so nebulous to me. I can't find any guide, or
critical instructional info on syntax & variables. Any suggestions?

 

[Stewart]

I solved it!

strSQL = "DELETE * FROM tblPageLog WHERE [RecipID] = " & RecID

One more on this same thread...

I had to change a Text field of a table to Numeric Long,(and form variable
to Long)now I get invalid data type when using this syntax::
(Dim RecID As Long)
strSQL = "DELETE * FROM tblPageLog WHERE [RecipID] = """ & RecID & """;"

But,
strSQL = "DELETE * FROM tblPageLog WHERE [RecipID] = 7
Works fine.

When I view the first incorrect syntax in a MsgBox, the 0ne-digit value of
RecID has quotes, "7". I can't get rid of the quotes.

Do you have any links for variable syntax in sql criteria?

The core idea is simple. Use:
- # as a delimiter around literal dates in the query, e.g.:
WHERE [MyDate] = #1/1/2007#
- quote marks as the delimiter around literal text in the query, e.g.:
WHERE [City] = "New York"

Do not use any delimiter around literal values applied against a Number
field, e.g.:
WHERE [Amount] = 999

The tricky part comes when you try to put quotes inside quotes. For an
explanation of how to do this, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

If you live in a country that uses non-US date formats, there's a couple of
tricks to getting the dates right too. Details in:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Assuming RecipID is a Text field (not a Number field when you open the
table
in design view):

strSQL = "DELETE * FROM tblPageLog WHERE [RecipID] = """ & RecipID &
""";"

Can you make a suggestion on on syntax relating to this?

Using a string variable named RecipID
strSQL = "DELETE * FROM tblPageLog WHERE [RecipID] = '4'" 'Works
perfect!

But I just can't get the syntax correct to use the variable -- that I
really
need.


[RecipID] is the field in the table. strIN is a variable i'm using. So
I'm
going to try:
strSQL = "DELETE * FROM tblPageLog WHERE [RecipID] = """ & strIN& """;"

It Worked!!!!

Thank you again.

I believe this is traditionally why I have avoided using sql query
strings,
because the syntax is so nebulous to me. I can't find any guide, or
critical instructional info on syntax & variables. Any suggestions?