Code Stops Due to No Values In Query

A

Ange Kappas

Hi All,
I have a code which runs ok, when there is a value in my
QUERY called "qryDEPARTURES" but when the QUERY has no value on certain days
it stops on the line below:

If Myset5![STATUS] = "IN" Then

how can I make it continue when the query returns no values.

The code is:

Private Sub Toggle8_Click()
Dim PoseidonHotelProgram As Database
Dim rsta As DAO.Recordset
Dim frm As Form
Dim Msg, Style, Title, Response, MyString

Set db1 = CurrentDb()
Set Myset5 = db1.OpenRecordset("qryDEPARTURES")

If Myset5![STATUS] = "IN" Then

Msg = "There Are Still Departures as 'IN'"
Style = vbOK + vbInformation + vbDefaultButton1 ' Define buttons.
Title = "DEPARTURES PENDING" ' Define title.
Response = MsgBox(Msg, Style, Title)
DoCmd.OpenForm "DEPARTURES", acNormal

Else


Msg = "CONFIRM CLOSE DAY YES to Continue NO to Change/Edit Transaction ?"
' Define message.
Style = vbYesNo + vbInformation + vbDefaultButton1 ' Define buttons.
Title = "FINALIZE CLOSE DAY" ' Define title.

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.


MyString = "Yes" ' Perform some action.

Set Myset = db1.OpenRecordset("TODAY CHARGES")
Set Myset2 = db1.OpenRecordset("RESPEL ALL CHARGES")
Set Myset4 = db1.OpenRecordset("RUNDATE")


With Myset
If Not .EOF Then
.MoveFirst
End If
Do While Not .EOF
Myset2.AddNew
Myset2![Date] = Myset![Date]
Myset2![PELNMB] = Myset![PELNMB]
Myset2![RESNO] = Myset![RESNO]
Myset2![RESNAME] = Myset![RESNAME]
Myset2![COMPANY] = Myset![COMPANY]
Myset2![PRICELIST] = Myset![PRICELIST]
Myset2![HOTELAPT] = Myset![HOTELAPT]
Myset2![ROOMNO] = Myset![ROOMNO]
Myset2![ROOMTYPE] = Myset![ROOMTYPE]
Myset2![BASIS] = Myset![BASIS]
Myset2![ARRIVAL] = Myset![ARRIVAL]
Myset2![DAYS] = Myset![DAYS]
Myset2![DEPARTURE] = Myset![DEPARTURE]
Myset2![SURNAME] = Myset![SURNAME]
Myset2![NAME] = Myset![NAME]
Myset2![DAILY CHARGE] = IIf(Myset![PRICELIST] = "Z", Myset![DAILY CHARGE],
Myset![Price])
Myset2.Update
..MoveNext
Loop
End With
Myset2.Close


Myset4.MoveLast
Myset4.Delete
Myset4.AddNew
Myset4![Date] = Me![NEW DATE]
Myset4.Update
Myset4.Close
DoCmd.OpenQuery "UPDATE STATUS RESPEL ALL CHARGES"
DoCmd.OpenQuery "UPDATE STATUS RESERVATIONS"
Else


MyString = "No" ' Perform some action.
DoCmd.OpenForm "MAIN MENU"


End If
End If
End Sub

Any help much appreciated
Ange
 
D

Douglas J. Steele

To know whether a recordset returned any values, check its EOF property:

Set Myset5 = db1.OpenRecordset("qryDEPARTURES")

If Myset5.EOF = True Then
' the recordset returned nothing
Else
If Myset5![STATUS] = "IN" Then


To be completely sure, you could also check its BOF property:

If Myset5.BOF = True And Myset5.EOF = True Then
 
A

Ange Kappas

Hi Doug,
I have included your line, but the code stops before the
line:

Msg = "CONFIRM CLOSE DAY YES to Continue NO to Change/Edit Transaction ?"
' Define message.

without continuing to execute the code after that. It does not show a debug
problem, it just executes and stops.

Can you suggest what I should add so it can continue.

Thanks
Ange







Private Sub Toggle8_Click()
Dim PoseidonHotelProgram As Database
Dim rsta As DAO.Recordset
Dim frm As Form
Dim Msg, Style, Title, Response, MyString

Set db1 = CurrentDb()
Set Myset5 = db1.OpenRecordset("qryDEPARTURES")

If Myset5.BOF = True And Myset5.EOF = True Then

Else

If Myset5![STATUS] = "IN" Then



Msg = "There Are Still Departures as 'IN'"
Style = vbOK + vbInformation + vbDefaultButton1 ' Define buttons.
Title = "DEPARTURES PENDING" ' Define title.
Response = MsgBox(Msg, Style, Title)
DoCmd.OpenForm "DEPARTURES", acNormal

Else


