Delete Current Record from Unbound Form

[danitrin via AccessMonster.com]

I'm using the following code to delete a record from an unbound form. For
some reason the code does not work. Any suggestions?




Dim strSQL As String
Dim db As DAO.Database

If MsgBox("Are you sure you want to delete this record?", vbQuestion +
vbYesNo, "Delete Record") = vbYes Then




Set db = CurrentDb

strSQL = "DELETE * FROM tblablExamHistory WHERE [ablexamId] =me.
txtablexamID"
db.Execute strSQL
End If

 

[Dirk Goldgar]

I'm using the following code to delete a record from an unbound form. For
some reason the code does not work. Any suggestions?




Dim strSQL As String
Dim db As DAO.Database

If MsgBox("Are you sure you want to delete this record?", vbQuestion +
vbYesNo, "Delete Record") = vbYes Then




Set db = CurrentDb

strSQL = "DELETE * FROM tblablExamHistory WHERE [ablexamId] =me.
txtablexamID"
db.Execute strSQL
End If

You need to concatenate the value of Me.txtableexamID into the string:

strSQL = "DELETE * FROM tblablExamHistory WHERE [ablexamId] = " & _
Me.txtablexamID

If ablexamID is a text field, you'll also need to build in some quotes
around it.

 

[Marshall Barton]

I'm using the following code to delete a record from an unbound form. For
some reason the code does not work. Any suggestions?

Dim strSQL As String
Dim db As DAO.Database

If MsgBox("Are you sure you want to delete this record?", vbQuestion +
vbYesNo, "Delete Record") = vbYes Then

Set db = CurrentDb
strSQL = "DELETE * FROM tblablExamHistory WHERE [ablexamId] =me.
txtablexamID"
db.Execute strSQL
End If

You need to put the value of the text box in the SQL
statement:

strSQL = "DELETE * FROM tblablExamHistory WHERE
[ablexamId]=" & Me.txtablexamID

Access (not Jet) will resolve a full references for you:

strSQL = "DELETE * FROM tblablExamHistory WHERE
[ablexamId]=Forms!nameofform.txtablexamID"

BUT you would then have to use RunSQL to run the query
through Access. RunSQL is not a slightly more convenient
version of Execute in that Access processes RunSQL in an
asynchronous background task while VBA/Jet processes an
Execute synchronously (i.e. the VBA code will not proceed
until execution of the query completes). In addition,
Execute allows errors to be trapped via the dbFailOnError
argument (RunSQL displays warning dialog boxes) and Execute
can tell you how many records were successfully processed
via the db.RecordsAffected property.

 

[danitrin via AccessMonster.com]

I edited the code to reflect
strSQL = "DELETE * FROM tblablExamHistory WHERE [ablexamId]=" & Me.
txtablexamID

However this still does not work. It appears to but the record remains. I'm
not clear on the last part of your post, I assume that is what is keeping
this from working. Can you please elaborate? Thanks

 

[Marshall Barton]

I edited the code to reflect
strSQL = "DELETE * FROM tblablExamHistory WHERE [ablexamId]=" & Me.
txtablexamID

However this still does not work. It appears to but the record remains. I'm
not clear on the last part of your post, I assume that is what is keeping
this from working. Can you please elaborate?

If you have no idea what I was blathering on about, just
ignore it. In this case, it has nothing to do with your
your specific question.

If your ablexamId field is a Text field, then you need to
quote the value:

strSQL = "DELETE * FROM tblablExamHistory WHERE
[ablexamId]=""" & Me.txtablexamID & """"

If you don't understand why it takes so many quote marks.
post back. One unnecessary explanation per question is
enough ;-)

 

[danitrin via AccessMonster.com]

Thank you! I think this might work if I can figure out why it's giving me a
data type mismatch error

 

[Marshall Barton]

Thank you! I think this might work if I can figure out why it's giving me a
data type mismatch error

I don't think so. That error means the field is one type
and the value it's compared to is a different type.

I was only guessing because you never di say what the
field's data type is in its table. At first I assumed that
a field with ID in its name was a number type field, but you
said that didn't work, so I then guessed that it might be a
Text field.

Back to assuming it's a number type, the problem appears to
be to figure out why the record is not being deleted. Are
you certain that the value in the ablexamId field matches
the value in the text box? Is there a recordset or
something else that has latched onto that record?

 

[danitrin via AccessMonster.com]

ablexamid is a number(long integer) and txtablexamid is set as a general
number. As far as the recordset goes...not that I am aware of. Unfortuantly
this is an inherited db, so I'm still trying to figure out what the previous
person did.

 

[Marshall Barton]

ablexamid is a number(long integer) and txtablexamid is set as a general
number. As far as the recordset goes...not that I am aware of. Unfortuantly
this is an inherited db, so I'm still trying to figure out what the previous
person did.

Then first thing I would do at this point would be to add
the line:
Debug.Print strSQL
right before the Execute line. Then check the Immediate
window to make sure the query is correct and has the right
field value. You could then use Copy/Paste of the SQL
statement into a new query and see if it runs correctly from
the query design window (with the form still in the same
state). If it doesn't delete the record, maybe(?) you will
get a useful error message.

