Solved

On Click Event and Recordsource ???

Posted on 2008-06-18
10
280 Views
Last Modified: 2013-11-28
I currently have a main form named "JOBMGMT" which includes 2 subforms.  The subform at the top is called "JOBS" and teh subform below it is called "ISSUES".  These 2 subforms are tied together by the field "MAGSO" which acts as my secondary key. Both subforms are also tied to their relative table, which holds all the information for each.  I am trying to make it to where when I click on a certain row within my subform "JOBS", the subform below ("ISSUES") will query itself and return all the fields which contain the same MAGSO that was highlighted (clicked on) above in the JOBS subform.

Here is what I currently have done in order to get this to work...

I set up my On Click event for the top subform ("JOBS").... It reads as follows - forms("JOBMGMT").controls("ISSUES").requery

I then set up my recordsource for teh bottom subform.  

I clicked on the bottom subform and on the form within the data tab i clicked on the button with 3 dots.  The wizard tehn asked me to choose a table to query so I chose the JOBS table that the subform is linked to.  I chose all the fields and below the common field (MAGSO) in the criteria field i used the wizard to create the following: [Forms]![JOBMGMT]![JOBS].[Form]![MAGSO]


Can anyone help me??  What am i doing wrong?
0
Comment
Question by:joegio2504
  • 4
  • 3
10 Comments
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 21816230
try this in the criteria
[Forms]![JOBMGMT].[MAGSO]

instead of this
[Forms]![JOBMGMT]![JOBS].[Form]![MAGSO]
0
 

Author Comment

by:joegio2504
ID: 21816271
Nope.. that didn't work
0
 
LVL 8

Expert Comment

by:masterjojobinks
ID: 21816380
Ok, on the click event for the form JOBS you need to change the record source for the ISSUES subform based on whatever you clicked on (which should contain the MAGSO as the found field, assuming the whatever is a listbox or combo box) then requery the subform.

Something like this:
forms!issues.recordsource = "SELECT tblissues.field1 FROM tblIssues WHERE tblissues.MAGSO = '" & forms![jobs].(whatever object you are clicking on).selection & "';"
forms!issues.requery

0
 

Author Comment

by:joegio2504
ID: 21816439
I do not understand that... could you make an example in access and send it to me??? It would be greatly appreciated...
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 8

Expert Comment

by:masterjojobinks
ID: 21816662
It would be easier and faster for you to post a steralized copy of your DB.
0
 

Author Comment

by:joegio2504
ID: 21816695
It is the form JOBMGMT... I am trying to get the bottom subform to show all related records in relation tothe field that you click on in the above subform.
MAG-NEW-DATABASE.mdb
0
 
LVL 8

Accepted Solution

by:
masterjojobinks earned 250 total points
ID: 21817195
Joegio2504,
What is the purpose of the JobMGMT form.  It looks like you are just using it to hold the two subforms.  If that is the case I would eliminate the jobMGMT form and just put the tblContacts subform onto the TblOrderInfo subform.  Thinking about it even if the jobMGMT form is used i would still nest the subforms.  The control of said forms becomes a lot easier.  You can then use the link child and master fields to control what you want to see.

What I did was change your forms to display as single forms (has to be this way or continuous forms) for a sub form to work.  Nested the  tblContacts subform into the TblOrderInfo subform and set the link child/master fields. this way as you go from record to record in the TblOrderInfo subform the tblContacts subform changes to match.

To accomplish what you invision, that is two seperate sub forms, would require massive amounts of code using either subforms or listboxes.  What I have attached should give you guidence on how to use the nested subforms.
Copy-of-MAG-NEW-DATABASE.mdb
0
 
LVL 3

Assisted Solution

by:bandriese
bandriese earned 250 total points
ID: 21817221
See the sample database below. Look at how that is designed and I think things will start to make more sense. All I used was Wizards to create this, didn't type any code. Also look at the "Relationships" that I defined in the database.

What you need to use is something like the below. The issue is that you don't appear to have a related field in the second sub form that relates to the one above. You need to have a field, for example MasterID that exists in both tables so that they can be linked together. The correct terminology here is, you need to have a relationship between the two tables.

Normaly, how I would design a form like this is to start with the form wizard and build a form based on both tables. Then take the child form and use that as a subform in its parent. In other words, open TblOrderInfo subform in design mode, then insert TblContacts subformtest into that form. If you still want to see a list of records, create a list box that shows all the records in TblOrderInfo.

I don't think you really need to write much if any code to do what you're trying to accomplish. I hope the sample will help make things clearer.



 

Forms![JOBMGMT]![TblContacts subformtest].Form.Filter = "[MasterID] =" & Forms![JOBMGMT]![TblOrderInfo subform].Form![MasterID] 
 

Forms![JOBMGMT]![TblContacts subformtest].Form.Requery

Open in new window

Sample.mdb
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now