Form Design 2

[zimmer9]

I wanted to create a state form that would allow the user to edit the records (add, change, delete functionality) of 1 table.

This State table has 50 rows for the 50 states. It has fields for the State code, a field with a dollar amount and 3 fields that would have integer values. An example of a row would be as follows:

State Code Aggreg. Amount Cash Stocks IRAS Bonds
---------- -------------- ---- ------ -----------
ID(aho) $400.00 7 5 6

I was thinking of having the top part of the form be an area where the user could modify the fields of 1 state record and the bottom portion of the form would display as many records from the state table as would fit on 1 screen so that the user could see which state records need to be modified.

A subform seemed like a good approach, but I guess it is only used with a minimum of 2 tables. I noticed that when using the form wizard, you only have the subform option when you are working with multiple tables.

I was looking for suggestions on the form design, if you had 1 table with 50 rows and you wanted to allow the user the capability of updating any of the individual state records.

 

[ZmrAbdulla]

Can't you use a listbox instead of subform? So that you can diplay more records than a subform can.. So once the user click on the listitem the top portion will be allowed user to edit the selected record. You can use the listbox wizard and use the third option Find a record...
Hope this helps

Zameer Abdulla
JVBP MDS
_{Jack of Visual Basic Programming, Master in Dining & Sleeping}
Visit Me

 

[TheAceMan1]

How are ya zimmer9 . . . .

Since your [blue]record count is small[/blue], a [blue]Form in Continuous View[/blue] should do nicely . . . and its easy to setup!

See Ya! . . . . . .

 

[zimmer9]

I created a form in Continuous View to display the fields from 1 table because there are only 50 records in the table and 6 fields.

I used the form wizard in tabular layout to create the form and the controls in the form that are automatically generated are a series of text boxes.

My goal is to have the user of this form be able to update all the fields on this continuous form. The values for theses fields will be from a small list of possible values. For example, the primary key field is a 2 digit state code.

My question is would it be a normal form convention to have the user go to any row on this continuous form and make changes in any field by selecting a value from a drop down list box that I was going to create for each field (by converting the text box controls into list boxes).

If this is the case, what is the convention to allow a user to either ADD or DELETE records from this Continuous View. For example, would I train the user to highlight the Record Selector for the row that he or she wishes to DELETE and then hit the DELETE KEY.

And if the user wanted to ADD a record, would they go to the Navigator Bar and hit the New Record button.

Or should I create ADD and DELETE buttons at the bottom of the Continuous form ?

 

[RoyVidar]

I suppose that would depend on your overall application, the goals etc... Lot of systems uses that approach (just continuous subform, allowing the users to select row, edit data, delete). But for instance, if you are afraid they are going to accidently alter data, you may consider using the .AllowEdits, .AllowAdditions... properties of the form to have them "lock/unlock" the abilities to edit/add.

For displaying all records, and having the possibility of altering single records, I'm a fan of using listboxes, and have the users doubleclick an entry to open a single form to alter the selected record. Well, and open the form in add mode to add new records...

See also this "rant" thread702-810477, and perhaps in particular MichealRed's method of using continuos forms, it seems close to what you describe in the initial question.

To allow updates of a whole column, I like using listboxes, have the user enter the values in unbound controls, den fire off an update query.

Roy-Vidar

 

[TheAceMan1]

zimmer9 . . . . .

I would expect [blue]you need to maintain all 50 records[/blue], so I'm a little apprehensive about allowing deletions . . . Questions:
[ol][li]Do you really want to [blue]allow add/delete records[/blue] or [blue]simply modify[/blue] the 50 existing?[/li]
[li]If its your intent to [blue]allow add/delete records[/blue], then [purple]your talking maintaining a history of changes[/purple]. This would require an [blue]additional table[/blue] and an [purple]Date Field for time stamping the changes[/purple]. Perhaps this is what your after?[/li][/ol]
[purple]The idea of the comboboxes is a good one, but holdoff until you answer the above . . .[/purple]

See Ya! . . . . . .

 

[zimmer9]

I should have been more specific about my objective. In reality I have 2 state tables. 1 state table represents information for Fall States. Thoe other table represents information for Spring States. A state will be on either one of the 2 state tables. Thus, a state could migrate between the 2 tables (for ex: FL could move from the Fall table to the Spring table).

I have another question though.

If I want to allow the user to be able to edit all fields except the primary key field (state code) on a contiuous form, then how would I go about NOT ALLOWING EDITS on just the primary key field (the user can edit all other fields).
However, when the user Adds a new record, he or she should be able to create a new primary key as part of the new record.

 

[RoyVidar]

Use the .Locked and/or .Enabled property of the control holding the primary key.

For an addnew, reset them to .Enabled=true, .Locked=false, and keep them that way until the before update event of the form, which fires whenver there's an attempt to save (can be cancelled too). Perhaps just pop up a message there, to allow the users to cancel the save and alter the key before the save? Then reset the controls to .Enabled=false/.Locked=true.

One question - having the states in two tables - and have a possibility of moving them between tables, wouldn't that create a possibility of two states with the same primary key? Couldn't you keep them in one table, and toggle the a status between fall and spring?

Roy-Vidar

 

[TheAceMan1]

zimmer9 . . . . .

I totally agree with [blue]RoyVidar[/blue]. In fact an [blue]Yes/No field[/blue] or a [blue]field where you assign spring/fall[/blue] would do the trick. With query, spring & fall would be [purple]easy to pullout . . .[/purple]

See Ya! . . . . . .