newbie field update question

[njem]

Organizing a fund raiser in a bowling alley. One table is of team
captains & their details. Another table is of lanes and times and
whether a captain has been assigned to that lane&time. When I change
the assigned captain in the schedule table the related field on the
Captains form doesn't update to show the change.

Details:
Captains table is keyed by combo of last&first name & comp name.
CaptNum is an auto-number, indexed no dupes

Schedule table is keyed on combo of lane & hour.
CaptNum is a number, indexed, no dupes, not required
I used the lookup wizard to make this a pull-down look-up field and
included all 3 fields in Schedule, checked "don't show key field"
which hid the CaptNum in the pull down. Pull down show last, first, co
name.

1-to-1 relationship between CaptNum fields, only where both are equal.
Have tried enforce integrity on and off.

On the Captains form I put a copy of the Schedule:Hour field, set it
to be locked not editable.

It worked! Used the pull down to assign a captain to a slot, opened
the Captains form and went to that captain and there was the right
hour. But if I go back to the schedule and delete them from one time
slot and assign them to another, then open the the captain form, the
time doesn't change. No amount of refreshing or closing re-opening
things fixes it.

A: How do I get it to refresh?

B: I really don't want the lane & hour on the Captains form to be a
field you can put a cursor in. It should just be a label or display
that you can't select. It's never change there. Text boxes can be
assigned a data source but still let you put the cursor in and try to
change it, labels don't let you assign a data source. So?

Thanks,
Tom
Access 2007

 

[Wayne-I-M]

I think you may need to re-think the DB layout and the relationships - sorry
about that.

The basic problem is that you have the time in the wrong table and the
captains need linking to the game "not" the lane.

What you have here is a Many to Many relationship.
Many Captains can play in Many lanes
Many Lanes have Many captains playing in them
Then add in Many Captains play in Many Lanes a Many times etc.

Plus - as you say captains may change

aaaaarrrrgggghhhh

Not too hard to sort out though

You need a linking table

Table1
Participants details
ID
Is Captain (yes/no)
plus other details

Table 2
Lane details (not the time)
LaneID
Plus other details

Linking table
Game details includeing the time
this will include LaneID and ParticipantsID (if is Captain = -1)
Plus other details that refer to "this" game only - eg. score, winners, etc

Create a Game form with Linked tabs
1st with lane details
2nd with captains details.

Of course there are Many ways to do this and this is just one (simple)
method. I don't understand just what it is you're looking for so this may
not be just right but it seems a better idea for something so simple like a
bowling competition. Of course if you wanted t epand the DB (for future
competetions etc) you would need add other "stuff" but for a one off this
will work fine.

Good luck

 

[njem]

Thanks for your detailed response. It is a one-to-one relationship so
I'm not sure if a linking table helps, but let me ask this so I
understand the reason it might help. Is the idea of a link table
supposed to clarify/simplify the relationship for Access? In both my
tables the primary key is not a single field or ID# but a combo of
LName+FName+Co. or Lane+Time. There is a captain auto-number but I
added it later and it's not the primary key. Besides I want things
like the pull-down in scheduling to sort by lname, fname, co name. I
suppose I could make the captain num the primary key if that makes any
difference.

It's a 1-to-1 because a captain (and their team, which is not in this
db) play one game. The Schedule table has one record for each lane
+time combo. A given lane+time can only go to one captain, and a
captain only gets one lane+time.

A game IS a lane a time and a captain. That's all that's unique about
it. So a game table would replicate the schedule table.

A little progress. I think part of my confusion was that on the
Captains Form, the query for its data source was set to only show
where Captain table and Schedule table matched, making unscheduled
captains invisible. I changed the join type to all captains and
matching schedules. I now have it set so a btn next to lane&time on
the Captains form opens a Schedule form as modal, so the user has to
close it after making changes to get back to Captains, and then do a
Refresh All (has to be all), and get back to that captain's record,
then the time & lane on the Captains form is correct.

There will be four people using this at the same time. They each have
their own set of captains so they won't overlap there, but they all
have to schedule lanes. I hope the Refresh All doesn't mess other
operators up.

P.S. I was in Italy last year and my daughter will be studying there
next year.

 

[njem]

I tried a quick experiment with a middle table to link various data
and I still have the same problem that the field that is pulled onto a
form from another table (schedule time) doesn't update after I go to
the Schedule and make a change.

I have it working manually as described but I can't figure out how to
make it work for real. I have to manually click on the "Refresh All"
on the tool bar. That resets me to record 1 so I have to go find the
record I was looking at. I can figure how to set a btn to save the
current rec num and find it again later, but I can find no command
that equals the Refresh All. I've tried macro "Refresh", VB
recordset.update, etc. Nothing seems to equal Refresh All, which is a
heck of a routine to ask operators to do. I don't understand why
getting a field on a form to reflect the data in a table is so hard.
Regardless of how the data is structured getting a field to reflect an
update just can't be this hard. I would think I have it completely
broken but, as I say, Refresh All and go find the record again works,
so it does have the connection. Just stubborn about reflecting the
change.

 

[John W. Vinson]

There will be four people using this at the same time. They each have
their own set of captains so they won't overlap there, but they all
have to schedule lanes. I hope the Refresh All doesn't mess other
operators up.

In that case it's NOT a one to one relationship! Each Lane can involve
multiple Captains (four, from the four different people).

 

[njem]

Sorry, my unclear description. There will be only one captain on one
lane. What I meant to describe was that there will be four people
using the db, mutli-user. I have made a crude workaround. A btn that
opens the Schedule modal, then when that closes it notes what captain
record it's on, what the filter state is, closes and then reopens the
Captain form, restores filter state and goes to the record. I'm sure
this isn't necessary, but it's what I have working.

 

[pompom]

Organizing a fund raiser in a bowling alley. One table is of team
captains & their details. Another table is of lanes and times and
whether a captain has been assigned to that lane&time. When I change
the assigned captain in the schedule table the related field on the
Captains form doesn't update to show the change.

Details:
Captains table is keyed by combo of last&first name & comp name.
CaptNum is an auto-number, indexed no dupes

Schedule table is keyed on combo of lane & hour.
CaptNum is a number, indexed, no dupes, not required
I used the lookup wizard to make this a pull-down look-up field and
included all 3 fields in Schedule, checked "don't show key field"
which hid the CaptNum in the pull down. Pull down show last, first, co
name.

1-to-1 relationship between CaptNum fields, only where both are equal.
Have tried enforce integrity on and off.

On the Captains form I put a copy of the Schedule:Hour field, set it
to be locked not editable.

It worked! Used the pull down to assign a captain to a slot, opened
the Captains form and went to that captain and there was the right
hour. But if I go back to the schedule and delete them from one time
slot and assign them to another, then open the the captain form, the
time doesn't change. No amount of refreshing or closing re-opening
things fixes it.

A: How do I get it to refresh?

B: I really don't want the lane & hour on the Captains form to be a
field you can put a cursor in. It should just be a label or display
that you can't select. It's never change there. Text boxes can be
assigned a data source but still let you put the cursor in and try to
change it, labels don't let you assign a data source. So?

Thanks,
Tom
Access 2007