Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 702
  • Last Modified:

Access 2010 update a continuous form if field value in ALL rows is equal to "Confirmed"

Hi,

I have a cmdButton on a continuous form that I want to use to update another table only if the field named "txtBookingStatus" in ALL rows is equal to "Confirmed".

Private Sub cmdCloseBooking_Click()
On Error Resume Next

If Me.txtBookingStatus = "Confirmed" Then
    MsgBox "You need to update the Booking Status for all customers before you can continue"
    Exit Sub
Else
    'UPDATE tblHASchedules.Status as "Closed"
        DoCmd.SetWarnings False
        DoCmd.RunSQL "UPDATE tblHASchedules SET tblHASchedules.Status = 'Closed' " & _
        "WHERE tblHASchedules.HASchedule_ID=" & Me.txtHASchedule_ID & ";"
        DoCmd.SetWarnings True
    'END UPDATE
   
     DoCmd.Close acForm, "frmGSDetails"
       
End If

End Sub
0
andrewpiconnect
Asked:
andrewpiconnect
  • 12
  • 7
  • 4
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
what is the name of field where txtBookingStatus is bound to?


        DoCmd.RunSQL "UPDATE tblHASchedules SET tblHASchedules.Status = 'Closed' " & _
        "WHERE tblHASchedules.HASchedule_ID=" & Me.txtHASchedule_ID & " and [BookingStatus]='Confirmed'"
0
 
IrogSintaCommented:
You can do a DCount of the table. for example:
If DCount("*","tblHASchedules","[BookingStatus]='Confirmed'")>0 Then

Open in new window

0
 
andrewpiconnectAuthor Commented:
I have a query that opens the form and the txtBookingStatus is bound to BookingStatus from that query. The default value of BookingStatus when the query is first run is "Confirmed".

I have a button on each row in the continuous form that updates that BookingStatus field to "Re-Confirmed" and then requeries the form.

The new cmdButton I wish to use is on the header of the form and its purpose is to update a separate table (tblHASchedules.Status) to "Closed" ONLY if txtBookingStatus in ALL rows in the continuous form is equal to "Re-Confirmed".

If the txtBookingStatus in any row is still equal to "Confirmed" ie it has not been updated then the error msg is to appear:
MsgBox "You need to update the Booking Status for all customers before you can continue"
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
IrogSintaCommented:
Use the query in the DCount:
If DCount("*","NameOfYourQuery","[BookingStatus]='Confirmed'")>0 Then

Open in new window

Also,use a Me.Refresh instead of Requery so that you don't lose your record position.
0
 
andrewpiconnectAuthor Commented:
I cant use the query in the DCount because that query will pull every record in the Bookings Table.

My continuous form only uses that query when it is opened but it is opened with a filter from the previous form ie:

'CODE FROM PREVIOUS FORM
Dim strLinkCriteria As String
    strLinkCriteria = "[HASchedule_ID]=" & Me.txtHASchedule_ID
    DoCmd.OpenForm "frmGSDetails", acNormal, , strLinkCriteria
0
 
andrewpiconnectAuthor Commented:
I only need to check the values of the BookingStatus fields that are opened in the Continuous Form but i cannot seem to get my code to run through each of these fields and check them
0
 
IrogSintaCommented:
Then use this:
If DCount("*","NameOfYourQuery","[HASchedule_ID]=" & Me.txtHASchedule_ID & " AND [BookingStatus]='Confirmed'")>0 Then

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
what is the recordsource of your continuous form?

is txtBookingstatus in the continuous form?

you may need recordsets to do this,
see simialr thread

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28002721.html#a38801100
0
 
andrewpiconnectAuthor Commented:
The recordsource is qryGSDetails (but this opened by a filter from the previous form Dim strLinkCriteria As String
strLinkCriteria = "[HASchedule_ID]=" & Me.txtHASchedule_ID
DoCmd.OpenForm "frmGSDetails", acNormal, , strLinkCriteria
)

Yes, txtBookingStatus is in the continuous form and is bound to the control source BookingStatus.

Have taken a look at the similar thread and still a little miffed by this
0
 
Rey Obrero (Capricorn1)Commented:
try this

with me.recordsetclone
      .movefirst
      do until .eof
      if me.txtBookingstatus="Re-Confirmed" then
          .edit
          ![Status]="Closed"
          .update
      end if
      .movenext
     loop

end with
0
 
andrewpiconnectAuthor Commented:
No luck.

I have placed the [Status] field in the header of the form alongside the cmdButton and it remains the same.

I also require an msgbox alert if any of the rows have not been updated to "Re-Confirmed" and still showing as "Confirmed" prior to clicking the cmdButton
0
 
Rey Obrero (Capricorn1)Commented:
what did you do?
0
 
andrewpiconnectAuthor Commented:
I pasted your code into the sub and added the "Status" field to the header of the form.

What i really need is something like this. But cant see how your code is going to fit.

If Me.txtBookingStatus = "Confirmed" Then
    MsgBox "You need to update the Booking Status for all customers before you can continue"
    Exit Sub
Else
    'UPDATE tblHASchedules.Status as "Closed"
        DoCmd.SetWarnings False
        DoCmd.RunSQL "UPDATE tblHASchedules SET tblHASchedules.Status = 'Closed' " & _
        "WHERE tblHASchedules.HASchedule_ID=" & Me.txtHASchedule_ID & ";"
        DoCmd.SetWarnings True
    'END UPDATE
   
     DoCmd.Close acForm, "frmGSDetails"
       
