Deleting a record from a form

D

DanRoy

I have a table with an autonumber field as a key. I linkeda form to all of
the fields in this table. In the footer section of the form I added text
boxes in the same qty as i have fields. When ever the user clicks on a
displayed row in the "details" section of the form, the record has its field
values copied to the footer section. This provides the user with a visual
queue of the record selected.

The user can do 3 things with the selected record. (1) He can modify any
field value contained in the text box and save the record,(2) he can provide
data in a field which normally would not have data and save the change, (3)
or he can delete the record (presumably he made a mistake in adding).

I have completed parts 1 and 2, but am having trouble with part 3.

In the details section, the fields are linked via the control source
property of the textbox. In the footer section, text13 hold the value from
the ID field for the row selected.

My Delete SQL is:
Private Sub Toggle24_Click()
Dim StrSQL As String
StrSQL = "Delete * from [MBOM_EXCEPTION_For_Task_List] where
[MBOM_EXCEPTION_For_Task_List].[ID] = '" & Text13.Value & "'"
Label26.Caption = StrSQL
CurrentDb.Execute StrSQL, dbFailOnError
End Sub

But it is giving me an error " Run-time error 3464 Data Type Mismatch in
criteria expression.
I print the strsql string to a label on my form and the value for
text13.value is displayed '21' with single quotes around the number. Does
this signify a string? Do i have to perform a val(Text13.value)?

Can anyone provide any help?
Thanks
 
D

Dirk Goldgar

DanRoy said:
I have a table with an autonumber field as a key. I linkeda form to all of
the fields in this table. In the footer section of the form I added text
boxes in the same qty as i have fields. When ever the user clicks on a
displayed row in the "details" section of the form, the record has its
field
values copied to the footer section. This provides the user with a visual
queue of the record selected.

The user can do 3 things with the selected record. (1) He can modify any
field value contained in the text box and save the record,(2) he can
provide
data in a field which normally would not have data and save the change,
(3)
or he can delete the record (presumably he made a mistake in adding).

I have completed parts 1 and 2, but am having trouble with part 3.

In the details section, the fields are linked via the control source
property of the textbox. In the footer section, text13 hold the value
from
the ID field for the row selected.

My Delete SQL is:
Private Sub Toggle24_Click()
Dim StrSQL As String
StrSQL = "Delete * from [MBOM_EXCEPTION_For_Task_List] where
[MBOM_EXCEPTION_For_Task_List].[ID] = '" & Text13.Value & "'"
Label26.Caption = StrSQL
CurrentDb.Execute StrSQL, dbFailOnError
End Sub

But it is giving me an error " Run-time error 3464 Data Type Mismatch in
criteria expression.
I print the strsql string to a label on my form and the value for
text13.value is displayed '21' with single quotes around the number.
Does
this signify a string?
Yes.

Do i have to perform a val(Text13.value)?
No.

Can anyone provide any help?

Maybe. <g>

It's your own code that is putting the single-quotes around the ID value.
You just have to change the line that builds the QL string so that it
doesn't do that. Make it like this:

StrSQL = _
"Delete * from [MBOM_EXCEPTION_For_Task_List] " & _
"where [MBOM_EXCEPTION_For_Task_List].[ID] = " & Me.Text13
 

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