Solved

CREATE GRID FORM WITH EDIT/ADD/DELETE BUTTONS

Posted on 2006-11-05
26
284 Views
Last Modified: 2012-06-27
I am creating an Access application & have a Form that I plan to populate from a table of student records, called tblStudents, with fields, FirstName, LastName, MaiBox#, Email (Primary Key). The purpose of the Form is to be able to EDIT/DELETE/ADD student records, and at the foot of the forms there will be Command Buttons for EDIT/DELETE/ADD. The Form should have scroll bars when viewing & user should be able to Select a student record, then click the EDIT/DELETE/ADD button, as needed. I know that one can create a vbGrid & populate the form, however, I am not familiar with how to do it. Also, I select a specific record on the form, how can I reference which row this is, i.e., if I select a record & then click the EDIT button, I would like to either (1) get a small form with that record displayed, make my changes, choose SAVE & then have the record updated. I am confused as to how the EDIT command button would know which record I have clicked on in the form, ie, which record needs to be edited. If there is complete code somewhere as how to do this, I would appreciate it. This is time sensitive & very urgent. PLease call me at <Removed by kb Experts Exchange Moderator>
0
Comment
Question by:nemesis7
  • 12
  • 9
  • 4
  • +1
26 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
Hi nemesis7,

> I am creating an Access application & have a Form that I plan to populate from a
> table of student records, called tblStudents, with fields, FirstName, LastName,
> MaiBox#, Email (Primary Key).

IMHO, an email address is a bad choice for a primary key, for many reasons.

1) People's email addresses change from time to time.  You do *not* want to have to keep editing a primary key.
2) Primary keys often act as foreign keys to other tables, and an email address is clumsy at best as a foreign key.

> The purpose of the Form is to be able to EDIT/DELETE/ADD
> student records, and at the foot of the forms there will be Command Buttons for
> EDIT/DELETE/ADD. [...]

Why not simply use a regular bound form?  It seems to me, unless I am totally misunderstanding your needs,
that regular Access form functionality will do exactly as you desire.

> This is time sensitive & very urgent. PLease call me at <phone number deleted>.

Per official site policy, you are not supposed to communicate outside of EE in working through a question.

Regards,

Patrick
0
 

Author Comment

by:nemesis7
Comment Utility
1. The requirement of email as PrimaryKey is not my choice, so I have to stick to what the requirements are
2. If I use a Form bound to the data, then lets say I want to edit a record, how do I do that? I dont want to edit directly in the records of the form. I would like to Select a particular record, then click on the EDIT button at the bottom of the form. This should either unlock only that specific record for updating or bring up that record in a small form where I can perform the update & click submit...this updates that specific record. As I said, time is of the essence, so please let me know of a solution, rather than suggesting that I change what I want. Dont mean to be rude, but please understand.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
Comment Utility
I'll generally second what Patrick has already said.  (So bear in mind that's effectively all this is - back up).

1. How do you mean?  In that you've been told that you have to use it?  Or you're working with a table that isn't your making - and it has Emal as the PK?
But then - you're the one editing, deleting and appending to it - so it's yours now.
Perhaps you're confusing the need for a field to be the PK for it to be enforced to be unique.  That's definitely not the case.
You could maintain an autonumber PK and just enforce uniqueness on the email address - keeping it as an efficient searching device (or whatever).
But as mentioned by PM (Prime Minister? :-S) a text based PK is inefficient (clumsy is a good choice of word actually Patrick, I like it) especially one as long as an email address and can only lead to complications and regrets later on.

