command button not opening related fields
in [Tables]
|
From: MTSmith on 11 Nov 2009 21:08 I've made a command button with the wizard to open a related form from the main form. Ex: On the contact main form, push a button to see all their contributions. The wizard asks which form to open, I say the Contributions Form, then it says to match the ID field. However, the Main form listed on the left has no fields listed. The one on the right lists all the fields for the Contributions. Why does the Main form not show any fields? Terry Smith
From: BruceM via AccessMonster.com on 12 Nov 2009 08:19 The main form doesn't show any fields? What does it show? Where is this left and right to which you refer? It would help to know something of your database design and structure. Also, post the command button code. If you have Contacts and Contributions you should have a table for each, related by ContactID (one contact, several contributions). With a main form based on the Contacts table and a subform based on the Contributions table there is no need to open another form. Rather, you can make the subform visible or not as needed. If you wish to use a separate form for Contributions you still need it to be related to the Contact table. MTSmith wrote: >I've made a command button with the wizard to open a related form from the >main form. Ex: On the contact main form, push a button to see all their >contributions. The wizard asks which form to open, I say the Contributions >Form, then it says to match the ID field. However, the Main form listed on >the left has no fields listed. The one on the right lists all the fields for >the Contributions. Why does the Main form not show any fields? > >Terry Smith -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200911/1
From: MTSmith on 12 Nov 2009 23:45 I do have 2 tables. One is all the client info with the clientID as the primary key. A related table is the contributions table, where ClientID is the related field. When I use the button wizard, it asks what action I want to perform. I choose Open Form. The next window I say to open the Contribution form. Then I choose open form and find specific data to display. The next window is Which fields contain the matching data? the main form is listed at the top of the box on the left, and the contribution form is on the right. It shows all the fields in the contribution form, but no fields on the left in which to match the ClientID. Very weird. I guess I should explain that I want to do probably two different things here with 2 different buttons. One-Be on a client record. Click a button to open the Contributions form to be able to enter a new contribution. I thought I'd have to do a setvalue to set the ClientID on the contribution form to match the ClientID on the main record? But, I cannot figure out how to do the setvalue in a macro. Two-open the Contribution form just to view all the contributions by the person in the main record. Are you saying it's better just to put a subform at the bottom of the main client record and to hide or show it as needed? I already have one subform on the main record. Would having 2 or 3 subforms slow down the performance? Thanks for your input on this. It's very much appreciated. Terry "BruceM via AccessMonster.com" wrote: > The main form doesn't show any fields? What does it show? Where is this > left and right to which you refer? > > It would help to know something of your database design and structure. Also, > post the command button code. > > If you have Contacts and Contributions you should have a table for each, > related by ContactID (one contact, several contributions). With a main form > based on the Contacts table and a subform based on the Contributions table > there is no need to open another form. Rather, you can make the subform > visible or not as needed. If you wish to use a separate form for > Contributions you still need it to be related to the Contact table. > > MTSmith wrote: > >I've made a command button with the wizard to open a related form from the > >main form. Ex: On the contact main form, push a button to see all their > >contributions. The wizard asks which form to open, I say the Contributions > >Form, then it says to match the ID field. However, the Main form listed on > >the left has no fields listed. The one on the right lists all the fields for > >the Contributions. Why does the Main form not show any fields? > > > >Terry Smith > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200911/1 > > . >
From: MTSmith on 13 Nov 2009 00:05 I put a 2nd subform for the contributions, and it is what I wanted. It automatically fills in the ClientID number and I can add new entries right there. Is this the best way to do it? I still wonder about slowing down performance with too many subforms. I guess I used Access so long ago that I keep thinking I have to open a separate form to add contributions, or view a list of contributions. It's just that when you do use separate forms for entering data, it does not automatically fill in the Client ID #, even though the tables are related. Please let me know if it's better just to use the subform. My other 2 subforms will be a list of each family member, and a list of events the family participates in. "MTSmith" wrote: > I do have 2 tables. One is all the client info with the clientID as the > primary key. A related table is the contributions table, where ClientID is > the related field. > > When I use the button wizard, it asks what action I want to perform. I > choose Open Form. The next window I say to open the Contribution form. Then I > choose open form and find specific data to display. The next window is Which > fields contain the matching data? the main form is listed at the top of the > box on the left, and the contribution form is on the right. It shows all the > fields in the contribution form, but no fields on the left in which to match > the ClientID. Very weird. > > I guess I should explain that I want to do probably two different things > here with 2 different buttons. > One-Be on a client record. Click a button to open the Contributions form to > be able to enter a new contribution. I thought I'd have to do a setvalue to > set the ClientID on the contribution form to match the ClientID on the main > record? But, I cannot figure out how to do the setvalue in a macro. > Two-open the Contribution form just to view all the contributions by the > person in the main record. > > Are you saying it's better just to put a subform at the bottom of the main > client record and to hide or show it as needed? I already have one subform on > the main record. Would having 2 or 3 subforms slow down the performance? > > Thanks for your input on this. It's very much appreciated. > > Terry > > "BruceM via AccessMonster.com" wrote: > > > The main form doesn't show any fields? What does it show? Where is this > > left and right to which you refer? > > > > It would help to know something of your database design and structure. Also, > > post the command button code. > > > > If you have Contacts and Contributions you should have a table for each, > > related by ContactID (one contact, several contributions). With a main form > > based on the Contacts table and a subform based on the Contributions table > > there is no need to open another form. Rather, you can make the subform > > visible or not as needed. If you wish to use a separate form for > > Contributions you still need it to be related to the Contact table. > > > > MTSmith wrote: > > >I've made a command button with the wizard to open a related form from the > > >main form. Ex: On the contact main form, push a button to see all their > > >contributions. The wizard asks which form to open, I say the Contributions > > >Form, then it says to match the ID field. However, the Main form listed on > > >the left has no fields listed. The one on the right lists all the fields for > > >the Contributions. Why does the Main form not show any fields? > > > > > >Terry Smith > > > > -- > > Message posted via AccessMonster.com > > http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200911/1 > > > > . > >
From: BruceM via AccessMonster.com on 16 Nov 2009 08:12 If you use a subform only occasionally you can load its record source as needed. Maybe the subform control is hidden, and there is no record souce assigned to the subform. When you click a button to show the subform you could load its Record Source: Dim lngID as Long Dim strSQL as String lngID = Me.ID strSQL = "SELECT * FROM tblContribution " & _ "WHERE [ID] = " & lngID & _ " ORDER BY [ContributionDate]" Me.SubformControlName.Form.RecordSource = strSQL You would need to reload the record source for every main form record in which you want to view it, which you could do by setting the subform control Visible property to False in the main form's Current event, forcing you to click the button to show the subform, and thereby run the record source SQL again. Or you could just use a named query as the Record Source, so that once the form is visible the related records appear for every main form record. In any case, when entering related records you need a way to populate the linking field's value. The most automatic way to do that is to use the Link Master and Link Child properties of the subform control (the "box" on the main form that contains the subform). You could do this with a standalone form for related records, but it would involve some coding. For instance, in that form's Current event: Dim lngID as Long lngID = Forms!MainForm!ID Me.txtID.DefaultValue = lngID ID is the primary key field of the main form's table and the linking field for the related table. However, if you want to enter contributions from a second client I expect you would need to run the code again to establish the default value, but I am not exactly sure how this would work. I would use form/subform with linking fields, as it is a convenient built-in way of accomplishing this. No need to reinvent the process. If you need to open a form to show related records you could use the Where condition of OpenForm, something like: DoCmd.OpenForm "frmContributions", , , "ID = " & Forms!frmMain!ID However, I will just say again that using Form/Subform probably gives you what you need. If things seem to be slowing down you can rethink things then. In my experience there will not be much of a performance hit unless the subform query has a number of calculations. If there are performance issues the first thing is to be sure you have applied indexes to all fields used for sorting, filtering, and linking. Placing subforms on a tab control may give you a neater form. MTSmith wrote: >I put a 2nd subform for the contributions, and it is what I wanted. It >automatically fills in the ClientID number and I can add new entries right >there. Is this the best way to do it? I still wonder about slowing down >performance with too many subforms. > >I guess I used Access so long ago that I keep thinking I have to open a >separate form to add contributions, or view a list of contributions. It's >just that when you do use separate forms for entering data, it does not >automatically fill in the Client ID #, even though the tables are related. > >Please let me know if it's better just to use the subform. My other 2 >subforms will be a list of each family member, and a list of events the >family participates in. > >> I do have 2 tables. One is all the client info with the clientID as the >> primary key. A related table is the contributions table, where ClientID is >[quoted text clipped - 46 lines] >> > > >> > >Terry Smith -- Message posted via http://www.accessmonster.com
|
Pages: 1 Prev: Help with pivot table Next: 2 different Qty in order details in northwind |