Select a certain record from a listbox

[Trachr]

Ok I have 2 questions, hopefully easy ones

Im pretty new to access, used excel for pretty much everything up till now so I think on flat terms generally but Im trying to force myself to think outside of the box now and learn access... finally.

Anyway Im making a form that will consist of just a few elements, a listbox, a text box, and 3 buttons

The table this particular form is accessing is one of the most simple tables I have, it consists of 1 whole field lol the purpose of this table is just to have a list of publishers so I dont screw up spelling later, so as I add things it will reference this list and only allow me to use names off of said list.

Anyway the listbox will show this list, this part was easy... anyway the buttons will be if I can manage it... Add, Edit, and Delete

Anyway I would like to have it set up so when I load the form my text box is empty then I can type in something and then simply click add and it adds the name to my table... again this part was easy, I figured this one out

what I havent figured out was this:

The delete button is pretty obvious I click on a record selected in my list box and click delete and it deletes that record.

The edit button I would like it so I could click a selection in my list and then hit the edit button and it brings that name down into my text box allowing me to edit then save that selection, Im guessing this one is the hardest and its not strictly needed just thought it would be a nice lil feature if possible

Are my last 2 buttons at all possible and if so could you please point me in the direction to figure out how to manage it?

Thank you!

 

[pbaldy]

To delete:

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

To edit, you could pull up a form:

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

 

[Trachr]

ok Ill try that on the delete... I was doing this and got all sorts of errors

Private Sub Command32_Click()
If IsNull(Me.addpub_publist) Then
MsgBox "Please select a record from the list to delete", vbCritical
Else

DoCmd.SetWarnings False
If MsgBox("Are you sure you want to remove '" & Me.addpub_publist.Column(1) & "' from the list?", vbYesNo + vbDefaultButton2 + vbCritical, "Warning") = vbYes Then
DoCmd.RunSQL "Delete * from tblpublisher Where ID=" & Me.addpub_publist
Me.addpub_publist.Requery
End If
DoCmd.SetWarnings True

End If
End Sub


but that was coding and I suck at coding in vb lol

 

[pbaldy]

What I posted is code. Not knowing the errors you got, hard to say what's wrong with the code you posted. Looks okay offhand. This might help:

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

as would knowing the data type of the ID field.

 

[Trachr]

lets see what I had pop up first was this:
When the warning screen popped up I got Are you sure you want to remove " from the list?
I would click yes even though it didnt tell me what I wanted to delete, it would then pop up a Enter Parameter Value box and would show the value that I wanted deleted followed by a text box in the window that popped up for me to enter something...

If I hit enter without entering anything the entry was not deleted... as for the data type of the field it is shorttext

 

[Trachr]

I edited this line:
If MsgBox("Are you sure you want to remove '" & Me.addpub_publist.Column(1) & "' from the list?", vbYesNo +

to remove the .column(1) and now it asks if I want to delete the correct thing but it still throws up an enter parameter value box which Im unsure what that is

 

[Trachr]

Ok update.. I figured out what that parameter value was... it wanted literally the ID number so on a test I changed

DoCmd.RunSQL "Delete * from tblpublisher Where ID=" & Me.addpub_publist

to

DoCmd.RunSQL "Delete * from tblpublisher Where ID=" & Me.addpub_publist
DoCmd.RunSQL "Delete * from tblpublisher Where Publisher=" & Me.addpub_publist

Then I could type the publisher name and it would delete, so I figured out that part was wrong... but its still not seeing =" & Me.addpub_publist for some reason

 

[pbaldy]

When you don't specify the column you get the bound column. Is that perhaps blank? If column 1 works, how about:

DoCmd.RunSQL "Delete * from tblpublisher Where Publisher=" & Chr(34) & Me.addpub_publist.Column(1) & Chr(34)

 

[Trachr]

I was pretty sure the bound column wasnt blank so I figured I would add what you had in stages starting with the Chr(34) and it fixed it right away simply by adding the Chr(34 to what I already had leaving my code:

Private Sub Command32_Click()
If IsNull(Me.addpub_publist) Then
MsgBox "Please select a record from the list to delete", vbCritical
Else

DoCmd.SetWarnings False
If MsgBox("Are you sure you want to remove '" & Me.addpub_publist & "' from the list?", vbYesNo + vbDefaultButton2 + vbCritical, "Warning") = vbYes Then
DoCmd.RunSQL "Delete * from tblpublisher Where Publisher=" & Chr(34) & Me.addpub_publist & Chr(34)
Me.addpub_publist.Requery
End If
DoCmd.SetWarnings True

End If
End Sub



What does the Chr(34) do? Im confused about the function of the chr(34) lol it worked but Im curious as to why... If Im going to start learning more about coding I should try to start understandings the whys of things lol

Thanks!... Now onto the edit part button lol

 

[pbaldy]

A text data type requires delimiters in a query, or by extension a query run from code. JasonM wrote a tutorial here:

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

specifically the section about half way down goes over various methods.

 

[Trachr]

ok, thank you!

 

[pbaldy]

Happy to help!