How do I edit a list box in access?

I have no idea how to use list boxes. But what i have is a list of the fields from a table. And i want to add a record, and edit or delete the selected field from the list box. How do i do that?

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.

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?

ah... I see create a subform that will be bound to your events table and will show the events.

now you can edit/delete/add new.

I suppose I could do that too. However, I think the buttons would be easier for people to use, can you, or anyone post the code to make the buttons work maybe?

thanks in advance

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.

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

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.

interesting. Could you provide me with some code or an example of either solution possibly?

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

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:

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 Sub

hey guys. I'm sorry for being a bit stubborn, but i did not really want to use 3 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?

CurrentDb.Execute strSQL, dbFailOnError

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.

@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:

DoCmd.OpenForm "frmAddEvent", acNormal, "EventName = " & strSQL, , acFormEdit

Well... I am still not sure, sorry. But this is what I got:

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 Sub

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.