Macro help - annoying dialog box.

[Mark-BES]

Hi there,

On my form, the user types in the customers postal code in to a field, clicks the "search address" button.

This in turn runs a macro based on a query that finds the previous customer details and copies them to the latest record on the form (saving much time re-entering existing customers details).

However, If the customers details do not exist on the Db. The macro simply throws up "Copy is not available now...." then display's the macro halt dialog window. Very annoying.

Is there any way to just have a message box stating "No details available"
Any help would be much appreciated.

 

[ColinEssex]

why would you want to re-enter existing customer details? surely a customer is entered only once. Or am I misreading your thread

Col

 

[Mark-BES]

Hi,

We re-enter existing customer details for job sheets to repair customer equipment. A job number is issued (auto number) each time we create a new record.

 

[KenHigg]

And how could you look up a customer based on a postal code?

Or am I also misreading your thread?

kh

 

[Mile-O]

And how could you look up a customer based on a postal code?

Because they already have the customer's address stored and post codes are unique.


Anyway:

If DCount("MyPostCodeField", "MyTable", "MyPostCodeField = """ & Me.MyTextBox & """") = 0 Then
    MsgBox "No details available.", vbExclamation
Else
    DoCmd.RunMacro "MyMacro"
End If

Personally, though, I'd convert the macro to VBA and tidy up its code.

 

[KenHigg]

Because they already have the customer's address stored and post codes are unique.

Hum...

Over here you may hundreds of thousands of customers with the same postal code, or as we call it 'zip' codes...

kh

 

[Mile-O]

Ours, is like this: G2 7TR

The first part lists the area : G is for Glasgow. The 2 represents a section of Glasgow. The 7 the T and R cascade down to an individual location.

 

[ColinEssex]

Ours, is like this: G2 7TR

The first part lists the area : G is for Glasgow. The 2 represents a section of Glasgow. The 7 the T and R cascade down to an individual location.

But you can still have several different houses with the same postcode

 

[indigomonkey]

Yep, usually a small road (100 houses max?) will have its own postcode.

 

[Mile-O]

Oh. Strange, when I used the Open University website I put in my postcode and it told me my address.

 

[ColinEssex]

Oh. Strange, when I used the Open University website I put in my postcode and it told me my address.

In the majority of cases though Mile-O, it will tell you everything except the house number. Presumably if you have a "one house" postcode then it will tell you the number too.

Going back to Marks initial question, personally, I would use a listBox with a surname search linked to it so you can then select the customer from there (or add new if not registered)

Col

 

[Rich]

Oh. Strange, when I used the Open University website I put in my postcode and it told me my address.

Had you already entered your name?

 

[Mile-O]

Can't remember although I do think I have a 1 house post code. Who cares, really?

 

[Mark-BES]

Hi,

I am only a mere amateur when it comes to this stuff. A little more help if I may? Please see the attached Db sample with dummy data.

If I type the postal code "AJ1 1AJ" (an existing customer) and click "Search P/Code" it will copy and paste from Table1.

If the postal code does not exist eg: Z1 Z11 the macro ends and you have to close a number of windows (the annoying bit).

I thought I could add a condition so if there is no postal code it closes the windows and displays my own message.

 

[Mile-O]

After looking at your database I can see that this problem is the least of your worries. You have everything in one table, repeating groups, and many other issues with database normalisation.

I suggest you read this and learn how to design a database before you go ahead and make a mess of one.

 

[ColinEssex]

After looking at your database I can see that this problem is the least of your worries. You have everything in one table, repeating groups, and many other issues with database normalisation.

Aha!!! thats why I asked why the detail needed to be re-entered.

If it is for the job sheet then a query could pull it out from the customers table.

Col

 

[Mark-BES]

Thanks. I have a pretty good understanding of database design and ensuring normalisation.

I am only trying to polish this old thing (origanally designed by my predecessor).

I will be designing a new one when I have the time. At the momont this works for what we use it for. I just need a bit more advise on that macro issue. Thanks again.

 

[Mile-O]

I am only trying to polish this old thing

Polish? It needs ripped apart!

I just need a bit more advice on that macro issue. Thanks again.

The code I printed above will do what you want anyway.