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

x
?
Solved

New Help to Open Subform to a specific record

Posted on 2011-03-10
12
Medium Priority
?
280 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 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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

916 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