Lookup Logic and Display Values

[Robert T]

Hello:

A co-worker just showed me her database that has tabbed forms. The first tab
has information on the call while the 2nd tab has all of the information on
the caller such as address, city, state, phone number, etc. There's also 3
other tabs for related information.

When someone calls in to make a complaint, she wants to look up the caller's
name in the database, and if their name and corresponding information is
already stored in the table, she wants Access to automatically fill in about
20 fields on the next tab. I'm not sure why she's repeating the same
information that's already stored in another table, but I didn't design the
application.

I suggested using a combo box to look up the caller in the relevant table.

Here are my questions:

1. If the caller's info is already stored in the previous callers table
[this will be true more often than not], can we just display all of the
information in the 2nd tab of the form instead of filling in redundant data?
We're talking about the address, city, state, zip, phone no., etc.

2. If we can't simply display the info, I'm fairly confident I can write
code that will fill in the relevant data fields from the combo box lookup.
But hopefully we can just display the caller's info on the next tab of the
form.

3. I also need help with writing the code in Access 2003 to do the
following. If the caller's info is NOT already stored in the table, we just
want to skip the above process and enter all of his/her data manually. That
means, name, address, city, state, zip, phone number, etc.

Any thoughts?

Robert

 

[Albert D. Kallal]

Don't write code to copy all that stuff over and over.

If you use the relational abilities of ms-access, you can have all of that
information display WITHOUT WRITING ONE LINE of code.

ms-access is a *relational* database, so, get ms-access to do all the dirty
work!!1

So, I would use a combo box to select the customer, and it would save the
"customer" id into a field. You then just have a sub-form display all of the
values. This part will take ZERO lines of code. Even better is that you can
update phone numbers, or correct address, and it will be updating only ONE
copy of the customer file. To copy over and over is to copy a mess over and
over.

I talk about this idea here:

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

The only part you need is the ability to add a "NEW" customer to the combo
list if the existing customer is not yet on file...

You can use:

Private Sub Distributee_NotInList(NewData As String, Response As Integer)

if MsgBox("Do you want to add this value to the list?", _
vbYesNo) = vbYes then
DoCmd.OpenForm "frmAddClient", , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

Note that by setting response = acDataErrAdded, then ms-access does a
re-load, and a re-query of the comb box list for you. In other words, quite
a bit of stuff happens to ensue that the combo box is re-loaded, and re-set.

In the above
example note how I am passing the newdata as a openargs.

Obviously, in the forms on-load event, I go:


if isnull(me.OpenArgs) = false then

me.CompanyName = me.Openargs

endif

It is important to note that while you are saving the id in the combo box
list, the newdata is in fact the actual text used, and if you change that
text, then you need to modify the newdata to MATCH what your user typed in
that form.

The solution is to simply grab what value the user entered into the form.
The
trick in doing this is to simply modify the above code as:

strF = "frmAddClient"

if MsgBox("Do you want to add this value to the list?", _
vbYesNo) then
DoCmd.OpenForm strF, , , , acFormAdd, acDialog, NewData

if isloaded(strF) = true then
Response = acDataErrAdded
NewData = forms(strF)!CompanyName
docmd.Close acForm,strF
else
Response = acDataErrContinue
endif

Else
Response = acDataErrContinue
End If

To see how you get a form to RETURN values, but wait for user input, simply
read the following article of mine:

http://www.members.shaw.ca/AlbertKallal/Dialog/Index.html


To make life a bit easier to your users,
there are a good number of things you should set in frmAddClent to make life
better.

They are:

Set the forms "cycle" property to current. This means the cursor when on the
bottom of the screen at the last field does not jump to another record. It
is horrible when a user bumps the tab key, and access goes to another
record, so, set the forms cycle property (other tab) to current. In fact,
this is a good setting for most forms, and NOT just this add example.

Since this is just a add form, then turn off the record navigation at the
bottom, again this serves only to confuse the user (since you have a
frmAddClient, I bet you already did this too). Why confuse the user!

Further, turn off the forms ability to "add new" records. Yes, you read this
100% correct. You want turn off the forms allow additions property. The
reason for this is that the above "open" form will OVER RIDE this setting.
This is ideal, since once again, hitting page/down key, or even the mouse
wheel will not cause the form to jump to another record, and confuse the
heck out of the user (this is along the same lines as the tab key setting
above as per "cycle" setting).

Note if you need this form to do "double duty" and want to use it in other
places in the application to allow the user will add MORE THEN ONE record at
a time, then put the following in the forms on open

if isnull(me.OpenArgs) = false then

me.Company = me.Openargs
me.AllowAdditions = false
Me.NavigationButtons = False

endif

That way, this form can be used else where in the program, but for our combo
box, the navigation buttons, and accidental adding of MORE THEN one record
will not occur.

All in all, you don't have to do any of the additional things I mention
above, but can *just* use the short sample code, but all of the above
together makes a real slick app.

 

[Robert T]

Hello Albert:

Her design didn't feel right to me and after I sent the message, I knew that
wasn't the correct way to do such. I knew that was a serious violation of
Normalizatiion. After reading your detailed reply and suggestions, I felt
much better as things seem clearer after I've had a little bit of time to
think about the situation. I truly appreciate you taking the time to write
such a detailed response.

I'm going to work on your suggestions tomorrow morning.

Thanks,
Robert