Msg = "CONFIRM CLOSE DAY YES to Continue NO to Change/Edit Transaction ?"
' Define message.
Style = vbYesNo + vbInformation + vbDefaultButton1 ' Define buttons.
Title = "FINALIZE CLOSE DAY" ' Define title.

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.


MyString = "Yes" ' Perform some action.

Set Myset = db1.OpenRecordset("TODAY CHARGES")
Set Myset2 = db1.OpenRecordset("RESPEL ALL CHARGES")
Set Myset4 = db1.OpenRecordset("RUNDATE")


With Myset
If Not .EOF Then
.MoveFirst
End If
Do While Not .EOF
Myset2.AddNew
Myset2![Date] = Myset![Date]
Myset2![PELNMB] = Myset![PELNMB]
Myset2![RESNO] = Myset![RESNO]
Myset2![RESNAME] = Myset![RESNAME]
Myset2![COMPANY] = Myset![COMPANY]
Myset2![PRICELIST] = Myset![PRICELIST]
Myset2![HOTELAPT] = Myset![HOTELAPT]
Myset2![ROOMNO] = Myset![ROOMNO]
Myset2![ROOMTYPE] = Myset![ROOMTYPE]
Myset2![BASIS] = Myset![BASIS]
Myset2![ARRIVAL] = Myset![ARRIVAL]
Myset2![DAYS] = Myset![DAYS]
Myset2![DEPARTURE] = Myset![DEPARTURE]
Myset2![SURNAME] = Myset![SURNAME]
Myset2![NAME] = Myset![NAME]
Myset2![DAILY CHARGE] = IIf(Myset![PRICELIST] = "Z", Myset![DAILY CHARGE],
Myset![Price])
Myset2.Update
..MoveNext
Loop
End With
Myset2.Close


Myset4.MoveLast
Myset4.Delete
Myset4.AddNew
Myset4![Date] = Me![NEW DATE]
Myset4.Update
Myset4.Close
DoCmd.OpenQuery "UPDATE STATUS RESPEL ALL CHARGES"
DoCmd.OpenQuery "UPDATE STATUS RESERVATIONS"
Else


MyString = "No" ' Perform some action.
DoCmd.OpenForm "MAIN MENU"


End If
End If
End If
End Sub


Douglas J. Steele said:
To know whether a recordset returned any values, check its EOF property:

Set Myset5 = db1.OpenRecordset("qryDEPARTURES")

If Myset5.EOF = True Then
' the recordset returned nothing
Else
If Myset5![STATUS] = "IN" Then


To be completely sure, you could also check its BOF property:

If Myset5.BOF = True And Myset5.EOF = True Then



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ange Kappas said:
Hi All,
I have a code which runs ok, when there is a value in my
QUERY called "qryDEPARTURES" but when the QUERY has no value on certain
days it stops on the line below:

If Myset5![STATUS] = "IN" Then

how can I make it continue when the query returns no values.

The code is:

Private Sub Toggle8_Click()
Dim PoseidonHotelProgram As Database
Dim rsta As DAO.Recordset
Dim frm As Form
Dim Msg, Style, Title, Response, MyString

Set db1 = CurrentDb()
Set Myset5 = db1.OpenRecordset("qryDEPARTURES")

If Myset5![STATUS] = "IN" Then

Msg = "There Are Still Departures as 'IN'"
Style = vbOK + vbInformation + vbDefaultButton1 ' Define buttons.
Title = "DEPARTURES PENDING" ' Define title.
Response = MsgBox(Msg, Style, Title)
DoCmd.OpenForm "DEPARTURES", acNormal

Else


Msg = "CONFIRM CLOSE DAY YES to Continue NO to Change/Edit Transaction ?"
' Define message.
Style = vbYesNo + vbInformation + vbDefaultButton1 ' Define buttons.
Title = "FINALIZE CLOSE DAY" ' Define title.

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.


MyString = "Yes" ' Perform some action.

Set Myset = db1.OpenRecordset("TODAY CHARGES")
Set Myset2 = db1.OpenRecordset("RESPEL ALL CHARGES")
Set Myset4 = db1.OpenRecordset("RUNDATE")


With Myset
If Not .EOF Then
.MoveFirst
End If
Do While Not .EOF
Myset2.AddNew
Myset2![Date] = Myset![Date]
Myset2![PELNMB] = Myset![PELNMB]
Myset2![RESNO] = Myset![RESNO]
Myset2![RESNAME] = Myset![RESNAME]
Myset2![COMPANY] = Myset![COMPANY]
Myset2![PRICELIST] = Myset![PRICELIST]
Myset2![HOTELAPT] = Myset![HOTELAPT]
Myset2![ROOMNO] = Myset![ROOMNO]
Myset2![ROOMTYPE] = Myset![ROOMTYPE]
Myset2![BASIS] = Myset![BASIS]
Myset2![ARRIVAL] = Myset![ARRIVAL]
Myset2![DAYS] = Myset![DAYS]
Myset2![DEPARTURE] = Myset![DEPARTURE]
Myset2![SURNAME] = Myset![SURNAME]
Myset2![NAME] = Myset![NAME]
Myset2![DAILY CHARGE] = IIf(Myset![PRICELIST] = "Z", Myset![DAILY
CHARGE], Myset![Price])
Myset2.Update
.MoveNext
Loop
End With
Myset2.Close


