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 and a subform in it. I added New cancel button in the form so that the the user can cancel the record creation and no record will be inserted in the parent table.
But when details are entered in the subform (a datasheet) row records will be created in the subform table. what is the correct method or how to cancel these records if the user choose to click cancel button on the parent form.
Hi, i have created two tables, containing the same fields. One table if for '1 day tests' and the other is for '2 day tests'. I need to keep these two in seperate tables.
At the moment i have two seperate forms, one for each type of test, but as the fields are all the same, i was wondering if i had a combo box, so the user could choose whether they wanted a one day or two day test and filled in the rest of the fields, the record would then be stored in the appropriate table. Ie 2 day test table, if the chose 2 day test on the combo box
I have two date and time fields which format are like: 15/09/05 09:48:43,I have another field which calculates the time difference between these two fields. I am doing a calculation on the time difference field and want the time to be displayed as for example 03:00, but at the moment it just shows 3:00.
This is the control source which has some formatting in it:
I've tried every way i can, and i cant get the rowsource query right.
If someone could look at this and post a fix, so that when one uses the "training record" form it allows you only to select course dates that are already in the tbl_course_dates table.
I'm trying to create a little dbase for personal keeping track of a number of companies I deal with, so that i store their details and also the products they distribute. In more detail, I want to have 'fixed' product categories, which will be assigned to supplier, and each category will include the product models I have set, including their details (such as price, manufacturer, country of origin, etc).
My point is to be able to create several customised reports and listing: 1. All product models I have in a category including their details. 2. Report per supplier to view their details and the products they distribute 3. Certain product models in a list with their local distributor (companies I deal with).
I assume it involves a couple of many2many relationships, however I have created a file in MS Access but never managed to have it work properly.
I want to delete a relationship temporarily so that I can delete a record. After the record is deleted I will restore the relationship immediately. But I am concerned that this will ruin any queries I have already established or ruin my other records. Could you let me know if this is possible without making a mess of things that have already been established.
Any way, im not nearly as experience as all you, I have a small form, on the form I have a combo box, the form shows the relevant info with regards to what is selected in the combo box, the source of the combo box is a table, the data in the table is alaphabetical, but when you select the combo box the data shown to select from is not alphabetical its all over the place!
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 a table linked to an SQL Server table in my MS Access 2000 Application containing 5 text-fields (SQL Server Type, in Access seen as MEMO-fields) and several other fields (in total about 140 fields). Updating any field works fine except for some records. In those records I can only edit the non-text-fields in my application. When I try to edit one of the text-fields I get a error message telling me that another user has edited the record and i have to discard my changes.
The strange thing is that this problem only crops up for some records. (1 in a 100 or so) All other records work fine.
Does anyone know what happens here? And how this can be fixed?
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 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.
I've got my form working, and all of the record updating is working fine, so now I am working on error trapping. I need to check if any of the two textboxes are empty, or nothing has been selected from the combobox. I am using the BeforeUpdate method. I am not getting any syntax errors. I am using an INSERT INTO SQL statement. Thanks.
related DB fields: SOPNumber (text) RevisionNumber (text) TrainingDate (date/time)
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.cboSOPNumber) Then Cancel = True MsgBox "The SOP number is required", vbOKOnly, "Notice" Me.cboSOPNumber.SetFocus End If
If IsNull(Me.txtRevisionNumber) Then Cancel = True MsgBox "The Revision Number is required", vbOKOnly, "Notice" Me.txtRevisionNumber.SetFocus End If
If IsNull(Me.txtTrainingDate) Then Cancel = True MsgBox "The training date is required", vbOKOnly, "Notice" Me.txtTrainingDate.SetFocus End If
I'm using Access 2K. I have created a Form BeforeUpdate event to check that certain fields in the form have been filled. However, I don't want to do the check until after a subform entry is also complete, but the BeforeUpdate event fires as soon as the subform is entered. Is there any way I can around this?
I've got a form with Text boxes StartDate (datatype = Date / Time) and Interval (datatype = Numeric). Both text boxes are bound controls.
I'd like to validate whether summing the StartDate with the Interval entered results in a calculated date, falling on either Saturday or Sunday. If so, the Interval needs to be reset so that the calculated day will fall on the first Monday falling after the StartDate.
The code I've got so far results in an error:
Run-time error '-2147352567 (80020009):
The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Microsoft Access from saving the data in the field.
I can understand why this error is generated. What I can't figure out is how to adapt the code. The AfterUpdate event is no option as this is triggered too late...
BTW, I'm on Access2000
Here's the code I've got so far:
Private Sub Interval_BeforeUpdate(Cancel As Integer)
Dim Response Dim strInterval As Integer
If Weekday(Me.Startdatum + Me.Interval, vbMonday) = 6 Or Weekday(Me.Startdatum + Me.Interval, vbMonday) = 7 Then Response = MsgBox("Eerstvolgende klusdatum valt in het weekend, laten vallen op maandag?", vbYesNo) End If If Response = vbYes Then strInterval = 8 - Weekday(Me.Startdatum, vbMonday) Me.Interval = strInterval Else If Response = vbNo Then Me.Undo End If End If
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 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?
