Solved

On Click Event and Recordsource ???

Posted on 2008-06-18
10
285 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: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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
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…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

773 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