Myset4.MoveLast
Myset4.Delete
Myset4.AddNew
Myset4![Date] = Me![NEW DATE]
Myset4.Update
Myset4.Close
DoCmd.OpenQuery "UPDATE STATUS RESPEL ALL CHARGES"
DoCmd.OpenQuery "UPDATE STATUS RESERVATIONS"
Else


MyString = "No" ' Perform some action.
DoCmd.OpenForm "MAIN MENU"


End If
End If
End Sub

Any help much appreciated
Ange
Click to expand...
Click to expand...
 
D

Douglas J. Steele

What does your code look like now?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ange Kappas said:
Hi Doug,
I have included your line, but the code stops before
the line:

Msg = "CONFIRM CLOSE DAY YES to Continue NO to Change/Edit Transaction ?"
' Define message.

without continuing to execute the code after that. It does not show a
debug problem, it just executes and stops.

Can you suggest what I should add so it can continue.

Thanks
Ange







Private Sub Toggle8_Click()
Dim PoseidonHotelProgram As Database
Dim rsta As DAO.Recordset
Dim frm As Form
Dim Msg, Style, Title, Response, MyString

Set db1 = CurrentDb()
Set Myset5 = db1.OpenRecordset("qryDEPARTURES")

If Myset5.BOF = True And Myset5.EOF = True Then

Else

If Myset5![STATUS] = "IN" Then



Msg = "There Are Still Departures as 'IN'"
Style = vbOK + vbInformation + vbDefaultButton1 ' Define buttons.
Title = "DEPARTURES PENDING" ' Define title.
Response = MsgBox(Msg, Style, Title)
DoCmd.OpenForm "DEPARTURES", acNormal

Else


Msg = "CONFIRM CLOSE DAY YES to Continue NO to Change/Edit Transaction ?"
' Define message.
Style = vbYesNo + vbInformation + vbDefaultButton1 ' Define buttons.
Title = "FINALIZE CLOSE DAY" ' Define title.

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.


MyString = "Yes" ' Perform some action.

Set Myset = db1.OpenRecordset("TODAY CHARGES")
Set Myset2 = db1.OpenRecordset("RESPEL ALL CHARGES")
Set Myset4 = db1.OpenRecordset("RUNDATE")


With Myset
If Not .EOF Then
.MoveFirst
End If
Do While Not .EOF
Myset2.AddNew
Myset2![Date] = Myset![Date]
Myset2![PELNMB] = Myset![PELNMB]
Myset2![RESNO] = Myset![RESNO]
Myset2![RESNAME] = Myset![RESNAME]
Myset2![COMPANY] = Myset![COMPANY]
Myset2![PRICELIST] = Myset![PRICELIST]
Myset2![HOTELAPT] = Myset![HOTELAPT]
Myset2![ROOMNO] = Myset![ROOMNO]
Myset2![ROOMTYPE] = Myset![ROOMTYPE]
Myset2![BASIS] = Myset![BASIS]
Myset2![ARRIVAL] = Myset![ARRIVAL]
Myset2![DAYS] = Myset![DAYS]
Myset2![DEPARTURE] = Myset![DEPARTURE]
Myset2![SURNAME] = Myset![SURNAME]
Myset2![NAME] = Myset![NAME]
Myset2![DAILY CHARGE] = IIf(Myset![PRICELIST] = "Z", Myset![DAILY CHARGE],
Myset![Price])
Myset2.Update
.MoveNext
Loop
End With
Myset2.Close


Myset4.MoveLast
Myset4.Delete
Myset4.AddNew
Myset4![Date] = Me![NEW DATE]
Myset4.Update
Myset4.Close
DoCmd.OpenQuery "UPDATE STATUS RESPEL ALL CHARGES"
DoCmd.OpenQuery "UPDATE STATUS RESERVATIONS"
Else


MyString = "No" ' Perform some action.
DoCmd.OpenForm "MAIN MENU"


End If
End If
End If
End Sub


Douglas J. Steele said:
To know whether a recordset returned any values, check its EOF property:

Set Myset5 = db1.OpenRecordset("qryDEPARTURES")

If Myset5.EOF = True Then
' the recordset returned nothing
Else
If Myset5![STATUS] = "IN" Then


