Help with List box and subform

How can I make a sub form go a record based on the selection made in a list 
box in the main form?
thanks
0
Utf
12/5/2007 4:28:02 PM
access.formscoding 7493 articles. 0 followers. Follow

6 Replies
1331 Views

Similar Articles

[PageSpeed] 5

The simplest way would be to bind your subform to the mainform using the Link 
Child and Link Master fields on the subform controls data property tab.  If 
that won't work for you, for some reason (if for example the subform is 
already bound by some other field, and you are using the listbox to expand on 
that feature), try:

Private Sub lst_Test_Click()

    Dim rs As dao.Recordset
    Dim frm As Form

    Set frm = Me.sub_MySub.Form
    Set rs = frm.RecordsetClone

    rs.FindFirst "[Numbers] = " & Me.lst_Test.Value
    If Not rs.NoMatch Then frm.Bookmark = rs.Bookmark

    rs.Close
    Set rs = Nothing
    
End Sub

Note:
1.  the "sub_MySub" text on the line that starts "Set frm = " should be 
replaced by the name of the subform control on your main form, not the name 
of the form being used as the subform. 
2.  "lst_Test" should be replace with the name of the listbox control on 
your form.

HTH
Dale
-- 
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



"Al" wrote:

> How can I make a sub form go a record based on the selection made in a list 
> box in the main form?
> thanks
0
Utf
12/5/2007 6:50:05 PM
Al wrote:

>How can I make a sub form go a record based on the selection made in a list 
>box in the main form?


Here's one way to do that using the list box's AfterUpdate
event:

With Me.yoursubformcontrolname.Form.Recordset
	.FindFirst "[your PK field] = " & Me.[thelistboxname]
End With

-- 
Marsh
MVP [MS Access]
0
Marshall
12/5/2007 7:03:09 PM
Marshall,

If you "know for certain" that there is a record in the subform that matches 
the value you are looking for this works great.  

But if you are not sure, and the FindFirst method doesn't find the value you 
are looking for, it moves the record pointer off of the record that was 
currently selected in the subform to the first record in the subform.

I'm not sure I would want that result.

Dale
-- 
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



"Marshall Barton" wrote:

> Al wrote:
> 
> >How can I make a sub form go a record based on the selection made in a list 
> >box in the main form?
> 
> 
> Here's one way to do that using the list box's AfterUpdate
> event:
> 
> With Me.yoursubformcontrolname.Form.Recordset
> 	.FindFirst "[your PK field] = " & Me.[thelistboxname]
> End With
> 
> -- 
> Marsh
> MVP [MS Access]
> 
0
Utf
12/5/2007 8:11:00 PM
Thank you, both ways work
Al

"Marshall Barton" wrote:

> Al wrote:
> 
> >How can I make a sub form go a record based on the selection made in a list 
> >box in the main form?
> 
> 
> Here's one way to do that using the list box's AfterUpdate
> event:
> 
> With Me.yoursubformcontrolname.Form.Recordset
> 	.FindFirst "[your PK field] = " & Me.[thelistboxname]
> End With
> 
> -- 
> Marsh
> MVP [MS Access]
> 
0
Utf
12/5/2007 8:21:00 PM
Thanks Dale

"Dale Fye" wrote:

> Marshall,
> 
> If you "know for certain" that there is a record in the subform that matches 
> the value you are looking for this works great.  
> 
> But if you are not sure, and the FindFirst method doesn't find the value you 
> are looking for, it moves the record pointer off of the record that was 
> currently selected in the subform to the first record in the subform.
> 
> I'm not sure I would want that result.
> 
> Dale
> -- 
> Don''t forget to rate the post if it was helpful!
> 
> email address is invalid
> Please reply to newsgroup only.
> 
> 
> 
> "Marshall Barton" wrote:
> 
> > Al wrote:
> > 
> > >How can I make a sub form go a record based on the selection made in a list 
> > >box in the main form?
> > 
> > 
> > Here's one way to do that using the list box's AfterUpdate
> > event:
> > 
> > With Me.yoursubformcontrolname.Form.Recordset
> > 	.FindFirst "[your PK field] = " & Me.[thelistboxname]
> > End With
> > 
> > -- 
> > Marsh
> > MVP [MS Access]
> > 
0
Utf
12/5/2007 8:22:00 PM
Dale Fye wrote:

