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
|
|||
|
|||
form - fills tables but doesn't read from them
I have two related tables - Events and Contacts. I have created a form which
populates both but the only records it will show are the most recent ones typed into the form itself - it does not pull in the existing information from the tables. I have checked the Properties and Data Entry is set to No and Allow Edit, etc are all Yes. (Interestingly, it also populates the form "Events List" - from the Access 2007 Events Template - which I would like to be able to use but which again will populate the Events table but won't update from it). What do I need to do to get a mutual dialogue going between the tables and the forms? Or is it a case of re-inputing everything through the form(s)? I hope that makes sense. If I need to use code, please could it be explained very simply! Many thanks |
#2
|
|||
|
|||
form - fills tables but doesn't read from them
On Thu, 1 Apr 2010 09:56:05 -0700, Kathryn
wrote: I have two related tables - Events and Contacts. I have created a form which populates both but the only records it will show are the most recent ones typed into the form itself - it does not pull in the existing information from the tables. I have checked the Properties and Data Entry is set to No and Allow Edit, etc are all Yes. (Interestingly, it also populates the form "Events List" - from the Access 2007 Events Template - which I would like to be able to use but which again will populate the Events table but won't update from it). What do I need to do to get a mutual dialogue going between the tables and the forms? Or is it a case of re-inputing everything through the form(s)? I hope that makes sense. If I need to use code, please could it be explained very simply! Many thanks What is the Recordsource property of the form? Open it in design view and view its Properties. Normally the recordsource will be a query. If each Contact may be involved in multiple Events and each Event may involve multiple Contacts, you need another table! How are these two tables related currently? What is each table's Primary Key? -- John W. Vinson [MVP] |
#3
|
|||
|
|||
form - fills tables but doesn't read from them
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. Am away for a few days now (cool the brain down a bit!) but will check out responses when I get back. Many thanks again. "John W. Vinson" wrote: On Thu, 1 Apr 2010 09:56:05 -0700, Kathryn wrote: I have two related tables - Events and Contacts. I have created a form which populates both but the only records it will show are the most recent ones typed into the form itself - it does not pull in the existing information from the tables. I have checked the Properties and Data Entry is set to No and Allow Edit, etc are all Yes. (Interestingly, it also populates the form "Events List" - from the Access 2007 Events Template - which I would like to be able to use but which again will populate the Events table but won't update from it). What do I need to do to get a mutual dialogue going between the tables and the forms? Or is it a case of re-inputing everything through the form(s)? I hope that makes sense. If I need to use code, please could it be explained very simply! Many thanks What is the Recordsource property of the form? Open it in design view and view its Properties. Normally the recordsource will be a query. If each Contact may be involved in multiple Events and each Event may involve multiple Contacts, you need another table! How are these two tables related currently? What is each table's Primary Key? -- John W. Vinson [MVP] . |
#4
|
|||
|
|||
form - fills tables but doesn't read from them
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] |
#5
|
|||
|
|||
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] . |
Thread Tools | |
Display Modes | |
|
|