To be completely sure, you could also check its BOF property:

If Myset5.BOF = True And Myset5.EOF = True Then



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ange Kappas said:
Hi All,
I have a code which runs ok, when there is a value in my
QUERY called "qryDEPARTURES" but when the QUERY has no value on certain
days it stops on the line below:

If Myset5![STATUS] = "IN" Then

how can I make it continue when the query returns no values.

The code is:

Private Sub Toggle8_Click()
Dim PoseidonHotelProgram As Database
Dim rsta As DAO.Recordset
Dim frm As Form
Dim Msg, Style, Title, Response, MyString

Set db1 = CurrentDb()
Set Myset5 = db1.OpenRecordset("qryDEPARTURES")

If Myset5![STATUS] = "IN" Then

Msg = "There Are Still Departures as 'IN'"
Style = vbOK + vbInformation + vbDefaultButton1 ' Define buttons.
Title = "DEPARTURES PENDING" ' Define title.
Response = MsgBox(Msg, Style, Title)
DoCmd.OpenForm "DEPARTURES", acNormal

Else


Msg = "CONFIRM CLOSE DAY YES to Continue NO to Change/Edit Transaction
?" ' Define message.
Style = vbYesNo + vbInformation + vbDefaultButton1 ' Define buttons.
Title = "FINALIZE CLOSE DAY" ' Define title.

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.


MyString = "Yes" ' Perform some action.

Set Myset = db1.OpenRecordset("TODAY CHARGES")
Set Myset2 = db1.OpenRecordset("RESPEL ALL CHARGES")
Set Myset4 = db1.OpenRecordset("RUNDATE")


With Myset
If Not .EOF Then
.MoveFirst
End If
Do While Not .EOF
Myset2.AddNew
Myset2![Date] = Myset![Date]
Myset2![PELNMB] = Myset![PELNMB]
Myset2![RESNO] = Myset![RESNO]
Myset2![RESNAME] = Myset![RESNAME]
Myset2![COMPANY] = Myset![COMPANY]
Myset2![PRICELIST] = Myset![PRICELIST]
Myset2![HOTELAPT] = Myset![HOTELAPT]
Myset2![ROOMNO] = Myset![ROOMNO]
Myset2![ROOMTYPE] = Myset![ROOMTYPE]
Myset2![BASIS] = Myset![BASIS]
Myset2![ARRIVAL] = Myset![ARRIVAL]
Myset2![DAYS] = Myset![DAYS]
Myset2![DEPARTURE] = Myset![DEPARTURE]
Myset2![SURNAME] = Myset![SURNAME]
Myset2![NAME] = Myset![NAME]
Myset2![DAILY CHARGE] = IIf(Myset![PRICELIST] = "Z", Myset![DAILY
CHARGE], Myset![Price])
Myset2.Update
.MoveNext
Loop
End With
Myset2.Close


Myset4.MoveLast
Myset4.Delete
Myset4.AddNew
Myset4![Date] = Me![NEW DATE]
Myset4.Update
Myset4.Close
DoCmd.OpenQuery "UPDATE STATUS RESPEL ALL CHARGES"
DoCmd.OpenQuery "UPDATE STATUS RESERVATIONS"
Else


MyString = "No" ' Perform some action.
DoCmd.OpenForm "MAIN MENU"


End If
End If
End Sub

Any help much appreciated
Ange
Click to expand...
Click to expand...
Click to expand...
 
A

Ange Kappas

Hi Doug,
My code is like below, but I am attaching it again
below:


Private Sub Toggle8_Click()
Dim PoseidonHotelProgram As Database
Dim rsta As DAO.Recordset
Dim frm As Form
Dim Msg, Style, Title, Response, MyString

Set db1 = CurrentDb()
Set Myset5 = db1.OpenRecordset("qryDEPARTURES")


If Myset5.BOF = True And Myset5.EOF = True Then

Else

If Myset5![STATUS] = "IN" Then



Msg = "There Are Still Departures as 'IN'"
Style = vbOK + vbInformation + vbDefaultButton1 ' Define buttons.
Title = "DEPARTURES PENDING" ' Define title.
Response = MsgBox(Msg, Style, Title)
DoCmd.OpenForm "DEPARTURES", acNormal

Else


Msg = "CONFIRM CLOSE DAY YES to Continue NO to Change/Edit Transaction ?"
' Define message.
Style = vbYesNo + vbInformation + vbDefaultButton1 ' Define buttons.
Title = "FINALIZE CLOSE DAY" ' Define title.

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.


MyString = "Yes" ' Perform some action.

Set Myset = db1.OpenRecordset("TODAY CHARGES")
Set Myset2 = db1.OpenRecordset("RESPEL ALL CHARGES")
Set Myset4 = db1.OpenRecordset("RUNDATE")


