Using Contents of text box on form for Table name
| Author |
Message |
|
Ryan Gunni
#1 / 6
|
 Using Contents of text box on form for Table name I am trying to append the data from one table to another table. The table bname can be different from day to day. I have a form that has the table name in a Text box on it. I want to use that text box's contents as the table name in my SQL statement: DoCmd.RunSQL "INSERT INTO [Trace Report] ( [Date],
[Time], Seq, Log, Aux, State, Skill, Duration, HLD, Rec,
MCT, RLS, [Calling Party], [Call Work Code], [Digits
Dialed], AST, CNF, TSF, [Field1] )" & _
"SELECT [Forms]![LoginForm]![Text2].Date, [Forms]!
[LoginForm]![Text2].Time, [Forms]![LoginForm]![Text2].Seq,
[Forms]![LoginForm]![Text2].Log, [Forms]![LoginForm]!
[Text2].Aux, [Forms]![LoginForm]![Text2].State, [Forms]!
[LoginForm]![Text2].Skill, [Forms]![LoginForm]!
[Text2].Duration, [Forms]![LoginForm]![Text2].HLD, [Forms]!
[LoginForm]![Text2].Rec, [Forms]![LoginForm]![Text2].MCT,
[Forms]![LoginForm]![Text2].RLS, [Forms]![LoginForm]!
[Text2].[Calling Party], [Forms]![LoginForm]![Text2].[Call
Work Code], [Forms]![LoginForm]![Text2].[Digits Dialed],
[Forms]![LoginForm]![Text2].AST, [Forms]![LoginForm]!
[Text2].CNF, [Forms]![LoginForm]![Text2].TSF, [Forms]!
[LoginForm]![Text2].[Field1]" & _
"FROM [Forms]![LoginForm]![Text2];"
I keep getting a "Syntax error in From Clause" error. ANy
ideas on how to fix this?
|
| Sat, 22 May 2004 01:55:42 GMT |
|
 |
