How to delete a record using an unbound form

[rnashting]

Good Day everyone! Here's what I'm trying to do in a nutshell. I have a table that I want to allow people to manipulate via forms. I'm using unbound forms because I don't want users to be able to add/delete/edit fields without validations. For my delete form, I want to use a combo box that loads up all of the items in column 1 (serial#) during the form load event. The user will pick an item and use a command button called "find record". I want the click event of that button to populate the rest of the fields in the related record to text boxes. Then the user will use a "delete record" command button to delete the record. I've declared the recordset, and I'm thinking I need to use a dynamic array, but I'm finding that this might be more advanced than I thought...does anyone have any suggestions?

[missinglinq]

"I'm using unbound forms because I don't want users to be able to add/delete/edit fields without validations."

I wish I knew what idiot started this completely false rumor about needing unbound forms in order to do validation!

A big part of the reason to use Access for database development is the speed with which it can be created, using bound forms. Several developers I know, experienced in Visual Basic database development and Access development, estimate that development using unbound forms by experienced developers takes twice as long as it does when using Access and bound forms.

The belief that unbound forms are necessary in order to do data validation (the reason usually given) is simply not true! Validation on bound forms is easily done thru use of the Form_BeforeUpdate event.

If you insist on using unbound forms, you'd be far better off using a straight VB or C++ front end with a SQL Server or Oracle back end.

1. You can create an EXE file which gives total protection to your code/design
2. You can distribute the db to PCs without a copy of Access being on board
3. Your data security if far, far better than anything you can do in Access

Linq ;0)>

[Jerry Maiapu]

I think the trick is this:

1.Keep the form bound to the table by dragging the table into the form and delete rest and leave the desired columns. This creates a sub form.
To keep users away from editing/modifying the displayed records,set Enable: Yes and Locked: Yes
and also set the Navigation Buttons:NO on the sunform

From now I do not think the users will be exposed to any buttons to edit/modify the records.

To do the search, simple create combo using wizard and follow prompts or you can create a form based search.

Just an idea there..I am quite rushing

Sorry its late now..I need to go home..post comment and I'll properly see into this 2morow..

[NeoPa]

Like Linq, I'm confused as to why so many members feel that coding around Access, rather than using Access to do what it does best, as in unbound and bound forms respectively, is somehow a better approach. If you use unbound forms then much of what is available to help you validate the operator choices is missing. I have to say my advice would be to use bound forms, and if you feel there are holes that don't allow you to do as you think is right, then perhaps raise those as questions. I expect there are people here who can point you in the right direction from there.

Otherwise, as Linq says also, you may as well be using a proper programming language. There's little benefit to using VBA unless you take advantage of the Office functionality.

[Jerry Maiapu]

Most Newbie do not seem to reply to responses to their questions.
Why is that?. If someone is serous about getting their problem solved then I think they should follow posts and reply accordingly..

With respect to what NeoPa mentioned, I think Access database designers should look at what access could offer the users rather than what they could offer.

From what I have seen in many ACCESS forums is that designers are looking for changes beyond what MS access could provide while in actual fact the real solution that meets their requirement is in the tip of their nose and they are ignorant. Most questions asked are mere cosmetic, i.e. is not really necessary. This is not a good practice as working around cosmetic changes would dramatically eat away one's time.

[NeoPa]

Jerry Maiapu: Most Newbie do not seem to reply to responses to their questions.
Why is that?. If someone is serous about getting their problem solved then I think they should follow posts and reply accordingly.

That can be for various reasons Jerry. Some have very little in the way of manners, but others get confused easily as they are unused to working within this medium. Still others don't check very frequently, possibly not expecting such prompt replies.

At the end of the day you will only make yourself unhappy if you rely on how other people behave. Remember, for every member short on manners, there are a number of other readers (members and otherwise) who can get benefit from your posts.

[Jerry Maiapu]

Ok. It was just a personal comment.

Thanks for the clarification NeoPa.

[Jerry Maiapu]

Oh ya I forget to thank you for creating a new threat for my Post:Count of working days

[NeoPa]

No worries Jerry. You're fast becoming a very helpful member and I'm always pleased to do things for those of you that help out :)

[rnashting]

Thanks for all of the answers! Sorry for the long reply time ( I try to reply Jerry...I feel your pain), we had a family tragedy that required an extended leave of absence. As to the solutions above, I think that the reason us newbies go right to code instead of bounds is that alot of us find sights like this while in school or training, and schools only teach what the course teaches. I never heard of a recordset once while in VBA/Access class! Bytes has been awesome in trying to get what school taught me and the real world merged into something useful.

I think the biggest problem with the bound forms that I haven't solved yet is how to bind the form, but not allow them to add a record to the table by tabbing through. The goal of my form is to allow them to view the records, but not actually edit them without clicking a "delete" or "add" button. I need to dumb this down to the level of people who still don't understand how to shut down a computer properly.

Thanks again for all of y'alls help.

[NeoPa]

rnashting: I think the biggest problem with the bound forms that I haven't solved yet is how to bind the form, but not allow them to add a record to the table by tabbing through. The goal of my form is to allow them to view the records, but not actually edit them without clicking a "delete" or "add" button.

That's actually a very common confusion. As it's been asked, why don't I see if I can explain somewhat.

Actually, as it's so important, I just wrote an article to explain it. Have a look at Stop Bound Forms Updating Automatically.

[rnashting]

Thanks again! I'm not in the office until tuesday, but I'm going to try to use this for a delete form too. We never learned a thing about the Form_BeforeUpdate event. Now that school is over I'm finding out how many things I wish they had taught us...

Thanks again!

[NeoPa]

I'm afraid, now you're out of school, you'll find you're expected to learn a lot for yourself anyway.

School being fundamentally useless is actually good preparation for the adult world :D

[Jim Doherty]

@missinglinq
Here, here bravo Linq!!

What makes me smile many times is the assertion that just because you can make forms lightweight by rendering unbound as opposed to bound in favour of dealing with everything in a module with no form code that somehow you work more efficiently.

They never mention that by the time development finishes you are left with a module that is xxxxx thousand lines long and guess what! the developer moves on, someone else inherits the project and then sits for goodness knows how long unravelling a plate of spaghetti simply because that 'efficient' individual thought the advantages to be had from dealing with everything unbound were huge... oh! and by the way he left without ever documenting anything.

Use Access in the way it was intended! and stop worrying about the 0.010 efficiency gain from having something unbound and late binding.

The scaleability arguments for me dont add up, especially considering that at the end of a normal or usual software lifecycle the underlying language has probably dramatically changed so much anyway and you are most likely going to want to re-write the application particularly if you are upsizing.

I know I am off rather topic with this one but I couldnt agree with linq any more and simply had to drop this rant in

:)