With Myset
If Not .EOF Then
.MoveFirst
End If
Do While Not .EOF
Myset2.AddNew
Myset2![Date] = Myset![Date]
Myset2![PELNMB] = Myset![PELNMB]
Myset2![RESNO] = Myset![RESNO]
Myset2![RESNAME] = Myset![RESNAME]
Myset2![COMPANY] = Myset![COMPANY]
Myset2![PRICELIST] = Myset![PRICELIST]
Myset2![HOTELAPT] = Myset![HOTELAPT]
Myset2![ROOMNO] = Myset![ROOMNO]
Myset2![ROOMTYPE] = Myset![ROOMTYPE]
Myset2![BASIS] = Myset![BASIS]
Myset2![ARRIVAL] = Myset![ARRIVAL]
Myset2![DAYS] = Myset![DAYS]
Myset2![DEPARTURE] = Myset![DEPARTURE]
Myset2![SURNAME] = Myset![SURNAME]
Myset2![NAME] = Myset![NAME]
Myset2![DAILY CHARGE] = IIf(Myset![PRICELIST] = "Z", Myset![DAILY CHARGE],
Myset![Price])
Myset2.Update
..MoveNext
Loop
End With
Myset2.Close


Myset4.MoveLast
Myset4.Delete
Myset4.AddNew
Myset4![Date] = Me![NEW DATE]
Myset4.Update
Myset4.Close
DoCmd.OpenQuery "UPDATE STATUS RESPEL ALL CHARGES"
DoCmd.OpenQuery "UPDATE STATUS RESERVATIONS"
Else


MyString = "No" ' Perform some action.
DoCmd.OpenForm "MAIN MENU"








End If
End If
End If
End Sub



Ange



Douglas J. Steele said:
What does your code look like now?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ange Kappas said:
Hi Doug,
I have included your line, but the code stops before
the line:

Msg = "CONFIRM CLOSE DAY YES to Continue NO to Change/Edit Transaction ?"
' Define message.

without continuing to execute the code after that. It does not show a
debug problem, it just executes and stops.

Can you suggest what I should add so it can continue.

Thanks
Ange







Private Sub Toggle8_Click()
Dim PoseidonHotelProgram As Database
Dim rsta As DAO.Recordset
Dim frm As Form
Dim Msg, Style, Title, Response, MyString

Set db1 = CurrentDb()
Set Myset5 = db1.OpenRecordset("qryDEPARTURES")

If Myset5.BOF = True And Myset5.EOF = True Then

Else

If Myset5![STATUS] = "IN" Then



Msg = "There Are Still Departures as 'IN'"
Style = vbOK + vbInformation + vbDefaultButton1 ' Define buttons.
Title = "DEPARTURES PENDING" ' Define title.
Response = MsgBox(Msg, Style, Title)
DoCmd.OpenForm "DEPARTURES", acNormal

Else


Msg = "CONFIRM CLOSE DAY YES to Continue NO to Change/Edit Transaction ?"
' Define message.
Style = vbYesNo + vbInformation + vbDefaultButton1 ' Define buttons.
Title = "FINALIZE CLOSE DAY" ' Define title.

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.


MyString = "Yes" ' Perform some action.

Set Myset = db1.OpenRecordset("TODAY CHARGES")
Set Myset2 = db1.OpenRecordset("RESPEL ALL CHARGES")
Set Myset4 = db1.OpenRecordset("RUNDATE")


With Myset
If Not .EOF Then
.MoveFirst
End If
Do While Not .EOF
Myset2.AddNew
Myset2![Date] = Myset![Date]
Myset2![PELNMB] = Myset![PELNMB]
Myset2![RESNO] = Myset![RESNO]
Myset2![RESNAME] = Myset![RESNAME]
Myset2![COMPANY] = Myset![COMPANY]
Myset2![PRICELIST] = Myset![PRICELIST]
Myset2![HOTELAPT] = Myset![HOTELAPT]
Myset2![ROOMNO] = Myset![ROOMNO]
Myset2![ROOMTYPE] = Myset![ROOMTYPE]
Myset2![BASIS] = Myset![BASIS]
Myset2![ARRIVAL] = Myset![ARRIVAL]
Myset2![DAYS] = Myset![DAYS]
Myset2![DEPARTURE] = Myset![DEPARTURE]
Myset2![SURNAME] = Myset![SURNAME]
Myset2![NAME] = Myset![NAME]
Myset2![DAILY CHARGE] = IIf(Myset![PRICELIST] = "Z", Myset![DAILY
CHARGE], Myset![Price])
Myset2.Update
.MoveNext
Loop
End With
Myset2.Close


