 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / July 2008Writting two subforms to the same table.
I have two subforms sitting on a form that I am attempting to write to the same table. On the two subforms (A and B) exists the same/identical fields from the single table (table 1). What unique qualifier or identifier (or even programming) is needed to allow me to write both of these subforms (A and B) to the table (table 1). I am able to write to the table now, but when I go back into the form to look at the subforms and call up the subforms through the forms unbound field, it cannot retrieve the information successfully. I believe due to the lack of a unique identifier on the subforms for some reason. Thanks. You've described "how" you are trying to accomplish something (i.e., two subforms, both based on the same table). You've not described "what" or "why" -- the underlying business need. I ask, not out of curiosity, but because there may be alternatives to the approach you've chosen, if only we understood what (not "how") you want to accomplish. Regards Jeff Boyce Microsoft Office/Access MVP >I have two subforms sitting on a form that I am attempting to write to the > same table. [quoted text clipped - 15 lines] > > Thanks. The database as a whole starts with one form (screening) writing to table (person). This is what creates the information for the rest of the database. The user then opens the second form (visit) which contains four subforms (Baseline, Test 2) which write to person table, and (Test 3, Test 4) which write to Visit table. The user selects an unbound drop box at the top of the visit form which pulls in the information for all of the subforms. The problem lies with the subforms Test 3 and Test 4 which contain identical information except for the test name (i.e. Test 3 and Test 4). I have put a drop box at the top of the form for the user to select Test 3 or Test 4, and it gets written to the table Visit (with the autoID) however, you can only write one due to the autoID being unique (this may be a problem.) So...... table visit has 20ish fields that are contained on forms Test 3 and Test 4. Auto ID doesn't work to keep the information unique (from what I have done). The forms will write to the table without the auto ID, however when the forms are called up again in the future, they only bring up one (Test 3 or Test 4). Ideally, they should display Test 3 and Test 4 pulled from the table, but it isn't recalling from the table correctly. Sorry, I can't give any more unique identifiers as it is a medical study. Thanks. No need for specific data values (HIPAA & all!)... Your description is still focusing more on the "how" (use this form, use that subform, ...) rather than the underlying table structure. I may not have understood your explanation, but it sounds like you have repeating fields (Baseline, Test2, ... TestN) in your tblPerson. This might be how you'd have to do it if you were using a spreadsheet, but Access doesn't perform well if you feed it 'sheet data. And there's more ... if I've correctly understood (and you have repeating fields), you have a maintenance nightmare! If you have your test (results) showing as columns/fields in your tblPerson, then you have to customize your queries, forms (and subforms), reports (and subreports), code, macros, etc. to handle the table structure. Then the regulations change and you have to add (or subtract) a Test! Now you have to modify your table structure, queries, forms, subforms, code ...! What a pain!! If, on the other hand, you used a table to hold test results, and a table to hold tests, you can add a new test (or end-date an existing test) without having to make ANY changes to your database/application structure! Or have I misunderstood your description? Regards Jeff Boyce Microsoft Office/Access MVP > The database as a whole starts with one form (screening) writing to table > (person). This is what creates the information for the rest of the [quoted text clipped - 34 lines] > > Thanks. I guess I don't understand the underlying table structure question, I will try again. tblPerson is not really the problem, and I can ignore, tying to give some background as to how it fits in. tblVisit, with primary keys infusionType and subjID has a list of 20+ other 'test values' I am trying to use subform (Test 3) and subform (Test 4) to both write to tblVisit. All 20+ 'test values' are identically displayed in the same fashion on both subforms. I want to be able to select the 'infusionType' (somehow, or have it hidden to automatically identify the subform). To be able to uniquely write both subforms with the identical fields to the same table (which works, I just can't bring it back up to display after writing to the table). Thanks!! >No need for specific data values (HIPAA & all!)... > [quoted text clipped - 30 lines] >> >> Thanks. It all starts with the data... If you don't have that well-normalized, you (*and Access*) will struggle to make the forms and subforms do what you want. Please post a description of your table structures ... table names, field names, and a description of what kind of data is being recorded in those fields. For example, if you were working with signing folks up for classes, you might have something like: tblStudent StudentID FirstName LastName DateOfBirth tblClass ClassID ClassTitle ClassDescription MaximumCapacity trelRegistration RegistrationID StudentID ClassID DateOfRegistratioin (I haven't described the contents of these fields because the fieldnames are fairly self-explanatory. A fieldname like "Test2" doesn't really tell me much about what is stored in the field.) Regards Jeff Boyce Microsoft Office/Access MVP >I guess I don't understand the underlying table structure question, I will > try again. [quoted text clipped - 62 lines] >>> >>> Thanks. tblPerson subjID intAge intHeight intWeight chkBloodWork tblVisit subjID infusionType (Test type) intRestBP (resting Blood Pressure) intRestHR (restingHR) The subforms screening, baseline and 'test1/DXA' are all storing their information efficiently into tblPerson, works great and I use this in many applications. The problem *only* lies with tblVisit and the infusionType. I want to record subjID, intRestBP, intRestHR from both subforms 'Test2/Asc' and 'Test3/Sal'. The only field that contains unique information on the subforms is the field 'infusionType' where I have a drop box to select 'Test2/Asc' or 'Test3/Sal'. I am pretty sure that this is an 'onEvent' or 'onOpen' type of function that needs just a little bit of VB.... I just have no knowledge of programming :( >It all starts with the data... > [quoted text clipped - 40 lines] >>>> >>>> Thanks. It sounds like you've already decided "how" you'll handle this. I'll just point out that your tblVisit doesn't appear to have any date/time information. When did the visit occur? Can you have more than one visit? I'll also point out that saving intAge means you'll have to regularly (?!daily?!) re-check all the folks listed in tblPerson, since people have birthdays most every day of the year, and your information will be potentially invalid the day after Age is recorded. You're better off recording DOB, and using a query to calculate "age" (as of 'today'). I don't get how "screening, baseline and 'text1/DXA' data are being stored in tblPerson -- I didn't notice fields for whatever data you may be recording about these. I'll step back, as it seems you've already determined the way you will do this. Perhaps another newsgroup reader can provide a way to do it your way. My concern is still that you are trying to do something that Access is not designed to (easily) do. Good luck! Regards Jeff Boyce Microsoft Office/Access MVP > tblPerson > subjID [quoted text clipped - 28 lines] > needs just a little bit of VB.... I just have no knowledge of programming > :( The 'how' behind the project is determined by a team of doctors and statisticians long before I ever see it come to me. date/time is stored on the tables but it is more for perspective of the study, a patient can always only have one visit for one study that is why they are called Test 1, 2, 3, etc. intAge is determined, as above, by the PI, long before Analyst receive the project. Along with HIPAA, there are many security reasons we do not handle date of birth. Screen is a form written to tblPatient. Visit contains the subforms Base, DXA, Sal, and Asc. When opening Visit a drop down box is selected that is populated by an unbound field from tblPatient (studyID). >It sounds like you've already decided "how" you'll handle this. > [quoted text clipped - 30 lines] >> needs just a little bit of VB.... I just have no knowledge of programming >> :( If anyone out there is looking for a solution in the future. In order to have multiple subforms (in a tab format) that have identical fields from the same table (read/write): On the subform properties --> in the record source field --> I have written a query SELECT Visit.* FROM Visit WHERE (((Visit.infusionType)=0)); Visit = table infusionType = only unique field between the two identical subforms (one is set to 0 the other is set to 1 for the two unique types) On the subform properties --> onCurrent ---> I created the following VB Private Sub Form_Current() infusionType = 0 End Sub This sets the two forms (uniquely) to default to the type 0 or the type 1. This will allow you to use two identical subforms/identical fields (with the above exception) to write to the same table. >The 'how' behind the project is determined by a team of doctors and >statisticians long before I ever see it come to me. [quoted text clipped - 18 lines] >>> needs just a little bit of VB.... I just have no knowledge of programming >>> :( |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.