Control Button to Create a New Record in Another Form

[Guest]

I've developed a MS Access application for Trade Shows. One of my tables
contains all basic client info. This table is used as the data source for
both a complete client form and a partial data form. Both forms can show all
clients (individually). Using the partial data form I want to create a
button that copies the current record and creates a new record in a form
titled Trade Show Attendees. Other data in the Trade Show Attendees must be
entered to complete that attendee's record. Some of the data entered will
populate the TradeShow Attendees table.

Can anyone describe to me how I might accomplish the above by using a
control button?

 

[ruralguy via AccessMonster.com]

All you should need to pass is the ClientID to the next form which can be
passed in the OpenArgs argument. The next form should be based on a query
that joins the Attendees table with the Client table on the ID field. Other
than Foreign Keys, data should not be duplicated in a Relational Database.

 

[Guest]

Thanks for your reply!

I created a query which linked the ClientIDs of the 2 tables (as per your
suggestion). I then created a form populated by fields from the query. For
some reason, when I try to view the form, all I see is a blank form with
neither labels nor fields. I do not understand what's causing this problem.
Any ideas?

 

[ruralguy via AccessMonster.com]

You are describing a form that has no records in its RecordSource. Open the
query in Datasheet mode to see what records return.

Thanks for your reply!

I created a query which linked the ClientIDs of the 2 tables (as per your
suggestion). I then created a form populated by fields from the query. For
some reason, when I try to view the form, all I see is a blank form with
neither labels nor fields. I do not understand what's causing this problem.
Any ideas?

All you should need to pass is the ClientID to the next form which can be
passed in the OpenArgs argument. The next form should be based on a query

[quoted text clipped - 12 lines]

 

[Guest]

The Datasheet View shows only the Field Labels, No data fields at all.

You are describing a form that has no records in its RecordSource. Open the
query in Datasheet mode to see what records return.

Thanks for your reply!

I created a query which linked the ClientIDs of the 2 tables (as per your
suggestion). I then created a form populated by fields from the query. For
some reason, when I try to view the form, all I see is a blank form with
neither labels nor fields. I do not understand what's causing this problem.
Any ideas?

All you should need to pass is the ClientID to the next form which can be
passed in the OpenArgs argument. The next form should be based on a query

[quoted text clipped - 12 lines]

Can anyone describe to me how I might accomplish the above by using a
control button?

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[ruralguy via AccessMonster.com]

There is something in the way you have constructed the query that eliminates
all returned records from the selection. Switch to SQL view in the query and
post the results here and we'll see if we can help.

The Datasheet View shows only the Field Labels, No data fields at all.

You are describing a form that has no records in its RecordSource. Open the
query in Datasheet mode to see what records return.

[quoted text clipped - 12 lines]

 

[Guest]

The SQL view is as follows:

SELECT tbTradeShows.MM_TradeShowNum, tb1Company.CompanyID,
tbTradeShows.MM_ExhibitID, tbTradeShows.MM_Year, tbTradeShows.MM_Name,
tbTradeShows.MM_BoothType, tbTradeShows.MM_ShowCompanyType,
tbTradeShows.MM_ElecRequirements, tbTradeShows.[MM_Water Requirement],
tbTradeShows.MM_Booth1, tbTradeShows.MM_Booth2
FROM tb1Company INNER JOIN tbTradeShows ON (tb1Company.CC_Name =
tbTradeShows.MM_Name) AND (tb1Company.CompanyID = tbTradeShows.MM_ExhibitID);

There is something in the way you have constructed the query that eliminates
all returned records from the selection. Switch to SQL view in the query and
post the results here and we'll see if we can help.

The Datasheet View shows only the Field Labels, No data fields at all.

You are describing a form that has no records in its RecordSource. Open the
query in Datasheet mode to see what records return.

[quoted text clipped - 12 lines]

Can anyone describe to me how I might accomplish the above by using a
control button?

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[ruralguy via AccessMonster.com]

And you feel certain that there is at least one record that meets the
criteria:

tb1Company.CC_Name = tbTradeShows.MM_Name
AND
tb1Company.CompanyID = tbTradeShows.MM_ExhibitID

...is that correct? Try removing each criteria one at a time and see how many
records each one has.

The SQL view is as follows:

SELECT tbTradeShows.MM_TradeShowNum, tb1Company.CompanyID,
tbTradeShows.MM_ExhibitID, tbTradeShows.MM_Year, tbTradeShows.MM_Name,
tbTradeShows.MM_BoothType, tbTradeShows.MM_ShowCompanyType,
tbTradeShows.MM_ElecRequirements, tbTradeShows.[MM_Water Requirement],
tbTradeShows.MM_Booth1, tbTradeShows.MM_Booth2
FROM tb1Company INNER JOIN tbTradeShows ON (tb1Company.CC_Name =
tbTradeShows.MM_Name) AND (tb1Company.CompanyID = tbTradeShows.MM_ExhibitID);

