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

[KayCee]

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.

[MMcCarthy]

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.

[KayCee]

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

[MMcCarthy]

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:

 
Private sub Form_Current()
 
  DoCmd.OpenForm "CLENT LIST FORM", , , "[Client#]=" & Me.[Client#]
 
End Sub

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

[KayCee]

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.

[MMcCarthy]

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#]

[KayCee]

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?

[MMcCarthy]

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#]

[KayCee]

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.

[NeoPa]

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.

[MMcCarthy]

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

[NeoPa]

Let's just hope the answer is just that simple - KayCee, we await your response with anticipation.

[KayCee]

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.

[MMcCarthy]

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:

 
Private Sub Form_Click()
 
 
End Sub

Add the line I gave you earlier and you should end up with:

 
Private sub Form_Click()

  DoCmd.OpenForm "CLENT LIST FORM", , , "[Client #]=" & Me.[Client #]

End Sub

[KayCee]

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?