A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

command button not opening related fields



 
 
Thread Tools Display Modes
  #1  
Old November 12th, 2009, 03:08 AM posted to microsoft.public.access.tablesdbdesign
MTSmith
external usenet poster
 
Posts: 11
Default command button not opening related fields

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
  #2  
Old November 12th, 2009, 02:19 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default command button not opening related fields

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/For...esign/200911/1

  #3  
Old November 13th, 2009, 05:45 AM posted to microsoft.public.access.tablesdbdesign
MTSmith
external usenet poster
 
Posts: 11
Default command button not opening related fields

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/For...esign/200911/1

.

  #4  
Old November 13th, 2009, 06:05 AM posted to microsoft.public.access.tablesdbdesign
MTSmith
external usenet poster
 
Posts: 11
Default command button not opening related fields


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/For...esign/200911/1

.

  #5  
Old November 16th, 2009, 02:12 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default command button not opening related fields

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:05 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.