Is automated record completion possible based on user selection...

[Guest]

Okay, this is kind of a long winded question so if you even get through
reading this, much less have advice, thanks from the get go! Also, I'm self
taught in Access and have no other real IT training so if I'm not spot on
with the lingo, please forgive!

I have previously set up a db for my law firm to track case information.
There is so much info to be input (and so much of it is basically all the
same) that now I'm trying to go back and create a new form so that the users
do not actually data enter into each (of 60) fields because there's too much
room for differentiation and error. Alot of this information is contact
information for judges, mediators, etc. So, here's my question: Ideally, I'd
like to set up my form so that, basically, I create a list box with each,
say, judge's name, and then once the user selects judge a, b, or c (of about
30 options) their correct address and contact information automatically is
entered into the appropriate fields. I just don't even know where to start -
is this something that would be done through conditional formatting? parent-
child? And then...how would I go about figuring out how to code all of this,
etc?

If someone could PLEASE give me a little help, I would GREATLY appreciate
it!!

 

[KML]

I'm sure there's ways to do this with no code, but since I am a
developer I like to code whenever possible. Here is what I would do:

Have a table to hold information about Judges:
*note* all specific field names in my example can be changed

Table Name: tblJudges
Primary Key: JudgeID (Autonumber)
Other Fields: JudgeName (Text)
Address (Text)
City (Text)
State (Text)

Form Name: frmCaseInfo
In your case form, create a combo box using the wizard which looks up
the judge table and has the fields JudgeID and JudgeName. You will
most likely want to hide the Primary Key field as users don't need to
see it. In my example, I renamed the combo control to "cmbJudges".
I also added the following fields for the sample:
txtJudgeAddress
txtJudgeCity
txtJudgeState

Here is the code behind the form:

Option Compare Database

Option Explicit

Private Sub cmbJudges_Change()

Call SubLoadJudgeAddress

End Sub

Private Sub SubLoadJudgeAddress()

'Declare Variables
Dim objADORecordset As New ADODB.Recordset
Dim strSQL As String

'Check to make sure a Judge is selected
If IsNull(cmbJudges.Value) = True Then

'Exit Sub
Exit Sub

End If

'Declare SQL String
strSQL = "SELECT * FROM tblJudges WHERE JudgeID = " &
cmbJudges.Value

'Retrieve Recordset
objADORecordset.Open strSQL, CurrentProject.Connection,
adOpenDynamic, adLockReadOnly, adCmdTableDirect

'Check to make sure a record was retrieved
If objADORecordset.EOF = True Then

'No Record??
Call MsgBox("Error! Unable to find Address information for " &
cmbJudges.Value & Chr(13) & _
"Please contact your System Administrator.",
vbOKOnly, "Error Retrieving Address")

'Exit Sub
Exit Sub

End If

'Retrieve first record
objADORecordset.MoveFirst

'Set Address values
txtJudgeAddress.Value = objADORecordset.Fields("Address")
txtJudgeCity.Value = objADORecordset.Fields("City")
txtJudgeState.Value = objADORecordset.Fields("State")

'Close Recordset
objADORecordset.Close

'Clean Up
Set objADORecordset = Nothing

End Sub

Keep in mind that a couple of these lines of code wrapped and should be
on the same line.

As far as storing the information, you really only should be storing
the Judge ID with the Case Information. Storing any other information
beyond that is redundant because you already have it in "tblJudges".

If you have any questions, let me know!

 

[Guest]

First of all, thank you SO very much for your help and taking the time to
give such a thorough response, I really appreciate it. I do have a few
concerns though:
The judge example was really just an example of one set of data that I want
to be automatically filled upon user selection, there are about five or six
other "sets" of records that I want to be able to do the same with, but each
is slightly different and my coding abilities could barely be described as
basic.

Secondly, and this is the clincher, our firm already has about 250 merge
documents in Word that we use this database to complete. So, while all the
information being in one table is VERY redundant, I'm concerned about moving
that data seperate tables just due to the amount of changes that would have
to be made to all the documents we already have.

