Access 21 problem with where command

[ChrisLeicester]

Hi All
I haven't really used Access much for a few years and must be rusty. I have also just upgraded from Access 2007 to Access 21 but don't think that is my issue.

I have a main form with a subform which in turn has its own subform.

Main form - purchaseOrderForm
1st Subform - purchaseordersubfm
2nd subform - purchaseitemsub

Set up because 1 supplier has many purchase orders which in turn one purchase order has many items.

I have a modal form which I am using for a selection form. It opens and has an unbound combo box where the supplier is selected and an action button where I want to open the main form filtered to the supplier chosen from the combo box.

In the event on click I have the macro to open this main form but it won't as it asks for a parameter.

In the where option of the macro, I have

[SupplierID]=form![selectsupplier]![supplierselected]

[SupplierSelected] being the name.of the combo box on the modal form

When run I get the error select parameter 'supplierselected' and it doesn't recognise the combo box entry.

Where am I going wrong.

Thanks

Chris

 

[MajP]

check the name and spelling on the combobox. That could be a simple typo in the name based on that error message.

 

[Gasman]

Shouldn't that be forms?
However if that code is in the form, just reference it locally Me.supplierselected

 

[arnelgp]

your macro should be (Forms, with "s"):

[SupplierID]=Forms![selectsupplier]![supplierselected]

 

[ChrisLeicester]

Hi,
Sorry that's my typo. I used the builder so it would have been forms!...

 

[Gasman]

Please copy and paste in future.
Do not retype.

Upload enough of the db to see the issue.

 

[ChrisLeicester]

Hi
I deleted the named unbound combobox and added a new one and that button now works for finding the correct main form.

On this modal form I have two filter methods, one as above to bring up purchase orders for a specific supplier and another text box where you can enter a specific purchase order number and it should find that individual purchase order.

To do that, i need the where command to look for a specific field on the subform, not on the main form. I have entered the following in the where section of the open form command in the macro;

[Forms]![NewPurchaseOrderFM]![PurchaseOrderMainSub1FM]![ProdOrderID]=[Forms]![SelectPurchaseOrderFM]![Text15]

1. [ProdOrderID] is the primary key field in the PurchaseOrderMainSub1FM subform
2. [Text15] is the unbound textbox on the modal for the user to enter what purchase order they want to view.

When the command button is clicked, it does open the [NewPurchaseOrderFM] form but a blank one with no data, it doesn't find the record requested.

Thanks

Chris

 

[Gasman]

Walk your code and see what you actually have. Not what you think you have.
I have no idea as to why your are using full form references for code inside a fomr/subform that refer to either?

See here for control syntax examples.

Also start using valid control names. Text15 is not going to mean anything to anyone, including you after a few weeks.
You can use the value in the Openform command in the WHERE argument.

 

[ChrisLeicester]

Hi
I do usually give my text boxes a meaningful name, i haven't in this instance as I was just trying to get it to work, once I did that I would tidy it up.

I tried using the full form references as the shorter ones didn't work either, it kept asking me for a parameter as it didn't recognise the intended field.

 

[June7]

Use VBA instead of macro and debugging will be easier (I never use macros).

You have a modal form with a macro that is opening another form and that form has subform you want to filter? Conventional approach is to filter main form and subform master/child links synchronize subform.

As suggested, you should probably upload database for analysis.

 

[ChrisLeicester]

Hi
Yes, maybe I am going about it the wrong way.

The main form will open a specific supplier, the subform holds 'many' purchase orders. I want it to bring up the customer and then show the subform for that chosen purchase order number.

Maybe I need it to query the purchase orders, find the supplier and then use a goto/find command to go to the correct record, which will need more actions rather than just open form with a where command.

Database uploaded, still very much work in progress

 

[Gasman]

How about some instructions on how to operate this?
If I select Suppliers button, I get a form titled Suppliers and neither View or Add supplier button works?

Edit: That is because there is no code for them?

 

[ChrisLeicester]

Hi Sorry
1. click logon, there is no logon programmed yet
2.click the purchases button
3. click the view purchase order button which will bring up the modal form I am talking about
4. the bottom text box is where you enter a number. 1 is the only record entered

thanks

 

[June7]

Right, OpenForm and its WHERE criteria cannot be applied to filter a subform as subforms are not a "form" in the Forms collection. You can apply criteria to subform Filter property (which I think can be done with macro or VBA) or use some "go to record" method. A common approach uses Bookmarks. Review https://www.access-programmers.co.u...-to-a-specific-record-using-gotorecord.36579/

You need to open form to supplier then apply filter (or "go to") to subform (if criteria is provided). Or open the purchase order form independently, not as a subform.

I would probably build search utility on NewPurchaseOrderFM instead of a popup.

 

[ChrisLeicester]

Thanks, I thought that might be the case.
I think I may have been lazy in trying to use the Main Purchase order form for too many things rather than more forrms.

I now think I can easily do another form where the main form is the purchase order and then use the where command to find it.

That form works well for adding a new order but two forms will be better than one,

Thanks for the tips, I will take them onboard
Chris

 

[Gasman]

I do not use macroes either, except perhaps for batch processes.

Your opening form should be

[Forms]![NewPurchaseOrderFM]![PurchaseOrderMainSub1FM]![ProdOrderID]=[Forms]![SelectPurchaseOrderFM]![Text15]

as the macro does not recognise Text15 on it's own.
However that will still not work, as it is.

Another reason to use VBA. Now I see why you are always using full form references. Yet another reason to learn VBA.

You could Dlookup() the supplier for that PO and use that to get the correct supplier on mainform.
Pass in thye PO as an OpenArgs argument, and if present then locate that record in the subform, with FindFirst?

That is just due to how you have it ATM.

 

[June7]

but two forms will be better than one

Not my philosophy. I make forms do double duty whenever possible.

I may have edited my previous post after you read it.

 

[ChrisLeicester]

Thanks all, definitely food for thought.
i originally thought the less forms the better, thats why I tried to use the same form for both searches, then thought the path of least resistance was two forms.

I didnt think about a search box on the main form, thats also an idea,

I have used VBA before but not for over 6 years as the dog breeding access database I built then has worked well for those years without needing any updates and I only just grasped VBA for that one, so I need to go back and refresh myself.

 

[Gasman]

I think you will find it more flexible than macros. Everything is pretty much one Google away. Not so for Access macros.

 

[ChrisLeicester]

Hi
I decided to split the functions and design two forms and not use the one for both functions,

I have worked out the where function which works for both forms and have moved onto the next refinement where i want to check that the 'purchase order' requested does exist in the form/query and if not gives a message box to say not found.

Taking onboard what you suggest I am trying to refresh my limited knowledge of VBA and have written the following;

Private Sub CheckPORecords
Dim POexp as string                   ‘Expression for Dcount command
Dim Formdomain as string        ‘Domain for Dcount command
POexp = “[ProdOrderID]”           ‘ Primary key field called [ProdOrderID]
Querydomain = “PurchaseOrderListQ”   ‘Query [PurchaseOrderListQ] underlining form
If DCount ( POexp , Querydomain , )=0 then
MsgBox "Purchase Number Not Found", vbOKOnly, "Incorrect P.O. Number"
Me.”POSelected” = null
Else
DoCmd.OpenForm "ViewPurchaseOrderFM", , ,"ProdOrderID = '[Forms]![SelectPOnumberFM]"
DoCmd.Close acForm, "SelectPOnnumberFM", acSaveNo
End if
End sub

How does this look, before I entered it in the access I thought it better to check if I have made any glaring mistakes.

Thanks

Chris