Myset4.MoveLast
Myset4.Delete
Myset4.AddNew
Myset4![Date] = Me![NEW DATE]
Myset4.Update
Myset4.Close
DoCmd.OpenQuery "UPDATE STATUS RESPEL ALL CHARGES"
DoCmd.OpenQuery "UPDATE STATUS RESERVATIONS"
Else


MyString = "No" ' Perform some action.
DoCmd.OpenForm "MAIN MENU"


End If
End If
End If
End Sub


Douglas J. Steele said:
To know whether a recordset returned any values, check its EOF property:

Set Myset5 = db1.OpenRecordset("qryDEPARTURES")

If Myset5.EOF = True Then
' the recordset returned nothing
Else
If Myset5![STATUS] = "IN" Then


To be completely sure, you could also check its BOF property:

If Myset5.BOF = True And Myset5.EOF = True Then



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi All,
I have a code which runs ok, when there is a value in my
QUERY called "qryDEPARTURES" but when the QUERY has no value on certain
days it stops on the line below:

If Myset5![STATUS] = "IN" Then

how can I make it continue when the query returns no values.

The code is:

Private Sub Toggle8_Click()
Dim PoseidonHotelProgram As Database
Dim rsta As DAO.Recordset
Dim frm As Form
Dim Msg, Style, Title, Response, MyString

Set db1 = CurrentDb()
Set Myset5 = db1.OpenRecordset("qryDEPARTURES")

If Myset5![STATUS] = "IN" Then

Msg = "There Are Still Departures as 'IN'"
Style = vbOK + vbInformation + vbDefaultButton1 ' Define buttons.
Title = "DEPARTURES PENDING" ' Define title.
Response = MsgBox(Msg, Style, Title)
DoCmd.OpenForm "DEPARTURES", acNormal

Else


Msg = "CONFIRM CLOSE DAY YES to Continue NO to Change/Edit Transaction
?" ' Define message.
Style = vbYesNo + vbInformation + vbDefaultButton1 ' Define buttons.
Title = "FINALIZE CLOSE DAY" ' Define title.

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.


MyString = "Yes" ' Perform some action.

Set Myset = db1.OpenRecordset("TODAY CHARGES")
Set Myset2 = db1.OpenRecordset("RESPEL ALL CHARGES")
Set Myset4 = db1.OpenRecordset("RUNDATE")


With Myset
If Not .EOF Then
.MoveFirst
End If
Do While Not .EOF
Myset2.AddNew
Myset2![Date] = Myset![Date]
Myset2![PELNMB] = Myset![PELNMB]
Myset2![RESNO] = Myset![RESNO]
Myset2![RESNAME] = Myset![RESNAME]
Myset2![COMPANY] = Myset![COMPANY]
Myset2![PRICELIST] = Myset![PRICELIST]
Myset2![HOTELAPT] = Myset![HOTELAPT]
Myset2![ROOMNO] = Myset![ROOMNO]
Myset2![ROOMTYPE] = Myset![ROOMTYPE]
Myset2![BASIS] = Myset![BASIS]
Myset2![ARRIVAL] = Myset![ARRIVAL]
Myset2![DAYS] = Myset![DAYS]
Myset2![DEPARTURE] = Myset![DEPARTURE]
Myset2![SURNAME] = Myset![SURNAME]
Myset2![NAME] = Myset![NAME]
Myset2![DAILY CHARGE] = IIf(Myset![PRICELIST] = "Z", Myset![DAILY
CHARGE], Myset![Price])
Myset2.Update
.MoveNext
Loop
End With
Myset2.Close


Myset4.MoveLast
Myset4.Delete
Myset4.AddNew
Myset4![Date] = Me![NEW DATE]
Myset4.Update
Myset4.Close
DoCmd.OpenQuery "UPDATE STATUS RESPEL ALL CHARGES"
DoCmd.OpenQuery "UPDATE STATUS RESERVATIONS"
Else


MyString = "No" ' Perform some action.
DoCmd.OpenForm "MAIN MENU"


End If
End If
End Sub

Any help much appreciated
Ange
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Afraid nothing jumps out as looking incorrect.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ange Kappas said:
Hi Doug,
My code is like below, but I am attaching it again
below:


Private Sub Toggle8_Click()
Dim PoseidonHotelProgram As Database
Dim rsta As DAO.Recordset
Dim frm As Form
Dim Msg, Style, Title, Response, MyString

Set db1 = CurrentDb()
Set Myset5 = db1.OpenRecordset("qryDEPARTURES")


If Myset5.BOF = True And Myset5.EOF = True Then

Else

If Myset5![STATUS] = "IN" Then



Msg = "There Are Still Departures as 'IN'"
Style = vbOK + vbInformation + vbDefaultButton1 ' Define buttons.
Title = "DEPARTURES PENDING" ' Define title.
Response = MsgBox(Msg, Style, Title)
DoCmd.OpenForm "DEPARTURES", acNormal

