Error trapping a query with no records as a result

[JamesBBB]

Hi There, Could someone help me with little problem with Access 2003, I have a database with address details and sometimes I need to search on a post code (zip code). The way I have done this is to set a public variable with the post code which is then passed to a query as criteria.
On the after update function of the (search for postcode) field on the form, I change the recordsource to this query and it brings up just the records with that exact postcode.

However, if no such post code exists, then the query still runs, but with no records as the result which means the recordsource is set against this query with no records and consequentially the form just displays as a completely blank screen as there are no records.

My question is, how can I trap this so that if the post code does not exist, the record source is not changed and I can put up a message box saying there are no matches.

I’m really stumped at the moment, any help would be gratefully received, even a better way of performing the task

Many thanks

James

 

[dhookom]

If you showed us your code, someone might be able to make a suggestion. We don't have a clue regarding your table structure, query SQL, form or control names,...

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya JamesBBB . . .

Running the [blue]DLookup[/blue] aggregate function would tell you if the zipcode existed in the table ... allowing you to take appropriate action:

[blue]   Dim Cri As String
   
   Cri = "[ZipCode] = '" & Me!ZipCode & "'"
   
   If IsNull(DLookup("[ZipCode]", "TableName", Cri)) Then
      [green]'Your MsgBox Here[/green]
   Else
      [green]'Change the RecordSource[/green]
   End If[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]