Full Version: After Update Query
UtterAccess Forums > Microsoft® Access > Access Forms
williams9969
Hello again
uick question please
If I have a form that is filtered by a checkbox...departed (true) / present (false)
My function sets the depart checkbox to true, updates the table...but now I would like to requery the form.
Oused me.requery to requery the open form...but the record is still there until I close the form and open the form back up
Any sugestions on t his?
VR
DW
Jack Cowley
Try a
oCmd.RunCommand acCmdSaveRecord
before you do the requery.
hth,
Jack
williams9969
Hi Jack-
The save does not work...
THere is my current code..I am not getting any errors, but is just not requerying
CODE
Private Sub Label184_Click()
     Dim intResponse As Integer
     intResponse = MsgBox("Warning:  You are about to depart this soldier!  Are you sure you want to do this?", vbYesNo + vbExclamation, "Depart SM")
     Select Case intResponse
        Case vbYes
            DoCmd.OpenReport "perOPReport", acViewNormal
            Dim DepSQL As String
                DepSQL = "UPDATE tblMain SET Departed = True " & _
                "WHERE SSN = '" & [Forms]![xx_Main]![SSN] & "'"
                DoCmd.RunCommand acCmdSaveRecord
                Me.Requery
                Me.cboSearch.Requery
                Me.cboSearch.SetFocus
            Case Else
                MsgBox "Depart action canceled.", vbOKOnly + vbInformation, "Depart Canceled"
                Me.Departed = False
                Me.cboSearch.SetFocus
            End Select
            CurrentDb.Execute DepSQL, dbFailOnError
        End Sub

Thanks for the help
VR
DW
Aquadevel
Not saying Jack's advice is wrong, since he get's 3 cents, but
I've had to use:
DoCmd.RunCommand acCmdRefreshPage
Just my 2 1/2 cents worth. frown.gif
Aqua
Jerry Dennison
You should not modify the existing record in the first place. When you modify the existing record you will lose any history of the modification. It would be better to add a record in a linked table noting the departure (you can also capture where they went). In this same table you can also capture when they come back, so you will have a full history of all of their deployments.
nyway, you are creating a SQL statement but not executing it. The DoCmd.RunCommand acCmdSaveRecord is not the appropriate method for what you're attempting. You would want to use either the .Execute or .RunSQL methods.
williams9969
The 'refresh page' is a no-go as well...hmmm ? crazy.gif
nything else I could try?
VR
DW
Jack Cowley
Replace the DoCmd.RunCommand code with:

CurrentDb.Execute DepSQL , dbFailOnError

and remark out your current code. You are requerying before the data has been updated...

Take heed of Jerry Dennison's warning about changing existing data...

Jack
Edited by: Jack Cowley on Fri Jan 13 16:46:00 EST 2006.
williams9969
Jack / Jerry-
o I should create another table for Departed Persons and append to that...or update to that?
Also, and this is a good question for me...what is the purpose of a "Linked Table"
and how does one accomplish what Jerry was describing...I want to get this right.
Thanks so much for your help
VR
DW
Jack Cowley
I do not know what you tables are called, but here is the idea:
blSoldiers
SoldierID (PK and auto)
LastName
FirstName
tblDeployment
DeploymentID (PK and auto)
SoldierID (FK)
DeploymentDate
DepartureDate
ReturnDate
Something along those lines will give you a history of each soldiers deployment, return, etc. tblDeployment would be a subform in a form based on tblSoldiers....
hth,
Jack
williams9969
Jack-
do not need to track deployments at all.
All I am trying to do is mark the soldier as departed...but keep his record...so if he ever comes back I already have his record. Then I depart him (no longer in my unit) and requery my form so th user does not see the person on the UI.
Ochanged the executable part to : CurrentDb.Execute DepSQL , dbFailOnError
And you noted that I am 'requerying' before data was updated..if so, when should I requery?
Thanks again for your help
VR
DW
Jack Cowley
If the Departed checkbox is just to let you know that someone is no longer 'active', on the base or whatever, then what you are doing is fine. Forget what has been said about a related table.
If you move the CurrentDB.Execute to where you had the DoCmd code then the next line was the Requery code. If it stays there it should work OR you may want to place it where you had the CurrentDb.Execute code. Are you still not able to see the record after your code has run? If the form is based on a query and you have checked this person as Departed should you still see them (as they are filtered out by the query the form is based on).
I am guessing here as I do not know your setup so I could have the requery thing wrong.
Jack
williams9969
I was hoping that was fine.
solved that by moving the line and adding a message box ...thanks for that frown.gif
THere is what I did and am doing... frown.gif
After the user clicks on depart I want to be able to check if the depart date and depart reason are not null...and not complete the action until the date and reason are both filled in.
The message box's work, but it goes ahead and completes the entire line of code...
CODE
Private Sub Label184_Click()
     Dim intResponse As Integer
     intResponse = MsgBox("Warning:  You are about to depart this soldier!  Are you sure you want to do this?", vbYesNo + vbExclamation, "Depart SM")
        Select Case intResponse
            Case vbYes
                If IsNull(Me.DepartDate) Then
                    MsgBox "Please fill in Departure Date", vbOKCancel + vbCritical, "Depart Date Missing"
                    Me.DepartDate.SetFocus
                End If
                If IsNull(Me.DepartReason) Then
                    MsgBox "Please fill in Departure Reason", vbOKCancel + vbCritical, "Depart Reason Missing"
                    Me.DepartReason.SetFocus
                End If
                DoCmd.OpenReport "perOPReport", acViewNormal
                Dim DepSQL As String
                DepSQL = "UPDATE tblMain SET Departed = True " & _
                "WHERE SSN = '" & [Forms]![xx_Main]![SSN] & "'"
                CurrentDb.Execute DepSQL, dbFailOnError
                MsgBox "SM Departed", vbOKOnly + vbInformation, "Departed"
                Me.cboSearch.SetFocus
                Me.Requery
            Case Else
                MsgBox "Depart action canceled.", vbOKOnly + vbInformation, "Depart Canceled"
                Me.Departed = False
                Me.cboSearch.SetFocus
        End Select
    End Sub

