form/subform issue 3

[THWatson]

Using Access 2000

I'm having a brain cramp concerning a subform and can't quite figure out what's going wrong.

tblMembers has, among others, the following fields
MemberID (primary key)
LastName
FirstName

tblAwards has the following 3 fields
AwardID (primary key)
Award
MemberID (foreign key to tblMembers)

The Award field has 2 entries, with more to be added later. The two entries are "Kiwanian of the Year" and "Mel Osborne Fellow."

On frmMembers, I have put a subform called fsubAwards, linked to frmMembers by MemberID. The subform has a combo box in which you select either "Kiwanian of the Year" or "Mel Osborne Fellow."

So on the page for the first member, P. Anderson, I click on "Kiwanian of the Year" and it seems to stick. However, when I then check tblAwards, I see a third line entered in the Award field, this third line having a new AwardID, the AwardID number for the "Kiwanian of the Year" (this being 1) and the corresponding correct number for the MemberID.

Obviously, I have something wrong and would appreciate being kicked in the right direction.

Thanks.

Tom

 

[RuralGuy]

Did you put any code in the SubForm because none is required since Access will do everything you need as far as saving records goes.

RuralGuy (RG for short) aka Allan Bunch MS Access MVP acXP winXP Pro
Please respond to this forum so all may benefit

 

[THWatson]

RuralGuy
Thanks for the reply.

No, there is no code in the subform.

Tom

 

[RoyVidar]

This kind of main/sub constallation, I think, is usually used in a M:N constellation, where you store the award id, together with the member id in a junction table (between the award and member table) - maybe that is what you need here?

If not, then I think you'd need to consider using the combobox unbound, and in the after update event of it, add the member id to the correct award record, then requery the subform.

Roy-Vidar

 

[RuralGuy]

What version of Access are you using? Am I correct that you are expecting 1 record in the tblAwards table but you have 3? How about code in the main form? Is there any come or macros anywhere in the db?

RuralGuy (RG for short) aka Allan Bunch MS Access MVP acXP winXP Pro
Please respond to this forum so all may benefit

 

[THWatson]

Rural Guy
I am using Access 2000 (I showed that at the top of the original post).

What I am expecting (or at least need) is a way to keep track of the various awards that members receive. That could be none, or one, or it could be several.

There are no macros anywhere, but there is code behind the main form - mainly on the Current event, for self-designed navigation buttons, for syncing a combo box as the user shifts from member to member.

Roy-Vidar
The thing that puzzles me is that I have done this same thing before, but it's been a while. Perhaps you are right in the approach you recommend.

Tom

 

[RuralGuy]

Sorry Tom.
I guess I need better glasses. Was I correct that you are expecting 1 record in tblAwards but getting three? Hopefully your Awards are enumerated in a table of their own so you can add some at a later date.

RuralGuy (RG for short) aka Allan Bunch MS Access MVP acXP winXP Pro
Please respond to this forum so all may benefit

 

[RoyVidar]

Consider what you're doing;

You have main form (parent table - member), and a subform (child table - award).

When choosing a member, you will get all subform records (child table - award) where it's member id field matches the member id field in the parent table/form. First time it will be none.

When entering new data in the subform (either by some kind of selection, or typing), you will create a new record. That's how it's supposed to work - think for instance Customer (Parent) - Order (Child), you choose between existing orders (Order records where it's Customer ID match this Customer), or create a new one.

Since both the subform and the combobox have the same record/row source, this is perhaps also clouding the issue?

Unbind your combo (remove the controlsource), then in the after update event, use something like

[tt]SQL = "UPDATE tblAwards SET MemberID = " & Me.Parent!txtMemeberID & _
" WHERE AwardID = " & Me!cboNameOfCombo
currentdb.execute SQL, dbfailonerror
me.requery[/tt]

Of course, with a little question - do you want to add this award...

Roy-Vidar

 

[THWatson]

RuralGuy and Roy

Thanks for the input. I really appreciate it.

Here's the direction I chose.

I made a table called tblAwards. It has only one field - Award.

Secondly, I made a table called tblAwardsGiven. This has...
AwardID (PK), Award, DateGiven, MemberID (FK to tblMembers)

The subform now is constructed from tblAwardsGiven, and the combo box shows the awards available from tblAwards.

I will also put code in the Not In List event, so that other awards that might be formulated can be added.

Tom