Can it be done?

[Melissa]

Hi there

I would just like to know if it can, and what is the best way to do a
database which is capable of the following:

i need to list all the clients on a form, and then all of their advertising
information and options on another form (there are hundreds of these
positions and options). what would be the best way to be able to select the
advertising options and methods? a subform? a list box?

 

[Jim Franklin]

Hi Melissa,

It looks like you need 2 subforms on your main form. Subform 1 will list
your clients, the other shows positions & options for the client selected in
subform 1.

Your subform 2 form should use as its recordsource a query which uses the ID
field in subform 1 as part of its WHERE clause.

Your subform 1 form should have an OnCurrent event, which requeries Subform
2 every time a different client record is selected.

Hope this helps point you in the right direction,

Jim

 

[BruceM]

The place to start with such a question is the database design. Presumably
you have a Client table and a related AdvertisingInfo table. Is there also
an Options table, or is that part of AdvertisingInfo? Does AdvertisingInfo
or an Option apply to just one client, or could it apply to several clients?
Why do you want to show the Client AdvertisingInfo and Options on a seperate
form from the Client information?

Or do you mean you would have a table with hundreds of AdvertisingInfo or
Options records, and that you select one or more for each client?

The short answer is that you would probably use a combo box or list box to
select the options. A subform is a way to display records that are related
to the main form record, but does not by itself provide selection options.

More information is needed for there to be a meaningful response. It would
help if you describe in real-world terms, perhaps with some sample data,
just what you hope to accomplish.

 

[Jason]

What would be on the parent form?

Hi Melissa,

It looks like you need 2 subforms on your main form. Subform 1 will list
your clients, the other shows positions & options for the client selected
in subform 1.

Your subform 2 form should use as its recordsource a query which uses the
ID field in subform 1 as part of its WHERE clause.

Your subform 1 form should have an OnCurrent event, which requeries
Subform 2 every time a different client record is selected.

Hope this helps point you in the right direction,

Jim

 

[Jim Franklin]

Hi Jason,

You don't need anything on the parent form, apart from the 2 subforms. This
is because Access does not like having a subform in a continuous form
(although it does seem to be possible to put a subform in the footer of a
continuous form, ignoring any error messages along the way!)

Using my method:
Suppose you have 2 tables, tbl_Customers and tbl_Orders. Each table has an
ID field, e.g. CustomerID, OrderID. The Orders table also has a CustomerID
field to relate back to the Customers table. Your three forms are called
frm_Customers, frm_Orders and frm_Main. frm_Main is your parent, containing
subforms for the other two, which are continuous forms or datasheets. (In
the example below, I name my subform controls "sfrm_xxxxxx")

frm_Customers uses tbl_Customers as its recordsource. The recordsource for
frm_Orders would then be a query with SQL something like:

SELECT * FROM tbl_Orders WHERE
tbl_Orders.CustomerID=[Forms]![frm_Main]![sfrm_Orders].[Form]![CustomerID];

frm_Orders then needs the following code in its OnCurrent event, to requery
the Orders subform every time a different Customer record is selected:

Me.Parent.sfrm_Orders.Requery

This should then give you two lists, both of which are updateable. The
Orders subform will automatically show only the orders related to the
currently selected record in the Customers subform. Note that if you are
ADDING order records here, you would also need a bit of code in the
BeforeInsert event to populate the CustomerID field of the new record.

Hope this helps!

Jim

 

[Acc]

Thank you so much for the information which you provided.

Basically what needs to be done is as follows:

It is for an advertising company which provides clients with laminated ads
on restaurant tables on a wooden unit.

so what needs to happen is to have a form for all the clients (advertisers),
which i have. with all their contact information and contract
information(how many ads, unit price, total price, etc)

then i have a form which is for the restaurants - the restaurant name,
address, contact info, and number of tables in the restaurant. Each time an
advertiser's (clients') ads are put into the various restaurants, then i
need a way to somehow select which restaurants their ads have gone into, and
be able to print out which restaurants their ads are in.

Say for example i have a client named: ABC Removals, their adverts were put
into the following restaurants: 123Food, THe Grill, and Spur...- Once i
have somehow selected these restaurants for ABC Removals, I would also need
to print "per restaurant", so i would need to be able to print out which
Adverts are in spur, for example.

I hope this is more helpful...

If using a listbox would be the most suitable way to select the restaurant,
where would i send the "selectedrestaurants" to ? seperate tables?

THank you once again for all your help in this matter, it is greatly
appreciated

Kind Regards


Melissa

 

[John W. Vinson]

Thank you so much for the information which you provided.

Basically what needs to be done is as follows:

It is for an advertising company which provides clients with laminated ads
on restaurant tables on a wooden unit.

so what needs to happen is to have a form for all the clients (advertisers),
which i have. with all their contact information and contract
information(how many ads, unit price, total price, etc)

then i have a form which is for the restaurants - the restaurant name,
address, contact info, and number of tables in the restaurant. Each time an
advertiser's (clients') ads are put into the various restaurants, then i
need a way to somehow select which restaurants their ads have gone into, and
be able to print out which restaurants their ads are in.

Say for example i have a client named: ABC Removals, their adverts were put
into the following restaurants: 123Food, THe Grill, and Spur...- Once i
have somehow selected these restaurants for ABC Removals, I would also need
to print "per restaurant", so i would need to be able to print out which
Adverts are in spur, for example.

I hope this is more helpful...

If using a listbox would be the most suitable way to select the restaurant,
where would i send the "selectedrestaurants" to ? seperate tables?

THank you once again for all your help in this matter, it is greatly
appreciated

First off: Stop thinking about Forms or Printouts until you have your tables
designed correctly. Forms and Reports are *secondary* - the tables are the
basis of your database, and unless they're set up right you're going to be in
a lot of trouble!

You have a Many to Many relationship: each client will provide ads to many
restaurants, and each restaurant will have many ads. To model this you need
*THREE* tables, not two: a table of Clients, a table of Restaurants, and a
table of Ads. The Ads table would have a field linked to the primary key of
the Clients table (indicating which client is being advertised) and another
field linked to the primary key of the Restaurants table (indicating where
this ad goes). There will undoubtedly be other fields such as the date this ad
started, any special features of the ad in this restaurant as distinct from
other restaurants, etc.

The way you would do the data entry is to use a Form based on the Clients
table (or the Restaurant table, or both - whichever is more convenient) with a
Subform based on the Ads table. If Human Pretzel Chiropractic wants to
advertise in a dozen restaurants, you would open the Clients form, navigate to
that client (or add their data as a new client), and then on the subform use a
combo box to add new records to the Ads table, selecting the relevant
restaurants.

Your printouts would be based on a Query joining all three tables with
appropriate criteria to select what you want to see.

 

[Jim Franklin]

Hi Melissa,

Just to point out, John is completely correct with this. My posting about
having a form with 2 subforms was based on having a one to many
relationship, as was indicated by your original post (user 'Melissa'.) From
what you said in your follow up posting (user 'Acc') this is clearly not the
case.

What John has described is not as difficult as it perhaps sounds, but if you
are still struggling, drop me an email and I will see if I can help you out.

Jim