Else


Msg = "CONFIRM CLOSE DAY YES to Continue NO to Change/Edit Transaction ?"
' Define message.
Style = vbYesNo + vbInformation + vbDefaultButton1 ' Define buttons.
Title = "FINALIZE CLOSE DAY" ' Define title.

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.


MyString = "Yes" ' Perform some action.

Set Myset = db1.OpenRecordset("TODAY CHARGES")
Set Myset2 = db1.OpenRecordset("RESPEL ALL CHARGES")
Set Myset4 = db1.OpenRecordset("RUNDATE")


With Myset
If Not .EOF Then
.MoveFirst
End If
Do While Not .EOF
Myset2.AddNew
Myset2![Date] = Myset![Date]
Myset2![PELNMB] = Myset![PELNMB]
Myset2![RESNO] = Myset![RESNO]
Myset2![RESNAME] = Myset![RESNAME]
Myset2![COMPANY] = Myset![COMPANY]
Myset2![PRICELIST] = Myset![PRICELIST]
Myset2![HOTELAPT] = Myset![HOTELAPT]
Myset2![ROOMNO] = Myset![ROOMNO]
Myset2![ROOMTYPE] = Myset![ROOMTYPE]
Myset2![BASIS] = Myset![BASIS]
Myset2![ARRIVAL] = Myset![ARRIVAL]
Myset2![DAYS] = Myset![DAYS]
Myset2![DEPARTURE] = Myset![DEPARTURE]
Myset2![SURNAME] = Myset![SURNAME]
Myset2![NAME] = Myset![NAME]
Myset2![DAILY CHARGE] = IIf(Myset![PRICELIST] = "Z", Myset![DAILY CHARGE],
Myset![Price])
Myset2.Update
.MoveNext
Loop
End With
Myset2.Close


Myset4.MoveLast
Myset4.Delete
Myset4.AddNew
Myset4![Date] = Me![NEW DATE]
Myset4.Update
Myset4.Close
DoCmd.OpenQuery "UPDATE STATUS RESPEL ALL CHARGES"
DoCmd.OpenQuery "UPDATE STATUS RESERVATIONS"
Else


MyString = "No" ' Perform some action.
DoCmd.OpenForm "MAIN MENU"








End If
End If
End If
End Sub



Ange



Douglas J. Steele said:
What does your code look like now?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ange Kappas said:
Hi Doug,
I have included your line, but the code stops before
the line:

Msg = "CONFIRM CLOSE DAY YES to Continue NO to Change/Edit Transaction
?" ' Define message.

without continuing to execute the code after that. It does not show a
debug problem, it just executes and stops.

Can you suggest what I should add so it can continue.

Thanks
Ange







Private Sub Toggle8_Click()
Dim PoseidonHotelProgram As Database
Dim rsta As DAO.Recordset
Dim frm As Form
Dim Msg, Style, Title, Response, MyString

Set db1 = CurrentDb()
Set Myset5 = db1.OpenRecordset("qryDEPARTURES")

If Myset5.BOF = True And Myset5.EOF = True Then

Else

If Myset5![STATUS] = "IN" Then



Msg = "There Are Still Departures as 'IN'"
Style = vbOK + vbInformation + vbDefaultButton1 ' Define buttons.
Title = "DEPARTURES PENDING" ' Define title.
Response = MsgBox(Msg, Style, Title)
DoCmd.OpenForm "DEPARTURES", acNormal

Else


Msg = "CONFIRM CLOSE DAY YES to Continue NO to Change/Edit Transaction
?" ' Define message.
Style = vbYesNo + vbInformation + vbDefaultButton1 ' Define buttons.
Title = "FINALIZE CLOSE DAY" ' Define title.

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.


MyString = "Yes" ' Perform some action.

Set Myset = db1.OpenRecordset("TODAY CHARGES")
Set Myset2 = db1.OpenRecordset("RESPEL ALL CHARGES")
Set Myset4 = db1.OpenRecordset("RUNDATE")


With Myset
If Not .EOF Then
.MoveFirst
End If
Do While Not .EOF
Myset2.AddNew
Myset2![Date] = Myset![Date]
Myset2![PELNMB] = Myset![PELNMB]
Myset2![RESNO] = Myset![RESNO]
Myset2![RESNAME] = Myset![RESNAME]
Myset2![COMPANY] = Myset![COMPANY]
Myset2![PRICELIST] = Myset![PRICELIST]
Myset2![HOTELAPT] = Myset![HOTELAPT]
Myset2![ROOMNO] = Myset![ROOMNO]
Myset2![ROOMTYPE] = Myset![ROOMTYPE]
Myset2![BASIS] = Myset![BASIS]
Myset2![ARRIVAL] = Myset![ARRIVAL]
Myset2![DAYS] = Myset![DAYS]
Myset2![DEPARTURE] = Myset![DEPARTURE]
Myset2![SURNAME] = Myset![SURNAME]
Myset2![NAME] = Myset![NAME]
Myset2![DAILY CHARGE] = IIf(Myset![PRICELIST] = "Z", Myset![DAILY
CHARGE], Myset![Price])
Myset2.Update
.MoveNext
Loop
End With
Myset2.Close


