469,328 Members | 1,316 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,328 developers. It's quick & easy.

ms access command button

553 512MB
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
Feb 14 '07 #1
24 5100
Rabbit
12,516 Expert Mod 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenQuery "Name of Query"
Feb 14 '07 #2
questionit
553 512MB
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:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenQuery "Name of Query"
yes i need seperate tables.. how can i do it then?
Feb 14 '07 #3
Rabbit
12,516 Expert Mod 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenTable "Table_" & Surname
Feb 14 '07 #4
questionit
553 512MB
Why do you need seperate tables?

The code to open a table for your naming convention would be:
Expand|Select|Wrap|Line Numbers
  1. 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
Feb 14 '07 #5
Rabbit
12,516 Expert Mod 8TB
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.
Feb 14 '07 #6
questionit
553 512MB
"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
Feb 14 '07 #7
questionit
553 512MB
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?
Feb 14 '07 #8
MMcCarthy
14,534 Expert Mod 8TB
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

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenTable "Table_" & Me.surName_Label.Caption
Mary
Feb 14 '07 #9
questionit
553 512MB
1. The Me. refers to the current form.
2. You cannot use .Value on a label, thy the following

Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 15 '07 #10
MMcCarthy
14,534 Expert Mod 8TB
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 ...

Expand|Select|Wrap|Line Numbers
  1.  DoCmd.OpenTable "Table_" & Me.surName
Feb 15 '07 #11
questionit
553 512MB
Try ...

Expand|Select|Wrap|Line Numbers
  1.  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 ?
Feb 15 '07 #12
MMcCarthy
14,534 Expert Mod 8TB
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 ...

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenTable Me.surName
Feb 15 '07 #13
questionit
553 512MB
If the table is called 'detail1', you don't need the 'Table_' part, just ...

Expand|Select|Wrap|Line Numbers
  1. 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?
Feb 15 '07 #14
MMcCarthy
14,534 Expert Mod 8TB
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
Feb 15 '07 #15
questionit
553 512MB
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 ?
Feb 15 '07 #16
MMcCarthy
14,534 Expert Mod 8TB
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.  
  3.   ' Replace FormName with the name of the Form with the surName textbox.
  4.   Me.RecordSource = Forms![FormName]![surName]
  5.  
  6. End Sub
Mary
Feb 15 '07 #17
questionit
553 512MB
You need to change the record source of the form each time. In the forms On load event put the following code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.  
  3.   ' Replace FormName with the name of the Form with the surName textbox.
  4.   Me.RecordSource = Forms![FormName]![surName]
  5.  
  6. End Sub
Mary
I cant find the ' On Load' event. it is not in the form properties box ?
Feb 15 '07 #18
MMcCarthy
14,534 Expert Mod 8TB
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
Feb 15 '07 #19
questionit
553 512MB
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?
Feb 16 '07 #20
MMcCarthy
14,534 Expert Mod 8TB
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
Feb 18 '07 #21
questionit
553 512MB
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?
Feb 18 '07 #22
questionit
553 512MB
. 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
Feb 18 '07 #23
questionit
553 512MB
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..
Feb 18 '07 #24
MMcCarthy
14,534 Expert Mod 8TB
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
Feb 18 '07 #25

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Nathan Bloom | last post: by
49 posts views Thread by Yannick Turgeon | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
reply views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.