2. You seem to be talking about a list to select from - and then a dialog form to work in.
Absolutely nothing wrong with that scenario at all  (see, we're not just picking on you ;-)
It's very standard.
You would have a bound list of records (be it just a combobox, a listbox, or a continuous/datasheet form of records).
But that needn't be an updateable list.  You can ensure it can't be altered - with something as simple as setting the forms properties - e.g.
AllowEdits : No
or equally
Recordset Type : Snapshot
Then you can create a command button to click that will open up another (bound) form based on the value in the combo, listbox or continuous form.
The wizard should actually give you enough to get you to the right record in that called dialog.

Post back if it doesn't :-)

(Consider though - that a list of thousands of records isn't a great deal of use... so you might ponder filtering it in some way in time...)
0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
<. The requirement of email as PrimaryKey is not my choice, so I have to stick to what the requirements are>'

you can make an artificail primary key (autonumber) and set a unique index on email address, this should fit your requirements but give you performance of a numeric PK.
0
 

Author Comment

by:nemesis7
Comment Utility
OK, I will add an AutoNumber PK to my tblStudents (call it MyPK) & set 'Indexed' to Yes (Duplicates OK) for the Email field, that OK? Now tblStudents is related to tblPackages which obviously is a record of all packages for students, 1 student can obviously have more than 1 package. In other words, the relationship between tblStudents & tblPackages should be 1-TO-MANY. Does this mean I will have to create an additional column in tblPackages called MyPK, so that the 2 tables can be related? I am getting back into MS Access after a long time, so pls dont laugh at my silly questions.
0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
you sound like you are doing a good job of picking it back up again ... your thoughts are directly on track

<create an additional column in tblPackages called MyPK, so that the 2 tables can be related>
yes :-)
0
 

Author Comment

by:nemesis7
Comment Utility
oops. I mean Yes (No Duplicates)
0
 

Author Comment

by:nemesis7
Comment Utility
OK, another question. Once I have created the additional column MyPK in tblPackages, is it necessary to enforce referential integrity between the MyPK from tblStudents & the MyPK in tblPackages. I am just wondering what the advantage is of having referential integrity...besides enforcing that no record with an invalid MyPK (ie a MyPK not present in tblStudents) might be entered in tblPackages. Becuz in all my queries, I am simply going to use WHERE tblPackages.MyPK = tblStudents.MyPK --- which can work with/without referential integrity
0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
ref integrity can buy you a bit more that just preventing orphan records (which in and of themselves can pose problems), you cvan cascade updates and deletes for any related records without having to code it all. Say you delete a student ... it is nice when all the packages related to that student also get deleted.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
Comment Utility
Enforcing the relationships in an application have several advantages.

They are an immediate documenter of your tables layout - discernable at a glance.
They (as you mention) enforce Ref Integ - which means you won't end up with unrelated records.
You can then enforce Cascade Delete (update is not as relevant with an autonumer PK) which saves tail chasing when deleting master records.

(Oh - and the tables auto join in the QBE grid - if that's important to you :-)
0
 
LVL 44

Expert Comment

by:Leigh Purvis
Comment Utility
Steve often steals my thoughts before I've had a chance to write them down... ;-)

(Don't you love getting the same advice twice?  How right must *that* be :-)
0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
don't look at the points or you will notice that Leigh is often quicker on the draw :-)
0
 

Author Comment

by:nemesis7
Comment Utility
I have a form with the fields bound to a table & with continuous view. if i want to be able to select multiple rows in the form at once & delete, how do i do that? how do i reference which rows are selected in VBA code & execute the delete command? is there some property or someting>?
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 44

Expert Comment

by:Leigh Purvis
Comment Utility
To do this through the UI then you'd need your continuous forms to have their recordselectors displayed.
Click and drag down the selectors - or click on the first, hold shift and click on the last.
Then hit the delete key - or run code (from the same form)
Docmd.RunCommand acCmdDeleteRecord

To do the same entirely through code would be more effort (and perhaps not worth your bothering yet?)
0
 

Author Comment

by:nemesis7
Comment Utility
Yes, it would be very much worth bothering. Please asnwer what I have asked for, if I am saying through code....then I mean through code. I would like to be able to select multiple record selectors, press a command buttong & delete all selecteted records.
0
 

Author Comment

by:nemesis7
Comment Utility
Also, when I try your method....it only deletes 1 single record, even though I press shift & select multiple records. I am pressing a delete command buttong that has the macro Docmd.RunCommand acCmdDeleteRecord in the Click event. Am I doing something wrong?
0
 

Author Comment

by:nemesis7
Comment Utility
also, if i have selected a specific record in the form  & I want to edit the record, how do I tell my code which record is selected, so that I may include that in my SQL update statement. in other words, how do i refrence the record selected property
0
 
LVL 44

Expert Comment

by:Leigh Purvis
Comment Utility
That will be because of losing focus on the list (to the command button).
Avoidable using a function call through a menubar.

The code solution you crave so much is what though - executing an SQL statement?
0
 

Author Comment

by:nemesis7
Comment Utility
how do I avoid that? Again, I am a novice, so can you tell me exactly how to avoid losing focus using the menubar? how exactly do I do that? also, I have repeated before, how do I reference the property of the record selected. In other words, lets say I have record # 4 selected, how do I tell the code to reference record # 4?
0
 
LVL 44

Expert Comment

by:Leigh Purvis
Comment Utility
You get the reference to the current record by simply refering to the control values.
It will always be the currently selected record values that you get back.

So if there is a textbox named ClientName.
Then Me.ClientName will return the value of that from whatever row is currently selected.

Is this a subform you're using - or a single continuous form - perhaps with command buttons in the form header?
0
 

Author Comment

by:nemesis7
Comment Utility
This is 1 single continuous form & I have the command buttons in the Form footer & my display data fields are in the Detail, while the labels are obviously in the Form Header.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
Comment Utility
You'll always struggle to delete more than one record at a time then.
That's because (as I mentioned) the focus is lost from the selected records.  (You can see it happen - select a few records then click and hold down on a command button.  The set of records is lost - only the current one is still marked with an >).

You have to use a menubar button to fire such code - as it doesn't take focus away from the current selection.

(You can create and customize menubars by right clicking on your menu/toolbars section and selecting Customize.)
0
 

Author Comment

by:nemesis7
Comment Utility
Appreciate your help, but a little more detail would definitely be very helpful. What do I select when I go to customize...can you tell me the exact steps please?
0
 
LVL 44

Expert Comment

by:Leigh Purvis
Comment Utility
If you've not created a new menu before then this may take a while...
(You might find Help useful).

You can add a command button to a command bar - that does one of many things (for example opens a form).
In such a button - if you are in Customize mode - and you right click on the button you can select Properties.

If you put in the On Action field the name of a function like
=FunctionName()

Then that function will be called when it is clicked.
You just need to name a public function appropriately.
The function can call the deleting code.

Try it - one step at a time.
0
 

Author Comment

by:nemesis7
Comment Utility
Thanks for all your help. I guess I got SOME help.
0
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 200 total points
Comment Utility
Did you need more?
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now