There is something in the way you have constructed the query that eliminates
all returned records from the selection. Switch to SQL view in the query and

[quoted text clipped - 7 lines]

 

[Guest]

I think there is some sort of misunderstanding.

The Trade Show Attendees table is NEW and is empty (no records yet!). I am
trying to create records in this table by clicking on a Client Info Form
button that uses the current record from the Client Info Form to create a new
record in the Trade Show Attendee Form. The Trade Show Attendee Form will
have fields specific for that form. The 2 forms are linked by the CompanyID
and the MM_ExhibitID.

And you feel certain that there is at least one record that meets the
criteria:

tb1Company.CC_Name = tbTradeShows.MM_Name
AND
tb1Company.CompanyID = tbTradeShows.MM_ExhibitID

...is that correct? Try removing each criteria one at a time and see how many
records each one has.

The SQL view is as follows:

SELECT tbTradeShows.MM_TradeShowNum, tb1Company.CompanyID,
tbTradeShows.MM_ExhibitID, tbTradeShows.MM_Year, tbTradeShows.MM_Name,
tbTradeShows.MM_BoothType, tbTradeShows.MM_ShowCompanyType,
tbTradeShows.MM_ElecRequirements, tbTradeShows.[MM_Water Requirement],
tbTradeShows.MM_Booth1, tbTradeShows.MM_Booth2
FROM tb1Company INNER JOIN tbTradeShows ON (tb1Company.CC_Name =
tbTradeShows.MM_Name) AND (tb1Company.CompanyID = tbTradeShows.MM_ExhibitID);

There is something in the way you have constructed the query that eliminates
all returned records from the selection. Switch to SQL view in the query and

[quoted text clipped - 7 lines]

Can anyone describe to me how I might accomplish the above by using a
control button?

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[ruralguy via AccessMonster.com]

Do you have AllowEdits and AllowAdditions set to YES on your form?

I think there is some sort of misunderstanding.

The Trade Show Attendees table is NEW and is empty (no records yet!). I am
trying to create records in this table by clicking on a Client Info Form
button that uses the current record from the Client Info Form to create a new
record in the Trade Show Attendee Form. The Trade Show Attendee Form will
have fields specific for that form. The 2 forms are linked by the CompanyID
and the MM_ExhibitID.

And you feel certain that there is at least one record that meets the
criteria:

[quoted text clipped - 21 lines]

 

[Guest]

YES.

Do you have AllowEdits and AllowAdditions set to YES on your form?

I think there is some sort of misunderstanding.

The Trade Show Attendees table is NEW and is empty (no records yet!). I am
trying to create records in this table by clicking on a Client Info Form
button that uses the current record from the Client Info Form to create a new
record in the Trade Show Attendee Form. The Trade Show Attendee Form will
have fields specific for that form. The 2 forms are linked by the CompanyID
and the MM_ExhibitID.

And you feel certain that there is at least one record that meets the
criteria:

[quoted text clipped - 21 lines]

Can anyone describe to me how I might accomplish the above by using a
control button?

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[ruralguy via AccessMonster.com]

Why are you trying to match up both the name and the ID field?

YES.

Do you have AllowEdits and AllowAdditions set to YES on your form?

[quoted text clipped - 12 lines]

 

[Guest]

Both the CompanyID and MM_ExhibitID are numbers that "share" the same number.
If you only have that number (as well as the other data needed for the Trade
Show) it would be difficult to identify the company attending. I therefore
show on the Trade Show Attendees Form the CompanyName that is part of the
record having that specific CompanyID number. I hope I've made myself clear!

Why are you trying to match up both the name and the ID field?

YES.

Do you have AllowEdits and AllowAdditions set to YES on your form?

[quoted text clipped - 12 lines]

Can anyone describe to me how I might accomplish the above by using a
control button?

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[ruralguy via AccessMonster.com]

Unless I misunderstand what you just said, I believe you will find that
"sharing" a number is not a good idea in a Relational Database. The
CompanyID should be the PrimaryKey field of the Client table (and hopefully
an AutoNumber) and putting this key value in a field in the TradeShow table
as a ForeignKey would make every field in the Client table available to a
query that joined the two tables on this field.

Both the CompanyID and MM_ExhibitID are numbers that "share" the same number.
If you only have that number (as well as the other data needed for the Trade
Show) it would be difficult to identify the company attending. I therefore
show on the Trade Show Attendees Form the CompanyName that is part of the
record having that specific CompanyID number. I hope I've made myself clear!

Why are you trying to match up both the name and the ID field?

[quoted text clipped - 5 lines]

 

[ruralguy via AccessMonster.com]

I re-read all of the posts to make sure I understand what you are trying to
accomplish. Where does the MM_ExhibitID come from? Is it an Exhibitors
table maybe? Does everyone have one of these ID's? Is there more than one
Trade show in the TradeShow table? Does the TradeShow table have a Primary
Key and is it an AutoNumber?

