How to create message box when no records found in access form 2013

[ESAKKI109]

In the attached image if I give input in the search criteria form it will bring the result in the query format based on input I key in . Incase if no records found after hitting RUN button how can I bring up the message "Sorry,no records found " instead of showing blank records in query .

when I checked the properties of run button some macro is running behind it . So can we add macro program in addition to that to show our message or do we have to write separate VBA program

[twinnyfo]

ESAKKI,

This will be much easier to do in VBA. You should be able to convert the macro behind that button to VBA code, then modify it, so that when the query returns no records, a MsgBox indicates this.

This should be relatively easy to do, once we see the VBA behind the macro.

[zmbd]

So as not to confuse you...
In EXCEL, the term Macro is used for a VBA script.
However, in ACCESS, there are two programing languages.
Access-Macro = Generally used for simple things and web database.

Access-VBA = Used by most of the Access programers here, I highly advise you to learn and use the VBA programing language.

As for your current situation...
You will have to use a conditional and the domain agregate functions
So open your form in design mode.
Insert an if then
insert an else tothe if then
move your open command down to the else
insert a message box into the true section
Use the DCOUNT() in your IF() criteria (hint, you can use a wild card for the expresion and you don't have any criteria)
...

Try this yourself... if you get stuck...
IN ACC2010:
Open the macro in the editor
Click into the editing box
<ctrl><A>
<ctrl><C>
Come back to the postbox here at bytes...
Please, click on the [CODE/] button in the post toolbar and then cut and paste your script between the [code] [/code] tags.

If you would like to do this in VBA - there are basiclly two methods - DCount() or Recordset
<MAKE A BACKUP OF YOUR FILE>
NB:This is a oneway trip for the open form. Once done you can not easily convert from VBA to Macro (yuck :P :d)
Open the form in design mode
In the ribbon, From Design Tools, Design, Tools
"Convert Form's Macros to Visual Basic."
Same section in the Ribbon,
"View Code"
If you haven't named the controls something usefull, then you'll get to hunt for controlnamehere_ONCLICK
You can recycle the VBA code written for you, add yuor IF...THEN consturcts as

[ESAKKI109]

Can we give me the VBA coding to show the message "No record found " as I don't have much idea how to write VBA coding for my requirement.

Form name in my database: new_orders_search_form
query name in my database:whole_order_track_list qry

the concept is once i keyed in the data in new_orders_search_form it will open up the query named as (whole_order_track_list qry) based on my input . what I have done is if I don't give any input in search form it will bring all the datas which already existed in the query .

because I have written criteria in whole_order_track_list qry that

"Like "*" & [FORMS]![NEWORDERS_SEARCH_FORM]![CUSTOMER_NAME]
& "*" .

So when I give input in new_orders_search_form if the relavant data does not exist in that query then it should pop up the "no record found message" instead of showing blank query .
what is the Vba coding for this case. also let me know where I have to implement the VBA coding . I mean what is the procedure?

[NeoPa]

Before we go much further you should understand that while we're happy to help you along and fill in any gaps in your knowledge, we are not a free code-writing service. We expect you to learn from the help you get here so that you are in a position to do it for yourself next time a similar issue comes up. Those members who try to use us simply to do their work for them are showing disrespect and will soon become very unpopular. So. Moving on.

I would suggest use of the Current event. It may be necessary (as I struggle to follow your explanations.) to ensure this is only ever done when the form is opened. The code I'll suggest incorporates this logic :

Private blnNotFirst As Boolean
 
Private Sub Form_Current()
    If blnNotFirst Then Exit Sub
    blnNotFirst = True
    If Not Me.Recordset.EOF Then Exit Sub
    Call DoCmd.Close(ObjectType:=acForm, ObjectName:=Me.Name, Save:=acSaveNo)
End Sub

bnlNotFirst will start as False by default. Once it's been through the routine the first time it will be set to True so the code will never run again.

Me.NewRecord is another way of testing for there being no data selected but this can be confused by a recordset which is not updatable or otherwise has no new record.

[zmbd]

ESAKKI109:
In the same thought as NeoPa; I've sent you a copy of my boilerplate with tutorials and (what I hope) are other resource links. Please check your Bytes.Com inbox. (^_^)

[AccessAirForce]

Hi there NeoPa,

You seem a bit salty ha ha , been burned I take it :)

Anyhoo, I am adding the same note into my VBA just browsing through to see what you all use. I believe my problem is placement of the code line.

Have a good day

[NeoPa]

Not sure I completely follow you, but we do have deal of experience as to how these forums benefit the members the most. The experts are human like us all and very few of us respond well to people treating us without respect.

Of course, the reverse is also true. Many experts here are very easy to take advantage of. Just a little respect and they'll go an extra mile for people.

So simple really, yet we've all had to deal many times with members that don't even get that. Such is life.

[aflores41]

If DCount("*", "table name or query") > 0 Then
DoCmd.Close
DoCmd.OpenForm "form name", acNormal, , , acFormReadOnly
Application.SetOption ("auto compact"), 1
Else
Application.SetOption ("auto compact"), 1
MsgBox "No Records To Display"
End If

[zmbd]

aflores41:
Better to do this as indicated in post#5 or via record set.
Also I highly recommend not using the "autocompact" as shown on lines 4 and 6 on close... Allen Browne - Bug Report unless you like your databases corrupted.