Problem creating a form for blank records

[MikeB]

I can't even find a suitable title for describing my problem.

I have a bunch of Players. each player plays in a number of Games. So
assuming I want a table of PlayerScores, the foreign keys of
PlayerScores needs to be the primary keys of Players and Games.

What I want now is a form (for a particular Game) that lists the names
of the Players and a blank control for entering the score of that
Player.

I can't get this form to display as I need. If I create records in the
table PlayerScores, I can display them, but I can't get it to create/
display records for a new game. Surely this must e possible? What am I
missing?

Essentially, I need to insert /create the records as I type the score
into each field.

Thanks!

 

[Douglas J. Steele]

Try a form/subform setup.

Bind the parent form to Games, building in the ability to add a new game to
that form.

Bind the subform to PlayerScores.

Have a combo box on the form being used as the subform that uses Players as
its RowSource.

 

[MikeB]

Try a form/subform setup.

Bind the parent form to Games, building in the ability to add a new game to
that form.

Bind the subform to PlayerScores.

Have a combo box on the form being used as the subform that uses Players as
its RowSource.

Doug, this is more or less what I think I tried. I have a form for
games. I can't (yet) add a game on the fly, but I have a different
form to add a game. I also have a subform. It has the following
fields:

Player and Score.

What I'm trying to do is to have a datasheet with the names of the
Players filled in and then I can just go down and type the score in
each field.

If I understand what you say, I have to use the "new record" button
"thingie" at the bottom of the form to create a new "row" in my
datasheet?

You see, if I use the combobox dropdown, then I have to either write
code that makes sure the combobox only presents names that have not
already been used, or I have to visually check that a player is not
accidentally duplicated - right now I have about 67 Players and so the
list is getting a bit long.

 

[Douglas J. Steele]

While I can understand your desire, it's really not a recommended approach,
as it would require that you populate PlayerScores with 67 records each time
you add a game. Putting "placeholder" rows into tables is generally
discouraged.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Try a form/subform setup.

Bind the parent form to Games, building in the ability to add a new game
to
that form.

Bind the subform to PlayerScores.

Have a combo box on the form being used as the subform that uses Players
as
its RowSource.

Doug, this is more or less what I think I tried. I have a form for
games. I can't (yet) add a game on the fly, but I have a different
form to add a game. I also have a subform. It has the following
fields:

Player and Score.

What I'm trying to do is to have a datasheet with the names of the
Players filled in and then I can just go down and type the score in
each field.

If I understand what you say, I have to use the "new record" button
"thingie" at the bottom of the form to create a new "row" in my
datasheet?

You see, if I use the combobox dropdown, then I have to either write
code that makes sure the combobox only presents names that have not
already been used, or I have to visually check that a player is not
accidentally duplicated - right now I have about 67 Players and so the
list is getting a bit long.

 

[John W. Vinson]

I'll just chime in to agree with Douglas that it's a Bad Idea to create empty
"placeholder" records. They have a way of never getting filled in, or not
filled in correctly!

Doug, this is more or less what I think I tried. I have a form for
games. I can't (yet) add a game on the fly, but I have a different
form to add a game. I also have a subform. It has the following
fields:

If your mainform is based on the games table (alone, it should not be based on
a query joining the other tables); and if it is set with Allow Updates and
Allow Additions set to Yes (the default), then there's no reason for a
separate form.

Player and Score.

What I'm trying to do is to have a datasheet with the names of the
Players filled in and then I can just go down and type the score in
each field.

That can be done in the form if need be, but it shouldn't be necessary.

If I understand what you say, I have to use the "new record" button
"thingie" at the bottom of the form to create a new "row" in my
datasheet?

There should be a blank "new row" there already by default. If you have a
paper list of the players in that game, you can simply select each player's
name from a combo box on the subform, enter that player's score, and go to the
next line. You don't even need to scroll down the combo; you can use the "auto
complete" feature, and just start typing the name. The combo will jump to that
name. It can be faster and more efficient than scrolling a 76 row datasheet!

You see, if I use the combobox dropdown, then I have to either write
code that makes sure the combobox only presents names that have not
already been used, or I have to visually check that a player is not
accidentally duplicated - right now I have about 67 Players and so the
list is getting a bit long.

No code is needed for either, and I'm not sure what you're getting at. You can
put a unique Index on the combination of GameID and PlayerID to prevent the
addition of a duplicate record; and - if you want - you can base the combo box
on a query showing only unselected players. This makes your form a bit more
complex, since it would conceal the names of the players you HAVE selected
unless you add another textbox.

 

[MikeB]

Thanks to bouth Doug and Steele and John Vinson. I get what you're
saying, it just isn't quite what I hoped to have. But it is what it
is, I guess.

Mike.

 

[John W. Vinson]

Thanks to bouth Doug and Steele and John Vinson. I get what you're
saying, it just isn't quite what I hoped to have. But it is what it
is, I guess.

Mike.

What you can do, if you want, is to base the Subform on a query joining the
table of Players to PlayerScores, using a *left outer join* - create the query
as usual, select the Join line in the query design window, and choose option 2
(or 3): "Show all records in Players and matching records in PlayerScores".
Include both tables' PlayerID fields (to make the query updateable).

Display the player name from Players and the score from Scores in the subform.
This will let you see all the players (whether their scores have been entered
or not) and update the scores.

 

[MikeB]

What you can do, if you want, is to base the Subform on a query joining the
table of Players to PlayerScores, using a *left outer join* - create the query
as usual, select the Join line in the query design window, and choose option 2
(or 3): "Show all records in Players and matching records in PlayerScores".
Include both tables' PlayerID fields (to make the query updateable).

Display the player name from Players and the score from Scores in the subform.
This will let you see all the players (whether their scores have been entered
or not) and update the scores.

You're my hero.... where can I send your medal?

 

[hawk]

Im new to this "chat help", so forgive my ignorence. Can anyone help me with
a issue? I created a form from 3 different (yet linked) tables. But the
form will not allow me to enter data in my fields. What's up? What did I
miss or do wrong?

 

[Jeff Boyce]

Search Access HELP re: updatable queries.

I'm guessing one reason you aren't able to update the underlying tables'
fields is because Access doesn't "know" which record is to be updated.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

Im new to this "chat help", so forgive my ignorence.

Well... it's not "chat", which is real time and conversational; it's more like
a bulletin board. But you're welcome! It's somewhat better to start a new
"thread" as a new message, rather than replying to an existing discussion,
though, if you want to get noticed.

Can anyone help me with
a issue? I created a form from 3 different (yet linked) tables. But the
form will not allow me to enter data in my fields. What's up? What did I
miss or do wrong?

Normally with multiple related tables it's better to use a Form for the "one"
(or "one-est") table, with one or more Subforms for the related tables. It
appears that you're creating a three-table query. This *can* be used but it is
somewhat harder to implement and often more confusing to use.

 

[MALASPINA FERDINANDO]

Im new to this "chat help", so forgive my ignorence. Can anyone help me
with
a issue? I created a form from 3 different (yet linked) tables. But the
form will not allow me to enter data in my fields. What's up? What did I
miss or do wrong?