Finding records with a form

[Bob Wickham]

I have a form with a query qryLoanClientForForm as its record source.
SELECT tblLoan.LoanNo, tblLoanType.LoanType, tblLender.Lender,
tblClient.Client
FROM tblLoanType INNER JOIN (tblLender INNER JOIN (tblClient INNER JOIN
tblLoan ON tblClient.ClientID = tblLoan.Client) ON tblLender.LenderID =
tblLoan.Lender) ON tblLoanType.LoanTypeID = tblLoan.LoanType;

On this form I have an unbound combo box with its Row Source
SELECT qryLoanClientForForm.LoanNo, qryLoanClientForForm.LoanType,
qryLoanClientForForm.Lender, qryLoanClientForForm.Client FROM
qryLoanClientForForm;

This allows me to enter a number (a LoanNo) in the combo box and have
the LoanNo, LoanType, Lender and Client transferred to bound text boxes
elsewhere on the form.

I want to be able to either enter a LoanNo OR a Client so that if a
Client is known but their LoanNo isn't I am still able to quickly to
find the details.

I have tried using another combo box with its row source altered so that
Client comes first. This works but having 2 combo boxes where one
isn't in sync with the other doesn't look very professional.

I have looked at examples using Search code but this seems like over-kill.

I'd be grateful for suggestions on how I could implement something
simple but elegant.

Thanks

Bob

 

[Tom Ellison]

Dear Bob:

I consider this a "nice" question. It has to do with making a properly
interpreted user interface. Here are my rules for doing so:

I put "search" functions in the top of my forms, the "header section" not
the "detail section". I draw a line across the form to visually separate
them.

The search functions start with a label with a yellow background (I could
use a command button, but I like the square corners, and labels make
perfectly good command buttons). There would be a series of these for
searches, one being a search by Client, another for search by LoanNo.
Clicking on one of these would cause the appropriate combo box to appear.
Both combo boxes would show both Client and LoanNo in them. The one for
search by Client would have the Client to the left and a column of LoanNo to
the right. After all, if there are two loans for the same client, you have
to be able to differentiate which you want to see, right? Also, you might
want some other information in additional columns in the combo box, to show
perhaps the date of the loan, whether it has been all paid, its current
balance, or anything of interest or which might help to choose the exact
loan desired. Some similar columns could go into the combo box for
searching by LoanNo.

You would never see both of these combo boxes simultaneously. When the form
opens, you see neither. When user clicks on the Client Search button (label
really) that button turns pink, and the appropriate combo box appears.
Click it again and it turns yellow again, and the combo box disappears.

