?
Solved

On Click Event and Recordsource ???

Posted on 2008-06-18
10
Medium Priority
?
301 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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 1000 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 1000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses

770 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