End If
0
 
andrewpiconnectAuthor Commented:
I've evn tried this and this doesnt work either. There are no records in tblHABookings that match the critieria in the DLookup but it still shows MsgBox "You need to update ALL customersw before you can close this booking"

!!!!! Totally perplexed now!


Dim strBookingRef As String

    strBookingRef = DLookup("BookingRef", "tblHABookings", "HASchedule_ID= 308 AND BookingStatus='Confirmed'")
   
    If IsNull(strBookingRef) Then
    MsgBox "Success"
    Else
    MsgBox "You need to update ALL customersw before you can close this booking"
    End If
0
 
andrewpiconnectAuthor Commented:
Solved......

Dim strBookingRef As String

    strBookingRef = DLookup("BookingRef", "tblHABookings", "HASchedule_ID= 308 AND BookingStatus='Confirmed'")
   
    If Nz(strBookingRef, "") = "" Then
    MsgBox "Success"
    Else
    MsgBox "You need to update ALL customers before you can close this booking"
    End If

Now i can put my events in where the MsgBoxes appear.

If anyone has a better work around to this then please let me know other wise i will mark this question as solved by myself.

Many thanks to all who tried to help
0
 
IrogSintaCommented:
What result did you get when you tried my answer here?
0
 
andrewpiconnectAuthor Commented:
heres my solution which both of you helped get me onto the right path with.

Dim strBookingRef As String
    'before 'Closing' check that ALL Booking Status on the flight are either 'Show' or 'No Show'
    'ie if still marked as 'Confirmed' then dont allow update
    'use DLookup to search for any 'Confirmed' Bookings where HASchedule ID = same as this booking
    strBookingRef = DLookup("BookingRef", "tblHABookings", "HASchedule_ID= " & Me.txtHASchedule_ID & " AND BookingStatus='Confirmed'")
           
    If Nz(strBookingRef, "") = "" Then
        'UPDATE tblHASchedules.Status as "Closed"
            DoCmd.SetWarnings False
            DoCmd.RunSQL "UPDATE tblHASchedules SET tblHASchedules.Status = 'Closed', " & _
            "tblHASchedules.ModifiedBy = '" & strUserName & "', " & _
            "tblHASchedules.ModifiedDate = '" & ModifiedDate & "' " & _
            "WHERE tblHASchedules.HASchedule_ID=" & Me.txtHASchedule_ID & ";"
            DoCmd.SetWarnings True
        'END UPDATE
       
        DoCmd.Close acForm, "frmGSBookingDetails"
    Else
        MsgBox "You need to update the Booking Status for all customers before you can Close this booking"
    End If

Thanks again!
0
 
IrogSintaCommented:
Your solution is not quite accurate.  DLookup up will only look at a single record and not the whole table.  In your case it is finding the first record in tblHABookings that matches your criteria.  This is hit or miss.  You would either need to use the DCount I proposed in this manner:
Dim iBookRefCount as Integer

iBookRefCount = DCount("*","NameOfYourQuery","[HASchedule_ID]=" & Me.txtHASchedule_ID & " AND [BookingStatus]='Confirmed' AND IsNull([BookingRef]))

If iBookRefCount >0 Then

Open in new window

Or you can add the BookingRef field to your criteria this way:
strBookingRef = DLookup("[BookingRef]", "tblHABookings", "[HASchedule_ID]= " & Me.txtHASchedule_ID & " AND [BookingStatus]='Confirmed' AND IsNULL([[BookingRef]])")

Open in new window

0
 
andrewpiconnectAuthor Commented:
Thanks,

I know DLookup finds the first record. But in this instance that should suffice. If any record is still showing as "Confirmed" then I want the msgbox to appear.
0
 
IrogSintaCommented:
I see what you mean.  I misunderstood your intent at first.  As an aside, it is better to us the Execute method for SQL statements instead of having to set your warnings off and then back on.  

DIM strSQL as String
strSQL = "UPDATE tblHASchedules SET tblHASchedules.Status = 'Closed', " & _
            "tblHASchedules.ModifiedBy = '" & strUserName & "', " & _
            "tblHASchedules.ModifiedDate = '" & ModifiedDate & "' " & _
            "WHERE tblHASchedules.HASchedule_ID=" & Me.txtHASchedule_ID & ";"

CurrentDb.Execute strSQL, dbFailOnError

Open in new window

0
 
andrewpiconnectAuthor Commented:
Many thanks for your continued advice.

May I ask why it is better to us the Execute method for SQL statements instead of having to set your warnings off and then back on?
0
 
IrogSintaCommented:
Certainly.  The biggest problem with turning of warnings is that it not only stops the dialog boxes from displaying, it also prevents any errors that come up from showing if your SQL statement fails.  Using the Execute method allows you to trap any errors (when you add dbFailOnError).

There are also a couple of benefits to this approach.  One is the RecordsAffected property which shows how many records are affected by the SQL statement.  The other is the ease of debugging your SQL string.  If you want to see the resulting string you can just add Debug.Print strSQL to your code and you can see if it came out the way you intended.
0
 
andrewpiconnectAuthor Commented:
Many thanks.

I think I will take your advice and use this approach going forward?

Much appreciated
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 12
  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now