Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Select Specific Record in Subform

Posted on 2008-10-01
20
Medium Priority
?
5,819 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 400 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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
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 1000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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…

926 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