error message - box

[Jen0dorf]

Hi

have text box used to setup the variable "varYearOfMarriage"

the form "frm_MarriageYearSearchResults" is called.

Private Sub btn_Search_Click()

    TempVars.Add "varYearOfMarriage", Me.txtYearOfMarriage.Value
    DoCmd.Close acForm, "frm_MarriageYearSearch"
    DoCmd.OpenForm "frm_MarriageYearSearchResults", acNormal, "", "", , acNormal

End Sub

The form "frm_MarriageYearSearchResults" is based on a query

SELECT tbl_Marriage.MarriageID, tbl_Place.Place, tbl_Marriage.DateOfMarriage, tbl_Marriage.YearOfMarriage, tbl_Marriage.GroomSurname, tbl_Marriage.BrideSurname, tbl_Marriage.GroomForenames, tbl_Marriage.BrideForenames, tbl_Marriage.Church, tbl_Marriage.FullDateOfMarriage
FROM tbl_Place INNER JOIN tbl_Marriage ON tbl_Place.ID = tbl_Marriage.[fk_PlaceId]
WHERE (((tbl_Marriage.YearOfMarriage) Like [TempVars]![varYearOfMarriage] & "*"))
ORDER BY tbl_Marriage.GroomSurname;

THis all works perfectly but I want to add some code so that if there is no marriage in that year then a message box appears saying "Sorry no records match your search"

can I add this to the query? I thought I could use the "or" line in query design but I have failed

any hints appreciated

thanks



Ian

[pbaldy]

I would use a DCount() with the TempVar and message the user if 0 records, else open the form.

[Micron]

Queries don't do things like open forms, reports or message boxes. On form opening, check the recordset count. You don't need to know the actual count, just if it's not zero.
If Me.Recordset.RecordCount = 0 Then
add your message box code
End If
If that doesn't work there are two other ways I can think of.
Edit:
Forgot to ask why you have this twice - acNormal?

[Jen0dorf]

HI

thanks, no doubt two great suggestions which will require me to learn something else ;-)

cheers

Ian

[Jen0dorf]

I have no idea why I have acnormal twice, have to confess that originaly I was using macros but several people suggested that vba was better and I discovered the access command to "convert macros to VBA"

Not a good thing to use?

cheers

Ian

[pbaldy]

I have no idea why I have acnormal twice, have to confess that originaly I was using macros but several people suggested that vba was better and I discovered the access command to "convert macros to VBA"

Not a good thing to use?

cheers

Ian

Sure, it's a decent tool. The second acNormal is for the window mode, and it's fine. Could also be left off since it's the default.

[Jen0dorf]

Hi

I liked it because I can look at the VBA code and compare it to the macro and try - note the try - to understand the coding.

[Micron]

Here's the vba syntax Docmd.OpenForm(FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)
AFAIK, the default for window mode is acWindowNormal; the form is acNormal.

It is a decent tool, but it has it's limitations. Any thing near the beginning of a code line that comes before a period is probably worth your while to Google. DoCmd will keep you busy for a while Then there is just about everything that comes to the right of a period

[Jen0dorf]

Hi

sorry to be a pain but I'm going around in circles with this issue

my button has the following code

Private Sub btn_Search_Click()

TempVars.Add "varSurname", Me.txtSurname.Value
DoCmd.Close acForm, "frm_BaptismSurnameSearch"

DoCmd.OpenForm "frm_BaptismSurnameSearchResults", acNormal, "", "", , acNormal

End Sub
[/CODE]
the form "frm_BaptismSurnameSearchResults" is based on the query below

SELECT tbl_Baptism.BaptismID, tbl_Place.Place, tbl_Baptism.DateOfBaptism, tbl_Baptism.YearOfBaptism, tbl_Baptism.ChildsName, tbl_Baptism.Surname, tbl_Baptism.FullDateOfBaptism
FROM tbl_Place INNER JOIN tbl_Baptism ON tbl_Place.ID = tbl_Baptism.[fk_PlaceId]
WHERE (((tbl_Baptism.Surname) Like [TempVars]![varSurname] & "*"))
ORDER BY tbl_Baptism.FullDateOfBaptism;

Ok now I understand the logic that I use the Dcount () to count the records in the recordset created by the above query if its 0 show a message box else open the form "frm_BaptismSurnameSearchResults".

But when is this recordset created? Is it before the form is opened or after the form is opened.

MY problem is I've experimented with various Dcount implementations but they dont seem to work and I wonder if I'm calling it in the correct location?

I've tried using the onload event for the form "frm_BaptismSurnameSearchResults" but again I failed. But I'm not sure if I'm putting the code in the correct location.

Sorry to be a nuisance

cheers

Ian

[orange]

Ian,

Do you have a sample database that you can post?
Something with some records for most tables.
Readers can set up some code/tests to highlight/assist on various points.

[Micron]

Not sure who you're asking.
Again, all you need is this: DoCmd.OpenForm "frm_BaptismSurnameSearchResults"

Ok now I understand the logic that I use the Dcount () to count the records in the recordset created by the above query if its 0 show a message box else open the form "frm_BaptismSurnameSearchResults".

This I don't agree with because your form is based on a query that either returns records or not. If not, the form recordset count is zero. To invoke the DCount function against your query would require an action on it when a recordset of it is already open, or before you try to open that form. No disrespect intended to anyone, but neither makes sense to me. Perhaps I'm missing something about the problem or suggestion.

My suggestion was to put If Me.Recordset.RecordCount = 0 Then in your form open event. I know that works.

[Jen0dorf]

Hi

attached is a copy of the database - cut down

if you open the year search and type in 1695 the form opens and a list is desplayed.

if you opne the form and type in 1887 then the form opens and no records are displayed as there are none.

What I would like is to have an else procedure

so that if the record count is 0 then open form "frm_noresults" if the record count is 1

then open the form "frm_BaptismSearchResults!

Thanks one and all,

cheers

Ianparish_records.zip

[Jen0dorf]

HI

thanks for the suggestion, and it's clearly me being less than clever ;-)

I did try your suggestions and it worked and displayed the message box but it also displayed the message box when there were records to show.

So clealry I have missed something and no listed it on the forum.

I have just posted a cut down copy of the database so that kind people can tell me where I've gone wrong

cheers

Ian

[pbaldy]

The suggestion to use DCount() was not from within the form, but before trying to open it:

If DCount("*", "QueryName") > 0 Then
  DoCmd.OpenForm...
Else
  MsgBox "No records"
End If

[Micron]

This is what I did.
In your button search click

Private Sub btn_Search_Click()
On Error GoTo errHandler
TempVars.Add "varYear", Me.txtYearOfBaptism.Value
DoCmd.Close acForm, "frm_BaptismYearSearch"
DoCmd.OpenForm "frm_BaptismYearSearchResults"

errHandler:
If Err.Number = 2501 Then 
  Exit Sub
Else msgbox "Error " & Err.Number & ": " & Err.Description
End If
End Sub

In your search result form

Private Sub Form_Open(Cancel As Integer)
If Me.Recordset.RecordCount = 0 Then
    MsgBox "No records exist for that year."
    Cancel = True
End If
End Sub

Not necessarily better, just the way I'd do it.