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

x
?
Solved

I need to open a main and sub form and have the subform open to a specific record

Posted on 2011-03-09
8
Medium Priority
?
256 Views
Last Modified: 2012-05-11
Hello

My main form is called frmProspects and the subform is frmQuotes.

The two are linked by customer number.

I need to open to a specific customer number and find a specific quote.

I'm sure this should be easy and I'm doing something wrong.  Just not very experienced with sub forms!

Do I set the recordsouce or linked property?

Let's say customer number is 12345 and I need to open to quote 223344

Can anyone help.
0
Comment
Question by:pcalabria
  • 5
  • 3
8 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35084220
Put a textbox on the main form to type in the QuiteID

put a button with this code to filter the subform:

dim lngFilter as long
lngFilter=me.SomeTextBox
    Me![YourSubForm].Form.Filter = "[QuoteID]=" & lngFilter
    Me![YourSubForm].Form.FilterOn = True

If Quote ID is text use this:
dim strFilter as String
strFilter =me.SomeTextBox
    Me![YourSubForm].Form.Filter = "[QuoteID]=" & strFilter
    Me![YourSubForm].Form.FilterOn = True
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35084237
oops sorry, ...for text QuoteID use this:

dim strFilter as String
strFilter =me.SomeTextBox
    Me![YourSubForm].Form.Filter = "[QuoteID]=" & "'" & strFilter & "'"
    Me![YourSubForm].Form.FilterOn = True
0
 

Author Comment

by:pcalabria
ID: 35086437
Thanks Jeff, but I'm still having trouble.
Let me give you the actual setup because I tried to simplify things before.

I have a form which serves as my QUOTE MENU.
From this form, the user can look up a quotenumber using a combo box.
The onupdate event of the combo box sets a global variable of the type long to the quote number.


stQuoteNumber=12345

Normally, when I open the NewProspect form (the main form) I see information about the company.  In the subform, I can navigate through all the quotes that we have generated to this company.

In this case, I would like to open the NewProspect form as usually, with all the quotes available in the subform, but to have the subform automatically connect the correct quote.

So I need to open the NewProspect form and link it to the subform with all the quotes for that company, in quote number order, however, the specific quote displayed should be the current quote.

After reading your email, I wrote the code below, however it does not work.  An inputbox is dispalyed asking me to enter the quotenumber.  Do you see anything here wrong?

Thanks

DoCmd.OpenForm "NewProspect", , , "[CustomerNumber]=" & intCustomerNumber
Forms![newprospect].Form.Filter = "[QuoteNumber]=" & stQuoteNumber
Forms![newprospect].Form.FilterOn = True
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 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 35087235
1. Well your code there does not have stQuoteNumber declared...?
(Or intCustomer, for that matter)
So since you did not post the full code, I can't really comment...
(There may be spelling errors, ..etc)
...and you did not explain what "it does not work" means (error message?, system crash?, incorrect result?)
;-)

2. I would not put the code on the AfterUpdate event of the combobox, because this will force the form to open as soon as the value is selected.
What if the user selected the wrong Quote?
What if you add a few more comboboxes for other things?
This functionality is typically put on a button, so the user can review all of their selections *then* trigger the code.

3. <I can navigate through all the quotes that we have generated to this company.>
"company"?, ...I thought were "Prospects"?

4. You are asking to filter a subform of a Main form without filtering the main form first.
In other words, you are asking the child record to filter the Parent.

Things like this are typically done in a "Grouped" report (This is even tricky to do in a SubReport) where the only the ChildID need be specified.

Sample attached

;-)

JeffCoachman
Database46.mdb
0
 

Author Comment

by:pcalabria
ID: 35088070
Jeff, here are my responses...

Let me thank you in advance for your continued help.

1.
I delcare stQuoteNumber along with all of my global variables in a another module.
intCustomer is actually pulled from another column in the query which displays the quote number in the combo box.

The form that I'm updating is intended to be a vehicle for the salesperson to quickly look at and edit the quote when he recieves an answer to a "follow up email".

Wiht respect to what does does not work mean, "An inputbox is dispalyed asking me to enter the quotenumber".
By this I mean, instead of openning the quote, a pop up box asks for the quotenumber.

2.
I understand your suggestion.  I may need to do this, however, the sales person usually cuts and pastes the quote number from an email he recieves, so I'm trying to save an extra step.  I may need to add to change the combo to a text box and add a go button if this doesn't work out well.

3.
I agree that Prospects is not a very good name for the table, however, it's such a pain to change a table name once lots of query's have been written that I just deal with it!   Prospects is actually a table listing all companies how have ever called us. Each record has a customernumber which is of the type long.

4.  
I'm not sue what this means but basically, when we create a new quote we basically create a new subform for the quote, and can then look at other quotes to the same company.   I'd like to bring up the same screen, but jump to the current quote without first making the sales person search through every quote we've generated for this company.

Thanks again.

0
 

Author Closing Comment

by:pcalabria
ID: 35100676
Although this solution did not completely solve my issue, it did give me a head start on finding the solution that I will ultimately use.  Thanks JEFF
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35105074
OK great.

;-)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35105154
Thanks, actually this was an interesting issue...
So I learned something here as well.

;-)

Jeff
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

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.
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.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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

963 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