I guess basically I'm trying to find a way to do this through controls,
functions, relationships or another way where I can leave the table the way
we have it and just have a better form for data entry for future new records.

Thank you again for your help!

 

[Albert D. Kallal]

A few things:

First, I think you received some non optimal advice here.

While it is a great idea to use a combo box to select a judge for example,
you DO NOT NEED TO WRITE one line of code to bring in those other fields.

Ms-access is what we call a relational database. This is simply a fancy term
that means ms-access can DISPLAY data from other tables in your form, but
you do NOT have to copy the data, or even have to write ONE LINE of code to
show the additional details.

So, for one, you don't need, nor want to "copy" that data from those other
tables. You can place a combo box on the form for the judge, and below that
combo box place a NICE little sub-form that will displays ALL OF the data
from the judge table. And, the beauty of this system is that you do NOT have
to write even one line of code to do this!!

I have some example screen shots and explain of how to do this here:

http://www.members.shaw.ca/AlbertKallal/Articles/fog0000000005.html

so, if possible, you do NOT want to copy the same data over. Even better, if
you use the relational abilities of ms-access, then you do NOT have to write
any code anyway!!

The following however is a major issue:

Secondly, and this is the clincher, our firm already has about 250 merge
documents in Word that we use this database to complete. So, while all the
information being in one table is VERY redundant, I'm concerned about
moving
that data seperate tables just due to the amount of changes that would
have
to be made to all the documents we already have.

You worries are 100% right on!! There is a EASY solution to bringing the
many tables together. And, further, this can work with the word merge. Lets
assumed that we move out data for judge (and a bunch of your other sets of
data).

The solution here is then to simply build a query that joins back together
all of these tables. If you do this, then you will actually have a resulting
query that returns the one record, but that record will ALSO HAVE all of the
fields from those related tables. If the field names remain the same, then a
very large portion of your existing word documents will continue to
function.

However, your problem is migration. We don't have the EXISTING data in this
new format!! So, you can either leave all the existing data as is, and then
perhaps create a NEW form + tables for the new data entry system. However,
the problem of the word merge documents being for the old records, and
layout, or for the new records and layout remains?

In other words, if you try and fix/setup your data correctly, then those
merge documents will have to be changed.

as you can see, having a less then optimal design to start with is a
handicap in terms of forwarded movement.

So, our problem is not design a new systems. as mention, you can even create
and build this new system and NOT likely have to write any code to copy
data. However, we also would want to move out the EXISTING data to those
related tables. If we do this, then we only have one appcation for the old +
new system, and those word merge documents could be modified to work with
the new layout. However, to move/copy and write out your EXISTING data out
to those other relational tables will requite an ADVANCED level of
ms-access skills. You would have to write code to pull out the fields that
are all messed up in the main record out to individual related tables, and
while I done this MANY times, it requites an advanced process of ms-access
skills+coding.

Last, but not least, you could keep your existing data, and actually WRITE
code to pull out the fields from the combo box selection, and copy the data
into those main table fields. This is messy, and keeps your less then
optimal design alive. (and, to be fair, this is kind of what the other
poster is suggesting).

So, while I come down hard on saying you don't need to write any code here
if you have a good design, you could keep your existing design alive, and
write code to "copy" that data when you make a selection in a combo box.

1) so, write some code, and keep your existing designs

2) Or, new design, does not need code, (but, will requite advanced coding
skills to fix, and spread out the existing data to those new related
tables).

3) create a new design, and just keep the old system around for previous
records (this choice is least amount of code you will have to write).


Only you can really decide based on your skills (or willingness to learn) as
to which approach is going to be better for you...

I just wanted you to realize that several approaches are available here, and
the *better* approaches not only eliminate the need to copy data, but also
eliminate the need to write code. Unfortunately, in your case, you likely
will have to consider writing code to fix your existing data design.

At least there is this newsgroup full of people like me that love to talk
and offer advice!!!