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

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 

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

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

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

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