Accessing form controls

[dante]

Hi
Excuse me if this thread is not in its right place.But I can't find somewhere better.
I have two questions.
1-I wanna use the user entry in four text fields in a form in an Alter table statement in a macro.I just don't know how to address them.this is my sql statement.

ALTER 'Forms![Form1]![tname].text' ADD 'forms![form1]![fname].text' 'forms![form1]![ftype].text' (forms![form1]![fsize].text)

I tried several alternatives but for all of them I get the syntax error in ALTER TABLE statement error.
2-I wanna design a query that has a field not existed by the tables and I wanna put the floor of the division of two number fields in it.But I don't know how to do this.
thanks for any post

[Max D]

You need to use regular VB expessions. It looks like this

SQL="ALTER "+tname.value+" ADD ..." + str(somenum)

You need to use str() function for numeric values.

[dante]

Should I write it in the macro definition?
And shouldn't I identify the table name before the field name?
thanks

[Max D]

Nope, thats VBA writing style, sorry.

[dante]

Hi.I wrote

"ALTER "+tname.value+" ADD "+fname.value+" "+ftype.value+"("+str(fsize.value)+")"

as the Sql statement.But I got the following error:

Invalid SQL statement;expected 'DELETE','INSERT','PROCEDURE','SELECT',or 'UPDATE'.

What's the problem now?
And what about my second question of my first post?
thanks

[Max D]

It says it will not allow you to alter tables with this query. I'm not sure if Access SQL supports ALTER statements at all - its just a subset of regular SQL standart.

2 - its easy: SELECT *, (a+b/2) as MyAverage FROM Mytable

[dante]

But I did it with the ordinary strings.I just have problem with accessing the form controls.
anyway do you know a better way to let the user add a field to a favorite table,with a favorite name and type?

[Max D]

Hm, in Access 2007 such a trick works normally:

Private Sub Command5_Click()
. DoCmd.RunSQL "ALTER TABLE Table1 ADD COLUMN new text"
End Sub

[dante]

I created a query with the sql statement below:

SELECT round(table1.timeLimit/(table1.timeNeeded*table1.numberNeeded))+1 AS [Title], table1.name, table2.name
FROM table2, table1
WHERE (((table1.name)=[V])) and (((table2.usedBy)=[V]));

But when I run it,after entering the V (a string)I get a table view that has just the labels.I mean there is nothing like a textbox below the labels that contain the name of each field.Some of the names are in my own language,Farsi.What is the problem?

I also created a form and connected its command button to a macro that has a runcode as its action.The code is like below:

DoCmd.RunSQL("alter table 'Form!tname.Text' add column 'Form!fname.Text'  'form!ftype.text'")

But when I click on the command button simply nothing happens.Exactly nothing.What is the problem?

Please answer rapidly.excuse me but it is emergency
thanks

[Max D]

Your query is wrong, because you are using 2 tables but you doesn't specify the condition for them to join. Second problem can be that you need to get in quotes the string variable.

[dante]

Could you explain more clear please!

[Max D]

Try to describe what are you trying to do with your query, and i'll try to form it in proper way.

[dante]

By first want,I want to show the result of the written expression along the name fields of two of my tables.
By second I want to create a field in a table that its name is given by the user,with the name and type that are given by the user.
I really can't explain more.In fact I don't know what to tell.
And remember it is emergency
thanks

[Max D]

Here is a sample for first query:

SELECT round(table1.timeLimit/(table1.timeNeeded*table1.numberNeeded))+1 AS [Title], table1.name, table2.name
FROM table1 JOIN table2 ON table1.name=table2.usedBy
WHERE table1.name='[V]'

Here is for second:

DoCmd.RunSQL ("alter table '"+tname.value+"' add column '"+fname.value+"' '"+ftype.value+"'")

[dante]

But I got the error below:

syntax error in FROM clause

for the first one and after clicking OK the JOIN keyword was highlighted.

And in the second one again nothing happens.I checked the event section of that command button properties menu and made sure the macro is selected in the on click section.I think the syntax has no problem.The problem is somewhere else.

Could you help again.Please soon.
Really thanks