Full Version: Pulling Data Into Related Tables/ Field Won't Allow Data Entry
UtterAccess Forums > Microsoft® Access > Access Forms
Olga W
[Whoops - title should have said "control" not "field"]
I have a table in which all people are stored - tblPeople. Potential donors needed to be selected out from this table into a certain form, and to do this I created a related table tblPotentialDonors. This uses fkPersonID as the foreign key field from tblPeople, and has PotentialDonorID as its primary key. To keep things safe I related the pk PersonID to fkPersonID one-to-one. Using this I sucessfully created a form involving the name fields from tblPeople, and an ApproximateWealth field from tblPotentialDonors. When name details are entered into the name control, and an approximate wealth level is entered into that control, the record is entered into both tblPerson and tblPotentialDonors - lovely. Furthermore, only potential donors come up in this form - very lovely.
BIG PROBLEM: I thought that by having the pk - PersonID - visible in the form, as a combo box (displaying the full name) , I could pull individuals already recorded in tblPerson into tblPotentialDonors. (Otherwise there will be massive duplication in tblPerson.) The drop down list from this control indeed listed ALL people in tblPerson. BUT! When I select a name, it isn't entered into the control. Experiments suggest that any form combining the two tables will be limited this way.
Does anyone know whether this is the case, and why? And does anyone know a better arrangement of tables and queries to limit the records in frmPotentialDonros to potential Donors, while allowing records from tblPeople to be pulled into it? I know I've gotten confused about this, and I suspect from the renowned scarcity of one-to-one relationships that this scheme was a bit of a botch job. I thought I'd got away scott free. I didn't want to have a field in the tblPeople listing whether they were a potential donor, I can't remember exactly why. Whatever technique I use to select people out into this form, it has to be sound, as it's going to be replicated many times over in this database. Ultimately we will want tables for staff, lawyers, advisors etcetera.
Thank you very much for any suggestions.
Olga W
I've kind of come up with a way round this. I've prevented additions to the form, and then inserted a button that takes users to a master form for person entry, in which there are subforms containing category specific fields - so you can enter or lookup a name and then pull them into the staff table, or the donor table etc by entering details into subform controls linked to each of those tables. But I'm still concerned about the underlying structure. Perhaps I should post this in the tables/relationships forum instead though....
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.