If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Forms for Data Entry
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. |
#2
|
|||
|
|||
Forms for Data Entry
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. -- Dave Hargis, Microsoft Access MVP "-PJ" wrote: 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. |
#3
|
|||
|
|||
Forms for Data Entry
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 structu 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. "Klatuu" wrote: 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. -- Dave Hargis, Microsoft Access MVP "-PJ" wrote: 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. |
#4
|
|||
|
|||
Forms for Data Entry
-- Dave Hargis, Microsoft Access MVP "-PJ" wrote: 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 structu 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? 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? 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. Thanks so much for taking the time to help a beginner. "Klatuu" wrote: 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. -- Dave Hargis, Microsoft Access MVP "-PJ" wrote: 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. |
#5
|
|||
|
|||
Forms for Data Entry
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! "Klatuu" wrote: -- Dave Hargis, Microsoft Access MVP "-PJ" wrote: 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 structu 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? 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? 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. Thanks so much for taking the time to help a beginner. "Klatuu" wrote: 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. -- Dave Hargis, Microsoft Access MVP "-PJ" wrote: 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. |
#6
|
|||
|
|||
Forms for Data Entry
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? -- Dave Hargis, Microsoft Access MVP "-PJ" wrote: 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! "Klatuu" wrote: -- Dave Hargis, Microsoft Access MVP "-PJ" wrote: 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 structu 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? 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? 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. Thanks so much for taking the time to help a beginner. "Klatuu" wrote: 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. -- Dave Hargis, Microsoft Access MVP "-PJ" wrote: 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. |
#7
|
|||
|
|||
Forms for Data Entry
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. "Klatuu" wrote: 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? -- Dave Hargis, Microsoft Access MVP "-PJ" wrote: 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! "Klatuu" wrote: -- Dave Hargis, Microsoft Access MVP "-PJ" wrote: 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 structu 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? 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? 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. Thanks so much for taking the time to help a beginner. "Klatuu" wrote: 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. -- Dave Hargis, Microsoft Access MVP "-PJ" wrote: 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. |
#8
|
|||
|
|||
Forms for Data Entry
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. -- Dave Hargis, Microsoft Access MVP "-PJ" wrote: 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. "Klatuu" wrote: 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? -- Dave Hargis, Microsoft Access MVP "-PJ" wrote: 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! "Klatuu" wrote: -- Dave Hargis, Microsoft Access MVP "-PJ" wrote: 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 structu 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? 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? 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. Thanks so much for taking the time to help a beginner. "Klatuu" wrote: 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. -- Dave Hargis, Microsoft Access MVP "-PJ" wrote: 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. |
#9
|
|||
|
|||
Forms for Data Entry
Ladies & Gentlemen, we have a winner. It works! Thanks so much for your
help & patience! "Klatuu" wrote: 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. -- Dave Hargis, Microsoft Access MVP "-PJ" wrote: 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. "Klatuu" wrote: 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? -- Dave Hargis, Microsoft Access MVP "-PJ" wrote: 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! "Klatuu" wrote: -- Dave Hargis, Microsoft Access MVP "-PJ" wrote: 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 structu 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? 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? 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. Thanks so much for taking the time to help a beginner. "Klatuu" wrote: 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. -- Dave Hargis, Microsoft Access MVP "-PJ" wrote: 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. |
#10
|
|||
|
|||
Forms for Data Entry
Now you are an Ace
-- Dave Hargis, Microsoft Access MVP "-PJ" wrote: Ladies & Gentlemen, we have a winner. It works! Thanks so much for your help & patience! "Klatuu" wrote: 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. -- Dave Hargis, Microsoft Access MVP "-PJ" wrote: 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. "Klatuu" wrote: 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? -- Dave Hargis, Microsoft Access MVP "-PJ" wrote: 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! "Klatuu" wrote: -- Dave Hargis, Microsoft Access MVP "-PJ" wrote: 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 structu 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? 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? 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. Thanks so much for taking the time to help a beginner. "Klatuu" wrote: 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. -- Dave Hargis, Microsoft Access MVP "-PJ" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|