Record Source on Form - Population

[Guest]

I have multiple fields in a form I created. Is it possible to populate the
fields in the form with information from different tables? Can I have more
than one record souce assigned to the form for the purpose of populating the
fields with information from different tables I created?

 

[Steve Schapel]

Jersey,

The short answer is that you should make a query, including the relevant
tables suitably joined, and then use this query as the Record Source of
your form.

If you need more explicit help, please post back with details of your
tables, and what you want your form to do.

 

[ManningFan]

You can only assign one recordsource to a form.

However, you can open multiple tables based on some piece of
information in your form, and extract data which can be used to
populate the form.

Let's say, for example, you have PartID in one table, Part Cost in a
different table and PartName in another. You can have something on the
backend that says,

PtName = DLookup("PartName", PartTable, "PartNum = " & txtPartID & "")

Then throw the result of PtName into the PartsName textbox on your form
and you've pulled that piece of data in.

I will say, however, that it's a very clunky and processor-intensive
process. It might be better to join the data in a query and link the
query to the form instead.

 

[Guest]

Basically I have one table that I am using to scroll
thru records on the form. What I want is the
applicable information that is related to each
particular record to come up in predefined fields that
I have added to the form (which is coming from
different tables). Once I see the information on the
form when I scroll thru the records I also want to be
able to input some information and be able to save the
data.
Any insight into this would be greatly appreciated.

Thx

 

[Steve Schapel]

Jersey,

Well, "the applicable infromation" doesn't really give us an awful lot
to go on. Bottom line here is that a form can only have one Record
Source. Either a table or a query. One option is you could make a
query by including the required fields from your related tables, so
these fields can then be represented on your form. Depending on the
structure of the data, the query may or may not be updateable, but this
approach is suitable in many scenarios. However, if you have
one-to-many relationships between the table that your original form is
based on, and the related table, then it may be more suitable to make a
form based on the related table, and then place it onto the existing
form as a subform. This will allow you to enter/edit multiple records
in the related table for each main form record.

 

[Guest]

In summary I am trying to make a DB that will track sales from selling
tickets. For example, I creaed one table with the matchup names of each game
I have for sale (ie. Cal vs USC, etc.) When I scroll thru those matchup
records I want the information that is corresponding to that particular game
to come up in the default fields I have created. The default field
information on the particular game (i.e. Cal VS. USC) is housed on a table
that I created specifically for each game (information on things like date of
game, location, etc). These will be one-to-one relationships, though I have
yet to define the relationships yet. Need more insight? How should I go about
tacklilng this?

Thx!

 

[Steve Schapel]

Jersey,

I must apologise for not being clear. When I suggested you give details
of your tables, I meant something like this...

Table: Games
GameSerial
NameOfGame
Location
BlaBla
MoreElse

Table: Sales
SaleID
DateOfSale
Purchaser

Table: SalesDetails
SaleDetailID
SaleID
GameSerial
NoOfTickets
UnitPrice
PaymentMethod
Whatever

One-to-one relationships are extremely rare in databases, and are only
applicable in very specific types of scenarios. Without knowing the
details, I am not 100% sure, but I would expect these specific scenarios
do not apply in your database. Therefore I would recommend that any
tables that are related one-to-one should be combined into a single table.

When I suggested you give specific details about your forms and
requirements, I meant something like this...

"I have a form for Sales, based on my Sales table, with a subform for
SalesDetails, based on my SalesDetails table. On the SalesDetails
subform, I use a combobox to enter the GameSerial, and I would like for
the related NameOfGame from the Games table to be shown on the form."
Or whatever.

By the way, if the above is in fact a close guess to what you really
want, then maybe the information in this article will be of help...
http://accesstips.datamanagementsolutions.biz/lookup.htm

 

[Guest]

How do I go about doing this than? Shoud I create one table (i.e. Cal vs.
USC) that will house all the applicable information for the game (i.e, date.
number of tickets, etc.)? In essence create a table for each of the game
matchups that I have? Than how do I go about making the tables accessible to
the one singular form?



Thx

 

[Guest]

Here is an example of what I currently have :

Table Team ID hold records of various game matchups. In the table there are
6 records with each representing a specific game matchup. For example I have
USC-Cal as one of the records.

I have created a table for each of the 6 records in Table Team ID that holds
the following information:

Date
Number of Tickets Bought
Face Value (per pair)
Gross Cost
Net Cost

For example Table USC-Cal has information in it that is applicable to the
above information that I inputed

I created a Form using Table Team ID as the Control Source. On the same Form
I have the following fields created which I want information to show for the
each particular matchup:

Date
Number of Tickets Bought
Face Value (per pair)
Gross Cost
Net Cost

I also have additional fields on the form such as :

Selling Price
Fees
Net Profit

This I want to input information into for the purpose of sales transactions.

Does this help?

Thx

 

[Steve Schapel]

Jersey,

I think it may help you if you realise that forms do not have data or
fields. Fields and data are in tables or queries. Forms provide a
medium for accessing the data.

Now, as regards "I have created a table for each of the 6 records in
Table Team ID", this is incorrect. All of this data for all or the
records should be in the one table, and there should be another field in
this table to identify the matchup that the tickets sold will relate to.
Thus you have a table of "Matchups", and you have a table of
"TicketSales". I believe what you are calling Table Team ID is the same
as what I am calling Matchups. This table needs a Primary Key field to
uniquely identify each Matchup. I will assume this field is MatchupID
(I assume it will not be Team ID, since presumably there are 2 Teams in
1 Matchup, right?) Ok, so then the TicketSales table also needs a
MatchupID field, and it is on the basis of this that the ticket sales
are identified as to which Matchup they pertain to. Thus, we have a
one-to-many relationship between Matchups and TicketSales, in the sense
that there can be multiple TicketSales for any given Matchup.

As I mentioned earlier, in Access a common way of representing this data
on your forms is to have a Matchups form based on the Matchups table,
and then a TicketSales form, in continuous view, is placed on the
Matchup form as a subform. In this way, you can access the record for a
particular Matchup, and then the TicketSales for that Matchup will be
shown on the subform, and new TicketSales can be entered.

As for the Selling Price, Fees, and Net Profit "fields", you can't have
any such controls on a form if there are no fields associated in the
form's underlying Record Source table/query. Well, with the exception
of an unbound control which you are using for a calculation based on
existing data. So, I am not sure where these fit in.

Sorry, I know this is an incomplete answer, but hopefully will help move
you forward.