Both the CompanyID and MM_ExhibitID are numbers that "share" the same number.
If you only have that number (as well as the other data needed for the Trade
Show) it would be difficult to identify the company attending. I therefore
show on the Trade Show Attendees Form the CompanyName that is part of the
record having that specific CompanyID number. I hope I've made myself clear!

Why are you trying to match up both the name and the ID field?

[quoted text clipped - 5 lines]

 

[Guest]

First I would like to thank you for your diligence and patience with a dummy
like me! My sophistication level in Access leaves much to be desired yet.

This is the overview:

I have a table called "tbCompany" that has over 3,000 company info records.

I also have a table called "tbTradeShowAttendees" that is currently empty
(no records yet).

I also have a table called "tbTSIndividualAttendees" that has 4000+ records

Each year we organize a Trade Show of which any of 3,000+ companies MAY
attend as well as the individuals associated with tht company.

Therefore each company may have multiple records in the
"tbTradeShowAttendees" table for any YEARS it attended the show. Similarly,
and Company attending the Trade show MAY have multiple individuals attending
the show for that year.

NOW MY STRATEGY:

Have a COMPANY INFO FORM that displays general company information. On this
form have a control button that; 1. Opens a new form called "TRADE SHOW
ATTENDEES FORM." 2. Automatically enter the CompanyID field (which is a
Primary Key-auto-number) into the "TRADE SHOW ATTENDEES FORM"'s field called
MM_ExhibitID. 3. Now that I have this ID into this form I can display the
COMPANY Name that is associated with that CompanyID from the "tbCompany"
table. 4. The "tbTradeShowAttendees" has a Primary Key (auto-numbered)
called "MM_TradeShowNum." The reason there is this Primary Key is that
another table called "tbTSIndividualAttendees" is linked to each Trade Show.
Each year's Trade Show MAY have different or the same individuals attending
the show from that company.

I hope I'm more clear now. Please note that if you can offer a solution,
take into consideration my experience level. Thanks!

I re-read all of the posts to make sure I understand what you are trying to
accomplish. Where does the MM_ExhibitID come from? Is it an Exhibitors
table maybe? Does everyone have one of these ID's? Is there more than one
Trade show in the TradeShow table? Does the TradeShow table have a Primary
Key and is it an AutoNumber?

Both the CompanyID and MM_ExhibitID are numbers that "share" the same number.
If you only have that number (as well as the other data needed for the Trade
Show) it would be difficult to identify the company attending. I therefore
show on the Trade Show Attendees Form the CompanyName that is part of the
record having that specific CompanyID number. I hope I've made myself clear!

Why are you trying to match up both the name and the ID field?

[quoted text clipped - 5 lines]

Can anyone describe to me how I might accomplish the above by using a
control button?

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[ruralguy via AccessMonster.com]

Sorry but I had to leave yesterday to complete a prior commitment. I've read
this over several time so I can get a good feel for your system. If I read
it correctly, the system centers around the COMPANY INFO FORM and It seems to
me that it should center around the TRADE SHOW ATTENDEES FORM with a ComboBox
to select a Company from the tbCompany table and a SubForm that displays the
individuals from the tbTSIndividualAttendees table for this company. Does
that make sense to you?

First I would like to thank you for your diligence and patience with a dummy
like me! My sophistication level in Access leaves much to be desired yet.

This is the overview:

I have a table called "tbCompany" that has over 3,000 company info records.

I also have a table called "tbTradeShowAttendees" that is currently empty
(no records yet).

I also have a table called "tbTSIndividualAttendees" that has 4000+ records

Each year we organize a Trade Show of which any of 3,000+ companies MAY
attend as well as the individuals associated with tht company.

Therefore each company may have multiple records in the
"tbTradeShowAttendees" table for any YEARS it attended the show. Similarly,
and Company attending the Trade show MAY have multiple individuals attending
the show for that year.

NOW MY STRATEGY:

Have a COMPANY INFO FORM that displays general company information. On this
form have a control button that; 1. Opens a new form called "TRADE SHOW
ATTENDEES FORM." 2. Automatically enter the CompanyID field (which is a
Primary Key-auto-number) into the "TRADE SHOW ATTENDEES FORM"'s field called
MM_ExhibitID. 3. Now that I have this ID into this form I can display the
COMPANY Name that is associated with that CompanyID from the "tbCompany"
table. 4. The "tbTradeShowAttendees" has a Primary Key (auto-numbered)
called "MM_TradeShowNum." The reason there is this Primary Key is that
another table called "tbTSIndividualAttendees" is linked to each Trade Show.
Each year's Trade Show MAY have different or the same individuals attending
the show from that company.

I hope I'm more clear now. Please note that if you can offer a solution,
take into consideration my experience level. Thanks!

I re-read all of the posts to make sure I understand what you are trying to
accomplish. Where does the MM_ExhibitID come from? Is it an Exhibitors

[quoted text clipped - 13 lines]