|
Sandra Daigl
#2 / 6
|
Using Contents of text box on form for Table name Ryan, The problem is that you've got the control reference inside the
quotes - so it is being used as the name of the table, rather than
being evaluated before the query is executed. You might consider using
an alias for the table name, and change your query to something along
these lines:
dim strSQL as string
strSQL = "INSERT INTO [Trace Report] ( [Date], [Time], Seq, Log, Aux,
State, Skill, Duration, HLD, Rec, MCT, RLS, [Calling Party], [Call
Work Code], [Digits Dialed], AST, CNF, TSF, Field1] ) SELECT A.Date,
a.Time, a.Seq, a.Log, a.Aux, a.State, a.Skill, a.Duration, A.HLD,
a.Rec, a.MCT, a.RLS, a.[Calling Party], a.[Call Work Code],a.[Digits
Dialed], a.AST,a.CNF, a.TSF, a.[Field1] FROM " &
[Forms]![LoginForm]![Text2] & " As A ;"
docmd.rusql strSQL
--
Sandra Daigle, Microsoft Access MVP
Quote: > I am trying to append the data from one table to another > table. The table bname can be different from day to day. > I have a form that has the table name in a Text box on > it. I want to use that text box's contents as the table > name in my SQL statement: > DoCmd.RunSQL "INSERT INTO [Trace Report] ( [Date],
> [Time], Seq, Log, Aux, State, Skill, Duration, HLD, Rec,
> MCT, RLS, [Calling Party], [Call Work Code], [Digits
> Dialed], AST, CNF, TSF, [Field1] )" & _
> "SELECT [Forms]![LoginForm]![Text2].Date, [Forms]!
> [LoginForm]![Text2].Time, [Forms]![LoginForm]![Text2].Seq,
> [Forms]![LoginForm]![Text2].Log, [Forms]![LoginForm]!
> [Text2].Aux, [Forms]![LoginForm]![Text2].State, [Forms]!
> [LoginForm]![Text2].Skill, [Forms]![LoginForm]!
> [Text2].Duration, [Forms]![LoginForm]![Text2].HLD, [Forms]!
> [LoginForm]![Text2].Rec, [Forms]![LoginForm]![Text2].MCT,
> [Forms]![LoginForm]![Text2].RLS, [Forms]![LoginForm]!
> [Text2].[Calling Party], [Forms]![LoginForm]![Text2].[Call
> Work Code], [Forms]![LoginForm]![Text2].[Digits Dialed],
> [Forms]![LoginForm]![Text2].AST, [Forms]![LoginForm]!
> [Text2].CNF, [Forms]![LoginForm]![Text2].TSF, [Forms]!
> [LoginForm]![Text2].[Field1]" & _
> "FROM [Forms]![LoginForm]![Text2];"
> I keep getting a "Syntax error in From Clause" error. ANy
> ideas on how to fix this?
|
| Sat, 22 May 2004 04:21:21 GMT |
|
|
|
John Spence
#3 / 6
|
Using Contents of text box on form for Table name Nice solution. I'd never have thought about using the alias to cut down the references to the control. Quote:
> Ryan,
> The problem is that you've got the control reference inside the
> quotes - so it is being used as the name of the table, rather than
> being evaluated before the query is executed. You might consider using
> an alias for the table name, and change your query to something along
> these lines:
> dim strSQL as string
> strSQL = "INSERT INTO [Trace Report] ( [Date], [Time], Seq, Log, Aux,
> State, Skill, Duration, HLD, Rec, MCT, RLS, [Calling Party], [Call
> Work Code], [Digits Dialed], AST, CNF, TSF, Field1] ) SELECT A.Date,
> a.Time, a.Seq, a.Log, a.Aux, a.State, a.Skill, a.Duration, A.HLD,
> a.Rec, a.MCT, a.RLS, a.[Calling Party], a.[Call Work Code],a.[Digits
> Dialed], a.AST,a.CNF, a.TSF, a.[Field1] FROM " &
> [Forms]![LoginForm]![Text2] & " As A ;"
> docmd.rusql strSQL
> --
> Sandra Daigle, Microsoft Access MVP
> > I am trying to append the data from one table to another
> > table. The table bname can be different from day to day.
> > I have a form that has the table name in a Text box on
> > it. I want to use that text box's contents as the table
> > name in my SQL statement:
> > DoCmd.RunSQL "INSERT INTO [Trace Report] ( [Date],
> > [Time], Seq, Log, Aux, State, Skill, Duration, HLD, Rec,
> > MCT, RLS, [Calling Party], [Call Work Code], [Digits
> > Dialed], AST, CNF, TSF, [Field1] )" & _
> > "SELECT [Forms]![LoginForm]![Text2].Date, [Forms]!
> > [LoginForm]![Text2].Time, [Forms]![LoginForm]![Text2].Seq,
> > [Forms]![LoginForm]![Text2].Log, [Forms]![LoginForm]!
> > [Text2].Aux, [Forms]![LoginForm]![Text2].State, [Forms]!
> > [LoginForm]![Text2].Skill, [Forms]![LoginForm]!
> > [Text2].Duration, [Forms]![LoginForm]![Text2].HLD, [Forms]!
> > [LoginForm]![Text2].Rec, [Forms]![LoginForm]![Text2].MCT,
> > [Forms]![LoginForm]![Text2].RLS, [Forms]![LoginForm]!
> > [Text2].[Calling Party], [Forms]![LoginForm]![Text2].[Call
> > Work Code], [Forms]![LoginForm]![Text2].[Digits Dialed],
> > [Forms]![LoginForm]![Text2].AST, [Forms]![LoginForm]!
> > [Text2].CNF, [Forms]![LoginForm]![Text2].TSF, [Forms]!
> > [LoginForm]![Text2].[Field1]" & _
> > "FROM [Forms]![LoginForm]![Text2];"
> > I keep getting a "Syntax error in From Clause" error. ANy
> > ideas on how to fix this?
|
| Sat, 22 May 2004 06:03:24 GMT |
|
|
|
Sandra Daigl
#4 / 6
|
Using Contents of text box on form for Table name I find any way I can to reduce typing <G>! --
Sandra Daigle, Microsoft Access MVP
Quote: > Nice solution. I'd never have thought about using the alias to cut down the > references to the control.
> > Ryan,
> > The problem is that you've got the control reference inside the
> > quotes - so it is being used as the name of the table, rather than
> > being evaluated before the query is executed. You might consider
using
> > an alias for the table name, and change your query to something
along
> > these lines:
> > dim strSQL as string
> > strSQL = "INSERT INTO [Trace Report] ( [Date], [Time], Seq, Log,
Aux,
> > State, Skill, Duration, HLD, Rec, MCT, RLS, [Calling Party],
[Call
> > Work Code], [Digits Dialed], AST, CNF, TSF, Field1] ) SELECT
A.Date,
> > a.Time, a.Seq, a.Log, a.Aux, a.State, a.Skill, a.Duration, A.HLD,
> > a.Rec, a.MCT, a.RLS, a.[Calling Party], a.[Call Work
Code],a.[Digits
> > Dialed], a.AST,a.CNF, a.TSF, a.[Field1] FROM " &
> > [Forms]![LoginForm]![Text2] & " As A ;"
> > docmd.rusql strSQL
> > --
> > Sandra Daigle, Microsoft Access MVP
> > > I am trying to append the data from one table to another
> > > table. The table bname can be different from day to day.
> > > I have a form that has the table name in a Text box on
> > > it. I want to use that text box's contents as the table
> > > name in my SQL statement:
> > > DoCmd.RunSQL "INSERT INTO [Trace Report] ( [Date],
> > > [Time], Seq, Log, Aux, State, Skill, Duration, HLD, Rec,
> > > MCT, RLS, [Calling Party], [Call Work Code], [Digits
> > > Dialed], AST, CNF, TSF, [Field1] )" & _
> > > "SELECT [Forms]![LoginForm]![Text2].Date, [Forms]!
> > > [LoginForm]![Text2].Time, [Forms]![LoginForm]![Text2].Seq,
> > > [Forms]![LoginForm]![Text2].Log, [Forms]![LoginForm]!
> > > [Text2].Aux, [Forms]![LoginForm]![Text2].State, [Forms]!
> > > [LoginForm]![Text2].Skill, [Forms]![LoginForm]!
> > > [Text2].Duration, [Forms]![LoginForm]![Text2].HLD, [Forms]!
> > > [LoginForm]![Text2].Rec, [Forms]![LoginForm]![Text2].MCT,
> > > [Forms]![LoginForm]![Text2].RLS, [Forms]![LoginForm]!
> > > [Text2].[Calling Party], [Forms]![LoginForm]![Text2].[Call
> > > Work Code], [Forms]![LoginForm]![Text2].[Digits Dialed],
> > > [Forms]![LoginForm]![Text2].AST, [Forms]![LoginForm]!
> > > [Text2].CNF, [Forms]![LoginForm]![Text2].TSF, [Forms]!
> > > [LoginForm]![Text2].[Field1]" & _
> > > "FROM [Forms]![LoginForm]![Text2];"
> > > I keep getting a "Syntax error in From Clause" error. ANy
> > > ideas on how to fix this?
|
| Sat, 22 May 2004 06:28:06 GMT |
|
|
|
Sandra Daigl
#5 / 6
|
Using Contents of text box on form for Table name One other thing - you've got a field named 'Date' which is a reserved word - sooner or later this is going to get you into trouble - I'd suggest you go ahead and change it. --
Sandra Daigle, Microsoft Access MVP
Quote: > Ryan, > The problem is that you've got the control reference inside the
> quotes - so it is being used as the name of the table, rather than
> being evaluated before the query is executed. You might consider
using
> an alias for the table name, and change your query to something
along
> these lines:
> dim strSQL as string
> strSQL = "INSERT INTO [Trace Report] ( [Date], [Time], Seq, Log,
Aux,
> State, Skill, Duration, HLD, Rec, MCT, RLS, [Calling Party], [Call
> Work Code], [Digits Dialed], AST, CNF, TSF, Field1] ) SELECT A.Date,
> a.Time, a.Seq, a.Log, a.Aux, a.State, a.Skill, a.Duration, A.HLD,
> a.Rec, a.MCT, a.RLS, a.[Calling Party], a.[Call Work Code],a.[Digits
> Dialed], a.AST,a.CNF, a.TSF, a.[Field1] FROM " &
> [Forms]![LoginForm]![Text2] & " As A ;"
> docmd.rusql strSQL
> --
> Sandra Daigle, Microsoft Access MVP
> > I am trying to append the data from one table to another
> > table. The table bname can be different from day to day.
> > I have a form that has the table name in a Text box on
> > it. I want to use that text box's contents as the table
> > name in my SQL statement:
> > DoCmd.RunSQL "INSERT INTO [Trace Report] ( [Date],
> > [Time], Seq, Log, Aux, State, Skill, Duration, HLD, Rec,
> > MCT, RLS, [Calling Party], [Call Work Code], [Digits
> > Dialed], AST, CNF, TSF, [Field1] )" & _
> > "SELECT [Forms]![LoginForm]![Text2].Date, [Forms]!
> > [LoginForm]![Text2].Time, [Forms]![LoginForm]![Text2].Seq,
> > [Forms]![LoginForm]![Text2].Log, [Forms]![LoginForm]!
> > [Text2].Aux, [Forms]![LoginForm]![Text2].State, [Forms]!
> > [LoginForm]![Text2].Skill, [Forms]![LoginForm]!
> > [Text2].Duration, [Forms]![LoginForm]![Text2].HLD, [Forms]!
> > [LoginForm]![Text2].Rec, [Forms]![LoginForm]![Text2].MCT,
> > [Forms]![LoginForm]![Text2].RLS, [Forms]![LoginForm]!
> > [Text2].[Calling Party], [Forms]![LoginForm]![Text2].[Call
> > Work Code], [Forms]![LoginForm]![Text2].[Digits Dialed],
> > [Forms]![LoginForm]![Text2].AST, [Forms]![LoginForm]!
> > [Text2].CNF, [Forms]![LoginForm]![Text2].TSF, [Forms]!
> > [LoginForm]![Text2].[Field1]" & _
> > "FROM [Forms]![LoginForm]![Text2];"
> > I keep getting a "Syntax error in From Clause" error. ANy
> > ideas on how to fix this?
|
| Sat, 22 May 2004 06:33:06 GMT |
|
|
|
John Vinso
#6 / 6
|
Using Contents of text box on form for Table name On Mon, 3 Dec 2001 09:55:42 -0800, "Ryan Gunnin" Quote:
>I am trying to append the data from one table to another
>table. The table bname can be different from day to day.
>I have a form that has the table name in a Text box on
>it. I want to use that text box's contents as the table
>name in my SQL statement:
You can't use a table or fieldname as a parameter - in fact, the need to do so suggests that you are improperly storing data in a table name, generally considered a very bad thing to do. If you want to do it anyway, you will need to write VBA code to
construct the SQL string by concatenating the form value.
John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
|
| Sat, 22 May 2004 09:32:18 GMT |
|
| |
|
