Change the recordsource of a subform that is on an unbound main fo

Hello all,
I know there are many appends on this but I just can't get this to work.  

I have an unbound main form (Main).  On it are several subforms.  The first 
subform (Vehicle) contains a summary of the vehicles.  When I click on one of 
the vehicles, I would like the second subform (Vehicle Allocation History) to 
display records for the vehicle selected in the Vehicle form.  Below is my 
code:

Dim VehAlloc As Control
Dim Main As Form
Dim VIN As String
Dim SQL As String

Set Main = Forms("VehicleSummaryForm")
Set VehAlloc = Main.VehicleAllocationHistory
VIN = Me!VIN
SQL = "SELECT * FROM Vehicle WHERE VIN = '" & VIN & "'"
VehAlloc.Form.RecordSource = SQL

I keep getting an error saying that I'm making an invalid reference.  I have 
tried the Parent property, fully qualifying form names and other stuff which 
is now all a blur.  I also tried the statement VehAlloc.Recordsource = SQL  
but that didn't work.  Does any one have any idea as to why this won't work?  
Any suggestions would be most appreciated.  Thanks in advance.
Debbie
0
Utf
1/17/2008 2:37:01 AM
access.formscoding 7493 articles. 0 followers. Follow

5 Replies
2411 Views

Similar Articles

[PageSpeed] 40

Dim strSQL As String
strSQL = "SELECT * FROM Vehicle WHERE VIN = '" & txtVIN & "'"
Me.NameOfSubform.Recordsource = strSQL

You may have a problem with ambiguous naming of VIN, so it would be wise to 
name the textbox txtVIN.
-- 
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Debbie" <Debbie@discussions.microsoft.com> wrote in message 
news:92846A60-907F-4768-8D9A-17FA1F944C38@microsoft.com...
> Hello all,
> I know there are many appends on this but I just can't get this to work.
>
> I have an unbound main form (Main).  On it are several subforms.  The 
> first
> subform (Vehicle) contains a summary of the vehicles.  When I click on one 
> of
> the vehicles, I would like the second subform (Vehicle Allocation History) 
> to
> display records for the vehicle selected in the Vehicle form.  Below is my
> code:
>
> Dim VehAlloc As Control
> Dim Main As Form
> Dim VIN As String
> Dim SQL As String
>
> Set Main = Forms("VehicleSummaryForm")
> Set VehAlloc = Main.VehicleAllocationHistory
> VIN = Me!VIN
> SQL = "SELECT * FROM Vehicle WHERE VIN = '" & VIN & "'"
> VehAlloc.Form.RecordSource = SQL
>
> I keep getting an error saying that I'm making an invalid reference.  I 
> have
> tried the Parent property, fully qualifying form names and other stuff 
> which
> is now all a blur.  I also tried the statement VehAlloc.Recordsource = SQL
> but that didn't work.  Does any one have any idea as to why this won't 
> work?
> Any suggestions would be most appreciated.  Thanks in advance.
> Debbie 


0
Arvin
1/17/2008 3:52:28 AM
Arvin,
Thank you for such a quick response!  I did change the name of the text box 
but I got a different error.  It is from the statement:
Me.NameOfSubform.Recordsource = strSQL

