ms access command button

[questionit]

Hi
I am new in Ms Access

I need to know how can i create a command button on a form. When it is clicked, a table is opened that is associated to the data in a field i have.

E.g: if ( surName == "abc") then open table Table_Abc
if ( surName == "qaz") then open table Table_Qaz

i will have a lot of surnames, so tables needs to be opened accordingly.

Will it be done by macro, or expressin builder? If it needs VB code, can you give me that as i dont know VB

thank you very much

hassan

[Rabbit]

Do you need a seperate table for each surname? It may be easier to create a query that filters for the surname and then open the query. To open a query you use the code:

DoCmd.OpenQuery "Name of Query"

[questionit]

Do you need a seperate table for each surname? It may be easier to create a query that filters for the surname and then open the query. To open a query you use the code:

DoCmd.OpenQuery "Name of Query"

yes i need seperate tables.. how can i do it then?

[Rabbit]

yes i need seperate tables.. how can i do it then?

Why do you need seperate tables?

The code to open a table for your naming convention would be:

DoCmd.OpenTable "Table_" & Surname

[questionit]

Why do you need seperate tables?

The code to open a table for your naming convention would be:

DoCmd.OpenTable "Table_" & Surname

What will keyword Table_ do?

will i have to re-write this line of code for every surname?

i have different tables that contains details (more than 5 rows) of every surname.
is there any better way doing it than having seperate tables?

thanks a lot

[Rabbit]

What will keyword Table_ do?

will i have to re-write this line of code for every surname?

i have different tables that contains details (more than 5 rows) of every surname.
is there any better way doing it than having seperate tables?

thanks a lot

"Table_" is not a keyword. I assumed you used the format Table_Surname to name all your tables. "Table_" & Me.Surname.Valu e will return the string Table_Surname assuming that Surname is a control on your form.

What details of the surname are you storing? One way to keep all the data centralized is to have a field that designates which surname the record belongs to.

Take a look at Normalisation and Table Structures, it will help you structure your tables.

[questionit]

"Table_" is not a keyword. I assumed you used the format Table_Surname to name all your tables. "Table_" & Me.Surname.Valu e will return the string Table_Surname assuming that Surname is a control on your form.

What details of the surname are you storing? One way to keep all the data centralized is to have a field that designates which surname the record belongs to.

Take a look at Normalisation and Table Structures, it will help you structure your tables.

thanks i am getting undertanding now.
What is Me keyword? will it be the name of the form? I have written this code and getting an error: "method or data member not found". ?

DoCmd.OpenTable "Table_" & Me.surName_Labe l.Value

[questionit]

thanks i am getting undertanding now.
What is Me keyword? will it be the name of the form? I have written this code and getting an error: "method or data member not found". ?

DoCmd.OpenTable "Table_" & Me.surName_Labe l.Value

I thought that surname_Label was wrong. this now code. Also i changed Me to Form (the name of the form)?

DoCmd.OpenTable "Table_" & Form.surName.Va lue

but now when i run i get error: .. cant find object Table_surName1.

surName1 is a table i have created. why it cant find it?

[MMcCarthy]

thanks i am getting undertanding now.
What is Me keyword? will it be the name of the form? I have written this code and getting an error: "method or data member not found". ?

DoCmd.OpenTable "Table_" & Me.surName_Labe l.Value

1. The Me. refers to the current form.
2. You cannot use .Value on a label, thy the following

DoCmd.OpenTable "Table_" & Me.surName_Label.Caption

Mary

[questionit]

1. The Me. refers to the current form.
2. You cannot use .Value on a label, thy the following

DoCmd.OpenTable "Table_" & Me.surName_Label.Caption

Mary

But i have tried this:

DoCmd.OpenTable "Table_" & Detail.surName. Value

surName is Text Box
Detail is the Detail portion where the surName TextBox is - under the Header

I get following error now: Method or Data Value not found?
Please help.

[MMcCarthy]

But i have tried this:

DoCmd.OpenTable "Table_" & Detail.surName. Value

surName is Text Box
Detail is the Detail portion where the surName TextBox is - under the Header

I get following error now: Method or Data Value not found?
Please help.

Try ...

 DoCmd.OpenTable "Table_" & Me.surName

[questionit]

Try ...

 DoCmd.OpenTable "Table_" & Me.surName

now i get a different error:

'.. cant find the object Table_detail1'

i have a surname value 'detail1' and have also a table called 'detail1', so there should be any error generated ?

[MMcCarthy]

now i get a different error:

'.. cant find the object Table_detail1'

i have a surname value 'detail1' and have also a table called 'detail1', so there should be any error generated ?

If the table is called 'detail1', you don't need the 'Table_' part, just ...

DoCmd.OpenTable Me.surName

[questionit]

If the table is called 'detail1', you don't need the 'Table_' part, just ...

DoCmd.OpenTable Me.surName

That worked. thanks

by the way. would you tell me why was i actually told to use 'Table_' .. when do we need this?

[MMcCarthy]

That worked. thanks

by the way. would you tell me why was i actually told to use 'Table_' .. when do we need this?

Rabbit was talking about naming conventions. By convention tables are named Table_Name or tblName.

Mary