Solved

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

Posted on 2013-01-21
23
636 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38801109
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
ID: 38801130
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
ID: 38801244
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 29

Expert Comment

by:IrogSinta
ID: 38801278
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
ID: 38801334
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
ID: 38801346
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
ID: 38801363
Then use this:
If DCount("*","NameOfYourQuery","[HASchedule_ID]=" & Me.txtHASchedule_ID & " AND [BookingStatus]='Confirmed'")>0 Then

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38801382
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
ID: 38801426
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 38801471
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
ID: 38801559
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38801595
what did you do?
0
 

Author Comment

by:andrewpiconnect
ID: 38801629
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
ID: 38801753
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
ID: 38801783
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
ID: 38822143
What result did you get when you tried my answer here?
0
 

Author Comment

by:andrewpiconnect
ID: 38823315
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
ID: 38823365
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
ID: 38823415
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
ID: 38823434
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
ID: 38824359
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
ID: 38824491
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
ID: 38824500
Many thanks.

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

Much appreciated
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

820 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