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
Solved

Select Specific Record in Subform

Posted on 2008-10-01
20
5,778 Views
Last Modified: 2013-11-29
I'm running Microsoft Access 2003 and I'm trying open up a form based on the selection in a subform which is based off of a query.  Ideally, when that record is selected(double clicked) I want it to open another form which uses the query output as a source but it positions the results at the selected record.  

I have tried a couple of things in my previous attempts.  Currently I have a button that opens up the other form with the right source(using a macro) however I can't get the form to open up to the selected record.
I've tried to use Forms!FormName!SubFormName.CurrentRecord as my offset using the "go to record" action.  But when I use it I get this error "The object does not contain the automation object "CurrentRecord" Is there a way to grab the selected record from the subform and use that number elsewhere?

Any help would be greatly appreciated.
0
Comment
Question by:ToyinOG
  • 11
  • 8
20 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22620571
what is the default view of your subform?

post the sql of the query where te record of the subform is based.

assuming you have a recordID field in the subform

private sub recrdid_dblclick(cancel as integer)

docmd.openform "formX", openargs:=me.recordid


end sub


in the load event of  "formX"

private sub form_load()

if len(me.openargs)>0 then
   with me.recordsetclone
        .findfirst "[recordID]=" & me.openargs

        if not .nomatch then
             me.bookmark=.bookmark
        end if

   end with
end if


end sub


0
 
LVL 13

Assisted Solution

by:wiswalld
wiswalld earned 100 total points
ID: 22623331
Try like this is you like

DoCmd.openform "formname", , , "[FieldName]=" & "'" & Forms!FormName!SubFormName.Form.FieldName & "'"
0
 

Author Comment

by:ToyinOG
ID: 22625801
Capricorn1,

The default view of the subform shows all the possible records.  Then the user uses the combo boxes on the form to filter the records shown in the subform.

