How to create a powerful "Customers" form in ADP???

[HowardIsHigh]

Hi all,

I work on Access 2000 ADP connected to SQL SERVER 2000.

i aim to create a simple form that would be based on a classic
"Customers" table (populated by nearly 700 rows).
The form must be able to edit a Customer's details, previously choosen
from a combobox sorted by customers names.
Then if the user click on a "modify" button, the form will allow edit
mode and a "record" button would be accessible.
I want also the form to permit to create a new Customers using a
"create new" button...

I know I have several ways to do this, but what is the simplest one?
Using only stored procedures to fill my combobox and to update or add
a customers?
Working with views? (the problem is that i cant sort data by using
views...)
Using VBA only? ADO?

How would you do it?

Cheers

 

[BobJacksonNCI]

I'm interested in other responses as I'm doing
something similar. My environment is Access 2000
(.adp) with SQL 7 back end.

The simplest, and IMHO, method is to bind
your form directly with the existing table.
If bound, you don't have to have buttons to
modify or record as that is provided by
the standard navigation buttons. This may not
be as user friendly, but it IS standard and
requires no programming. I tell users and
management that I CAN make it prettier, but
not any more functional and it will take longer.
AND, once users get used to using the navigation
buttons that can be the standard for future databases.

Some of my users complain the standard navigation
buttons are too small, but I resist modifying because
they invariably have high resolution monitor settings.
Users with monitors set to 640X480 and 800X600 don't
usually complain. And, althought it's not that
difficult to use the button wizard to duplicate
navigation button functions with larger buttons,
the standard buttons may just require getting used to.

Suggestion: Put your validation routines on your
form controls, not within the table. The reason is
because since your tables are on an SQL server, if
the validation is within the table (constraints),
then data entry errors will result in non user
friendly messages. By validating on the form, you
can specify the message you want your users to
receive.

Good Luck!
Bob

 

[HowardIsHigh]

Hi Bob and thank you very much for your answer.

Indeed binding the form directly to the specific table may be the easiest way to do what i want. But, i need the form to distinguish the reading process and the updating/adding process. The reason is that I don't want users to accidently modify a record.

Do you have any ideas on how to handle this?

Regards.

 

[BobJacksonNCI]

Hello Howard! And you're welcome!

I have to type quickly because I've got to process
the company's mid-month billing today.

I cut-and-paste a procedure that is in final
testing. Note Me.NewRecord - that's an automatic
event that is triggered when Access knows you're
starting a new record as opposed to editing an
existing one. lblEdit and lblEnter are just two
text boxes on top of each other and with differing
background colors. This tells the user whether they
are editing an existing record or putting in a new
one.

My situation is a bit different from yours, but
there's no need to go into detail. My form IS
bound to an underlying table, but when I know
the user is adding a new record, I prefill known
information from another table (bound to the
calling form).

For me, one of the larger learning tasks of
Access is keeping in the back of my mind how many
automatic events there are and when it's appropriate
to use them...

You could actually make the form controls read-only
when your user is looking at an existing record and
then write-only when they're on a new record.

Got to work, will check back this afternoon, EDT.
Bob

Private Sub Form_Current()
Dim rs As Object
Set rs = Me.Recordset.Clone
If Not Me.NewRecord Then
Me.lblEdit.Visible = True
Me.lblEnter.Visible = False
'IF EXISTING RECORD, NO PREFILL AS USER MAY EDIT
Exit Sub
Else
Me.lblEdit.Visible = False
Me.lblEnter.Visible = True
'IF NEW RECORD, PREFILL DATA FROM CALLING FORM
Me![txtSSN] = Forms!frm_MAIN!SSN
Me![txtEmpLastName] = Forms!frm_MAIN!EmpLastName
Me![txtEmpFirstName] = Forms!frm_MAIN!EmpLglFirstName
Me![txtEmpMidName] = Forms!frm_MAIN!EmpMidName
Me![txtTeam] = Forms!frm_MAIN!Team
Me![txtDept] = Forms!frm_MAIN!Dept
End If
End Sub

 

[HowardIsHigh]

I'll try today,

thanks again for this precious explanation!

cheers