>Marshall,
>
>If you "know for certain" that there is a record in the subform that matches 
>the value you are looking for this works great.  
>
>But if you are not sure, and the FindFirst method doesn't find the value you 
>are looking for, it moves the record pointer off of the record that was 
>currently selected in the subform to the first record in the subform.
>
>I'm not sure I would want that result.


You're right, Dale.

I don't think the issue will arise in this case, but safe is
better than sorry.

-- 
Marsh
MVP [MS Access]
0
Marshall
12/5/2007 9:39:30 PM
Reply:

Similar Artilces:

Corrupt money data file
I've posted this on the Money UK site but with no reaction ; I wonder if I can reach a larger audience on here - I'm desperate to sort this out, thanks. ----------------------------------------------------------------------------------------- On running Money, I received a message "Money has encountered a problem and needs to close" - no indication of what the problem was. I couldn't get past the splach screen I found article 836197 in the knowledge base which seems to covers this ; did what it advised, even to the extent of doing an uninstall and re-install, but ...

SEARCH BOX
First thank you in advance for any help. I want to put a search box on the first sheet of my program to enter data, either a name or an assigned number, and have it search for and go to that line of information on another sheet. Can I do this or do you have another suggestion? This should do it. Change sheet name and column numbers to suit Sub nameornumberfind() With Sheets("sheet34") mv = InputBox("Enter name or number") If IsNumeric(mv) Then 'MsgBox "number" mc = 4 Else 'MsgBox "text" mc = 1 End If Set mf = .Columns(mc).fi...

sent mail appears in my in-box
How can I change settings to prevent e-mail that I send also going to my in-box? Thank you in advance Under Tools->Options->Preferences->Email Options->Advanced Email Options, do you have the box checked to save mail in the same folder? Do you have any rules that affect mail after it is sent? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and finding no answer: TopCat <topcat@vettenut.com> asked: | How can I change settings to prevent e-mail that I send | also going to my in-box? | | Tha...