Thanks again for help with this...and all...
Thanks again
VR
DW
Jack Cowley
To stop the code from executing when the user selects No in the message box you need to add an Exit Sub in the If - Then statments after the message box code and the set focus code... I hope that that was your question.
ack
williams9969
Jack-
wanted to stop it if it is null...not when the user selects no...
However..I did fix it all and I ask that you please take a look for any gleaming errors frown.gif
That way I can move on with a clear head frown.gif
Thanks all for help...very much
CODE
Private Sub Label184_Click()
    DoCmd.RunCommand acCmdSaveRecord
    If IsNull(Me.DepartDate) Then
        MsgBox "Can't Depart.  Please fill in Departure Date", vbOKCancel + vbCritical, "Depart Date Missing"
        Me.DepartDate.SetFocus
        Exit Sub
    End If
    If IsNull(Me.DepartReason) Then
        MsgBox "Can't Depart.  Please fill in Departure Reason", vbOKCancel + vbCritical, "Depart Reason Missing"
        Me.DepartReason.SetFocus
        Exit Sub
    End If
    Dim intResponse As Integer
     intResponse = MsgBox("Warning:  You are about to depart this soldier!  Are you sure you want to do this?", vbYesNo + vbExclamation, "Depart SM")
        Select Case intResponse
            Case vbYes
                DoCmd.OpenReport "perOPReport", acViewNormal
                Dim DepSQL As String
                DepSQL = "UPDATE tblMain SET Departed = True " & _
                "WHERE SSN = '" & [Forms]![xx_Main]![SSN] & "'"
                CurrentDb.Execute DepSQL, dbFailOnError
                MsgBox "SM Departed", vbOKOnly + vbInformation, "Departed"
                Me.cboSearch.SetFocus
                Me.Requery
                Me.cboSearch.Requery
            Case Else
                MsgBox "Depart action canceled.", vbOKOnly + vbInformation, "Depart Canceled"
                Me.Departed = False
                Me.cboSearch.SetFocus
        End Select
    End Sub

Thank you again
VR
DW
Jerry Dennison
Actually Jack, it would be better to have the following:
blDeployment
DeploymentID 'PK autonumber
SoldierID 'FK link to tblSoldiers
DeploymentDate
DeploymentTypeID 'FK link to tblDeploymentType where you would record the type of deployment, i.e. departure or arrival or whatever
You would then append to this table each deployment transaction. Using your structure you would have to find and then modify the appropriate existing record. In my opinion, never a good idea.
Jack Cowley
Jerry -

Thank you, as always, for your excellent suggestion. Don't mention this to Ricky, but I always learn more from your posts!! wink.gif

HAs I understand what the OP is after the checkbox is just a way to know is someone is 'active' or not, kind of like an active/not active members list.

Congratulations, again, on your Microsoft Access MVP. You certainly deserve it!!!

Jack
Jack Cowley
DW -
don't see any glaring errors and if the code is working for you then there is an excellent chance it is OK. Continued success with your project...
Jack
Jerry Dennison
You're welcome Jack.
You're doing an excellent "job" helping so many people. Keep up the good "work".
Thanks,
Jerry
R. Hicks
There is no need for him to tell me ... wink.gif
DH
williams9969
Thanks all for your expertise
ery, very much
VR
DW
Jack Cowley
Jerry -
Thank you for your kind words and encouragement!
Jack
For as Ricky would say
The Old Goat
Jack Cowley
Ricky -
There would I be without your constant support, understanding and marvelous sense of humor? I would still be referring to the fields on a form....
The Old Goat
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.