With the syntax you gave me, it looks like code that would run on the main 
form.  I need the code to run from within the subform Vehicle and based on 
the current row there, requery another subform on this screen called Vehicle 
Allocation History.  The main form is unbound.  (I'm sure this late hour 
isn't helping either of us!)
Do you see what I mean?  Thanks,
Debbie

"Arvin Meyer [MVP]" wrote:

> Dim strSQL As String
> strSQL = "SELECT * FROM Vehicle WHERE VIN = '" & txtVIN & "'"
> Me.NameOfSubform.Recordsource = strSQL
> 
> You may have a problem with ambiguous naming of VIN, so it would be wise to 
> name the textbox txtVIN.
> -- 
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.mvps.org/access
> http://www.accessmvp.com
> 
> "Debbie" <Debbie@discussions.microsoft.com> wrote in message 
> news:92846A60-907F-4768-8D9A-17FA1F944C38@microsoft.com...
> > Hello all,
> > I know there are many appends on this but I just can't get this to work.
> >
> > I have an unbound main form (Main).  On it are several subforms.  The 
> > first
> > subform (Vehicle) contains a summary of the vehicles.  When I click on one 
> > of
> > the vehicles, I would like the second subform (Vehicle Allocation History) 
> > to
> > display records for the vehicle selected in the Vehicle form.  Below is my
> > code:
> >
> > Dim VehAlloc As Control
> > Dim Main As Form
> > Dim VIN As String
> > Dim SQL As String
> >
> > Set Main = Forms("VehicleSummaryForm")
> > Set VehAlloc = Main.VehicleAllocationHistory
> > VIN = Me!VIN
> > SQL = "SELECT * FROM Vehicle WHERE VIN = '" & VIN & "'"
> > VehAlloc.Form.RecordSource = SQL
> >
> > I keep getting an error saying that I'm making an invalid reference.  I 
> > have
> > tried the Parent property, fully qualifying form names and other stuff 
> > which
> > is now all a blur.  I also tried the statement VehAlloc.Recordsource = SQL
> > but that didn't work.  Does any one have any idea as to why this won't 
> > work?
> > Any suggestions would be most appreciated.  Thanks in advance.
> > Debbie 
> 
> 
> 
0
Utf
1/17/2008 4:42:00 AM
I think you are going down the wrong path.  A better approach would be to 
place an unbound textbox on the Main form, call it txtVIN (set its visible 
property to false.)

Then add one line of code the Vehicle subform in its Current event:

Private Sub Form_Current()

Parent.txtVIN= VIN

End Sub

Then in the history subform's properties, link the child and master fields 
using the textbox as you would a field name in a table:

Link Child Fields = VIN
Link Master Fields = txtVIN

Not whenever you "select" a vehicle in the vehicle subform, the txtVIN will 
contain the VIN of the Vehicle and the child/master linking will 
automatically requery the history subform using the value in txtVIN.  This is 
one of the really nice features of Microsoft Access!

Please note:
I am assuming the Vehicle table has a VIN field and the Vehicle Allocation 
History table has a VIN field.



"Debbie" wrote:

> Hello all,
> I know there are many appends on this but I just can't get this to work.  
> 
> I have an unbound main form (Main).  On it are several subforms.  The first 
> subform (Vehicle) contains a summary of the vehicles.  When I click on one of 
> the vehicles, I would like the second subform (Vehicle Allocation History) to 
> display records for the vehicle selected in the Vehicle form.  Below is my 
> code:
> 
> Dim VehAlloc As Control
> Dim Main As Form
> Dim VIN As String
> Dim SQL As String
> 
> Set Main = Forms("VehicleSummaryForm")
> Set VehAlloc = Main.VehicleAllocationHistory
> VIN = Me!VIN
> SQL = "SELECT * FROM Vehicle WHERE VIN = '" & VIN & "'"
> VehAlloc.Form.RecordSource = SQL
> 
> I keep getting an error saying that I'm making an invalid reference.  I have 
> tried the Parent property, fully qualifying form names and other stuff which 
> is now all a blur.  I also tried the statement VehAlloc.Recordsource = SQL  
> but that didn't work.  Does any one have any idea as to why this won't work?  
> Any suggestions would be most appreciated.  Thanks in advance.
> Debbie
0
Utf
1/17/2008 9:19:02 PM
Yes, you need to use your own subform's name not NameOfSubform.
-- 
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Debbie" <Debbie@discussions.microsoft.com> wrote in message 
news:8DB66BDD-E8DA-4942-B729-B03CB4F81C21@microsoft.com...
> Arvin,
> Thank you for such a quick response!  I did change the name of the text 
> box
> but I got a different error.  It is from the statement:
> Me.NameOfSubform.Recordsource = strSQL
>
> With the syntax you gave me, it looks like code that would run on the main
> form.  I need the code to run from within the subform Vehicle and based on
> the current row there, requery another subform on this screen called 
> Vehicle
> Allocation History.  The main form is unbound.  (I'm sure this late hour
> isn't helping either of us!)
> Do you see what I mean?  Thanks,
> Debbie
>
> "Arvin Meyer [MVP]" wrote:
>
>> Dim strSQL As String
>> strSQL = "SELECT * FROM Vehicle WHERE VIN = '" & txtVIN & "'"
>> Me.NameOfSubform.Recordsource = strSQL
>>
>> You may have a problem with ambiguous naming of VIN, so it would be wise 
>> to
>> name the textbox txtVIN.
>> -- 
>> Arvin Meyer, MCP, MVP
>> http://www.datastrat.com
>> http://www.mvps.org/access
>> http://www.accessmvp.com
>>
>> "Debbie" <Debbie@discussions.microsoft.com> wrote in message
>> news:92846A60-907F-4768-8D9A-17FA1F944C38@microsoft.com...
>> > Hello all,
>> > I know there are many appends on this but I just can't get this to 
>> > work.
>> >
>> > I have an unbound main form (Main).  On it are several subforms.  The
>> > first
>> > subform (Vehicle) contains a summary of the vehicles.  When I click on 
>> > one
>> > of
>> > the vehicles, I would like the second subform (Vehicle Allocation 
>> > History)
>> > to
>> > display records for the vehicle selected in the Vehicle form.  Below is 
>> > my
>> > code:
>> >
>> > Dim VehAlloc As Control
>> > Dim Main As Form
>> > Dim VIN As String
>> > Dim SQL As String
>> >
>> > Set Main = Forms("VehicleSummaryForm")
>> > Set VehAlloc = Main.VehicleAllocationHistory
>> > VIN = Me!VIN
>> > SQL = "SELECT * FROM Vehicle WHERE VIN = '" & VIN & "'"
>> > VehAlloc.Form.RecordSource = SQL
>> >
>> > I keep getting an error saying that I'm making an invalid reference.  I
>> > have
>> > tried the Parent property, fully qualifying form names and other stuff
>> > which
>> > is now all a blur.  I also tried the statement VehAlloc.Recordsource = 
>> > SQL
>> > but that didn't work.  Does any one have any idea as to why this won't
>> > work?
>> > Any suggestions would be most appreciated.  Thanks in advance.
>> > Debbie
>>
>>
>> 


0
Arvin
1/18/2008 3:50:28 AM
Thank you both so much.  The main/subform idea worked great.  I actually had 
that at one point but discarded because I thought both the main form had to 
be bound.  This is really valuable stuff!  Thanks so much!
Debbie

"n00b" wrote:

> I think you are going down the wrong path.  A better approach would be to 
> place an unbound textbox on the Main form, call it txtVIN (set its visible 
> property to false.)
> 
> Then add one line of code the Vehicle subform in its Current event:
> 
> Private Sub Form_Current()
> 
> Parent.txtVIN= VIN
> 
> End Sub
> 
> Then in the history subform's properties, link the child and master fields 
> using the textbox as you would a field name in a table:
> 
> Link Child Fields = VIN
> Link Master Fields = txtVIN
> 
> Not whenever you "select" a vehicle in the vehicle subform, the txtVIN will 
> contain the VIN of the Vehicle and the child/master linking will 
> automatically requery the history subform using the value in txtVIN.  This is 
> one of the really nice features of Microsoft Access!
> 
> Please note:
> I am assuming the Vehicle table has a VIN field and the Vehicle Allocation 
> History table has a VIN field.
> 
> 
> 
> "Debbie" wrote:
> 
> > Hello all,
> > I know there are many appends on this but I just can't get this to work.  
> > 
> > I have an unbound main form (Main).  On it are several subforms.  The first 
> > subform (Vehicle) contains a summary of the vehicles.  When I click on one of 
> > the vehicles, I would like the second subform (Vehicle Allocation History) to 
> > display records for the vehicle selected in the Vehicle form.  Below is my 
> > code:
> > 
> > Dim VehAlloc As Control
> > Dim Main As Form
> > Dim VIN As String
> > Dim SQL As String
> > 
> > Set Main = Forms("VehicleSummaryForm")
> > Set VehAlloc = Main.VehicleAllocationHistory
> > VIN = Me!VIN
> > SQL = "SELECT * FROM Vehicle WHERE VIN = '" & VIN & "'"
> > VehAlloc.Form.RecordSource = SQL
> > 
> > I keep getting an error saying that I'm making an invalid reference.  I have 
> > tried the Parent property, fully qualifying form names and other stuff which 
> > is now all a blur.  I also tried the statement VehAlloc.Recordsource = SQL  
> > but that didn't work.  Does any one have any idea as to why this won't work?  
> > Any suggestions would be most appreciated.  Thanks in advance.
> > Debbie
0
Utf
1/18/2008 6:50:01 PM
Reply:

Similar Artilces:

changing report headings names
I would like to give the user the option of changing the report heading names, I can do this with the Docmd.openreport, stdocname, acviewdesign,,,achidden and then grab the current heading and display that heading and allow the user to change it. I then do a docmd.close acreport, stdocname, acSaveYes, this works just fine when I test but when I compile the program into a mde it doesn't like the it. Is there another way of doing this? Thanks for any help. Tom you can write the current heading names to a table, making sure the table holds only one record. add the table to the quer...

Change of public IP for fighting spam
Hi, If I ask my dns registor to change my ip to another public ip. Is this going to help us fight spam? Is there's a risk of not getting email? Example old: www.company.com = 60.1.1.1 Examble new: www.company.com = 60.1.1.5 By the way, I noticed that most big companies cannot be ping anymore. Is this advisable too? Thanks, Ricky On Thu, 20 Apr 2006 08:06:02 -0700, RickyVene <RickyVene@discussions.microsoft.com> wrote: >Hi, > >If I ask my dns registor to change my ip to another public ip. Is this >going to help us fight spam? Is there's a ris...

How do I change a flyer (word document) into a picture?
I created a flyer and I want to change it into a picture so I can upload it as a picture on facebook. How do I change the 2007 word document into a picture? This newsgroup is for Microsoft Project, a planning and scheduling application. Questions about Microsoft Word, should be published to the Word newsgroup. To address your question, try using File/Save As... and select type PDF. If the document must be an image format (such as BMP, JPG, GIG, etc) you will probably need some screen capture software such as Snag-It or the clipping tool available with Windows Vista and later. ...

Hyperlink Changed
The local power company had a problem last week. The power nearly went off and came back rapidly several times. I have a spreadsheet with a lot of hyperlinks to other files. After the power fluctuations the hyperlinks have the wrong path names to the files. The file names are all correct, but the path to them are all wrong. Is there a way to restore the proper path name in mass, or must I correct each and every one? The wrong path names are all the same. Does anyone know how this could have happened in the first place? ...

why cant i change font
i select all the text in a textbox and try to apply a font. nothing happens. that's the case with 4/5 fonts, only a few work. the same appears in both publisher 2003 and 2007. Are you creating a web page? Publisher will show 11 or so fonts when you are creating web pages. To use all the fonts, on the toolbar, Format, Font, uncheck Show only Web fonts. If above is not the issue, are you getting an error? -- Mary Sauer MVP http://msauer.mvps.org/ "ayudameconpublisher" <ayudameconpublisher@discussions.microsoft.com> wrote in message news:1BE1262A-A70B-4...

Change the Default Language Setting
Is it possible to set the language to 'English (Australia)' so it will be the default across all office applications? I keep having to change it manually in outlook at the moment. ...

Best way to change style of a table
I have a table I added via VBA like this: set oTable = ActivePresentation.Slides("mySlide").Shapes.AddTable(...) I then add some cells to it by: oTable.Cell(...).Shape.TextFrame.TextRange.Text = "some text" However, I cannot get a handle on these cells to update the font, fill, etc. What is the best way to do this? On Sun, 13 Dec 2009 21:01:01 -0500, Clifton Ivey <clifton ivey <"africom.mil>"> wrote: > I have a table I added via VBA like this: > > set oTable = ActivePresentation.Slides("mySlide").Shapes.AddTa...

"change order form"
Does anyone have a change order template that I can review? ...

changing from outlook 2000 to outlook xp
i just bought a new computer and want to move all my outlook 2000 personal folders from my old computer--to my new computer which is running outlook xp. What's the best way to do this? thanks, Sammy http://www.slipstick.com/config/backup.htm --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and finding no answer sammy russo <sammy@aorafting.com> asked: | i just bought a new computer and want to move all my | outlook 2000 personal folders from my old computer--to my | new computer which is runnin...

Changing formula in multiple cells or range simultaneously
I am trying to change the value in multiple cells in a large worksheet simultaneuously. I want to identify the range and then adjust the formula in the entire range. Is there a way that I can highlight the range and then change to formula in each, simultaneously? For example, if I wanted to double the value in the entire range, how would I do this? Thanks, Michael You could put 2 in an empty cell. Copy it Edit|Paste special|click on Multiply under the operation section. Then clear out that 2. But it really depends on what kind of change you're making. If you wanted to ad...

Changing margins on different pages in document templates.
I was wondering how you change the margins for different pages in a template. Specifically, my firm is using a letterhead for the first page but blank pages after that so the header and footer margins on the first page ONLY must be larger than normal, while the following pages are fine with default settings for the margins. How do you do this and save it as a template? See http://sbarnhill.mvps.org/WordFAQs/Letterhead.htm, especially the "More complex letterhead" section. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://wo...

Date Changes to 01/00/00 when i enter any date.
Using XL2003, I have a problem. When i enter a date in cell, such as 08/25/10, or use the shortcut Ctrl ;, XL displays 01/00/00 in the cell instead. I have formatted the cell as a date. What can I do to fix this problem? Thank you so much. Tonso ...

How to change Marketing list member "All members" to "Active membe
How to change Marketing list member "All members" to "Active members" in CRM 4.0? Any help is appreciated. I have used the following code giving object required error code: /************************************************************** * Change the default view of the Marketing List Members (Account) **************************************************************/ if (crmForm.all.createdfromcode.DataValue == 1) { if (crmForm.FormType != 1 && crmForm.FormType != 5 && crmForm.FormType != 6) { /************************************************************** ...

Subform..Deleting a Record by code
I have a Before Update event to Trap an Entry of "SN" (which is not acceptable) and results in a Msgbox and "Cancel = True". If the User backspaces the "SN" leaving a Null, and leaves the Record, how can I delete that record by code? TIA - Bob "Bob Barnes" <BobBarnes@discussions.microsoft.com> wrote in message news:46A5C7AA-F19F-477E-938A-D99EE9CBF4DC@microsoft.com... > I have a Before Update event to Trap an Entry of "SN" (which is not > acceptable) and results in a Msgbox and "Cancel = True". > > If the U...

word doc lost all changes
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I made lot's of changes to a word doc on Friday, after every few minutes I saved the document. Now that I have opened it again today, it seems to be in it's original format with absolutely no changes. <br><br>Is it possible I could have deleted the saved document rather than the original - but i renamed it with a different version! Can I back up the Mac to Friday evening so that both docs will be there? <br><br>Any other suggestions?? <br><br>Help - please! Hi Dawn...

How do I change reply to or senders address
How do I change the Reply to: email address or the sender’s address? My infrastructure consists of: 1 – Windows 2003 AD box 1 – Exchange 2003 box Many – Outlook 2003 email clients. Is there a way to change the senders email address, example; An email note that’s generated by joe in the domain test.foo.com (joe@test.foo.com) can this address be changed in some setting in either Exchange or Outlook, so that the recipient of the note sees the senders address as joe@foo.com? The Outlook clients are configured for an Exchange connection. On Tue, 1 Mar 2005 11:03:46 -0800, HMR <HMR@discu...

rule
There is a nice rule at outlook express so that messages from certain poeple are in different colors. Why there is not such rule at Outlook 2003? There is and it's even better! See; http://www.howto-outlook.com/howto/coloremail.htm and http://www.howto-outlook.com/howto/coloremailadvanced.htm -- Roady [MVP] www.howto-outlook.com Tips of the month: -Setting Permissions on a Mailbox -Create an Office XP CD slipstreamed with Service Pack 3 ----- "constantinos" <anonymous@discussions.microsoft.com> wrote in message news:5B1EAC31-909A-4088-97EB-BD6A97A71EC0@microsoft.com.....

Change what populates subject line when send pdf report in email
When I choose to send my POP Purchase Order Blank Form to a mail recipient (pdf), information is populating the subject line and body of my e-mail by default. Specifically the name of the report is populating the subject line. Is there a way to change this? I would rather see the PO number populating that field. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggest...

Hiding a subform
I have a subform on a main form which is shown/hidden by tab control,but when it is hidden all I see/get is a blank tab control. I have tried various macros to close it or minimise it, but they don't work. So is there a way to hide a subform when not needed and then show it when it is. Thanks Use the Change event for the tab control. The way I do it is like this--> Private Sub ClientTab_Change Select Case Me.ClientTab Case 0 Me.[NameOfSubformControlA].SourceObject = "[FormA]" Me.[NameOfSubformControlB].SourceObject = "" ...

Outlooks "Incoming mail (POP3)" keeps changing to "local"
Hi everyone, I have a question on Outlook 2000, every few days my account setting for the "Incoming mail (POP3)" change from the mail server I have specified to "local" and the mailServer then gets added to the "Account name" /meMailServerName Any ideas on why is it doing this, and can I stop it from doing this, cause each time it does, I have to manually change it back to original setting so I can get my mail. Shawn This is your antivirus software, most likely (or antispam software). Outlook doesn't do this. Check with the mfr. to see if they have an...

Requery one subform from another
Hi guys, I have a combobox on a subform within a subform on a tabbed control on a mainform. The content of this combo box is "Username" and determined by a username field in a users table. That users table can be appended using another subform on the mainform. Does anyone know the requery code so that if I add a record to the table, the combobox will automatically be updated so that the new record is available to select in the combo box? Tony Hiya I figured it out. I was going about it in totally the wrong way. I just stuck a Requery in the OnGotFocus event on the combobox. I ca...

Cell change on User Click
Is it possible to have a cell change to a default value when a use clicks on it? I have an old sheet I am working on that people put in manual "X" in t select options. Most of the sheet uses these X's to calulate option and such and I would rather not rewrite the entire spread. Any help would be appreciated. Dre -- Message posted from http://www.ExcelForum.com hibatt wrote: > *Is it possible to have a cell change to a default value when a use > clicks on it? > > I have an old sheet I am working on that people put in manual "X" i > to select option...

Pie of Pie
I've made a Pie of Pie chart and want to change the name of the wedge that get broken out from "other" to "Agency". Hi, Manually you can select the data labels and then select the specific label and edit its text. Cheers Andy smccarvi wrote: > I've made a Pie of Pie chart and want to change the name of the wedge that > get broken out from "other" to "Agency". > > -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Subform Filter with Multiple Criteria
Hi all, I've got a subform and can filter via this code: frmSUB.Form.Filter = strFilter frmSUB.Form.FilterOn = True But, it only works with a single criteria, so when strFilter = something like [Application] = 'ProgramA' If I try multiple criteria, I get error 2448. Something must be wrong w/ my syntax, but I don't know what it could be. Anyone got any ideas? Thanks in advance - [Application] = 'ProgramA';[Profile] = 'AMAS' On Aug 3, 9:35 am, Zilty <ziltm...@yahoo.com> wrote: > Hi all, > > I've got a subform and can filter via this ...

Changing References Inadvertently
This is a multi-part message in MIME format. ------=_NextPart_000_0121_01C6BB96.36E602D0 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable A friend has an Excel workbook that contains one overall summary = worksheet, and about 20 individual worksheets that each pull data from = the overall summary sheet. She asked me to help troubleshoot some = problems that she was having. What I found was that some of the 3D references had changed. Instead of = looking from the individual sheet to the summary worksheet in the = current workbook, the ...