IF Function Help
How do I write a command that says: =if(a1=1&b1=a number, c1/d1,"na") How do I write the "a number" part? Hi Flutie99 Try: =IF(A1=1,IF(ISNUMBER(B1)=TRUE,C1/D1,"na")) -- Sincerely, Michael Colvin "Flutie99" wrote: > How do I write a command that says: > > =if(a1=1&b1=a number, c1/d1,"na") > > How do I write the "a number" part? If I am reading your post correctly you want a cell to equal C1/D1 when A1=1 and B1= some some number and for the cell to = "na" otherwise =if(and(A1=1,B1=xx),C1/D1,&...

Help in my VB Code .. to Delete
This is a multi-part message in MIME format. ------=_NextPart_000_0089_01C34629.B378D5B0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable hi,=20 I have been trying to solve this problem since morning, and haven't = really come up with a solution. it is frustrating. please help. =20 i have a datagrid and got a delete column =20 <asp:datagrid id=3D"dgdocument" runat=3D"server" = AutoGenerateColumns=3D"False" EnableViewState=3D"True" = OnDeleteCommand=3D"DeleteCommand"> <Columns&...

CPropertySheet create failing when adding to dialog box
Hi all, I am having problems with a class that I made that inherits from CPropertySheet. I need to add this class to a dialog box with the following code: m_appPropSheet.AddPage(m_saleIllustrationPropPageClientOne); m_appPropSheet.AddPage(m_saleIllustrationPropPageClientTwo); m_appPropSheet.Create(this, WS_CHILD | WS_VISIBLE, 0); m_appPropSheet.EnableStackedTabs(FALSE); m_appPropSheet.ModifyStyleEx (0, WS_EX_CONTROLPARENT); m_appPropSheet->ModifyStyle(0, WS_TABSTOP); m_appPropSheet.SetWindowPos(NULL, 0, 25, 0, 0, SWP_NOZORDER | SWP_NOSIZE | SWP_NOACTIVATE); ...

Publisher documents do not print. Help!
I just set up a new computer with an HP Deskjet 5440 and Microsoft Publisher 2003. Documents print perfectly from every software package except Publisher. Publisher documents don't print at all, even after I reinstalled the printer drivers. The entire document or just the pictures? If it is just the pictures, click the advanced print settings button in the print dialogue, graphics and fonts tab. Be sure "Do not print any graphics" is checked. Look on the HP site to be certain you have the most current driver. http://h10025.www1.hp.com/ewfrf/wc/softwareList?lc=en&cc=us...

HELP: Need To Print Better Quotes
I am having a terrible time being able to print a good looking quote from MS CRM. it either prints the plain quote (Address, amount..etc) or i can print the product list (With no address, or nothing).. is there anyway to print a normal quote as others do where you can have the company informaiton and also all of the products that they are buying listed there? one would think that is the most basic feature that is not there.. don't you guys think? ANY help you can provide will be appreciated.. Thanks Quoteless In DC :-) Add a customized quote via Reports using Crystal. >-----Origin...

Using a publisher document with images in list builder
I have created a newsletter in Publisher, saved as a web page, tried to upload to list builder - but when we get to the point where we need to upload the images , there are many more images listed than what I have actually used in the doc. And I can't get the 4 images that ARE in the document to load correctly in the right spots. It is asking me to upload about 12 images and like I said, we have only included 4 - a logo and 4 seasonal images. I have pulled my hair out all day trying to accomplish a mass mailing and could really use some help with this last hurdle. ...

Help building a filter on a form
Okay, I downloaded the Issue database template and simplified it greatly. Now, I can't get the "search issues" form to work. I have the following controls on my search form: Application (dropdown list) TextSearch (text field) I'd like to modify the existing code so that when I make an entry and click the "search" button, it will make the form footer visible (where the "browse_all_issues" form is imbedded as a subform). That part works. But, the next lines set the filter and turn it on... Me.FormFooter.Visible = True DoCmd.Mo...

All help is worthy
I have set up a simple database in excel, using barcodes (i found this easier to scan and lookup, than access) plus the people working with this will find it simple to use. I have however reached a giant full stop. The database is pulled from Dave Peterson's update log worksheet, which works very well, the problems is that on a lookup sheet( using Vlookup ) where the user scans the barcode it will pull all info from the "database" sheet in reference to the barcode, ie: barcode number, surname,forename,address, early greenfees purchased,mid purchased,late. Is it possible to manipu...

Getting Combo boxes to change options based on other Combo boxes.
Using Excel 2007. I am trying to create either Form Control Combo Boxes or ActiveX Combo Boxes that will change the selectable options based on selections made in previous combo boxes. I am having a hard time finding any information that will work for me. Can someone tell me how to make 5 combo boxes change available listed options when specific options are selected in another combo box? Please? I've been working on this for a very long time without success. I've tried using Data Validation, but my formulas are too long for all the different possible combinations. You are...

ShAutComplete
Hello there, I am working on a browser based application, currently using ShAutoComplete in order to provide Auto complete facility for combo box used to enter the address of page that you want to browser. Used ShAutoComplete with SHACF_URLALL to show list of recently browsed web pages and webpage in history. Now how can I add address of web page browsed through my application into that list ? Means If I browser http://msn.com in my application, then it should go in the recently browsed page list that ShAutoComplete uses. Awaiting for your comments. The interface you are looking for ...

Need help with cursor spacing problem
> This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3173510125_666133 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: 8bit Hello: I�m trying to fill in a form that�s MS Word based. I�m using MS Office X on a Mac G4 with system 10.3.4. When I type, the cursor seems to have an extra space between the last letter and the place where I want to put a new one. In other words, I�m unable to align the cursor exactly where I want it to go. It makes typing almost imposs...

Help Everything is gone. It's all been deleted.
I went on excel last on Dec 5th and I saved before exiting as usual. When I opened the file today everything was gone. There is nothing there. The file it's saved as is still there but it's a new page. I don't know what to do. I need that information. HELP! Are you sure you opened the same workbook? What do you mean by "page"? Excel has pages only when you print. Do you mean a blank worksheet? Are there any other worksheets available or just the one blank one? Did you create a backup of the workbook when you saved? If you "need" the inf...

Adding an address to a Contact List
The client is using Outlook 2003 SP2. I know you can click on an email address in an email and add it as a contact to my main Contacts list. Is there a way to do the same task but add it to my other Contact Lists already setup as an addressbook...? Thanks, Tom... Tom Karpowski wrote: > The client is using Outlook 2003 SP2. > I know you can click on an email address in an email and add it as a > contact > to my main Contacts list. > Is there a way to do the same task but add it to my other Contact Lists > already setup as an addressbook...? > >...

alter the content of the from box in a message
in outlook my "from" box automatically defaults to the email from me and that is the email address issued to me by my ISP that I entered at inception. I want my default email to read a different email which corresponds not to my ISP but to my web site and email service by my web hosters? for example my outlook says that my emails are from blanchard@btinternet.com in my signature block i type my small company details and include my company website and my email which is mail@blanchardcon.com etc how do i get this email to appear as a default in the "from" box? Enter i...

Vlookup from Combo Box
Hello, I am trying to use the Vlookup function from a Combo Box. The combo box will contain currencies (USD, JPY, EUR). Once a user selects a currency, the lookup will use that currency to find the exchange rate from a table. Something like this =VLOOKUP("Forms.ComboBox1",Currency!A1:B4,Currency!B18,FALSE) Any help would be much appreciated. Thanks ACase If it's a Combo Box from the Control Toolbox toolbar, you can set the "LinkedCell" property (right-click on the combo box and go into "Properties") to a worksheet cell. When the user makes a select...

Arrears Help Files
Hello all, I don't seem to have the help file for the Arrears transactions. I can't find it on my system don't see a reference to it in the main GP 10 help file. We have GP 10 SP 4. I looked in KB see no reference to that particular problem anywhere. TIA Anyone? On Feb 10, 8:12=A0am, Brian <nixtoo...@hotmail.com> wrote: > Hello all, I don't seem to have the help file for the Arrears > transactions. =A0I can't find it on my system don't see a reference to it > in the main GP 10 help file. =A0We have GP 10 SP 4. =A0I looked in KB see= no...

Lists
I have a user who is importing an excel spreadsheet into a Sharepoint List, but some of the column types that SP is selecting is incorrect. I can't find any place to change the type of these columns (like from text to text with link, etc). Is this possible? Yes, you can change the column type after importing. Just go to the list settings page and choose the column you would like to update. The very first section of the column settings page will be for the data type of the column. -RH "J. Belcher" wrote: > I have a user who is importing an excel sprea...

how can we transfer data from child dialog box to parent Dialog Box. #2
hi guys, i have created modalless child dialog box by parent dialogbox using UI Thread . now i want to do 2 things first i want to transfer CPoint variable from child to parent and second i want to call OnLButtonDown() function of parent DialogBox. i have hint but not proper please help me to complete this one. SendMessage(m_pParent, WM_LBUTTONDOWN, x,y); // from child side void CHRS_MoleDlg::OnLButtonDown(UINT nFlags, CPoint point) //on parent dialog box first of all tell me is it possible if yes then what will be "x" and "y" so that they can convert into CPoint v...

Using Drop Down box that can be edited
Hi, I have created a form in Word 2007, but would like to have the drop down box I created to also be overwritten by the user if the items in the drop box doesn't include their choice. Thanks fjcunninghamjr One possibility would be to replace one of the items in the list with a user input item. You could do this with a macro run on exit from the dropdown field Assuming the dropdown field Dropdown1, put an item at the end of the dropdown list "Enter your own value" then run the following macro on exit from that field Dim sChoice As String Dim fDD As FormFie...

NEED HELP!!!!!! CRM 3.0 Outlook Client error
I have two users with problem. They frequently are getting asked for their credentials when accessing CRM via their Outlook. The options are not set to have them prompt for their credentials. If when you supply the system with valid credentials, the system again another 5-7 times before throwing an error or let you in. But if the system lets you in, it will ask again when you try to navigate somewhere else. I have added the CRM url to the Intranet sites and have the option to automatically logon for Intranet sites in IE7. Both users are the new version for CRM 3.0 that is com...

Thanks for the HELP
Thanks for the response. Working perfectly! -- Cheryl Do take a moment to re-post your feedback to the responder *in* your thread. It's more meaningful that way. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- ...

Formula Help #54
What would be a good formula to use in this situation? If you have in column A: 50010 Apples 50020 Bananas Subtotal 50110 Carrots 50120 Lettuce Subtotal In column B I have numbers 6 5 11 4 5 9 If I then get revised numbers without the description, lets say in column C and D respectively: 50010 10 50020 20 50030 30 50040 40 How can I create a formula in column B that takes the revised 50010 figure and moves it into column B replacing the old figure of 6 with 10 without doing it manually because I am looking at a much larger scale. Thanks for your help. -- billy2wil...