The click event of the combo box causes the form to move to the selected
row, probably using the LoanNo column (that's what is unique, right?).

Oh, I just remembered the big reason I use labels instead of command
buttons. You cannot color command buttons!

If I've understood your question correctly, then perhaps my idea appeals.
The form has multiple search options (a lot of forms would benefit from
this, for sure).

Don't forget the form can not only be used to search, but to filter. If the
user chooses a specific Client, might it not be good if scrolling through
the records shows only those loans for that Client. That's another reason
for the label turning yellow/pink (choose light colors so the text can still
be read easily). This says, "the form is filtered" or not.

Hey, it's been fun transmitting some of my forms design paradigm. A really
good this is to pick something simple, but effective, and make it very
consistent. Users just get a feel for what you're doing, and will be able
to use now forms you create with little or no instruction.

Tom Ellison

 

[Bob Wickham]

Dear Bob:

I consider this a "nice" question. It has to do with making a properly
interpreted user interface. Here are my rules for doing so:

I put "search" functions in the top of my forms, the "header section" not
the "detail section". I draw a line across the form to visually separate
them.

The search functions start with a label with a yellow background (I could
use a command button, but I like the square corners, and labels make
perfectly good command buttons). There would be a series of these for
searches, one being a search by Client, another for search by LoanNo.
Clicking on one of these would cause the appropriate combo box to appear.
Both combo boxes would show both Client and LoanNo in them. The one for
search by Client would have the Client to the left and a column of LoanNo to
the right. After all, if there are two loans for the same client, you have
to be able to differentiate which you want to see, right? Also, you might
want some other information in additional columns in the combo box, to show
perhaps the date of the loan, whether it has been all paid, its current
balance, or anything of interest or which might help to choose the exact
loan desired. Some similar columns could go into the combo box for
searching by LoanNo.

You would never see both of these combo boxes simultaneously. When the form
opens, you see neither. When user clicks on the Client Search button (label
really) that button turns pink, and the appropriate combo box appears.
Click it again and it turns yellow again, and the combo box disappears.

The click event of the combo box causes the form to move to the selected
row, probably using the LoanNo column (that's what is unique, right?).

Oh, I just remembered the big reason I use labels instead of command
buttons. You cannot color command buttons!

If I've understood your question correctly, then perhaps my idea appeals.
The form has multiple search options (a lot of forms would benefit from
this, for sure).

Don't forget the form can not only be used to search, but to filter. If the
user chooses a specific Client, might it not be good if scrolling through
the records shows only those loans for that Client. That's another reason
for the label turning yellow/pink (choose light colors so the text can still
be read easily). This says, "the form is filtered" or not.

Hey, it's been fun transmitting some of my forms design paradigm. A really
good this is to pick something simple, but effective, and make it very
consistent. Users just get a feel for what you're doing, and will be able
to use now forms you create with little or no instruction.

Tom Ellison

Hi Tom,
After posting my question I did a bit of searching through some Access
books I have and found a solution similar to your design.
The book, Access Version 2002 Inside Out by Helen Feddema is brilliant
and I have included some of the code I have borrowed below

Private Sub cmdSearchByClient_Click()
'Created by Helen Feddema 12-24-2000
'Last modified 12-24-2000

On Error GoTo ErrorHandler

Me![ComboLoanNo].Visible = False
With Me![ComboClient]
.Visible = True
.SetFocus
.Dropdown
End With

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit

End Sub

Her form uses a pair of command buttons and although the desired result
is achieved, I like your idea of color coding a label.
My form also has a sub-form where new records associated with the
LoanNo/Client can be added.

Thanks for your interest. I too, like a well designed interface.
Its annoying to use an app that doesn't behave as expected.

Bob

 

[Tom Ellison]

Dear Bob:

Yes, that's the key. Use a consistent and hopefully intuitive design
paradigm. I'm so glad you caught on. I'd hate to think I did all that
typing for nothing. Long winded, ain't I? Well, that's about the shortest
way I know of describing what I meant, and I'm very happy you took the time
to read it carefully enough to catch on.

I'm glad to see I don't have that exact book. It seems to me I came up with
my ideas independently, although it is hard to say. You absorb a lot along
the way, and make it your own. But I'm not surprised at others following
some of the same principles. We are probably all trying to find an approach
that seems natural and intuitive. That's a key to designs for Windows.

Tom Ellison

Dear Bob:

I consider this a "nice" question. It has to do with making a properly
interpreted user interface. Here are my rules for doing so:

I put "search" functions in the top of my forms, the "header section" not
the "detail section". I draw a line across the form to visually separate
them.

The search functions start with a label with a yellow background (I could
use a command button, but I like the square corners, and labels make
perfectly good command buttons). There would be a series of these for
searches, one being a search by Client, another for search by LoanNo.
Clicking on one of these would cause the appropriate combo box to appear.
Both combo boxes would show both Client and LoanNo in them. The one for
search by Client would have the Client to the left and a column of LoanNo
to the right. After all, if there are two loans for the same client, you
have to be able to differentiate which you want to see, right? Also, you
might want some other information in additional columns in the combo box,
to show perhaps the date of the loan, whether it has been all paid, its
current balance, or anything of interest or which might help to choose
the exact loan desired. Some similar columns could go into the combo box
for searching by LoanNo.

You would never see both of these combo boxes simultaneously. When the
form opens, you see neither. When user clicks on the Client Search
button (label really) that button turns pink, and the appropriate combo
box appears. Click it again and it turns yellow again, and the combo box
disappears.

The click event of the combo box causes the form to move to the selected
row, probably using the LoanNo column (that's what is unique, right?).

Oh, I just remembered the big reason I use labels instead of command
buttons. You cannot color command buttons!

If I've understood your question correctly, then perhaps my idea appeals.
The form has multiple search options (a lot of forms would benefit from
this, for sure).

Don't forget the form can not only be used to search, but to filter. If
the user chooses a specific Client, might it not be good if scrolling
through the records shows only those loans for that Client. That's
another reason for the label turning yellow/pink (choose light colors so
the text can still be read easily). This says, "the form is filtered" or
not.

Hey, it's been fun transmitting some of my forms design paradigm. A
really good this is to pick something simple, but effective, and make it
very consistent. Users just get a feel for what you're doing, and will
be able to use now forms you create with little or no instruction.

Tom Ellison

Hi Tom,
After posting my question I did a bit of searching through some Access
books I have and found a solution similar to your design.
The book, Access Version 2002 Inside Out by Helen Feddema is brilliant and
I have included some of the code I have borrowed below

Private Sub cmdSearchByClient_Click()
'Created by Helen Feddema 12-24-2000
'Last modified 12-24-2000

On Error GoTo ErrorHandler

Me![ComboLoanNo].Visible = False
With Me![ComboClient]
.Visible = True
.SetFocus
.Dropdown
End With

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit

End Sub

Her form uses a pair of command buttons and although the desired result is
achieved, I like your idea of color coding a label.
My form also has a sub-form where new records associated with the
LoanNo/Client can be added.

Thanks for your interest. I too, like a well designed interface.
Its annoying to use an app that doesn't behave as expected.

Bob