Solved

New Help to Open Subform to a specific record

Posted on 2011-03-10
12
275 Views
Last Modified: 2012-05-11
I have a form named NewProspect which is a MainForm.
It has a subform named NewQuote.

The two are connected by the CustomerNumber field, which is type LONG

If I open the subform properties I see:
LinkChildFields: CustomerNumber
LinkMasterFields: CutomerNumber

The Recordsource of the NewQuote form is a tale name Quotes

I need code that I can place in an event procedure located on another form that will open the NewProspect form to a specific CustomerNumber (for example, 530018) and the NewQuote form to a specific quote that matches a QuoteNumber (for example, 12543) that I will input with code.


As each customer may have a number of quotes, it would be ideal if the form opens with all the quotes that match the specified customernumber selected, and the user can cycle through all the quotes (for this customer) using the navigation buttons on the NewQuotes form---- however, the form must open with the specified quote selected.

Is this posible?

Thanks

0
Comment
Question by:pcalabria
[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
  • 5
  • 4
  • 3
12 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35100754
first, to open the form NewProspect to a specific CustomerNumber;

docmd.openform "NewProspect",,,"[CustomerNumber]= 530018"


now, give more details about the "another Form"


or upload a copy of the db
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 35100762
You use the Bookmark method to move a Form to a specific record. This method works whether the form is a mainform or a subform:

Dim rst As DAO.Recordset
Set rst = Forms("YourForm").RecordsetClone

rst.FindFirst "YourField=" & YourCriteria

If Not rst.NoMatch Then Forms("YourForm").Bookmark = rst.Bookmark

If you must refer to a Subform, you change those calls to the Forms collection to this:

Forms("YourParentForm")("YourSubformControl").Form.RecordsetClone

and

Forms("YourParentForm")("YourSubformControl").Form.Bookmark

Take care to properly build the Criteria also. Remember to surround Text values with single quotes, and Date values with hash marks:

Text:
rst.FindFirst "YourField='" & YourCriteria & "'"

Dates:
rst.FindFirst "YourField=#" & YourCriteria &"#"

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35100778
oops..
to find the record in the subform, you can use this codes

with me.NewQuote.form.recordsetclone
      .findfirst "QuoteNumber=12543"
     if not .nomatch then
      me.NewQuote.form.bookmark=.bookmark
      else
        msgbox "record not found"
    end if
end with
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:pcalabria
ID: 35102641
Cap,

I've been trying to get your code to work but am not sure where it goes.

Does this go in the on_click event for the button on the form that opens the NewProspect form, or in the OnOpen or OnCurrent event of the NewProspect or NewQuote form?

Sorry, but I've never worked with bookmarks before and feel like a fish out of water.
Ideally, all the code should go in the on_click event of the command button on the form that opens the NewProspect form so that I don't have to make changes to the code on the NewProspect and NewQuote forms (as they are used in other routines).

Thanks

LSM, still working on your fix.! Thanks
0
 

Author Comment

by:pcalabria
ID: 35102783
LSM,

I've studied your code and believe this is correct,
but have a couple of questions.

Where does the code go?

Also, do I need to cloes the recordset and set it equal to nothing?

Thanks


Dim rst As DAO.Recordset
Set rst = Forms("NewProspect")("NewQuote").Form.RecordsetClone
rst.FindFirst "QuoteNumber=" & 12543
If Not rst.NoMatch Then Forms("NewProspect")("NewQuote").Form.Bookmark

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35102955

<Does this go in the on_click event for the button on the form that opens the NewProspect form>
Yes, like this

private sub button_click()

docmd.openform "NewProspect",,,"[CustomerNumber]= 530018"

with Forms!NewProspect!NewQuote.form.recordsetclone
      .findfirst "QuoteNumber=12543"
     if not .nomatch then
      Forms!NewProspect!NewQuote.form.bookmark=.bookmark
      else
        msgbox "record not found"
    end if
end with


end sub
0
 

Author Comment

by:pcalabria
ID: 35103779
Cap,

I posted your quote as provided and get the following error message:

Disti-App can't find the  field 'newquote' referred to in your expression.

Does this make any sense?

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35103795
upload a copy of the db
0
 
LVL 84
ID: 35107394
You asked: "Where does the code go?"

I have no idea where you need to fire this, since it's up to you (and your application needs) to answer that. Your original question stated "I need code that I can place in an event procedure located on another form", so I presume you have some event in mind?

You asked: "Also, do I need to cloes the recordset and set it equal to nothing?"

You certainly can, and it's good coding practice to do so. Sorry about that, we sometimes get sloppy in the forums :)

Also, make SURE that you are properly referring to the Subform CONTROL. This is a control placed on your Mainform, and may or may not be named the same as the form you're using as a Subform. To insure that you've identified the Subform CONTROL, review the Properties of the item in Design view (i.e. click on the Subform, and then review the properties). If you see a property named "SourceObject", then you've got the right control. The Subform control has 2 events: Enter and Exit. If you see more than two events listed, you are NOT on the Subform Control.

Be careful with this - misnaming the Subform Control is the single biggest issue with code like this, by far.

0
 

Author Comment

by:pcalabria
ID: 35107495
I wish I could upload the db file, however, I would need permission from my company and that's not likely.  This system is considered company confidential.

By the way "Disti-App" is our name for the application, so I was surprised to see this name appear in the error message:

Disti-App can't find the  field 'newquote' referred to in your expression.

Also, <<<The Recordsource of the NewQuote form is a table named Quotes>>>
NewQuote is a FORM, not a FIELD.


And as previously stated, the line that causes the error is:
with Forms!NewProspect!NewQuote.form.recordsetclone


Thanks



0
 
LVL 84
ID: 35107532
Did you review my comment? Almost certainly you are not properly referring to the Subform CONTROL.
0
 

Author Comment

by:pcalabria
ID: 35318248
LSM,

You are correct, I missed the reference to the Subform Control.  
The code worked perfectly.

Thanks!!
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

751 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