Solved

Select Specific Record in Subform

Posted on 2008-10-01
20
5,799 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
[X]
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
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

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.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

631 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