 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / November 2006Table relationships
I have two forms based on a single table for company projects. The first form contains data regarding the projects; the second form contains multiple "updates", i.e. a "date" field and a "comment" field where users can add an "update" to the status/condition of the project. These are currently nicely related so that when a user selects a project on the Project form and then opens the Update form and enters a status update, it gets inserted into the appropriate row on the table. Problem is, I'm going to run out of room on the form and the table is eventually going to get messy. What I'd like to do is present the users, when they click the "Add/View Updates" button on the Project form, with a single set of update fields (Date, Comment). I'm thinking I'll need to separate my "Add/View Updates" button into two buttons, one for View and one for Add. When the user clicks View I'd like them to be able to scroll to view all comments for the selected project. When the user clicks Add, I'd like them to be able to add a Date and Comment to the selected project. To this end I've created a second "Comments" table...but I'm having trouble getting the whole thing to work the way I mentioned above. Any suggestions would be very much appreciated. Katie Since you describe a one-to-many relationship (one project can have multiple comments), you'll want a pair of tables (which seems to be what you've described). A common way to handle this in forms is to use a main form/subform construction, where the subform control on the main form uses the (in your case) projectID to link between project and comment. You are keeping ProjectID (or whatever you are calling it) as a foreign key in the Comments table, right?  SignatureRegards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ > I have two forms based on a single table for company projects. The first > form contains data regarding the projects; the second form contains multiple [quoted text clipped - 16 lines] > getting the whole thing to work the way I mentioned above. Any suggestions > would be very much appreciated. No, I don't have keys in either table yet (both have a "Project_ID" field); I can see how to set a primary key...but how do I set a foreign key? Also, my Project_ID field in the Project form is an auto-number field; should it be the same in the Update form, or just a Number field? > Katie > [quoted text clipped - 35 lines] > suggestions > > would be very much appreciated. You don't actually set foreign keys. Instead, you create a relationship between the two tables, and Access takes care of the rest. (Make sure you've defined the primary key in the parent table first). SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > No, I don't have keys in either table yet (both have a "Project_ID" > field); I [quoted text clipped - 53 lines] >> suggestions >> > would be very much appreciated. Katie You set a primary key in design mode. The "key" button (or right-click/select Primary Key) when you have the field highlighted will do the trick. You don't "set" a foreign key ... is just "is". In your child(many) table, make sure you have a field that is of compatible data type (LongInt matches Autonumber), and it helps if you use the same name. In your Comments table, include a field named ProjectID, LongInt. By the way, if you made the foreign key field an Autonumber, you'd be telling Access to invent/create a number that is unique ... which means it would have NO relationship to the Autonumber that Access creates in the Project table! Go to the relationships window and pull in both tables. Drag ProjectID (your Primary Key in Projects) over onto ProjectID (a foreign key in Comments). Access will prompt you for more clarification about the relationship. Check Referential Integrity (but don't bother checking the "cascade" checkboxes right now - you can always do it later, after reading up on what they do in Access HELP). SignatureRegards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ > No, I don't have keys in either table yet (both have a "Project_ID" field); I > can see how to set a primary key...but how do I set a foreign key? [quoted text clipped - 3 lines] > > > Katie Guys...thanks a bunch for the input! So far, so good...now I have to get the functionality set up as mentioned in my original post. I'd like to try to figure it out myself...but if you don't mind, I'll get back to you if I hit a roadblock. Thanks again, Katie > Katie > [quoted text clipped - 27 lines] > > > > > Katie You're welcome. Thank you for taking a run at the next step. Who knows, maybe you'll be back to answer the next question! Consider posting back what the issue was and what you figured out -- folks really benefit from seeing how someone else solve an issue they have... SignatureRegards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ > Guys...thanks a bunch for the input! So far, so good...now I have to get the > functionality set up as mentioned in my original post. I'd like to try to [quoted text clipped - 36 lines] > > > > > > > Katie Believe it or not, I've just gotten back to this! So...I now have a parent form, "Project", and a sub-form, "Updates", each based on separate tables of the same name. Each has a "Project_ID" field, of type auto-number in the Project form, and long integer number in the Updates form, and I've established the relationship between the two. In the Updates table I have a single field for "Date" and one for "Comment". My question is, can these be added to the table dynamically, i.e. user adds an update and a new set of columns is created in the table? Would an "insert" statement do the trick? Further, can they then be displayed dynamically? Thanks, Katie > You're welcome. Thank you for taking a run at the next step. Who knows, > maybe you'll be back to answer the next question! [quoted text clipped - 51 lines] > > > > > > > > > Katie Duh...I think that first question wasn't too bright; I gather that a new row is automatically inserted on a subform when a new entry is made. But I'm still wondering about the display. > Believe it or not, I've just gotten back to this! > [quoted text clipped - 67 lines] > > > > > > > > > > > Katie Katie See comments in-line below... > Believe it or not, I've just gotten back to this! > > So...I now have a parent form, "Project", and a sub-form, "Updates", each > based on separate tables of the same name. Whoa! "...same name..."? A main form based on the "Project" table and a sub-form based on the "Updates" table would be a standard approach. > Each has a "Project_ID" field, of > type auto-number in the Project form, and long integer number in the Updates > form, and I've established the relationship between the two. No, this is what I said would confuse Access and result in NO relationship. An Autonumber field in a table is an independent, arbitrary, unique identifier for rows IN THAT TABLE. If you relate two tables by their Autonumbers, you've just guaranteed that there is NO relationship between them. And by the way, the "field" is in the table, and the form has a "control" that is bound to the underlying field. The "Project_ID" field in "Project" could be Autonumber, but the related "Project_ID" field in "Updates" should be a long integer. > In the Updates table I have a single field for "Date" and one for "Comment". > My question is, can these be added to the table dynamically, i.e. user adds > an update and a new set of columns is created in the table? Would an > "insert" statement do the trick? Further, can they then be displayed > dynamically? The whole reason for determining the fields (i.e., columns) before starting is so that you don't have to "dynamically... add" new fields. If you know that comments will have a project to which they are related, then I'll infer that your Updates tables will need: UpdatesID (you could use an Autonumber here) Project_ID (a long int, pointed back at Projects) CommentDate (don't use "Date" - that's a reserved word in Access) Comment When you create two forms (one for Project, one for Updates), and put the Updates form in the Project form (as a subform), you'll be prompted by Access to show how they are connected. Show the Project_ID of Projects connected to the Project_ID of Updates. SignatureRegards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ > Thanks, > Katie Hi Jeff, Thanks for your response. Sorry...I wasn't very clear in some of my comments; I do, in fact, have a table "Projects" upon which the "Projects" form is based, and a table "Updates" upon which the "Updates" form is based. Each had a "Project_ID" field; in the Project table it is auto-number type, and in the Updates table it is number, long integer. So, I think I'm okay in that regard...and the functionality is what I was looking for. However, I do have one small glitch. I originally had "Project Name" (a field in the Project table/form) as a text box; users would navigate to the desired project using the standard navigation bar at the bottom of the form. I changed the "Project Name" field to a combo box to make it easier for users to find a project. Problem is, when I open the form and click the dropdown arrow in the Project Name combo box, I get an error message, which reads: "The value you entered isn't valid for this field. For example, you may have entered text in a numeric field or a number that is larger that the FieldSize setting permits". I can't see anything wrong with the field type (text) or the FieldSize setting (200). Any ideas? Thanks, Katie > Katie > [quoted text clipped - 47 lines] > > Thanks, > > Katie Forgot to mention the most important part of the problem I outlined in my earlier post...which is that after I get that message, the Project Name of the first project in the table is replaced by the last Project_ID number, i.e. the project "CSUM" becomes "130"...weird. > Katie > [quoted text clipped - 47 lines] > > Thanks, > > Katie Katie Based on your description, I would look at the (new) Project combo box on your form. I suspect you (still) have it bound to a field in the underlying source. To use the combo box to look up a project, consider making it an unbound control. Also, if there's "confusion" between the ID# and the text, is your underlying field defined as a "lookup" data type? SignatureRegards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ > Forgot to mention the most important part of the problem I outlined in my > earlier post...which is that after I get that message, the Project Name of [quoted text clipped - 52 lines] > > > Thanks, > > > Katie Hi Jeff, I unbound the combo box, and, yes, the underlying field is a lookup. I no longer get the message, but when the form opens, displaying the first project, all the data is displayed except that the "Project Name" is blank, and remains blank when navigating through the forms (using "Next" button). > Katie > [quoted text clipped - 73 lines] > > > > Thanks, > > > > Katie Katie One way to get a "Project Name" control to display is to use the AfterUpdate event of the combo box. Your code might look something like: Me!txtProjectName = Me!cboSelectProject.Column(n) where "n" is the n-1 column in your query (i.e., the one returning Project Name). Remember that .Column() is zero-based. I suggest that you not use "lookup" data type fields in your tables. Convert the "combo box" setting on the Lookup tab to "text". SignatureRegards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ > Hi Jeff, > [quoted text clipped - 3 lines] > project, all the data is displayed except that the "Project Name" is blank, > and remains blank when navigating through the forms (using "Next" button). Hi Jeff, I think I did what you suggested...but to clarify, here's what I currently have: In the Projects combobox properties, there are no entries for Control Source, Row Source Type, or Row Source I changed the Project column of the underlying table to "Text Box" in the Lookup tab. Also, FYI, here is the current code for the "After Update" event -- I wasn't sure where to insert the code you suggested... Private Sub Combo48_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[Project_ID] = " & Str(Nz(Me![Combo48], 0)) If Not rs.EOF Then Me.Bookmark = rs.Bookmark Me!txtProjectName = Me!cboSelectProject.Column(2) End Sub At this point, in addition to the earlier problems, the Projects combo box no longer has a list of projects. HELP! :-) > Katie > [quoted text clipped - 17 lines] > blank, > > and remains blank when navigating through the forms (using "Next" button). Hold everything...I've been experimenting (oh-oh!)...and have made some improvements. I left the underlying field properties (i.e. lookup tab) as mentioned below, but did re-enter the source in the combo box properties, thus: Control Source: ProjectName Row Source Type: Table/Query Row Source: Projects (db name) Bound Column: 2 Also, I realized I needed to make other changes to your event code, so it now reads: Me!txtProjectName = Me!cboCombo48.Column(2) Things are better -- when the form opens the name of the first project appears, and if I scroll through the projects using the "Next" button, all is well. However, the dropdown list contains numbers from the "Project_Number" column, which is the number 1 column, "Project_ID" being 0. I've tried all kinds of combinations, but can't seem to capture the list of projects. > Hi Jeff, > [quoted text clipped - 45 lines] > > blank, > > > and remains blank when navigating through the forms (using "Next" button). Katie Open the form in design mode. Highlight the combo box. Select Properties. Find the property dealing with Column Widths. Set the first (?your ID) column to width = 0. Now it won't show. SignatureRegards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ > Hold everything...I've been experimenting (oh-oh!)...and have made some > improvements. I left the underlying field properties (i.e. lookup tab) as [quoted text clipped - 66 lines] > > > blank, > > > > and remains blank when navigating through the forms (using "Next" button). > Katie > [quoted text clipped - 85 lines] > > > > > and remains blank when navigating through the forms (using "Next" > button). Sorry, I don't see a response in this thread. Jeff > > Katie > > [quoted text clipped - 85 lines] > > > > > > and remains blank when navigating through the forms (using "Next" > > button). Sorry...lost it somehow. Anyway, your last suggestion didn't work...so I think I'm giving up. I had wanted users to be able to select a project to view and/or update instead of having to navigate through all the projects. But, there are only about 4 users, updates won't be all that frequent, and there are only about 30 projects. And I've reached the point of diminishing returns! I'm sure there's a way to do it, but I know it's very difficult for you to "see" exactly what the problem is from a distance. In any event, thanks for all your help. I learned a lot, even if the result isn't quite what I wanted. > Sorry, I don't see a response in this thread. > [quoted text clipped - 104 lines] > "Next" > > > button). Katie Thanks for sticking with it ... and I understand "diminishing returns"! If the value in having this issue resolved is high enough for you, you could consider hiring the work done, or hiring a "mentor". There may also be college students in your area who would take on the challenge for love or classwork... Good luck! SignatureRegards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ > Sorry...lost it somehow. > [quoted text clipped - 118 lines] > > "Next" > > > > button). |
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.