Form Navigation Help

[Appeal]

Hi all,

I have created a multiple items form from a query and have a list of different products. From this list I would like on click of a particular product from the list it to open up the individual product form for that item. I have tried doing this using a macro openform with the where condition ="[ProductID] =" & [ProductID]. This does open the product form but only opens the product form for the top record on the multiple items form no matter which record I click in the list. Could anyone help please?

 

[Trevor G]

In the macro you need to set the condition to look at the source table to match the Form ID then you can open up the form with the single form in it.

 

[heinc1]

I have a similar problem and found this post, but do not know how to set this condition to look at the source table
I have a table [Residents Table] and a form [Residents Table Form] which is based on the Residents Table, but just contains a few of the fields . I would like to use the Residents Table Form to search for a resident, then double click on the ID number of the resident to open a form [Entry Form] to that exact same resident and ID number to enter new order information. Right now my macro from the Residents Table Form is: Where Condition=[ID]=[Forms]![ENTRY FORM]![ID] The Entry Form opens, but to a blank (new) record. The Data Entry property on both forms is set to No.
Any help you can provide would be greatly appreciated.

 

[Trevor G]

Welcome to the Forum,

When you set the Where Condition you need to make sure you include the table name and the ID field before it is equal to the Form name and id field then it will work.

 

[heinc1]

Thanks for your speedy reply Trevor! I appreciate your willingness to help!
I just tried the following:
Where Condition=[Tables]![Residents Table]![ID]=[Forms]![ENTRYFORM]![ID]
The result is an Enter Parameter Value dialog box opens saying Tables!Residents Table!ID. If I enter nothing in the parameter dialog box, it opens the form to a new record. If I enter an ID number, it also opens the form to a new record.
When I change it to[Residents Table]![ID]=[Forms]![ENTRYFORM]![ID] the parameter dialog box no longer pops up, but the form still opens to a new record.
Is there something wrong with the syntax?

 

[Trevor G]

Do you have anything in the form when it opens that it goes to a new record?

 

[heinc1]

I think you you are asking if I have a macro in "On Load" that would be telling the [Entry Form] to open to a new record. The answer is no, there is none.

 

[Trevor G]

Any of the Events may prevent this from showing the record.

On Activate
On Open


Please try this if it doesn't work then perhaps look to upload a sample of the database.

Open the form which shows the ID Field you are double clicking and then Open the MACRO in Design View, in your MACRO it will show you the Action OpenForm

In the Arguments below you are showing the form Name

Below in the Conditions delete what you see, then click the Ellipse button (3 dots) on the right this will invoke the Expression Builder, then double click the Tables section and then double click the table name, then in the next column double click the ID field this will add the Table and Field name in the top of the Expression. Then add the = sign and then double click the forms section and then loaded forms then your form name then in the next column the ID Field. Close the Expression Builder and Save your MACRO, there should be no further actions. So if you test the macro on the form it should open the form and show the record.

If that fails then try creating a New Form and add the MACRO to the form.

If that fails upload a sample of your database either directly in your thread (must be less than 2mg ) or via a third party like drop box.

 

[heinc1]

I just tried this and now the Where Condition =[Residents Table]![ID]=[ID]
The [Entry Form] opens to the very first record as the [Residents Table] is sorted (by the field LastName)
So, we have gotten past it opening to the new record, but it still is not matching the ID when the Entry Form opens.
I am going to create a New Form and double check that every field matches. Will let you know if that works. In the meantime, could the filter, filter on load, order by, order by on load contents have anything to do with this problem?

 

[heinc1]

Ok! I got it to work. Here is how it is set up.
I created an independent Macro named "Open Form".
Form Name: Entry Form
Where Condition =[Residents Table]![ID]=[Forms]![Residents Table Form]![ID]
On Design View of [Residents Table Form] On Dbl Click Right clicked on the Elipsis and chose the Macro Open Form. Closed and saved it.
Works like a charm.
Thanks for your help!!