Filter on Double Click (1 Viewer)

kevnaff

Member
Local time
Today, 22:06
Joined
Mar 25, 2021
Messages
141
Hello All.

My database has the following table called NSRSubform. It is a table that stores orders. We have been entering the details of the part that we want to order in the Description field but I have just added a PartNumber field, to separate the description from the part number.

1639651222223.png


After receiving a part, staff are meant to enter the Storemanager form where they can adjust the level of stock, but this is not always done. I have added an event on the double click of the PartNumber field so that this opens the store manager, which will initially help.

Now that each order will have a part number, I am hoping to be able to double click on the part number field on the subform, and this will open the Storemanager form and filter using the value in the PartNumber field.

The form that I wish to load with the filter on is called [Storemanager]. The field that I wish to filter by on load is called [Manufacturer Part No]. The field on the NSRSubform form that I wish to filter by is called [PartNumber] and is located in the NSRSubform table.

Is there an easy way to doing this?

Thanks for your help in advance
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:06
Joined
Feb 19, 2013
Messages
16,553
I have added an event on the double click of the PartNumber field so that this opens the store manager, which will initially help.
I am hoping to be able to double click on the part number field on the subform, and this will open the Storemanager form and filter using the value in the PartNumber field.
Can you clarify what the difference is between these two quotes. If you have already added an event to the partnumber control, what are you hoping to do?

Does the initial comment relate to a control on the main form?
 

kevnaff

Member
Local time
Today, 22:06
Joined
Mar 25, 2021
Messages
141
Can you clarify what the difference is between these two quotes. If you have already added an event to the partnumber control, what are you hoping to do?

Does the initial comment relate to a control on the main form?

Yes sorry. So currently I double click on the PartNumber textbox on my NSRSubform form, and this opens the Storemanager form using a Macro.

I would like to double click on the PartNumber field and this would open the Storemanager form but also filter by the value in the PartNumber field. This would open the Storemanager form to the specific record so that staff can adjust the stock more easily.

Hopefully this makes more sense.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:06
Joined
May 7, 2009
Messages
19,169
you already have the macro, you just add the "Where Condition".

"[Manufacturer Part No] = '" & [PartNumber] & "'"
 

kevnaff

Member
Local time
Today, 22:06
Joined
Mar 25, 2021
Messages
141
you already have the macro, you just add the "Where Condition".

"[Manufacturer Part No] = '" & [PartNumber] & "'"

Hi Arnel,

Thanks for your help. When I use the "Where Condition" above, I get the following:

1639661053138.png


Entering the part number does work, but I believe that it can also be done automatically.

Is this possible?
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:06
Joined
Sep 21, 2011
Messages
14,047
So it is NOT called PartNumber?
 

kevnaff

Member
Local time
Today, 22:06
Joined
Mar 25, 2021
Messages
141
you already have the macro, you just add the "Where Condition".

"[Manufacturer Part No] = '" & [PartNumber] & "'"

Also just in case I weren't clear.

The Manufacturer Part No is stored in a table called ImportParts.

The PartNumber is stored in a table called NSRSubform.
 

kevnaff

Member
Local time
Today, 22:06
Joined
Mar 25, 2021
Messages
141
that is what you wrote on post#1.
the forms and their respective field names.

Using the following "Where Condition":

"[Manufacturer Part No] = '" & [PartNumber] & "'"

It brings this popup box up:
1639661616243.png


If I enter a valid part number in, it just opens the Storemanager form but does not apply any filter.

I have instead entered the following "Where Condition"

[ImportParts]![Manufacturer Part No]=[NSRSubform]![PartNumber]

I now get a similar box, and when I type in a valid part number, it filters by the part number. However still this is not automatic and the user has to manually type in the part number.
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:06
Joined
Sep 21, 2011
Messages
14,047
Access brings up that prompt when it cannot tell what the parameter is, because it does not exist, spelt wrong? :(

So YOU have to get the controlname correct.
That is all you need to do.
I would expect a mismatch error if your syntax was incorrect for the type of control value.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:06
Joined
May 7, 2009
Messages
19,169
check the subform in design view, specially the PartNumber.
PartNumber can be a "label" but not the real Fieldname (bound fieldname).

if [Manufacturer Part No] is not in the table/query then how can you filter this form?
which field can you relate to a "partnumber"?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:06
Joined
Feb 19, 2002
Messages
42,976
This should NOT be a two step process. The form that receives the part should also accept the quantity. You need code in the form's BeforeUpdate event to ensure that all required fields are entered or you need to cancel the event to prevent the data from being saved.

To make this easier, I generally use the Order to do the receipt. The options allow the user to mark ALL items as 100% received or to go through them one at a time and mark each item separately as 100% received or add a quantity if too many or too few were received.
 

Users who are viewing this thread

Top Bottom