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 |
#21
|
|||
|
|||
Multiple Records
It could be the indexing in the Capacities table is wrong. The primary key
should be a composite one on all three columns, PersonnelID, CompanyName and Capacity. In table design view you select all there fields together and then right click and select Primary Key from the shortcut menu. The key symbol should thenshow against all three fields. The PersonnelID and CompanyName fields, as foreign keys, should each be indexed non-uniquely. This will show in the properties sheet of each field in table design view as 'duplicates allowed' in the index property. Its theoretically possible that there could be other indexes set, which you can check by selecting View | Indexes from the menu bar while in table design view. If any of the fields are individually indexed uniquely then this would prevent any rows being inserted into another row if the same value already exists in the field; if a PersonnelID value has already been entered for somebody as a director or shareholder for instance, and the PersonnelID is indexed uniquely (no duplicates) then it would prevent the same person being added as an officer. I'm dubious about this being the problem, however, as it would very coincidental if all officers had already been entered as directors or shareholders. I'd be more inclined to suspect the data as the first culprit. Are you sure that the officers are showing correctly as such in the source tblMyExcelStuff table? If so have they been appended correctly to the Personnel table? They'll just show as names in personnel of course, so you really need to lock for people who are officers but not also shareholders or directors to be sure they've all been appended correctly. Ken Sheridan Stafford, England "pupkiss1965" wrote: Okay, I am going to pull my hair out...I have checked everything I can regarding the append query for my Officers and I get the same error as I previously mentioned. I run it and it populates the query but it won't let me append to the Capacities Table....I have checked the format, the spelling on both the tblMyExcelStuff and even compared and rewrote the code but still nothing...why is it that the other 2 went through and are in the Capacities table but this one is going to drive me nuts? Any suggestions? |
#22
|
|||
|
|||
Multiple Records
Oh no...I didn't set the Primary key and the 2 foreign keys for the
capacities table...actually, I didn't realize I had to do that. So I guess I should redo all of my queries and such and try again. Do you have time to let me know where my primary keys and foreign keys should be and list the relationships to make sure that I have them right? At this point, I am doubting that I even had them right...boy I feel like a real newbie. Thank you so much for your patience and feedback with assistance. I have been following your instructions and they have been working but I don't want to have to redo again if I can avoid it. I do have a hard time with relationships and now obviously designating the proper Primary and Foreign Keys are an issue with me. |
#23
|
|||
|
|||
Multiple Records
Not having created the keys or relationships shouldn't have caused the
problem with officers, so doing so might not solve it unless you've inadvertently created an incorrect primary key or unique index in Capacities. Nevertheless it should be done. You should not have to run the queries again though; the data which has been inserted into the tables should not conflict with the keys or relationships. Incidentally a foreign key is not set in the table design, it’s a by-product of creating a relationship. Taking it table by table: 1. Companies Set the Company name field as the primary key by selecting it in table design by clicking on the field selector (the little square on the far left of the field), right clicking and then selecting primary key from the shortcut menu. If there are any other fields, e.g. Incorporation #, in this table whose values will be unique in the table, i.e. the same value can't appear in more than one row the index the field uniquely by selecting Yes (No Duplicates) as its Index property in its properties sheet. 2. Personnel Set the PersonelID field as the primary key in the same way as above. 3. Capacities The primary key of this table is all the fields in combination, so select all three by clicking on the field selector of each in turn while holding down the Ctrl key, or by clicking a dragging down over all three field selectors. The right click and select primary key. You should then see the key symbol against all the fields. The PersonnelID and Company name fields in this table will both be used as foreign keys, so index each of them non-uniquely by selecting Yes (Duplicates OK) as the Index property of each. The indexes will improve performance when the tables are joined in queries or linked in a form/subform. Relationships: To create the relationships open the relationships window. With the mouse pointer inside the window right click and select Show table. Add the three tables to the window and move them so that they are in a line with Companies on the left, Personnel on the right and Capacities in the middle. With the mouse click and drag from Company Name in the Companies table to Companies Name in the Capacities table. The 'Edit Relationship' dialogue will open; it should say 'one-to-many' at the bottom. In this check the 'Enforce referential integrity' check box and then click the Create button. It should create the relationship, but if you get an error message then there is something in the data which is preventing referential integrity being enforced. This should not happen, but if it does uncheck the 'Enforce referential integrity' check box and then create the relationship until you've sorted out what the problem with the data is (it means that there is a row in Capacities without a match in Companies). Then do exactly the same between Personnel and Capacities by clicking and dragging from PesonnelID in Personnel to PesonnelID in Capacities. You'll see now how the Capacities table has resolved the many-to-many relationship which exists between Companies and Personnel into two one-to-many relationships. This is always how a many-to-many relationship is represented, never directly between two tables. Tables like Capacities which do this are sometimes referred to as 'junction' tables or some other similar term, but that's just a folksy way of saying that they are modelling a relationship between other tables, usually a many-to-many relationship, but in some special circumstances it can be a one-to-many relationship. With the keys and relationships set up as above your 'logical model' or 'schema' is now securely set up so that you can't enter invalid or inconsistent data. However, I'm doubtful that it will solve the problem of appending the officers to Capacities. As I said before one explanation of that would be the presence of incorrect unique indexes, not the lack of indexing. Let me know how you get on with creating the keys, indexes and relationships, and we'll take it from there. Ken Sheridan Stafford, England "pupkiss1965" wrote: Oh no...I didn't set the Primary key and the 2 foreign keys for the capacities table...actually, I didn't realize I had to do that. So I guess I should redo all of my queries and such and try again. Do you have time to let me know where my primary keys and foreign keys should be and list the relationships to make sure that I have them right? At this point, I am doubting that I even had them right...boy I feel like a real newbie. Thank you so much for your patience and feedback with assistance. I have been following your instructions and they have been working but I don't want to have to redo again if I can avoid it. I do have a hard time with relationships and now obviously designating the proper Primary and Foreign Keys are an issue with me. |
#24
|
|||
|
|||
Multiple Records
Ken...you are a genius! Obviously I was doing something terribly wrong with
the Primary and Foreign keys because your guided assistance was successful!!!! I now have the 3 tables that we were trying to achieve with the Capacities Table showing the Officers, Shareholders and Directors together! So now I will attempt to make my forms as per your instruction previously. Is there anything I should do or tweak before setting out on the form tasks? I really really appreciate your assistance and especially your explanations..it starts to make a little more sense. I don't do code but with all of this, I was at least able to understand what we were trying to achieve. I will copy your instructions now for my forms and will let you know. THANKS AGAIN!!! |
#25
|
|||
|
|||
Multiple Records
Where do the 3 queries come in (director, shareholder and officer)? When I
made my Company form I selected the Personnel Table and Capacities Table but I didnt see where I had to put "ControlSource: PersonnelID RowSource: SELECT [PersonnelID], [FirstName] & " " & [LastName] FROM [Personnel] ORDER BY [LastName], [FirstName]; BoundColum: 1 ColumnCount: 2 ColumnWidths: 0cm;8cm From the instructions you gave, I think I missed an important step here. |
#26
|
|||
|
|||
Multiple Records
Take another look at my original reply. You'll see that what you need is a
Company form and then 3 subforms within it so the parent Company form doesn't include the Personnel or Capacities table; its based solely on the Company table. Rather than simply making the Companies table the form's RecordSource, however, its better to first create a query which sorts the companies in name order and then use the query as the RecordSource. The query is a very simple one: SELECT * FROM [Companies] ORDER BY [CompanyName]; Save the query, as qryCompanies say, and then base a form on it. You can use the form wizard to create the form and then amend it later when you add the subforms. The form must be in single form view, though. You now need to create three separate subforms, but as they'll be identical apart from their RecordSource properties you can just create one and then copy and paste it under new names twice to create the others. You just have to change the RecordSource property of the two copies. First create the three queries on which the subforms will be based: For directors: SELECT [CompanyName], [PersonnelID], [Capacity] FROM Capacities WHERE Capacity = "Director"; For shareholders: SELECT [CompanyName], [PersonnelID], [Capacity] FROM Capacities WHERE Capacity = "Shareholder"; And for officers: SELECT [CompanyName], [PersonnelID], [Capacity] FROM Capacities WHERE Capacity = "Officer"; Now create the first form which you'll use as the directors subform. This will be a form in continuous form view based on the first of the above queries. Each subform would be in continuous form view (you can use the wizard to create it) and have just one visible control, a combo box set up as follows: ControlSource: PersonnelID RowSource: SELECT [PersonnelID], [First Name] & " " & [Last Name] FROM [Personnel] ORDER BY [Last Name], [First Name]; BoundColum: 1 ColumnCount: 2 ColumnWidths: 0cm;8cm If your units of measurement are imperial rather than metric Access will automatically convert the last one. The important thing is that the first dimension is zero to hide the first column and that the second is at least as wide as the combo box. Each subform should also have a text box bound to the Capacity field, but with its Visible property set to False (No) and its DefaultValue property set to Director, Shareholder or Officer as appropriate in each case. So if you use the form wizard to create the first subform include the PersonnelID and Capacity fields, but then hide the latter by setting its Visible property to False (No) in form design view. In answer to your question you'll find ControlSource as a property of the combo box on the data tab of its properties sheet. Once you've created the first subform copy and paste it twice, giving it a different name each time, so you might end up with three identical forms called sfrDirectors, sfrShareholders and sfrOfficers for instance (what names you use is entirely your choice, but it helps if the names are meaningful). Open each of the two copies in design view and change the form's RecordSource property to the appropriate one of the three queries. You can now embed the three forms in the original Company form as subforms. Open the Company form in design view and size the window so there's some spare space and then drag the edge of the form design area to create some empty space on it to place the subforms. You can add each subform either by adding a subform control from the toolbox and setting the SourceObject property to one of the three subforms you created, or by dragging and dropping the source forms from the database window onto the design area of the company form. Move the subforms around to position them on the main parent form how you want and size them as appropriate. You'll probably find that putting the three side by side at the bottom makes sense. To link with the parent form the LinkMasterFields and LinkChildFields properties of each of the three subform controls should be CompanyName, though you'll very likely find that Access has already done this for you on the basis of the relationships you created. You should now find that as you moved from company to company through the main form the subforms will change to show the directors, shareholders and officers for the current company. Finally I explained in my first reply how to include unbound combo boxes on the main company form to search for a company or person, but I'll repeat what I said (make sure the field an table names in the code match what you've used) To search for a company you can use the built in 'Find' facility or you can add an unbound combo box to the main form with a RowSource of: SELECT [CompanyName] FROM [Companies] ORDER BY [CompanyName]; and with the following code in its AfterUpdate event procedu Dim rst As Object Dim ctrl As Control Set rst = Me.Recordset.Clone Set ctrl = Me.ActiveControl With rst ..FindFirst "[CompanyName] = """ & ctrl & """" If Not .NoMatch Then Me.Bookmark = .Bookmark End If End With To find a person, whatever their capacity add another unbound combo box to the main form set up in the same way as those in the subforms: RowSource: SELECT [PersonnelID], [FirstName] & " " & [LastName] FROM [Personnel] ORDER BY [LastName], [FirstName]; BoundColum: 1 ColumnCount: 2 ColumnWidths: 0cm;8cm In its AfterUpdate event procedure put the following code: Dim strFilter As String Dim ctrl As Control Set ctrl = Me.ActiveControl strFilter = "[CompanyName] IN" & _ "(SELECT [CompanyName] " & _ "FROM [Capacities] " & _ "WHERE [PersonnelID] = " & ctrl & ")" Me.Filter = strFilter Me.FilterOn = True Note that, unlike the combo box for finding a company, this filters the form. This is because more than one company might be associated with the selected person. You can clear the filter using the built in button on the toolbar, or you can add a 'Show All' button to the main form with the following in its Click event procedu Me.FilterOn = False One thing you might like to do before you do any of this is check that the tables, now that you've got all the data in, do give the correct results when joined. You can do this with a query which will list all companies, with their directors, officers, shareholders. You don't have to save the query if you don't wish to, just create it in SQL view and then switch to datasheet view to see the results: SELECT [Companies].[CompanyName], [Capacity], [FirstName], [LastName] FROM [Companies], [Capacities], [Personnel] WHERE [Companies].[CompanyName] = [Capacities].[CompanyName] AND [Personnel].[PersonnelID] = [Capacities].[PersonnelID] ORDER BY [Companies].[CompanyName], [Capacity], [LastName], [Firstname]; Ken Sheridan Stafford, England "pupkiss1965" wrote: Where do the 3 queries come in (director, shareholder and officer)? When I made my Company form I selected the Personnel Table and Capacities Table but I didnt see where I had to put "ControlSource: PersonnelID RowSource: SELECT [PersonnelID], [FirstName] & " " & [LastName] FROM [Personnel] ORDER BY [LastName], [FirstName]; BoundColum: 1 ColumnCount: 2 ColumnWidths: 0cm;8cm From the instructions you gave, I think I missed an important step here. |
#27
|
|||
|
|||
Multiple Records
Hi Ken
I have created the 3 queries for my subforms but am still a little confused. I made the 3 forms using the wizard and did as you said and changed the RecordSource accordingly. My main form is in Single view. I have made the PersonnelID not visible but I am stuck on something...your comment "Each subform would be in continuous form view (you can use the wizard to create it) and have just one visible control, a combo box set up as follows: ControlSource: PersonnelID " you are talking about the subform and then you talk about a combo box. Am I making a combo box in the subform? I made a combo box on the main form to see if that is what you were referring to but there was no option for the ControlSource being PersonnelID only CompanyName, Incorporation, DateOfIncorporation and SDCFileNumber ( Companies Table). So I am not sure where I am adding the code for the RowSource or selecting the ControlSource as PersonnelID ? I hope this makes sense to you "Ken Sheridan" wrote: Take another look at my original reply. You'll see that what you need is a Company form and then 3 subforms within it so the parent Company form doesn't include the Personnel or Capacities table; its based solely on the Company table. Rather than simply making the Companies table the form's RecordSource, however, its better to first create a query which sorts the companies in name order and then use the query as the RecordSource. The query is a very simple one: SELECT * FROM [Companies] ORDER BY [CompanyName]; Save the query, as qryCompanies say, and then base a form on it. You can use the form wizard to create the form and then amend it later when you add the subforms. The form must be in single form view, though. You now need to create three separate subforms, but as they'll be identical apart from their RecordSource properties you can just create one and then copy and paste it under new names twice to create the others. You just have to change the RecordSource property of the two copies. First create the three queries on which the subforms will be based: For directors: SELECT [CompanyName], [PersonnelID], [Capacity] FROM Capacities WHERE Capacity = "Director"; For shareholders: SELECT [CompanyName], [PersonnelID], [Capacity] FROM Capacities WHERE Capacity = "Shareholder"; And for officers: SELECT [CompanyName], [PersonnelID], [Capacity] FROM Capacities WHERE Capacity = "Officer"; Now create the first form which you'll use as the directors subform. This will be a form in continuous form view based on the first of the above queries. Each subform would be in continuous form view (you can use the wizard to create it) and have just one visible control, a combo box set up as follows: ControlSource: PersonnelID RowSource: SELECT [PersonnelID], [First Name] & " " & [Last Name] FROM [Personnel] ORDER BY [Last Name], [First Name]; BoundColum: 1 ColumnCount: 2 ColumnWidths: 0cm;8cm If your units of measurement are imperial rather than metric Access will automatically convert the last one. The important thing is that the first dimension is zero to hide the first column and that the second is at least as wide as the combo box. Each subform should also have a text box bound to the Capacity field, but with its Visible property set to False (No) and its DefaultValue property set to Director, Shareholder or Officer as appropriate in each case. So if you use the form wizard to create the first subform include the PersonnelID and Capacity fields, but then hide the latter by setting its Visible property to False (No) in form design view. In answer to your question you'll find ControlSource as a property of the combo box on the data tab of its properties sheet. Once you've created the first subform copy and paste it twice, giving it a different name each time, so you might end up with three identical forms called sfrDirectors, sfrShareholders and sfrOfficers for instance (what names you use is entirely your choice, but it helps if the names are meaningful). Open each of the two copies in design view and change the form's RecordSource property to the appropriate one of the three queries. You can now embed the three forms in the original Company form as subforms. Open the Company form in design view and size the window so there's some spare space and then drag the edge of the form design area to create some empty space on it to place the subforms. You can add each subform either by adding a subform control from the toolbox and setting the SourceObject property to one of the three subforms you created, or by dragging and dropping the source forms from the database window onto the design area of the company form. Move the subforms around to position them on the main parent form how you want and size them as appropriate. You'll probably find that putting the three side by side at the bottom makes sense. To link with the parent form the LinkMasterFields and LinkChildFields properties of each of the three subform controls should be CompanyName, though you'll very likely find that Access has already done this for you on the basis of the relationships you created. You should now find that as you moved from company to company through the main form the subforms will change to show the directors, shareholders and officers for the current company. Finally I explained in my first reply how to include unbound combo boxes on the main company form to search for a company or person, but I'll repeat what I said (make sure the field an table names in the code match what you've used) To search for a company you can use the built in 'Find' facility or you can add an unbound combo box to the main form with a RowSource of: SELECT [CompanyName] FROM [Companies] ORDER BY [CompanyName]; and with the following code in its AfterUpdate event procedu Dim rst As Object Dim ctrl As Control Set rst = Me.Recordset.Clone Set ctrl = Me.ActiveControl With rst .FindFirst "[CompanyName] = """ & ctrl & """" If Not .NoMatch Then Me.Bookmark = .Bookmark End If End With To find a person, whatever their capacity add another unbound combo box to the main form set up in the same way as those in the subforms: RowSource: SELECT [PersonnelID], [FirstName] & " " & [LastName] FROM [Personnel] ORDER BY [LastName], [FirstName]; BoundColum: 1 ColumnCount: 2 ColumnWidths: 0cm;8cm In its AfterUpdate event procedure put the following code: Dim strFilter As String Dim ctrl As Control Set ctrl = Me.ActiveControl strFilter = "[CompanyName] IN" & _ "(SELECT [CompanyName] " & _ "FROM [Capacities] " & _ "WHERE [PersonnelID] = " & ctrl & ")" Me.Filter = strFilter Me.FilterOn = True Note that, unlike the combo box for finding a company, this filters the form. This is because more than one company might be associated with the selected person. You can clear the filter using the built in button on the toolbar, or you can add a 'Show All' button to the main form with the following in its Click event procedu Me.FilterOn = False One thing you might like to do before you do any of this is check that the tables, now that you've got all the data in, do give the correct results when joined. You can do this with a query which will list all companies, with their directors, officers, shareholders. You don't have to save the query if you don't wish to, just create it in SQL view and then switch to datasheet view to see the results: SELECT [Companies].[CompanyName], [Capacity], [FirstName], [LastName] FROM [Companies], [Capacities], [Personnel] WHERE [Companies].[CompanyName] = [Capacities].[CompanyName] AND [Personnel].[PersonnelID] = [Capacities].[PersonnelID] ORDER BY [Companies].[CompanyName], [Capacity], [LastName], [Firstname]; Ken Sheridan Stafford, England "pupkiss1965" wrote: Where do the 3 queries come in (director, shareholder and officer)? When I made my Company form I selected the Personnel Table and Capacities Table but I didnt see where I had to put "ControlSource: PersonnelID RowSource: SELECT [PersonnelID], [FirstName] & " " & [LastName] FROM [Personnel] ORDER BY [LastName], [FirstName]; BoundColum: 1 ColumnCount: 2 ColumnWidths: 0cm;8cm From the instructions you gave, I think I missed an important step here. |
#28
|
|||
|
|||
Multiple Records
Oh man am I an idiot....I finally figured out what you have been saying...I
was reading it all wrong...I now realize that if I click on the Capacity field I can "Change To" a combo box! Sorry about that...it helps when you don't have an idiot reading instructions! LOL |
#29
|
|||
|
|||
Multiple Records
Ken
Well I thought I had it but obviously I don't. In my subform I made the Capacity field into a combo box and the label as the text box but it makes it unbound. I tried doing it with the add fields button but it still comes up as an unbound text box. When I go to the properties page and select control source to make it a bound text box the only options are the Capacity, PersonnelID and CompanyName. There isn't an option for me to select a DefaultValue property to Director |
#30
|
|||
|
|||
Multiple Records
Ken
I was wondering if I could send you what I have for review. I am not getting the results that I should be with the subforms. It is showing 3 records for Director and 3462 for Officers and 3600 for shareholders...I am pretty sure that one company doesn't have that many shareholders. LOL. You would really help me out if you could review this and advise me of things that I have done wrong. |
Thread Tools | |
Display Modes | |
|
|