Modules & VBA :: Beforeupdate On Textbox Not Reverting Back
Nov 14, 2013
I have an unbound textbox with certain value. I want the user to be able to change the value, but first, I want them to get a question about it. I have added the following code to the before update event for that textbox. If they answer "No" to the question i want the value to go back to what it was, but if they say "yes" then I want the value to stay and a table to be updated.
Code:
Private Sub txtCustRepID_BeforeUpdate(Cancel As Integer)
Dim CallIDVar As Long
Dim sName As String
Dim CustRepIDNew As String
Dim CustRepIDOr As String
CustRepIDOr = Me.txtCustRepID.Value
[CXode] ....
The code for "Yes" works well, but when the user selects "no", the value in the textbox does not reverse back to the original value.
What do I need to do?
Also, I would like to add a message box that says "The value has been changed from (original value) to (new value)" How do I do that?
I have a form with a date field that has a default value of =IIf(IsNull([DueDate]),Date()+14). I use the form for new entries and for modifying entries. So if it already has a value in it then don't put the default in For new records the default is fine until someone changes the date to something else and then enters other info into another field that has a default value.
How can I stop the date field from reverting back to the default value after someone has changed it.
I have a query where I group by EmpID, so duplicates do not show up. In this query I have two fields with no data. These fields are to be filled in after the query is run in a form. However, these fields are not editable due to the group by feature. I tried two unbound fields, which does allow me to type, but doesn't save the changes once I click out of the field. The field then reverts back to blank.
I have On Load, Current, After Update events on a form, which all work fine. But, when I add a Before Update, I get the error: "Procedure declaration does not match description of event or procedure having the same name". Following is my code:
Code:
Private Sub Form_Load() If InStr(Me.Filter, "=") > 0 Then If IsNumeric(Mid$(Me.Filter, InStr(Me.Filter, "=") + 1, Len(Me.Filter) - InStr(Me.Filter, "="))) Then Me.Tag = (Mid$(Me.Filter, InStr(Me.Filter, "=") + 1, Len(Me.Filter) - InStr(Me.Filter, "="))) End If End If
I'm working on a bit of code that before a combobox is changed checks with the user to confirm that they want the change to go ahead, if yes a recorded is added to a table
This all work fine apart from if no is selected - I am trying to get the combobox to undo the change however when you select no the msgbox pops up fine but the combobox does not undo
Can't see where I'm going wrong I thought undo worked for comboboxes
Code: ' Displays a message box with the yes and no options. Response = MsgBox(prompt:="Do you wish to change the status of this Job? 'Yes' or 'No'.", Buttons:=vbYesNo) ' If Yes button selected If Response = vbYes Then
I have created a database for my hobby of being an (American) football official. I have one table of officials and another for matches to track the matches that I have worked. There are 3-7 positions that officials can work in any one match.
I want a query that will tell me which officials I have worked with and in how many matches (in total regardless of position). I have created a union all query to give me a list of officials names each time they appear. Rather than giving me the result as the names it comes back as the ID numbers from the Officials table.
The SQL query is:
SELECT Ref FROM Matches WHERE Ref Is Not Null UNION ALL SELECT Umpire FROM Matches WHERE Umpire Is Not Null UNION ALL SELECT LineJudge FROM Matches WHERE LineJudge Is Not Null UNION ALL SELECT HeadLine FROM Matches WHERE HeadLine Is Not Null UNION ALL SELECT BackJudge FROM Matches WHERE BackJudge Is Not Null UNION ALL SELECT SideJudge FROM Matches WHERE SideJudge Is Not Null UNION ALL SELECT FieldJudge FROM Matches WHERE FieldJudge Is Not Null;
The field used in each position is a full name field ([Surname] & ", " & [Name])
I have a question that involves windows permissions as well as access links. I have a contacts database split into a front end and a back end. For security reasons I want to place the back end in a folder that is hidden from the users. In other words the users can access the front end as normal and run queries and reports but they are not able to navigate in Windows to the folder containing the back end or view its contents.
Is it necessary to use VBA to do this? If so what is the script? If not, how is it done.
Can I create a table in the back end of my database using VBA in the front end? My client is using this system and I can't easily go to their location. I need to create a table on the back end and have it available in the front end applications. My thought is to create a front end only for the purpose of updating the backend with this new table. My idea is now to have it open with a button for the user to click - this will add the table to the backend with the required fields, confirm the update was completed and then close. This app then would be trashed so it would not be run again. (Though I would put a check in the VBA that if the table existed, the user would get a message box and the coding stopped to avoid overwriting the existing file.)
And once the table is created, is there a way to programmatically link it to the front end. I will be providing a new front end with this update so I could add something in the new version.
I have a Back end (with password) which resides in a netdrive while the front end is installed in each individual users desktop, the problem is, some of the users netdrive was mapped in a different way (different letters..some are J others are G). I'm looking for code that I can relink the database to the front end in runtime, I did try to look in the net but I can't find anything that I can put the password as parameter.
this sample code from Dev is good, but i got an error because the database requires a password.where i can put the password?
Code:
Function fRefreshLinks(NewDbName As String) As Boolean Dim strMsg As String, collTbls As Collection Dim i As Integer, strDBPath As String, strTbl As String Dim dbCurr As Database, dbLink As Database Dim tdfLocal As TableDef
I want to copy three of my tables from the back end to another database (backup) at regular intervals. When using docmd.CopyObject and docmd.TransferDatabase it is the link that get copied (naturally) and not the table itself.
I have a form that has a single bound text box that is set to currency. The table is also set as currency with decimal places set at 2. I have my own save button
Now I want to catch if the user types in text instead of numers and display a suitable error message.
When the user presses the save button I call this code:
DoCmd.RunCommand acCmdSaveRecord
heres the before update event
Private Sub Form_BeforeUpdate(Cancel As Integer) On Error GoTo errHandle
If Not IsNumeric(hourlyRate) Then
MsgBox "Please enter a number for the hourly rate", vbInformation hourlyRate.SetFocus Cancel = True Else If (hourlyRate < 0) Then MsgBox "Please enter a positive hourly rate", vbInformation hourlyRate.SetFocus Cancel = True End If End If Exit Sub errHandle: MsgBox Err.Description & " " & Err.Number Resume Next End Sub
However the update event isnt called and I get an access message saying that the value you entered isnt correct for the field blah blah blah.
Q - How do I stop this access message from being shown???
btw - If I enter in a negative number my message box is shown, and then i get another access message saying that the run command was cancelled.
I not sure what I'm doing wrong here. I have searched several forums and "think" I'm doing this the same way.
I'm trying to validate some fields before allowing the record to be saved, which if the user doesn't fill in the appropiate fields then the msgbox does fire, and the record is not saved but my problem is that I don't want the form to close. I would like to force the user back to the form and fill in the required fields. Any idea's as to why the forms closes?
Here's my code.
Dim ctl As Control
For Each ctl In Me.Controls If ctl.ControlType = acTextBox Then With ctl If ctl.Tag = "Required" Then If ctl.Value = "" Or IsNull(ctl.Value) Then Me.PartNumber.SetFocus MsgBox "Missing data" Cancel = True ctl.Undo End If End If End With End If
I have the following code entered on a subform:Private Sub Form_BeforeUpdate(Cancel As Integer) If Me.RegVerify = -1 And Me.RegStatus = 0 Then Dim strMsg As String strMsg = "This household has not been registered." & Chr(10) & Chr(10) & _ "Do you wish to register the household now?" If MsgBox(strMsg, vbQuestion + vbYesNo, "Register Household?") = vbYes Then Me.RegStatus = -1 Me.CurrentYearRegDate = Format(Now(), "mm/dd/yyyy") Else End If End If End SubThe code is placed on the subform 'Form_Main_Hshld' with the intention that if the 'RegVerify' checkbox is selected the data entry person will receive a reminder that they did not register the current household. The code seemed to work fine, but there are 4 subforms on the 'Form_Main_Hshld' form and if any of those are selected the code will run inadvertantly. Is there anyway to code so that the program only runs the verification process when moving between records on the 'Form_Main_Hshld' form?
I'm a total novice with VBA/Access so would desperately appreciate some help!
After making some amendments to an Access form I didn't set up, I came across a runtime error 2115 (about BeforeUpdate).
I think that the code I have causes a further set of data input boxes to appear, dependent on whether 2 separate checkboxes are ticked or not. Anyway, the only thing I changed to the database that I can think of was adding an autonumber to the main table. It all seemed to be working before I did that.
Any help on this is much appreciated. (and yes, I've learned my lesson and won't be touching somebody elses database again!!)
Thanks.
This is my code (I'm afraid I've posted it all as I don't know which bit is useful)
Option Compare Database
Private Sub Combo39_AfterUpdate() Refresh End Sub
Private Sub Coroner_PM_BeforeUpdate(Cancel As Integer) If (Forms![Death Certificate DB]![Coroner PM] = False) Then Forms![Death Certificate DB]!Label22.Visible = True Forms![Death Certificate DB]!Label23.Visible = True Forms![Death Certificate DB]![Cause of Death 1(a)].Visible = True Forms![Death Certificate DB]!Label24.Visible = True Forms![Death Certificate DB]![Cause of Death 1(b)].Visible = True Forms![Death Certificate DB]!Label25.Visible = True Forms![Death Certificate DB]![Cause of Death 1(c)].Visible = True Forms![Death Certificate DB]!Label26.Visible = True Forms![Death Certificate DB]![Cause of Death 2].Visible = True Forms![Death Certificate DB]![Box21].Visible = True End If
DoCmd.RunCommand acCmdRefresh
If (Forms![Death Certificate DB]![Coroner PM] = True) Then Forms![Death Certificate DB]!Label22.Visible = False Forms![Death Certificate DB]!Label23.Visible = False Forms![Death Certificate DB]![Cause of Death 1(a)].Visible = False Forms![Death Certificate DB]!Label24.Visible = False Forms![Death Certificate DB]![Cause of Death 1(b)].Visible = False Forms![Death Certificate DB]!Label25.Visible = False Forms![Death Certificate DB]![Cause of Death 1(c)].Visible = False Forms![Death Certificate DB]!Label26.Visible = False Forms![Death Certificate DB]![Cause of Death 2].Visible = False Forms![Death Certificate DB]![Box21].Visible = False End If
End Sub
Private Sub Form_Current() 'NEW If (Forms![Death Certificate DB]![Coroner PM] = False) Then Forms![Death Certificate DB]!Label22.Visible = True Forms![Death Certificate DB]!Label23.Visible = True Forms![Death Certificate DB]![Cause of Death 1(a)].Visible = True Forms![Death Certificate DB]!Label24.Visible = True Forms![Death Certificate DB]![Cause of Death 1(b)].Visible = True Forms![Death Certificate DB]!Label25.Visible = True Forms![Death Certificate DB]![Cause of Death 1(c)].Visible = True Forms![Death Certificate DB]!Label26.Visible = True Forms![Death Certificate DB]![Cause of Death 2].Visible = True Forms![Death Certificate DB]![Box21].Visible = True End If
If (Forms![Death Certificate DB]![Coroner PM] = True) Then Forms![Death Certificate DB]!Label22.Visible = False Forms![Death Certificate DB]!Label23.Visible = False Forms![Death Certificate DB]![Cause of Death 1(a)].Visible = False Forms![Death Certificate DB]!Label24.Visible = False Forms![Death Certificate DB]![Cause of Death 1(b)].Visible = False Forms![Death Certificate DB]!Label25.Visible = False Forms![Death Certificate DB]![Cause of Death 1(c)].Visible = False Forms![Death Certificate DB]!Label26.Visible = False Forms![Death Certificate DB]![Cause of Death 2].Visible = False Forms![Death Certificate DB]![Box21].Visible = False Else End If
End Sub
Private Sub Hospital_PM_AfterUpdate()
If (Forms![Death Certificate DB]![Coroner PM] = False) Then Forms![Death Certificate DB]!Label22.Visible = True Forms![Death Certificate DB]!Label23.Visible = True Forms![Death Certificate DB]![Cause of Death 1(a)].Visible = True Forms![Death Certificate DB]!Label24.Visible = True Forms![Death Certificate DB]![Cause of Death 1(b)].Visible = True Forms![Death Certificate DB]!Label25.Visible = True Forms![Death Certificate DB]![Cause of Death 1(c)].Visible = True Forms![Death Certificate DB]!Label26.Visible = True Forms![Death Certificate DB]![Cause of Death 2].Visible = True Forms![Death Certificate DB]![Box21].Visible = True End If
DoCmd.RunCommand acCmdRefresh
If (Forms![Death Certificate DB]![Coroner PM] = True) Then Forms![Death Certificate DB]!Label22.Visible = False Forms![Death Certificate DB]!Label23.Visible = False Forms![Death Certificate DB]![Cause of Death 1(a)].Visible = False Forms![Death Certificate DB]!Label24.Visible = False Forms![Death Certificate DB]![Cause of Death 1(b)].Visible = False Forms![Death Certificate DB]!Label25.Visible = False Forms![Death Certificate DB]![Cause of Death 1(c)].Visible = False Forms![Death Certificate DB]!Label26.Visible = False Forms![Death Certificate DB]![Cause of Death 2].Visible = False Forms![Death Certificate DB]![Box21].Visible = False End If
End Sub
Private Sub Hospital_PM_BeforeUpdate(Cancel As Integer) If (Forms![Death Certificate DB]![Coroner PM] = False) Then Forms![Death Certificate DB]!Label22.Visible = True Forms![Death Certificate DB]!Label23.Visible = True Forms![Death Certificate DB]![Cause of Death 1(a)].Visible = True Forms![Death Certificate DB]!Label24.Visible = True Forms![Death Certificate DB]![Cause of Death 1(b)].Visible = True Forms![Death Certificate DB]!Label25.Visible = True Forms![Death Certificate DB]![Cause of Death 1(c)].Visible = True Forms![Death Certificate DB]!Label26.Visible = True Forms![Death Certificate DB]![Cause of Death 2].Visible = True Forms![Death Certificate DB]![Box21].Visible = True End If
DoCmd.RunCommand acCmdRefresh
If (Forms![Death Certificate DB]![Coroner PM] = True) Then Forms![Death Certificate DB]!Label22.Visible = False Forms![Death Certificate DB]!Label23.Visible = False Forms![Death Certificate DB]![Cause of Death 1(a)].Visible = False Forms![Death Certificate DB]!Label24.Visible = False Forms![Death Certificate DB]![Cause of Death 1(b)].Visible = False Forms![Death Certificate DB]!Label25.Visible = False Forms![Death Certificate DB]![Cause of Death 1(c)].Visible = False Forms![Death Certificate DB]!Label26.Visible = False Forms![Death Certificate DB]![Cause of Death 2].Visible = False Forms![Death Certificate DB]![Box21].Visible = False End If End Sub
Private Sub Place_of_Death_AfterUpdate() Refresh End Sub
I have some code in a form that calls for a public function and passes on some values. the function makes some calculations and assigns a value to the variable "percent" in the function itself.
How can I get the value of this variable back in the form so I can use it?
Form:
Code: Private Sub Form_Open(Cancel As Integer) Dim Table As String, TotalFields As Single Table = "tblAdmission"
I have code that from DB1 opens DB2 and runs a Function in DB2.
The DB2 function produces a Boolean result that i am trying to get back to DB1 without success. Below is the script.
Sub Test22() Dim AC As Object Set AC = CreateObject("Access.Application") rc = "K:ARSHRAutomation_ProjectsMikeFEDB2.accd b" AC.OpenCurrentDatabase (rc) AC.Visible = True AC.Run "SendVariable" End Sub
Because i am using AC.Run "SendVariable" i cant find a way to get the value of SendVariable!!
I've got a private sub that runs when the form loads. I want it to get the id of the record which has opened this new form.
The public sub populates the variable but it's Empty going back into my private sub. I'm sure its a simple school boy error but it has me stumped!
I want it public as I need to keep that id as new records are added in the form.
Private Sub Form_Load() FRStudentFundingRequestID 'fundingrequest = FundinRequestID Me.txtFKFReq = FundingRequest End Sub __________________________________ Public Sub FRStudentFundingRequestID() FundingRequest = [Forms]![frmFundingRequest]![txtpk] End Sub
I have a function which exports some data to Excel, performs some work, and then attaches the final report to an e-mail. As a result of various temporary columns being added / removed (for formulas to determine scope etc.), the main sheet ends up scrolled to the bottom of the used range. Before I attach it to the e-mail, I want to scroll the sheet back to the top-left corner (A1).
Code:
Public appExcel As Object ' Excel Application object (late-bound) Dim objWorksheet As Object ' Excel Worksheet object (late-bound) objWorksheet.Activate With appExcel With .ActiveWindow .ScrollRow = 1 .ScrollColumn = 1 End With End With
It worked up until this week, when I had to make some changes to the rest of the code to refine the output. Now, when it hits the ".ScrollRow = 1" line, I get RTE 91..how I can get the sheet to scroll to the top-left before I save it? (Such that, when subsequently attached to the e-mail, the recipient will open the workbook at the top-left cell and not sacrolled to the bottom).
I have pasted the code that SJ posted on another thread that any changes to a record will ask the user to save before closing the form, this located in the beforeupdate event.
Private Sub Form_BeforeUpdate(Cancel As Integer) If MsgBox("Save changes?", vbQuestion + vbYesNo) = vbNo Then Cancel = True End Sub
The problem i am getting is that if the user selects no it pops up another message saying "You can't save the record at this time" followed by a description. How do i prevent this second message appearing i cannot find a thread with this in?
I have a form where I want to validate enties before moving off the record.
I put my validation in the BeforeUpdate event of the form. If validation fails, then I set Cancel=True. So far, so good.
But if the user closes the form (either with my close button, or the standard close button), or presses 'Page down' to move to the next record, then even if validation fails, the form will close (or move record). What I want is for the user to be left with the form open, on the current record.
Question - How do you do data validation during the BeforeUpdate event? How do you halt the event without getting an error message?
I have tried the following:
If (Me.Control.Value <> "") Then 'Do nothing. Else Msgbox "Please enter a value for Control." Cancel = True End If
When Cancel = True is executed, I get the following message: You can't save this record at this time. DatabaseName may have encountered an error while trying to save a record. If you close this object now, the data changes you made will be lost. Do you want to close the database object anyway?
I can't seem to halt or "cancel" the BeforeUpdate event without getting this error message. After hours of looking for this answer, any suggestions would be appreciated.
I have the following function that I found online. Unfortunately I can't remember where I got it since I've had it for a little while. Today, when I tried to actually put it to use it didn't work.
I'm calling it from a form as follows: CompactDB (tblHotword)
tblHotword is just a random table from the back end. My understanding of the function was that it would use that table to connect and get the file name of the back end.
Whenever I run it, Access pops up a window that says "Object required" and nothing else. It doesn't look like a standard error message popup. When I click 'OK', Access continues with the rest of the code as if nothing went wrong. The function doesn't run though.
Code: Public Function CompactDB(TableName As String) As Boolean On Error GoTo Err_CompactDB Dim stFileName DoCmd.Hourglass True stFileName = db.TableDefs(TableName).Connect
I have a mainform with a search function so the user can select a record to view and update. The selected record is displayed in Subform1 on Page1 of a tab control. The record source of this form is Query1. Page2 of the tab control contains Subform2; record source is the result of Query1 joined to Query2.
The forms correctly displays, updates and adds records my problem occurs when I add VBA code to the BeforeUpdate event of a control on Subform1. The code appears to execute once for each page in the tab control.
Has anyone any suggestions on how I can correct my problem.
