Code Stops Due to No Values In Query

  • Thread starter Thread starter Ange Kappas
  • Start date Start date
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
 
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
 
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...
 
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...
 
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...
 
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...
 
Back
Top