Message Box Macro

[bobbobruns12]

IF Anyone can help this would be great as this seems like I've done it right. I have created a database which has stored numeric tables and have used Dlookup in the control properties to be able to automatically fill in Text boxes on forms. One of these text boxes, CityCode, fill's in the zip code of a city and state when the city and state are entered onto the form. However if this process completes and the city and state are not recognized, then it just leaves the textbox blank. I am trying to create a macro which displays an error message if the this field is left blank.
Here is the code i created for DLookUp:
=DLookUp("[ZipCode]","ZipCode","[City] = '" & [City] & "' AND [StateCode] = '" & [State] & "'")

In the Macro I have
Conditions: [CityCode] Is Null
Action: MsgBox
etc etc etc
I put this under the textbox CityCode Event Property on AfterUpdate yet the macro does not run when this textbox is left empty so I'm wondering if I may have missed something or maybe there's a better way to do it. Thanks to anyone who could help

 

[pbbriggs]

bobbobruns12,

I don't use macros, but it sounds like your macro may never be called if you use the "AfterUpdate" event of the DLookUp text box (CityCode) because - in the event the lookup city/state is not found, the box is not updated. Does the user click a button after entering the city/state combo (to execute the DLookup)? If so, try putting your macro into the OnClick event of the button. If not, try putting it into the AfterUpdate event of the last ENTRY textbox where the user enters the city or state.

I don't know if the syntax is the same for macros as it in VBA, but if you want to switch to VBA, putting the following code in the "On Click" event of a button (or the "AfterUpdate" event of either the city or state textboxes) will work.

If IsNull(txtCityCode) Then
MsgBox "There is no zip code!", vbOKOnly
End If

 

[bobbobruns12]

Thanks a lot PBBriggs I put it into the State textbox and it worked perfectly appreciate the help