If all else fails, you will have a lot of digging with
breakpoints and other debugging aids to track down whatever
is getting in the way.

 

[danitrin via AccessMonster.com]

That didn't do a thing for me. Thanks though, I'll keep digging. It's odd
though, when I mouse over the db.execute strsql, it shows the correct value.
If I use strSQL = "DELETE * FROM tblablExamHistory WHERE [ablexamId]=" & Me.
txtablexamID, it will show deleted on the form, but it dosen't delete from
the table.

 

[Marshall Barton]

That didn't do a thing for me. Thanks though, I'll keep digging. It's odd
though, when I mouse over the db.execute strsql, it shows the correct value.
If I use strSQL = "DELETE * FROM tblablExamHistory WHERE [ablexamId]=" & Me.
txtablexamID, it will show deleted on the form, but it dosen't delete from
the table.

I have never seen the likes of that before and an at a loss
to explain how that could happen.

 

[Marshall Barton]

That didn't do a thing for me. Thanks though, I'll keep digging. It's odd
though, when I mouse over the db.execute strsql, it shows the correct value.
If I use strSQL = "DELETE * FROM tblablExamHistory WHERE [ablexamId]=" & Me.
txtablexamID, it will show deleted on the form, but it dosen't delete from
the table.

Wait a minute there. How can an unbound form show "deleted"
for the record???

 

[danitrin via AccessMonster.com]

Let me start at the beginning (sorry should have done that in the first place)
There is a form in which you select your client from a drop down box.
THis populates a list box on the same form with Exam dates. Once you select
your exam date you are taken to the unbound form I'm having issues with.
When I used the code mentioned in the previous post to delete the record, the
unbound form closes and shows the 1st form. The 1st form shows that record
as #deleted. However, when I close the 1st form and then go back into it,
the record reappears.

 

[Mike Painter]

Let me start at the beginning (sorry should have done that in the
first place) There is a form in which you select your client from a
drop down box.
THis populates a list box on the same form with Exam dates. Once you
select your exam date you are taken to the unbound form I'm having
issues with.
When I used the code mentioned in the previous post to delete the
record, the unbound form closes and shows the 1st form. The 1st form
shows that record as #deleted. However, when I close the 1st form
and then go back into it, the record reappears.

This sounds awkward and I'm betting that at some point after deleting the
record you move from the form that still has the information in it and that
causes it to save what you have just deleted.

 

[Marshall Barton]

Let me start at the beginning (sorry should have done that in the first place)
There is a form in which you select your client from a drop down box.
THis populates a list box on the same form with Exam dates. Once you select
your exam date you are taken to the unbound form I'm having issues with.
When I used the code mentioned in the previous post to delete the record, the
unbound form closes and shows the 1st form. The 1st form shows that record
as #deleted. However, when I close the 1st form and then go back into it,
the record reappears.

I'm with Mike. Having the another form bound to the same
table is most likely interfering some way, some how.

Maybe you can find a way to delete the record through the
form that is bound to the table. Just speculating, but, if
the record you want to delete is the current record, try
something like:

Forms!theboundform.Recordset.Delete

I guess I should ask, if the form only has a combo box, a
list box and a button, why is the form is bound?

 

[danitrin via AccessMonster.com]

The combo box is on an unbound form but the listbox is a bound subform
implanted into the other. I didn't design this stupid thing . I'll try
your code and see how that goes.

 

[danitrin via AccessMonster.com]

I think I have figured out what is going on. This db is a a front end that
upon revisiting the 1st unbound form with the listbox, data from the backend
is refreshed. So, the data is being deleted in the table in the front end,
but not on the backend. It reappears in the list when the form is closed and
then reopened and refreshed. How do I get it to delete from the backend as
well?

 

[Marshall Barton]

I think I have figured out what is going on. This db is a a front end that
upon revisiting the 1st unbound form with the listbox, data from the backend
is refreshed. So, the data is being deleted in the table in the front end,
but not on the backend. It reappears in the list when the form is closed and
then reopened and refreshed. How do I get it to delete from the backend as
well?

What table in the front end?? Are you saying that there are
two tables in some strange kind of arrangement of duplicate
copies of all the records? What kind of coordinating
procedure is used to synchronize the records between the two
tables? Maybe you need to execute the delete query for
each table or maybe the sync procedure needs to be made to
deal with deleted records??

 

[danitrin via AccessMonster.com]

The front end table is called tblABLEXamHis, it is named the same in the back
end (the data is exactly the same in each table). Apparently it was coded
that anytime a form is opened it uses GETSHAREDATA to pull the data from the
tables in the backend.

 

[Marshall Barton]

The front end table is called tblABLEXamHis, it is named the same in the back
end (the data is exactly the same in each table). Apparently it was coded
that anytime a form is opened it uses GETSHAREDATA to pull the data from the
tables in the backend.

Whatever GETSHAREDATA is, it is beyond my experience. Maybe
there is some kind of PUTSHAREDATA that you can use to keep
things in sync????

If you can describe what the data store arrangement is and
how you are using it, I suggest that you start a new thread
that focuses on that with the delete problem as an issue.
Posting it to a forum more appropriate to how your data is
accessed would probably raise the chance of finding someone
familiar with your arrangement and how you should deal with
the delete issue.