Access 21 problem with where command (1 Viewer)

[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

 

[Pat Hartman]

How does this look,

Awful. You are documenting "nothing" and are not properly indenting the code to make it readable.

Putting the names of controls and queries into variables does nothing to simplify your code or make it reusable. It just makes it hard to follow because when you read the code, you KNOW that the format of a dcount(field, source, criteria). What you don't know is what those three things are and you are attempting to hide the details. The ONLY time you would EVER put one of the three things into a string is if the string were complex and you wanted to be able to easily debug the code to see that all the punctuation was correct. So, we frequently put the criteria into a variable but would never put simple single column or table/query name into a variable.

AND the OpenForm command is incorrect. The Where argument will evaluate to:
ProdOrderID = null

 

[ChrisLeicester]

Hi
Thanks for the feedback.

I will change the Dcount to not use the strings. I did that as a first to copy the syntax so I ensured I followed it.

Where are you getting the ProdOrderID will be null.
I was intending that the where command should be where ProdOrderID is equally to the POSelected which is the text box that the user enters the PO number they want to load.

I thought that POSelected only changes to null if no records are found and that command then clears the incorrect number so the user can try again.