Feb 2 2012, 03:28 PM
I am attaching a sample db. If you go to the Main form and press Search by Case and then just press Enter, you will see a list of records in the db.
Select the first Case 01-12 and press View Case.
There is a subform which shows related or associated cases. I want the user to be able to select an associated Case and press the GoTo Associated Case button and have the form open with that case as the main File(Case) number. (FileNumber is like a CaseNumber)
I don't write code well but I have tried both an event code on the button or a macro when you click the button. Neither is working so far.
I have the GoTo associated file button running a query that makes a temporary table with the associated file number in it so that I could refer to that somehow as the filter for the form when it reopens. I thought that if I just referred to the subform, that it would close and be lost before the form could open with the new associated file number. Not sure if I need to do that but any help would be appreciated.
So basically, if you are looking at FileNUmber 01-12, then I would like you to be able to select the associated file number 03-12 and press the goto associated file button and have the screen open with that filenumber at the top.
Feb 2 2012, 03:32 PM
DoCmd.OpenForm "formname",,,"[keyfield] = " & Me.keycontrol
where formname is the name of the form you want to open and keyfield is the name of the primary key in the recordsource of the form you are opening and keycontrol is the name of the control holding the value you want to filter for.
It would appear that FileNumber is your keyfield, but not sure what the control name is.
Feb 2 2012, 03:43 PM
I tried the following and it doesn't like the name for the Me.subformname
DoCmd.OpenForm "frmFile", , , "[FileNumber] = " & Me.frmFileAssociated
frmFileAssociated is the name of the subform that holds records for the associated file numbers.
There are two fields in this subform. One is FileNumber because that relates the filenumbers and the second field which is visible on the subform is called AssocFileNumber.
It is the field AssocFileNumber which I need to be used as the FileNUmber to open the main form.
So if FileNUmber 01-12 is on the Main form and the subform called frmFileAssociated has 3 records in it.
I would like to be able to pick 03-12 for example and have the form open with that FileNumber at the top.
Feb 2 2012, 04:28 PM
That's not going to work because a subform doesn't return a value.
The next problem is where you are putting this code. What I would do is put the following in the subforms, Form Double Click event.
DoCmd.OpenForm "frmFile", , , "[FileNumber] = " & Me.FileAssociated
If you then double click on the Record Selector to the left of the record in the subform the form should open to that record.
Feb 3 2012, 05:01 AM
Thank you so much. I think my problem was that my button to go to the associated record (filenumber) was actually on the main form just above the subform but not actually on the subform.
I moved the button onto the subform header and now it all seems to work. I am using your DoCmd statement.
Thanks a million! I have been struggling with this for 2 days now.
Feb 3 2012, 06:04 AM
That is another way to do it. The problem was mostly referencing the right value.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here