Select Specific Record in Subform

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.
ToyinOGAsked:
Who is Participating?
 
ToyinOGConnect With a Mentor Author Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
wiswalldConnect With a Mentor Commented:
Try like this is you like

DoCmd.openform "formname", , , "[FieldName]=" & "'" & Forms!FormName!SubFormName.Form.FieldName & "'"
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
ToyinOGAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
what is the [FieldName] that you use

or post the actual code you are using
0
 
ToyinOGAuthor Commented:
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
 
ToyinOGAuthor Commented:
Capricorn1,

 "[CaseBrowseQry.Case_Name]=" & "'" & Forms!CaseSearch!Cases.Form.Case_Name & "'"
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
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
 
ToyinOGAuthor Commented:
Sorry the code was "CaseBrowseQry.Case_Name = Forms!CaseSearch!Cases.Case_Name'"
0
 
ToyinOGAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
ToyinOGAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
ToyinOGAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
Rey Obrero (Capricorn1)Commented:
so you have a subform?
0
 
ToyinOGAuthor Commented:
Yes...
0
 
Rey Obrero (Capricorn1)Commented:
and you did not mention it..that is the reason why the code was failing..
0
 
ToyinOGAuthor Commented:
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
 
ToyinOGAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.