View Single Post
  #5  
Old April 15th, 2010, 04:26 PM posted to microsoft.public.access.gettingstarted
Kathryn
external usenet poster
 
Posts: 108
Default form - fills tables but doesn't read from them

Hi - the time lag was trying further to sort out various things... but this
is still not working happily. It may be that I need to repost this as a new
query but thought I'd carry on the thread for the moment.

To summarise -
I have put the Form to one side until I get the Tables and Relationships
sorted out.
I have now created a third table (for Billing Information which is linked
to the other Tables)

CONTACTS- It includes personal contact details, etc.
It may, as you suggest relate to zero, one or many Events.

EVENTS - This includes everything related to organising the event (catering/
equipment/rooms used, etc)
It will link to one contact for Billing (who may - or may not be the same as
the organiser).
It will also link to several Contacts (those who attended and who might be
invited to something similar in the future. This is less important at this
stage but might be worth building in now).

BILLING - includes pricing/ mode of payment/ and record of transactions.
It will relate to one Event and to one Contact.

I have puzzled over the table relationships... At the moment, I have EventID
(Events/ Autonumber) EventTitle(Billing/Text) - but they are not the same
data type so it is not happy about referential integrity. Similarly, with
ContactID (Contacts/Autonumber) Contact (Billing/Text). Should I put the
EventID and ContactID fields in the Billing Table and make the relationships
between those?

All the Queries, etc are working fine across the two tables I already had -
some using data from both of them. It really is "just" the form - but that
may suggest an underlying problem!

Hope the above makes sense - please let me know if there is anything I can
do to clarify the issue... And many thanks for any advice you can offer!
"John W. Vinson" wrote:

On Thu, 1 Apr 2010 13:11:01 -0700, Kathryn
wrote:

At the moment, the tables are pretty separate - the only Contact/Event links
are the organiser and, where needed, a Billing Contact - the other Contacts
are our mailing list... This may give a clue as to what another table needs
to be? My aom was to have one form for everything for myself and receptionist
and then various forms for details that others can access... this may be too
ambitious?!

Here, anyway, are the Form's RecordSource properties...

SELECT Events.*, Contacts.Title AS Title_Contacts, Contacts.[First Name] AS
[First Name_Contacts], Contacts.[Last Name] AS [Last Name_Contacts],
Contacts.Address1, Contacts.[Town/City], Contacts.County, Contacts.[Postal
Code], Contacts.Billingaddress1, Contacts.[BillingTown/City],
Contacts.BillingCounty, Contacts.Billingpostcode, Contacts.Billingtitle,
Contacts.BillingFirstName, Contacts.BillingLastName, Contacts.[Business
Phone], Contacts.[E-mail Address], Contacts.[Mobile Phone] FROM Contacts
INNER JOIN Events ON Contacts.[ContactID]=Events.[Contact ID];

The Contact table's primary key is ContactID, the Events simply ID. The
relationship is Contact: ContactID Events: Contact ID.


If each Contact is related to zero, one or many Events, and each Event
pertains to one and only one Contact, then I'd suggest using a Form based on
Contacts and a Subform based on Events. This will be a lot easier to manage
than munging the data from the two tables together in one query and splashing
them all up on a form.
--

John W. Vinson [MVP]
.