Conditions, Conditions

[Mark-BES]

I am new to all this and require a little help if I may.

I am slowly learning Access and ulitmately want to learn to build a DB relying on VB rather than macros. (VB... yet another thing to learn!)

I have a search post code macro running a select query (see attached)

I need to know what condition can I add to the macro to display my own message and stop the macro running?

If I type AJ1 1AJ or JS1 1JS into the post code field and click "Search P/Code" the macro will copy and paste the existing details into the form. If the postal code does not exist I get a number of errors indicating "copy cannot be used now" etc.

Any help would be great. Thanks.

 

[FoFa]

Typically you can use a DCOUNT function in the condition and check for a non-zero return to execute the statement or check for a zero returned then do not execute the statement/s or Stop the macro.

 

[Mark-BES]

Thank you, but forgive my ignorance. how would I use this? As I said, I am new to all of this.

Would I put this before the "is null" condition or does it replace it?

Thanks again!

 

[trucktime]

Postalcode

Wouldn't it be easier to have a combobox on your form and autopulate the fields from there? That way you don't need a macro.

 

[Mark-BES]

Thanks, An idea I thought about, but have too many customers for a combo box really.

Simply enter the post code, click the button and the result appears. The process works well.

The only problem is the two messages when the postal code does not exist on the system. Can't it have a mssg box "No matches found" then return to the normal window? At the moment users have to close the halted macro, close the query etc.

 

[trucktime]

Search

I understand what you're saying.

Still I think you should use a different solution, stay away from macros as much as you can.

You may want to check this link, it has a great search facility. You can download the sample and easy integrate it into your own application.

http://www.microsoft-accesssolutions.co.uk/text_search.htm

 

[FoFa]

Basically in the condition you can use
Check for a zero return value and run STOP MACRO command
0 = DCOUNT("ColumnName","MyTable","[Postal Code] = " & Forms!MyForm![Postal Code]

Or you can replace the 0 = with 0 < and then you can check to verify you have at least one that matches and run the query

as an example.

May need tweaking as this was just off the top of my head ans d of course you will have to adjust it for you stuff.

 

[Mark-BES]

Thats fantastic! Thank you.