List box item delete,edit, and new. (1 Viewer)
- Thread starter syntaktik
- Start date
if you don't know how to use listbox why do you think listbox is the correct way to do what you want.
listbox (and combox) are mostly used to select a value from one table to put in another table.
let's say you want to set an appointment with a doctor - you use the list box to select the doctor you want (from an existing list of doctors), and the other fields to select the date and time.
listbox (and combox) are mostly used to select a value from one table to put in another table.
let's say you want to set an appointment with a doctor - you use the list box to select the doctor you want (from an existing list of doctors), and the other fields to select the date and time.
I'm not sure, at least i think a list box would do in this situation. I have a list of events, and i want an easy way to add or remove them. Maybe this picture could help:
Is there a way to make those buttons work somehow?
Is there a way to make those buttons work somehow?
Last edited:
missinglinq
AWF VIP
- Local time
- Yesterday, 20:42
- Joined
- Jun 20, 2003
- Messages
- 6,423
I think we still need to know how you plan to use the listbox.
Also, what version of Access are you running? How you do these things to listbox items is version dependent.
Also, what version of Access are you running? How you do these things to listbox items is version dependent.
ok. What i'm using is Office 2007.
and I want the list box to list all of the events from another table. Then, if i click one of the events, and hit the delete button, the selected event gets deleted from the list. Same with the edit button, but rather opens another form which allows me to edit it. I also use that to add events as seen here:
i hope i'm not asking for too much haha
and I want the list box to list all of the events from another table. Then, if i click one of the events, and hit the delete button, the selected event gets deleted from the list. Same with the edit button, but rather opens another form which allows me to edit it. I also use that to add events as seen here:
i hope i'm not asking for too much haha
Last edited:
if you want to pop up another windows to add/Edit your data you can use the ListBox.
using the Add event (and also Edit) directly on the table as seems you do is not advised. even if the user click cancel the records will be added/changed.
you better use a temp table and if the user click OK use a query to add the record from the temp table (Append Query) to your main table, or to change the record in the main table (Edit Query). you will need to fill this table before editing a record.
for delete you can use a delete query. don't forget to make sure the user didn't click by mistake
you can still use buttons to control records on forms. I never said don't use buttons.
using the Add event (and also Edit) directly on the table as seems you do is not advised. even if the user click cancel the records will be added/changed.
you better use a temp table and if the user click OK use a query to add the record from the temp table (Append Query) to your main table, or to change the record in the main table (Edit Query). you will need to fill this table before editing a record.
for delete you can use a delete query. don't forget to make sure the user didn't click by mistake
you can still use buttons to control records on forms. I never said don't use buttons.
Last edited:
ho... you want me to write you the application
what you need to do is create a Temp table identical to your main one, with only the index key not being a key and not an auto number.
(I guess you used an auto numbering for your main index key)
you need few more querys, that you can create in SQL code or in the query builer:
1. delete query that will clear the temp table.
2. append query that will take the records from your Temp table and add them to your main table. add all fields but not the main index, it will added automaticely.
3. append query that will take the selected record on your form and add it to the Temp table, add all fields including the index key.
4. update query that will update the fields on the main table to the data in the Temp one. put both ables in the query and join by the index key, to ensure updating only this record.
5. delete query that will delete the record you select on the main form from the main table.
you can use the same form for edit/new records limiting it to the current record only.
link this form to the Temp table.
for adding a new record you do:
1. run query no.1
2. load the form
3. if OK button cliked:
4. run query no.2
for editing
1. run query no.1
2. run query no.3
2. load the form
3. if OK button cliked:
4. run query no.4
for deleting:
1. load a msgbox with the YesNo options
2. if Yes button clicked:
3. run query no.5
what you need to do is create a Temp table identical to your main one, with only the index key not being a key and not an auto number.
(I guess you used an auto numbering for your main index key)
you need few more querys, that you can create in SQL code or in the query builer:
1. delete query that will clear the temp table.
2. append query that will take the records from your Temp table and add them to your main table. add all fields but not the main index, it will added automaticely.
3. append query that will take the selected record on your form and add it to the Temp table, add all fields including the index key.
4. update query that will update the fields on the main table to the data in the Temp one. put both ables in the query and join by the index key, to ensure updating only this record.
5. delete query that will delete the record you select on the main form from the main table.
you can use the same form for edit/new records limiting it to the current record only.
link this form to the Temp table.
for adding a new record you do:
1. run query no.1
2. load the form
3. if OK button cliked:
4. run query no.2
for editing
1. run query no.1
2. run query no.3
2. load the form
3. if OK button cliked:
4. run query no.4
for deleting:
1. load a msgbox with the YesNo options
2. if Yes button clicked:
3. run query no.5
hey guys. I'm sorry for being a bit stubborn, but i did not really want to use 5 different queries for this simple form, and i did a bit more searching. I found some code that works perfect, except I get error messages on the delete, how do I get rid of them?
here is the code:
and how do use that to edit the selected item maybe?
here is the code:
Code:
Private Sub DeleteRecord_Click()
Dim strSQL As String
strSQL = "DELETE [EventName] FROM tblEvents WHERE " & _
"tblEvents.[EventName] = '" & Me![EventList] & "'"
DoCmd.RunSQL strSQL
Me![EventList].Requery ' requery the list
End Suband how do use that to edit the selected item maybe?
Last edited:
boblarson
Smeghead
- Local time
- Yesterday, 17:42
- Joined
- Jan 12, 2001
- Messages
- 32,059
Try changing this:
DoCmd.RunSQL strSQL
to this:
CurrentDb.Execute strSQL, dbFailOnError
That did the trick, thanks.
you need all these querys only of you take my advise to not work on the main table, and use a Temp one.
as I explained before - if you work directly on the main table closing the Add/Edit box (even if used the close on top right !!!) will save the record.
mostly having more queries can save you a lot of headache later finding currupted data.
trust me - I've beeing there, done that.
the code you use IS a query. query can be made in the query builder or run as code.
as I explained before - if you work directly on the main table closing the Add/Edit box (even if used the close on top right !!!) will save the record.
mostly having more queries can save you a lot of headache later finding currupted data.
trust me - I've beeing there, done that.
the code you use IS a query. query can be made in the query builder or run as code.
@Smig: Interesting insight, but as far as my experience goes I don’t think I am that far yet. I will try to do it this way till I’m done with the database. Afterwards I might go and change it towards your method.
But otherwise, how do i get the edit button to work? I tried adding a line to the previous code, and using the query as a filter for the event name with this:
But it does not seem to do anything... Any clues?
But otherwise, how do i get the edit button to work? I tried adding a line to the previous code, and using the query as a filter for the event name with this:
Code:
DoCmd.OpenForm "frmAddEvent", acNormal, "EventName = " & strSQL, , acFormEditboblarson
Smeghead
- Local time
- Yesterday, 17:42
- Joined
- Jan 12, 2001
- Messages
- 32,059
Yeah, you have your commas in the wrong places:
DoCmd.OpenForm "frmAddEvent", acNormal, , "EventName = " & strSQL, acFormEdit
Well... I am still not sure, sorry.
But this is what I got:
But when i run it I just get a runtime error '3075'.
But this is what I got:
Code:
Private Sub EditRecord_Click()
Dim strSQL As String
strSQL = "EDIT [EventName] FROM tblEvents WHERE " & _
"tblEvents.[EventName] = '" & Me![EventList] & "'"
DoCmd.OpenForm "frmAddEvent", acNormal, , "EventName = " & strSQL, acFormEdit
End Subboblarson
Smeghead
- Local time
- Yesterday, 17:42
- Joined
- Jan 12, 2001
- Messages
- 32,059
Okay, you can't use the strSQL that way. You could use
DoCmd.OpenForm "frmAddEvent", acNormal, , "[EventName] = '" & Me![EventList] & "'", acFormEdit
if that is what you are looking for. The frmAddEvent should already have its record source and it would include [EventName] which you are filtering on. I don't know what [EventList] has in it but it would appear from your code that it is a string value.
You might find this helpful.
DoCmd.OpenForm "frmAddEvent", acNormal, , "[EventName] = '" & Me![EventList] & "'", acFormEdit
if that is what you are looking for. The frmAddEvent should already have its record source and it would include [EventName] which you are filtering on. I don't know what [EventList] has in it but it would appear from your code that it is a string value.
You might find this helpful.
