By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,561 Members | 3,127 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,561 IT Pros & Developers. It's quick & easy.

ms access command button

100+
P: 553
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
Share this Question
Share on Google+
24 Replies


Rabbit
Expert Mod 10K+
P: 12,366
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

100+
P: 553
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
Expert Mod 10K+
P: 12,366
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

100+
P: 553
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
Expert Mod 10K+
P: 12,366
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

100+
P: 553
"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

100+
P: 553
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
Expert Mod 10K+
P: 14,534
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

100+
P: 553
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
Expert Mod 10K+
P: 14,534
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

100+
P: 553
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
Expert Mod 10K+
P: 14,534
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

100+
P: 553
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
Expert Mod 10K+
P: 14,534
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

100+
P: 553
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
Expert Mod 10K+
P: 14,534
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

100+
P: 553
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
Expert Mod 10K+
P: 14,534
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

100+
P: 553
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
Expert Mod 10K+
P: 14,534
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

100+
P: 553
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

100+
P: 553
. 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

100+
P: 553
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
Expert Mod 10K+
P: 14,534
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.