My subform query looks like this:
Select Table1.Table1FieldName1, Table2. Table2FieldName1, Table3.Table3FieldName1, Table4.Table 4FieldName1
From Table2 INNER JOIN....INNER JOIN...ON.....
Where (Table1.Table1FieldName1 Like IIf(IsNull(Forms!FormName!ControlName1(which is basically the same name as Table1FieldName1) AND (Table2.Table2FieldName1 Like IIf(IsNull(Forms!FormName!ControlName2(which is basically the same name as Table1FieldName1) AND (Table3.Table3FieldName1 Like IIf(IsNull(Forms!FormName!ControlName2(which is basically the same name as Table1FieldName1)  And (Table4.Table4FieldName1 Like IIf(IsNull(Forms!FormName!ControlName4(which is basically the same name as Table4FieldName1)  

I currently do not have a primary key within the subform.  I tried to do what wiswalled suggested and it worked however it filtered the second form to just that one record. I want the form to open to the current record selected but still be able to navigate to the other records.

Thanks for your help. I really appreciate it!
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22625831
what is the [FieldName] that you use

or post the actual code you are using
0
 

Author Comment

by:ToyinOG
ID: 22625838
Wiswalld,

Thanks I tried the code and it worked.  However, the code filtered the second form to just that one record. I want the form to open to the current record selected but still be able to navigate to the other records.  Do you have any other suggestions?

0
 

Author Comment

by:ToyinOG
ID: 22625954
Capricorn1,

 "[CaseBrowseQry.Case_Name]=" & "'" & Forms!CaseSearch!Cases.Form.Case_Name & "'"
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 22626022
try this


private sub Case_Name_dblclick(cancel as integer)

docmd.openform "formX", openargs:=me.[Case_Name]


end sub


in the load event of  "formX"

private sub form_load()

if len(me.openargs)>0 then
   with me.recordsetclone
        .findfirst "[Case_Name]='" & me.openargs &"'"

        if not .nomatch then
             me.bookmark=.bookmark
        end if

   end with
end if


end sub
0
 

Author Comment

by:ToyinOG
ID: 22626038
Sorry the code was "CaseBrowseQry.Case_Name = Forms!CaseSearch!Cases.Case_Name'"
0
 

Author Comment

by:ToyinOG
ID: 22628706
Capricorn1,

I tried your code. The second form opens up but it still doesn't open to the current record...any other suggestions. Thanks.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22628771
can you post the codes you are using, all of it.

also paste here the actual recordsource of the form you are opening.

better if you can upload here your db. check Attach File below
0
 

Author Comment

by:ToyinOG
ID: 22636508
Capricorn1,

The db I'm working on is on a closed system so I can't attach it. Sorry.  The record source of the CaseBowse form is a query which is:

Select Distinct CaseQry:Case_Name, Case Qry.ProgramName, CaseQry.RequirementType, CaseQry.Mission
From Case Qry

The CaseQry was the query I posted before.

Below is the rest of the VBA code I'm using:

Form A
Private Sub cmdOpenform_Click()
DoCmd.Openform "CaseBrowse", openargs:=Me.[Case_Name]
end sub

In the load event of  CaseBrowse
Private Sub Form_Load()

If Len(Me.Openargs)>0 Then
   With Me.RecordsetClone
        .findfirst "[Case_Name]='" & me.openargs &"'"

        if not .nomatch then
             me.bookmark=.bookmark
        end if
   end with
end if
end sub

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22636753
are there any other codes in the load event of the form?
please copy *all* the codes that ** you have ** and paste them here
0
 

Author Comment

by:ToyinOG
ID: 22637162
I can't copy & past the code because the db is on a closed system.  However, I can describe the form and retype the code.  The form have 4 combo boxes which record sources are based off of the CaseBrowse Qry results ie Case_Name, etc
There are 4 buttons that the user uses to navigate through the records.  A first button, next button, previous, and last button. The following is the first button code.  All of the other buttons have the same code but the last part of the DoCmd changes DoCmd.GoToRecord, , acFirst = acLast, etc

Private Sub first_click()
On Error Go To Err_first_click

DoCmd.GoToRecord, , acFirst
Exit_first_Click:
Exit Sub

Err_first_Click:
MsgBox Error.Description
Resume Exit first click  

Then there is a subform on the form which is based off of a query.  The query looks like this

Select Animal.Name, Animal.Weight, Animal.Color
FROM Animals INNER JOIN Case......ON.....
WHERE Forms!CaseBrowse!CaseName = Case.CaseName

That's everything....
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22637896
the codes i have given you is what you just need to locate  the particular record,
if it is not working, something is causing it not to function properly and since you can not provide an accurate info... sorry I can't no longer help you.
0
 

Accepted Solution

by:
ToyinOG earned 0 total points
ID: 22690024
Capricorn1, thanks for your help. However, I did provide you with every code I had. I actually got it working for anyone who was curious. I ended up using a different code.

Form A
Private Sub cmdOpenform_Click()
DoCmd.Openform "CaseBrowse", openargs:=Forms!FormA!Cases!CaseName
end sub

Then in the On Load event for CaseBrowse:

If Not IsNull(Me.OpenArgs) Then
Me![CaseName].SetFocus
DoCmd.FindRecord Me.OpenArgs, acEntire, , acSearchAll, , acCurrent
End If
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22690080
so you have a subform?
0
 

Author Comment

by:ToyinOG
ID: 22690315
Yes...
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22690534
and you did not mention it..that is the reason why the code was failing..
0
 

Author Comment

by:ToyinOG
ID: 22702363
Yes I did....I refer to it in my original question and in follow up responses..

"I'm running Microsoft Access 2003 and I'm trying open up a form based on the selection in a subform which is based off of a query.  Ideally, when that record is selected(double clicked) I want it to open another form which uses the query output as a source but it positions the results at the selected record"

"Then there is a subform on the form which is based off of a query.  The query looks like this

Select Animal.Name, Animal.Weight, Animal.Color
FROM Animals INNER JOIN Case......ON.....
WHERE Forms!CaseBrowse!CaseName = Case.CaseName"

0
 

Author Comment

by:ToyinOG
ID: 22702369
Yes I did....I refer to it in my original question and in follow up responses..sorry if it wasn't clear

"I'm running Microsoft Access 2003 and I'm trying open up a form based on the selection in a subform which is based off of a query.  Ideally, when that record is selected(double clicked) I want it to open another form which uses the query output as a source but it positions the results at the selected record"

"Then there is a subform on the form which is based off of a query.  The query looks like this

Select Animal.Name, Animal.Weight, Animal.Color
FROM Animals INNER JOIN Case......ON.....
WHERE Forms!CaseBrowse!CaseName = Case.CaseName"



0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

840 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