Buttons

[pm4698]

Hello there.I tried out the tutorial at
http://www.access-programmers.co.uk/...d.php?t=185430
that John Big Booty proposes.

In this form, i want to add 2 buttons:
Delete record button and Edit record button.
For the delete button i want to delete the record that is selected from the listbox (and i want this result to take place and at the record at the table) - perhaps with a warning message(not necessary).
For the edit button, i want to open a registration form i've made but i want the form to show the specific record that is chosen from the initial list box.

Any ideas?

Thanks in advance

 

[pbaldy]

For the edit button (presumes the listbox is single select):

http://www.baldyweb.com/wherecondition.htm

For the delete, something like:

CurrentDb.Execute "DELETE * FROM TableName WHERE KeyFieldName = " & Me.ListboxName

which also assumes the listbox is single select, and that the bound column is the appropriate key field. You can add a Yes/No message box to confirm the delete if you want.

 

[pm4698]

I am rookie so, sorry for the stupid question:

CurrentDb.Execute "DELETE * FROM TableName WHERE KeyFieldName = " & Me.ListboxName

which also assumes the listbox is single select, and that the bound column is the appropriate key field. You can add a Yes/No message box to confirm the delete if you want.

Where CurrentDb i write my database's name?
Do i have to replace the KeyFieldName with something else or its just a command?
My listbox is single select but from where can i see if the boudn column is the appropriate key field?
Note that the listbox reads from a querry and it shows 5 columns. So, i want to delete one of these records

 

[pbaldy]

No you don't change CurrentDb; change what's in red:

CurrentDb.Execute "DELETE * FROM TableName WHERE KeyFieldName = " & Me.ListboxName

The bound column is a property of the listbox, and should correlate to the key field in your table and "KeyFieldName" from the above.

 

[pm4698]

The edit button worked!Thank you very much!
The edit button worked!Thank you very much!

I found another code for delete button:

Private Sub delete_record_Click()
If edit_list.ListIndex > -1 And edit_list.Value <> "" Then
Dim response As Integer
response = MsgBox("Do you want to delete record?", vbYesNo, "Delete confirmation")
If response = vbYes Then
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM [table1] WHERE (([table1].[lastname] = " & edit_list.Column(2) & ")" & ")" & ";"
DoCmd.SetWarnings True
edit_list.Requery
Else
MsgBox "Delete canceled"
End If
Else
MsgBox "You did not choose record to delete"
End If
End Sub

where edit_list is a text frame where i show 4 fields of table 1 from a query
table1 is my table
lastname is the field name of the table which value will be the 2nd column of text frame

I get an error at this line:
DoCmd.RunSQL "DELETE * FROM [table1] WHERE (([table1].[lastname] = " & edit_list.Column(2) & ")" & ")" & ";"
Any ideas?

 

[pbaldy]

It would help if you said what the error was. For starters, get rid if the unnecessary concatenations. Since the value looks like text, try


CurrentDb.Execute "DELETE * FROM [table1] WHERE [lastname] = '" & edit_list.Column(1) & "'"

You mentioned the value was in the second column. The column property is zero based, so 1 is the second column.

 

[pm4698]

You were right about the column number.
Now its really working!

Thank you a lot for your time!

 

[pbaldy]

No problem, glad we got it working.