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

{SOLVED} Macros; Where Condition expression to move from form to form based on pk

P: 16
Hello All - I'm new to the community.

I have been asked to move the used of a database I created from a record in a table to the corresponding record of a form. After much research and trial & error - I have come the conclution that this is not possable (please let me know if I wrong.) So to achieve what has been requested I am planning to create a form that looks just like to table the user currently looks at and some how create the link between the two forms.

I have created a command button in the tool bar that will run a macro to OpenForm, but I have not had any luck creating a Where Condition expression to move from the primary key of the record that is being viewed to the corresponding primary key record in the other form.

Please let me know how to create the expression, or any other way of accomplishing this request. Thank you.
Nov 13 '06 #1
Share this Question
Share on Google+
16 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Hello All - I'm new to the community.

I have been asked to move the used of a database I created from a record in a table to the corresponding record of a form. After much research and trial & error - I have come the conclution that this is not possable (please let me know if I wrong.) So to achieve what has been requested I am planning to create a form that looks just like to table the user currently looks at and some how create the link between the two forms.

I have created a command button in the tool bar that will run a macro to OpenForm, but I have not had any luck creating a Where Condition expression to move from the primary key of the record that is being viewed to the corresponding primary key record in the other form.

Please let me know how to create the expression, or any other way of accomplishing this request. Thank you.
I don't really understand what you are trying to do.

You have a table and a form based on a different table?
You want to move a record from the first table to the table the form is based on?
Are both records exactly the same?

You will have to give a LOT more detail of what you're trying to do before we can help.
Nov 13 '06 #2

P: 16
Sorry I will try to be more clear. I have a table "CLIENT LIST" and a form "CLENT LIST FORM". The form has all of the information from the table, but it is layed out so that all of the information is viewable on one screen.

I have been asked to make it possable to move from a record in the table to the same record in the form with the push of a button.

If I have it right - it is not possible to move from a table to a form in this manner, but I should be able to move from a record in a form to the same record in a different form - right?

My plan is to create a form that looks like the table and use a command button in the tool bar to run a macro for OpenForm, but it is the Where Condition expression that is causing a struggle. The primary key the "Client #" is the same in the table and all of the forms. Hope that helps. KC
Nov 13 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
OK I understand now.

You are right in that you cannot set code to move from a table to a form.

You can create a datasheet form to look like the table however.

In whatever event you are going to go to the other form (the On Current event would probably work best) you need to set the criteria based on the tables primary key something like the following:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private sub Form_Current()
  3.  
  4.   DoCmd.OpenForm "CLENT LIST FORM", , , "[Client#]=" & Me.[Client#]
  5.  
  6. End Sub
  7.  
  8.  
This should open the form with only the exact record currently in focus.

Sorry I will try to be more clear. I have a table "CLIENT LIST" and a form "CLENT LIST FORM". The form has all of the information from the table, but it is layed out so that all of the information is viewable on one screen.

I have been asked to make it possable to move from a record in the table to the same record in the form with the push of a button.

If I have it right - it is not possible to move from a table to a form in this manner, but I should be able to move from a record in a form to the same record in a different form - right?

My plan is to create a form that looks like the table and use a command button in the tool bar to run a macro for OpenForm, but it is the Where Condition expression that is causing a struggle. The primary key the "Client #" is the same in the table and all of the forms. Hope that helps. KC
Nov 13 '06 #4

P: 16
Sorry - I am not understanding your answer.

I am looking for the expression I would type into the macro design for the OpenForm Where Condition.

If you can explain where I type the code you have provided - I will give it a try.

I am really new at this - hope it my ignorance is not to annoying.
Nov 13 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry - I am not understanding your answer.

I am looking for the expression I would type into the macro design for the OpenForm Where Condition.

If you can explain where I type the code you have provided - I will give it a try.

I am really new at this - hope it my ignorance is not to annoying.
The code I gave you was VBA.

For a macro try

"[Client#]=" & [Client#]
Nov 13 '06 #6

P: 16
Well almost. This expression gives a pop up that asks for the client number, and once entered goes to the right record, but I would like it to get that information based on the record being viewed. Any other thoughts?
Nov 13 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
Well almost. This expression gives a pop up that asks for the client number, and once entered goes to the right record, but I would like it to get that information based on the record being viewed. Any other thoughts?
It's a long time since I used macros

try

[Client#]=[Client#]
Nov 13 '06 #8

P: 16
That expression does the same as the last and I apologize - it does not go to the correct record when a client # is entered. It goes to the first record in the forms.
Nov 13 '06 #9

NeoPa
Expert Mod 15k+
P: 31,768
In your earlier post you refer to the field as "Client #".
Try replacing any suggestions MMcCarthy made (as [Client#]) with [Client #].
The space doesn't stand out when in simple post form. Sometimes these things are clearer when posted with the [code] tags.
Nov 13 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
In your earlier post you refer to the field as "Client #".
Try replacing any suggestions MMcCarthy made (as [Client#]) with [Client #].
The space doesn't stand out when in simple post form. Sometimes these things are clearer when posted with the [code] tags.
Good catch.

I just assumed it was a macro syntax problem. It's so long since I used macros to do anything.

Mary
Nov 13 '06 #11

NeoPa
Expert Mod 15k+
P: 31,768
Let's just hope the answer is just that simple - KayCee, we await your response with anticipation.
Nov 14 '06 #12

P: 16
Good catch, however I was using the expression builder and it had pulled the correct field name. I still just get the form to open to the first record.
Nov 14 '06 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
Good catch, however I was using the expression builder and it had pulled the correct field name. I still just get the form to open to the first record.
If you want to try using VBA, which should work.

Open the form in design view (the datasheet one). Then open the form properties and under the event tab (Note: make sure you're looking at the form properties) go to On Click. Click on the arrow on the right and choose [Event Procedure], then click on the button with the dots to the right of that.

The VB editor will open and you will see the following:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Form_Click()
  3.  
  4.  
  5. End Sub
  6.  
  7.  
Add the line I gave you earlier and you should end up with:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private sub Form_Click()
  3.  
  4.   DoCmd.OpenForm "CLENT LIST FORM", , , "[Client #]=" & Me.[Client #]
  5.  
  6. End Sub
  7.  
  8.  
Nov 14 '06 #14

P: 16
O.K. I have the code set up in the properties of the Table formated Form, now what? How do I run it? If I am in that form and want to go to the same record in the other form what do to need to push?
Nov 14 '06 #15

P: 16
I got it - I got it!!! I just double click on the line and it moves me to the other form.

Thank You! Thank You! Thank You! for all your time and your help.
Nov 14 '06 #16

MMcCarthy
Expert Mod 10K+
P: 14,534
I got it - I got it!!! I just double click on the line and it moves me to the other form.

Thank You! Thank You! Thank You! for all your time and your help.
You're welcome.

I going to change the tag to solved if nobody has any objection
Nov 14 '06 #17

Post your reply

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