Solved

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

Posted on 2013-01-21
23
625 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
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
 
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 119

Expert Comment

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

Accepted Solution

by:
Rey Obrero 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 119

Expert Comment

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

911 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

20 Experts available now in Live!

Get 1:1 Help Now