Open a second form to the records selected on the first form 1

[Lavenderchan]

Hello
I am trying to open a second form from my first form based on the records on the selected on the first. The code I am using is
DoCmd.OpenForm "Expense Report List", , , "id = " & Me.number
However it is only returning the first record from the first list and not all five. What am I doing wrong?
Thanks.

 

[MajP]

This question is unclear. Are you expecting the second form to open with 5 records? If so what about the five records on your first form is common? I assume they do not all have the same ID. If you mean you are unable to open other records, this code will open to the currently selected record. You may want to put the button for selecting in the detail section if this is a continuous form to move to that selection.

Also for testing put this in front of your code
msgbox "id = " & me.number
and tell us what you see.

 

[Lavenderchan]

Yes the code is opening the second form with the top most record. The msgbox returned id=80001 which confirms that. The id field is a unique record but are the same in both forms.

 

[MajP]

Again it is unclear.
1. Do you want to open a second form with 5 records
2. or do you want the ability to select any of the 5 to open.

If 1, You need a different filter. Need to filter on something that is common for the 5 records. They may have something in common but they do not have the same ID.

If 2, you need to be clicked onto the record you want to open. That was my suggestion for moving your button (I assume a command button) into the detail section. If it is in the footer or header there is nothing to force the current record unless you remember to physically move.

 

[Lavenderchan]

I want to open a second form with 5 records. I am using a split form so adding the command button to detail section will not open all 5 records.

 

[MajP]

Sorry I am so confused. Maybe you can post an image.

Your code currently says open a form that is filtered to ID = 80001 where 80001 is the current record of the first form, and it is doing that. Are you saying that you have 5 records with ID 80001? Based on what you said, that does not make sense to me because you said ID was a unique Key. Is this a foreign key and all 5 records share the same foreign key.

 

[MajP]

If I have 5 records on a second form that have a "status" of "New" this code will open the form filtered to five records
DoCmd.OpenForm "frmOrders", , , "status ='new'"
If I have 5 records on a second form with a CustomerID of 80001 this will open the second form filtered to five records
DoCmd.OpenForm "frmOrders", , , "CustomerID = 80001"

But if I have 5 records on form 1 and each has its own uniqueID then
DoCmd.OpenForm "Expense Report List", , , "id = " & Me.number
will open the second form based on whatever record was the current record on the first form. So if you a clicked on the record 80001 then me.number should return 80001. If you are clicked on 80002 then me.number should return 80002.

 

[Lavenderchan]

 

[dhookom]

I think the issue might be that you can select multiple contiguous records in a continuous form. I believe Lavenderchan wants to grab the CustomerID of the selected records which requires some special coding using seltop and selheight.

You might want to check out this thread on stackoverflow.


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

If this approach would work (hard-coding your IDs first):
[tt]
DoCmd.OpenForm "Expense Report List", , , "id [blue]IN(80001, 80002, 80003, 80004, 80005)[/blue]"
[/tt]
You may simply collect all desired IDs in code and pass it as a variable to your [tt]DoCmd.OpenForm [/tt] statement

Just a guess....


---- Andy

There is a great need for a sarcasm font.