Solved

On Click Event and Recordsource ???

Posted on 2008-06-18
10
296 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
10 Comments
 
LVL 8

Expert Comment

by:Joe Overman
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:Joe Overman
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
LVL 8

Expert Comment

by:Joe Overman
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:
Joe Overman 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

729 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