See comments inline...
On 8 Jul 2004 01:21:19 -0700,
solaris_nite@hotmail.com (Aravind)
wrote:
[color=blue]
>Hi folks.
>
>My database project has the following:
>
>--------------------------------------------------------------------------------
>tables:
>Member: MemName, MemNo [PK], MemType, CourseFaculty
>History: contains all of the above[/color]
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.
[color=blue]
>
>queries:
>qryHistory: based on History[/color]
This should be based on Member *and* History inner joined on MemNo.
[color=blue]
>
>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.).
>[/color]
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.
[color=blue]
>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:
>[/color]
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.
[color=blue]
>--------------------------------------------------------------------------------
>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.[/color]
I think approaching it along the lines mentioned above will solve this
problem along with other future problems.
- Jim
