filtering form by combo box not really working

[Aravind]

Hi folks.

My database project has the following:

--------------------------------------------------------------------------------
tables:
Member: MemName, MemNo [PK], MemType, CourseFaculty
History: contains all of the above

queries:
qryHistory: based on History

forms:
frmHistory: based on qryHistory. MemNo, MemType and CourseFaculty is
not Enabled and not Visible. Also contains a combo box "cboName", and
a label "lblTitle".
--------------------------------------------------------------------------------

frmHistory is used to display the borrowing habits of members (i.e.
which member borrowed what particular book at what date, etc.).

cboName is used to filter frmHistory by a member's name (for example,
if I choose "Aravind" from the list, frmHistory will be filtered to
display my borrowing habits). When I choose a name from the combo box
list, lblTitle will display the details of that particular member
(continuing from the example above, lblTitle will display my name,
I.D., MemType, and CourseFaculty). I use a macro (macro_cboName) to do
this:

--------------------------------------------------------------------------------
Action: OpenForm
Form Name: frmHistory
Where Condition: [MemName]=[Forms]![frmHistory].[cboName]

Action: SetValue
Item: [Forms]![frmHistory].[lblTitle].[Caption]
Expression: [Forms]![frmHistory].[cboName] & ", " &
[Forms]![frmHistory].[MemNo] & ", " & [Forms]![frmHistory].[MemType] &
", " & [Forms]![frmHistory].[CourseFaculty]

Action: SetValue
Item: [Forms]![frmHistory]![cboName]
Expression: Null
--------------------------------------------------------------------------------

The problem is that, if I choose a member name who doesn't have an
entry in History (and hence in qryHistory and frmHistory), lblTitle
will only display that member's name, whereas I want it to display
the details of that member (as in the examples above). How do I do
that? BTW, I'm using Access '97. Thank you.

[Jim Allensworth]

See comments inline...

On 8 Jul 2004 01:21:19 -0700, so**********@hotmail.com (Aravind)
wrote:

Hi folks.

My database project has the following:

--------------------------------------------------------------------------------
tables:
Member: MemName, MemNo [PK], MemType, CourseFaculty
History: contains all of the above This doesn't look normalized. The only common field between the two
tables should be MemNo. MemNo the PK in the Member table should only
be the FK in the History table. Don't store the same information in
more than 1 place. This could be the cause of your ambiguity.

queries:
qryHistory: based on History This should be based on Member *and* History inner joined on MemNo.

forms:
frmHistory: based on qryHistory. MemNo, MemType and CourseFaculty is
not Enabled and not Visible. Also contains a combo box "cboName", and
a label "lblTitle".
--------------------------------------------------------------------------------

frmHistory is used to display the borrowing habits of members (i.e.
which member borrowed what particular book at what date, etc.).
This would be best as a form/subform. The parent form would hold the
Member info and the subform would hold the History info - borrowing,
etc.
cboName is used to filter frmHistory by a member's name (for example,
if I choose "Aravind" from the list, frmHistory will be filtered to
display my borrowing habits). When I choose a name from the combo box
list, lblTitle will display the details of that particular member
(continuing from the example above, lblTitle will display my name,
I.D., MemType, and CourseFaculty). I use a macro (macro_cboName) to do
this:
I would base the combo on a query of Member:
SELECT MemNo, MemName FROM Member

Set the columns to 2 the first would have a width of 0 (so you can't
see it) and the second to whatever you need. This way the MemNo is the
reference for the recordset. (In case you have more than one member
with the same name)

Use the combo box as the criteria of the form's RecordSource.
I don't do macros so I won't offer any suggestions; but I would use
the cboName AfterUpdate event to requery the form which would bring up
the Member info along with the History info in the subform.
--------------------------------------------------------------------------------
Action: OpenForm
Form Name: frmHistory
Where Condition: [MemName]=[Forms]![frmHistory].[cboName]

Action: SetValue
Item: [Forms]![frmHistory].[lblTitle].[Caption]
Expression: [Forms]![frmHistory].[cboName] & ", " &
[Forms]![frmHistory].[MemNo] & ", " & [Forms]![frmHistory].[MemType] &
", " & [Forms]![frmHistory].[CourseFaculty]

Action: SetValue
Item: [Forms]![frmHistory]![cboName]
Expression: Null
--------------------------------------------------------------------------------

The problem is that, if I choose a member name who doesn't have an
entry in History (and hence in qryHistory and frmHistory), lblTitle
will only display that member's name, whereas I want it to display
the details of that member (as in the examples above). How do I do
that? BTW, I'm using Access '97. Thank you.

I think approaching it along the lines mentioned above will solve this
problem along with other future problems.
- Jim