My function sets the depart checkbox to true, updates the table...but now I would like to requery the form.
Any sugestions on t his?
before you do the requery.
"WHERE SSN = '" & [Forms]![xx_Main]![SSN] & "'"
MsgBox "Depart action canceled.", vbOKOnly + vbInformation, "Depart Canceled"
Just my 2 1/2 cents worth.
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.
The 'refresh page' is a no-go as well...hmmm ?
nything else I could try?
Edited by: Jack Cowley on Fri Jan 13 16:46:00 EST 2006.
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.
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.
And you noted that I am 'requerying' before data was updated..if so, when should I requery?
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.
I was hoping that was fine.
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.
MsgBox "Please fill in Departure Date", vbOKCancel + vbCritical, "Depart Date Missing"
MsgBox "Please fill in Departure Reason", vbOKCancel + vbCritical, "Depart Reason Missing"
MsgBox "SM Departed", vbOKOnly + vbInformation, "Departed"
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.
MsgBox "Can't Depart. Please fill in Departure Date", vbOKCancel + vbCritical, "Depart Date Missing"
MsgBox "Can't Depart. Please fill in Departure Reason", vbOKCancel + vbCritical, "Depart Reason Missing"
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.
Thank you, as always, for your excellent suggestion. Don't mention this to Ricky, but I always learn more from your posts!!
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!!!
You're doing an excellent "job" helping so many people. Keep up the good "work".