Myset4.MoveLast
Myset4.Delete
Myset4.AddNew
Myset4![Date] = Me![NEW DATE]
Myset4.Update
Myset4.Close
DoCmd.OpenQuery "UPDATE STATUS RESPEL ALL CHARGES"
DoCmd.OpenQuery "UPDATE STATUS RESERVATIONS"
Else


MyString = "No" ' Perform some action.
DoCmd.OpenForm "MAIN MENU"


End If
End If
End If
End Sub


To know whether a recordset returned any values, check its EOF
property:

Set Myset5 = db1.OpenRecordset("qryDEPARTURES")

If Myset5.EOF = True Then
' the recordset returned nothing
Else
If Myset5![STATUS] = "IN" Then


To be completely sure, you could also check its BOF property:

If Myset5.BOF = True And Myset5.EOF = True Then



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi All,
I have a code which runs ok, when there is a value in
my QUERY called "qryDEPARTURES" but when the QUERY has no value on
certain days it stops on the line below:

If Myset5![STATUS] = "IN" Then

how can I make it continue when the query returns no values.

The code is:

Private Sub Toggle8_Click()
Dim PoseidonHotelProgram As Database
Dim rsta As DAO.Recordset
Dim frm As Form
Dim Msg, Style, Title, Response, MyString

Set db1 = CurrentDb()
Set Myset5 = db1.OpenRecordset("qryDEPARTURES")

If Myset5![STATUS] = "IN" Then

Msg = "There Are Still Departures as 'IN'"
Style = vbOK + vbInformation + vbDefaultButton1 ' Define buttons.
Title = "DEPARTURES PENDING" ' Define title.
Response = MsgBox(Msg, Style, Title)
DoCmd.OpenForm "DEPARTURES", acNormal

Else


Msg = "CONFIRM CLOSE DAY YES to Continue NO to Change/Edit Transaction
?" ' Define message.
Style = vbYesNo + vbInformation + vbDefaultButton1 ' Define buttons.
Title = "FINALIZE CLOSE DAY" ' Define title.

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.


MyString = "Yes" ' Perform some action.

Set Myset = db1.OpenRecordset("TODAY CHARGES")
Set Myset2 = db1.OpenRecordset("RESPEL ALL CHARGES")
Set Myset4 = db1.OpenRecordset("RUNDATE")


With Myset
If Not .EOF Then
.MoveFirst
End If
Do While Not .EOF
Myset2.AddNew
Myset2![Date] = Myset![Date]
Myset2![PELNMB] = Myset![PELNMB]
Myset2![RESNO] = Myset![RESNO]
Myset2![RESNAME] = Myset![RESNAME]
Myset2![COMPANY] = Myset![COMPANY]
Myset2![PRICELIST] = Myset![PRICELIST]
Myset2![HOTELAPT] = Myset![HOTELAPT]
Myset2![ROOMNO] = Myset![ROOMNO]
Myset2![ROOMTYPE] = Myset![ROOMTYPE]
Myset2![BASIS] = Myset![BASIS]
Myset2![ARRIVAL] = Myset![ARRIVAL]
Myset2![DAYS] = Myset![DAYS]
Myset2![DEPARTURE] = Myset![DEPARTURE]
Myset2![SURNAME] = Myset![SURNAME]
Myset2![NAME] = Myset![NAME]
Myset2![DAILY CHARGE] = IIf(Myset![PRICELIST] = "Z", Myset![DAILY
CHARGE], Myset![Price])
Myset2.Update
.MoveNext
Loop
End With
Myset2.Close


Myset4.MoveLast
Myset4.Delete
Myset4.AddNew
Myset4![Date] = Me![NEW DATE]
Myset4.Update
Myset4.Close
DoCmd.OpenQuery "UPDATE STATUS RESPEL ALL CHARGES"
DoCmd.OpenQuery "UPDATE STATUS RESERVATIONS"
Else


MyString = "No" ' Perform some action.
DoCmd.OpenForm "MAIN MENU"


End If
End If
End Sub

Any help much appreciated
Ange
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Checking a Status Control from a Form Before Proceeding2 1
Copying Specific Records From A Field According to another Field By Code 2
Copying All Records from a Query to a Table 2
Problem with Code Because I already have an Open Query 10
Date Difference In Query 2

Top