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.Value 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.Value 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_Label.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_Label.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.Value

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

[questionit]

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

Mary

Ok
Now if i want to open detail1 table in a form.. I know i need to create a form first.

But there are many tables e.g detail1, detail2, etc ... but all have exactly similar fields.

How will i set it..... open a form that will actually be opening appropriate table i.e detail1, detail2, etc ?

[MMcCarthy]

Ok
Now if i want to open detail1 table in a form.. I know i need to create a form first.

But there are many tables e.g detail1, detail2, etc ... but all have exactly similar fields.

How will i set it..... open a form that will actually be opening appropriate table i.e detail1, detail2, etc ?

You need to change the record source of the form each time. In the forms On load event put the following code:

Private Sub Form_Load()
 
  ' Replace FormName with the name of the Form with the surName textbox.
  Me.RecordSource = Forms![FormName]![surName]
 
End Sub

Mary

[questionit]

You need to change the record source of the form each time. In the forms On load event put the following code:

Private Sub Form_Load()
 
  ' Replace FormName with the name of the Form with the surName textbox.
  Me.RecordSource = Forms![FormName]![surName]
 
End Sub

Mary

I cant find the ' On Load' event. it is not in the form properties box ?

[MMcCarthy]

I cant find the ' On Load' event. it is not in the form properties box ?

It should be under the event tab. Scroll down. It's definitely there.

Mary

[questionit]

It should be under the event tab. Scroll down. It's definitely there.

Mary

OnLoad event would be in the properties of the form, not of the button, right?
And It is not there under the Even Tab..

how can i attach an image with this message? the attach an image button prompts me and asks to enter text to be formatted- what is that

i dont know if i am gonig totally wrong. but under the event tab ,on load is not present?

[MMcCarthy]

OnLoad event would be in the properties of the form, not of the button, right?
And It is not there under the Even Tab..

how can i attach an image with this message? the attach an image button prompts me and asks to enter text to be formatted- what is that

i dont know if i am gonig totally wrong. but under the event tab ,on load is not present?

Are you sure you are in the forms properties. Does it say Form in the Blue Bar at the top of the properties box?

Mary

[questionit]

Are you sure you are in the forms properties. Does it say Form in the Blue Bar at the top of the properties box?

Mary

. My form is called 'names'.. so it says on the Blue bar: 'names: Form' . And the properties box says ' Section: Detail' - this text 'Secion: Detail' remains same no matter what the form is called.

Is there any way i can go directly into the code input area and type in the required code?

[questionit]

. My form is called 'names'.. so it says on the Blue bar: 'names: Form' . And the properties box says ' Section: Detail' - this text 'Secion: Detail' remains same no matter what the form is called.

Is there any way i can go directly into the code input area and type in the required code?

I am sorry, i have found OnLoad now. It was under 'Form'.. i was looking under the 'Detail'.

But with On Load code , there comes another confusion.

Previously, you had given me a code so that when the button is pressed, an appropriate table is opened (the same as the surName).. it worked fine!

But the On Load code, i have now put in the form's(called 'details') On Load property. This form need to be opened when the command button is pressed (on the main form).

But the table the 'details' form will use to get data will be different all the times (different with different surname!) ... so where would i put the OnLoad code?

i have tried putting onLoad code in the 'details' form's OnLoad property, but nothings happens... When i press the button, appropriate tables are opened -not the 'details' form ??


Thanks

[questionit]

I am sorry, i have found OnLoad now. It was under 'Form'.. i was looking under the 'Detail'.

But with On Load code , there comes another confusion.

Previously, you had given me a code so that when the button is pressed, an appropriate table is opened (the same as the surName).. it worked fine!

But the On Load code, i have now put in the form's(called 'details') On Load property. This form need to be opened when the command button is pressed (on the main form).

But the table the 'details' form will use to get data will be different all the times (different with different surname!) ... so where would i put the OnLoad code?

i have tried putting onLoad code in the 'details' form's OnLoad property, but nothings happens... When i press the button, appropriate tables are opened -not the 'details' form ??


Thanks

Mary..i have done it . it is working..

i put this code for the command button:

DoCmd.OpenForm "details"

and in the 'details' form onLoad properties, i put the code you have provided me

Thanks a lot..

[MMcCarthy]

I am sorry, i have found OnLoad now. It was under 'Form'.. i was looking under the 'Detail'.

But with On Load code , there comes another confusion.

Previously, you had given me a code so that when the button is pressed, an appropriate table is opened (the same as the surName).. it worked fine!

But the On Load code, i have now put in the form's(called 'details') On Load property. This form need to be opened when the command button is pressed (on the main form).

But the table the 'details' form will use to get data will be different all the times (different with different surname!) ... so where would i put the OnLoad code?

i have tried putting onLoad code in the 'details' form's OnLoad property, but nothings happens... When i press the button, appropriate tables are opened -not the 'details' form ??


Thanks

You need two pieces of code.

One for the command button to open the details form and the second one for the On Load event of the details form to change the record source to the table chosen in the previous form.

Make sure the Details form is set to Form view and not datasheet view.

Mary