Cant delete from list box

[ppoindexter]

News flash! Listbox problema causes Meltdown

Howdy all

i have a form (frm_Delete_Reference) whose purpose is to delete records from a table--a simple task so i thought

Act 1:
tbl_referencelist has 3 fields

fldreferenceid (auto number-Key)
fldreferencetypeid (number)
fldreference (text)

Act 2:
frm_Delete_Reference

control source = tbl_referencelist

combobox 1 - control source=fldreference
textbox 1 - control source = fldreferenceid

Problem: i pick the item from the combo box, click delete, it askes "do you really want to delete this?" i say "heck yeah" and then it procedes to PRETEND to delete the item,,,however when i check tbl_referencelist the item is STILL there....and sometimes i find numbers have replaced items in the fldreference...

also the text box never changes record numbers even though i change the contents of the combo box....

could someone give me an ideal as to what i am doing wrong before this meltdown i am having has me but a puddle on the floor....thank ya'll very much

 

[shay]

Hi

Couple of points:

Selecting a reference from the combo box won't automatically move you to the correct record - this is why the content of your text box doesn't change.

You say you 'click delete' - does this mean you've create a command button and if so, what code have you put behind it?

shay

 

[ppoindexter]

thanks for your reply

yes i placed a command button (delete) using the wizard

Private Sub Command3_Click()
On Error GoTo Err_Command3_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Command3_Click:
Exit Sub

Err_Command3_Click:
MsgBox Err.Description
Resume Exit_Command3_Click

End Sub

 

[Pat Hartman]

In the AfterUpdate event of the combo, you need to move the form to the selected record.

Dim rs as Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[YourFieldName] = " & Me.YourFieldName
Me.Bookmark = rs.Bookmark

I also prefer not to use old menu item references. The menu references in your code are for Access 95. I know that is what the button wizard generated but that doesn't make it right. Even Microsoft recommends not using .DoMenuItem. Who the hell knows what menu items 8 and 6 are for an 8 year old version of Access! Replace them with the following:

DoCmd.RunCommand acCmdSelectRecord ' selects the current record
DoCmd.RunCommand acCmdDeleteRecord ' deletes the selected record

 

[ppoindexter]

Greetings Pat

thanks for your response!!

I made the changes and i think i am on the right track..had another error but it isnt related to this so as soon as i get this one worked out..i believe it will work

a question though

if the delete wizard writes bad code....is it safe to assume that other code generated by the wizard is faulty as well? such as add new record, save, open form, etc

if so can you tell me what is wrong with it or point me in the direction to find these IMPERFECTION....fixes...

also i remember reading somewhere once that access left off something to do with "dirty" in the code for something..
do you have any ideal what this would be...sorry i cant remember more than that...i am just afraid some of my problems i experience may be due to me relying on access's wizards and such and when probelms occur i spend countless hours tring to figure out what i did wrong....

thanks again and happy new year........

 

[Pat Hartman]

I've used pretty much all the wizards at one point or another and they all work as far as I know. Although I think that the print current record one does not always create the correct value for strCriteria.

The other code is OK. I only object to the doMenuItem's because they are so cryptic. As I said there is no viable way to determine what those old menu item references are so you can't read the code. Your command button doesn't have a descriptive name. You didn't change the name generated by Access when you added the button to the form. I always change the generated names of form and report objects. You could rename the button from Command3 to cmdDeleteRec. Don't rush off to rename the button now because Access isn't smart enough to move the associated code. You'll have to delete the old click event, rename the button, then create a new click event with the new name and copy in the code. Access also isn't smart enough to get it right if you change both the control name and the generated sub name. You need to do it the way I said in order for the form to work.

I don't have a copy of A95 loaded on any of my PC's so I can't even look up the menu items and I'm not sure that you can even coax a current translation out of Access help.

 

[Rakier]

Another bit of screwy code that is generated by a wizard is when you create a button to open a form and show all values. The wizard adds a DIM stLinkCriteria line to the code and adds the stLinkCriteria to the DoCmd.Openform command. I always go in an delete this code because it is never assigned and you should never declare a variable and not use it.

I agree with Pat about the cryptic references on the wizard generated delete button. I never use that code.

 

[ppoindexter]

thanks for the information....

at this point with a near completed db
would you suggest leaving the crytptic references as is
or changing
i know the changing is a timely task
however, i dont want problems down the road.....

 

[Pat Hartman]

I would get rid of all the DoMenuItems while the application is still fresh in your mind. A few months down the road, you'll be trying to figure out what the code is doing.

 

[ppoindexter]

will do...thanks for the advice