Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

On Click Event and Recordsource ???

Posted on 2008-06-18
10
Medium Priority
?
307 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
8 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

571 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