Solved

Select Specific Record in Subform

Posted on 2008-10-01
20
5,743 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 119

Expert Comment

by:Rey Obrero
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
 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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 119

Expert Comment

by:Rey Obrero
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

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

Author Comment

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

Expert Comment

by:Rey Obrero
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

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.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

930 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now