Forms for Data Entry

  • Thread starter Thread starter -PJ
  • Start date Start date
P

-PJ

I'm very new to forms and my efforts have proven fruitless. Looking for help.

I have a table with a lot of information on different types of trading cards
and a table for players. They are joined on a card_ID. I'm trying to create
a form that will allow me to key in new information in the players table,
choosing card.card_name from a drop down box that will then fill in the
card_id in the player table.

I'm in Access 2003. I've tried several different approaches, either with a
relationship or without, using a query or not, using outer joins or not, none
of which have been successful. If someone could layout the basic steps or
point me to a site that tells me how to do this, I'd be very grateful.
Thanks.
 
For starters, you need a third table.
The player table should contain information about players. It should have a
unique primary key field that identifies the player. I usually use an
Autonumber field for this.
The card table should contain information about cards.It should also have a
unique primary key field to identify the card.
Since many players can own many types of cards, you have a many to many
relationship. Relational databases do not support many to many relationships
naturally, so the way to resolve that is to have a junction table. All it
needs is two fields, Assuming you are using Autonumber fields for the primary
keys of the other two tables, they should both be Long Integer data types.
tblPlayerCard
PlayerID
CardID
The combination of the two fields should be the primary key of the table.

Now, about the form. It needs to be a form/subform. The main form should
have a query based on the Player table. Each field except the primary key
should be bound to a control on the main form so you can enter and view
player data.

Get that working before you move on.

Now, you need a subform for the Card table. Subforms usually have the
default view set to data sheet since you will want to view multiple cards for
a player.

To put a subform on a form, first create a form with a query based on the
Card Table and the PlayerCard table joined on the CardID field.

To put the two together, you put a subform control on the main form. There
are 3 important properties to set for the subform control.
Source Object - Put the name of the form you created to be the subform
Link Master Field(s) - This will be the PlayerID field, that is the primary
key field Player table. This field must be in the main form's record source
query.
Link Child Field(s) - This will be the the CardID field from the subforms
recordset.

Now, this will tie the two together so that when you choose a player in the
main form, only the cards that player owns will be displayed. If you add a
new card for the player, it will update the Card table and the Player/Card
table so the relationship is established.
 
Thanks for the help. I'm still stuck a little. First, a correction, I do
have a link table, and I'm joining cards to the link table to decks (not
players). but that's irrelevant. Below is my basic table structure:

deck:
deck_id (autonumber)
deck information fields

link:
deck_id
card_id
quantity

card:
card_id (autonumber)
card_name
card information fields

OK, so I created a query that joins card and link on card_id (an inner join
- is that correct) and has all the fields from both tables.

I created the first form based on the deck table and included all of the
fields. You said this should be based on a query - just a straight "select *
from deck" query? How do I ensure that each field except the primary is
bound to a control on the main form?

If I manage that part, I'm not sure about some other parts. Where is the
source object? Is this the same as record source? How do I link the parent
& child fields?

Thanks so much for taking the time to help a beginner.
 
--
Dave Hargis, Microsoft Access MVP


-PJ said:
Thanks for the help. I'm still stuck a little. First, a correction, I do
have a link table, and I'm joining cards to the link table to decks (not
players). but that's irrelevant. Below is my basic table structure:

deck:
deck_id (autonumber)
deck information fields

link:
deck_id
card_id
quantity

card:
card_id (autonumber)
card_name
card information fields

OK, so I created a query that joins card and link on card_id (an inner join
- is that correct) and has all the fields from both tables.

I created the first form based on the deck table and included all of the
fields. You said this should be based on a query - just a straight "select *
from deck" query? How do I ensure that each field except the primary is
bound to a control on the main form?
Click to expand...

Yes, just a select query. You put a control on the form and open the
properties dialog. Then you select one of the fields in the list in the
Control Source box. Just check to make sure you have them all.
If I manage that part, I'm not sure about some other parts. Where is the
source object? Is this the same as record source? How do I link the parent
& child fields?
Click to expand...

No, the Source Object is a property of the subform control. A subform is
linked to the main form using a subform control. You put a subform control
on the main form and you put the name of the form to use as a subform in the
the Source Object property. The Link Master and Link Child field properties
of the subform control are used to relate the data between the two forms.
Link Master should have the name of the primary key field from the main
form's recordset and the Link Child should have the name of the field in the
subform's recordset that relates to the record in the main form.
 
Great, I'm making progress!

So now I have a form that has deck_id and some deck information fields, then
an embedded sub-form with card_id, card_name, etc. The missing link is that
I want the card_name field to be a drop-down (combo box) showing all
card_names in the card table, and when one is selected, the card_id field
auto-populates.

Thanks again!
 
Before I can answer this correctly, I need to know how you add new cards to
the cards table. In otherwords, if a card did not exist in the table, where
you you enter the data for the card including the card name?
 
I imported that information from a text file. I don't want to use the form
to update the card table, I just want to use the card table as a reference so
I know what card_name relates to a card_ID I'm updating the link table with.
 
Okay, good.
You can remove the card name control from the subform. The combo will
provide it for you, but it will be bound to the card_id field.
The combo's record source would be:
SELECT card_id, card_name FROM Card;
Set the following properties for the combo:
Column Count = 2
Bound Column = 1
Column Widths 0"; 2"
Limit To List = Yes
Control Source = card_id

The Column Widths property with the 0" will make the card_id hidden. The 2"
can be whatever you need to display the card name.
Now, when you select from the list, the card_id field of the current record
will be updated and the name will be displayed.
 
Ladies & Gentlemen, we have a winner. It works! Thanks so much for your
help & patience!
 
Back
Top