Solved

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

Posted on 2013-01-21
23
611 Views
Last Modified: 2013-01-27
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
Comment
Question by:andrewpiconnect
  • 12
  • 7
  • 4
23 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
You can do a DCount of the table. for example:
If DCount("*","tblHASchedules","[BookingStatus]='Confirmed'")>0 Then

Open in new window

0
 

Author Comment

by:andrewpiconnect
Comment Utility
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
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
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
 

Author Comment

by:andrewpiconnect
Comment Utility
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
 

Author Comment

by:andrewpiconnect
Comment Utility
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
 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 250 total points
Comment Utility
Then use this:
If DCount("*","NameOfYourQuery","[HASchedule_ID]=" & Me.txtHASchedule_ID & " AND [BookingStatus]='Confirmed'")>0 Then

Open in new window

0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
 

Author Comment

by:andrewpiconnect
Comment Utility
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 250 total points
Comment Utility
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
 

Author Comment

by:andrewpiconnect
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
what did you do?
0
 

Author Comment

by:andrewpiconnect
Comment Utility
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
 

Author Comment

by:andrewpiconnect
Comment Utility
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
 

Author Comment

by:andrewpiconnect
Comment Utility
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
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
What result did you get when you tried my answer here?
0
 

Author Comment

by:andrewpiconnect
Comment Utility
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
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
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
 

Author Comment

by:andrewpiconnect
Comment Utility
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
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
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
 

Author Comment

by:andrewpiconnect
Comment Utility
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
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
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
 

Author Comment

by:andrewpiconnect
Comment Utility
Many thanks.

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

Much appreciated
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now