Solved

New Help to Open Subform to a specific record

Posted on 2011-03-10
12
271 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
  • 5
  • 4
  • 3
12 Comments
 
LVL 119

Expert Comment

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

Expert Comment

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

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 119

Expert Comment

by:Rey Obrero
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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 119

Expert Comment

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…

744 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

13 Experts available now in Live!

Get 1:1 Help Now