423,688 Members | 2,147 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,688 IT Pros & Developers. It's quick & easy.

Form issue with change entries

P: 96
I have a form which is linked to a subform. Whenever we go back and try and change an entry or put something in one of the fields after entering data in the subform, sometimes it will not accept the change or additional data. We click on the sidebar but the pencil stays there. I have actually click it 5 to 10 times and nothing. As soon as I press the ESC key it accepts the changes sometimes others it deletes the changes and goes back to what was in the field to start with. I created a new form and copied all my fields and relinked everything and I still have this issue. Has anyone ever had a similar issue and if so how did you fix it?
Mar 12 '18 #1

✓ answered by PhilOfWalton

OK, assuming that the Shift Number always has to be entered, run the date calculation on the AfterUpdate of the Shift

Phil

Share this Question
Share on Google+
18 Replies


twinnyfo
Expert Mod 2.5K+
P: 2,667
DJ,

Is the subform in a parent/Child relationship with the main form? is the related key somehow one of the editable fields on the subform? Is there any code on the subform that performs any activity on the current record (OnCurrent property)?

Just tossing out some possibilities...
Mar 12 '18 #2

P: 96
Subform is linked to Main form as a child. Currently no issues with entering data in the subform only the mainform. There is code on the "onCurrent" the code is as folows
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  
  3.     ReSizeSubform Me
  4.  
  5. End Sub
Which fires this:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Const MaxRecs = 18
  5.  
  6. Function ReSizeSubform(frm As Form)
  7.     ' Runs on the OnCurrent of the MAIN Form (FrmLine)
  8.  
  9.     Dim NoRecs As Integer
  10.     Dim TotalHeight As Single
  11.  
  12.     With frm.subfrmDrawing.Form
  13.         If Not .Recordset.EOF Then
  14.             .Recordset.MoveLast
  15.             NoRecs = Nz(.Recordset.RecordCount) + 1
  16.             If NoRecs > MaxRecs Then
  17.                 NoRecs = MaxRecs
  18. '                .ScrollBars = 0             ' Show scrollbar
  19. '            Else
  20. '                .ScrollBars = 0             ' end if
  21.             End If
  22.             TotalHeight = .Section(acHeader).Height + .Section(acFooter).Height + (.Section(acDetail).Height * NoRecs)
  23.             If Not .Recordset.BOF Then
  24.                 .Recordset.MoveFirst
  25.             End If
  26.         End If
  27.  
  28.     End With
  29.  
  30.     frm.subfrmDrawing.Height = TotalHeight
  31.     frm.Repaint
  32.  
  33. End Function
  34.  
  35. Function ReSizeForm(frm As Form)
  36.     ' Runs on the After Update & After Delete of the SUBFORM (SubFrmDrawing)
  37.  
  38.     Dim TotalHeight As Single
  39.     Dim NoRecs As Integer
  40.  
  41.     With frm
  42.         If Not .RecordsetClone.EOF Then
  43.             .RecordsetClone.MoveLast
  44.             NoRecs = Nz(.RecordsetClone.RecordCount) + 1
  45.             If NoRecs > MaxRecs Then
  46.                 NoRecs = MaxRecs
  47. '                .ScrollBars = 0             ' Show scrollbar
  48. '            Else
  49. '                .ScrollBars = 0             ' end if
  50.             End If
  51.             TotalHeight = .Section(acHeader).Height + .Section(acFooter).Height + (.Section(acDetail).Height * NoRecs)
  52.         End If
  53.     End With
  54.  
  55.     frm.InsideHeight = TotalHeight
  56.     frm.Repaint
  57.  
  58. End Function
  59.  
Mar 12 '18 #3

twinnyfo
Expert Mod 2.5K+
P: 2,667
Very strange! Usually, changes to a main form don't affect anything on the sub-form, as long as your not making any changes to the related key (and vice versa).

It definitely sounds like when you make changes to the subform and return to the main that the record is being locked. Not sure if this will help, but try adding this to the Main Form's VBA:

Expand|Select|Wrap|Line Numbers
  1. Private Sub SubFormName_Exit(Cancel As Integer)
  2. On Error GoTo EH
  3.  
  4.     Me.[SubFormName].Form.Refresh
  5.  
  6.     Exit Sub
  7. EH:
  8.     MsgBox "There was an error exiting the Subform!  " & _
  9.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  10.     Exit Sub
  11. End Sub
This will force the Subform to refresh (and save) the data on the subform every time you return to the main form.

Let me know if that hepps!
Mar 12 '18 #4

P: 96
Thanks, I'll let you know ASAP.
Mar 12 '18 #5

P: 96
Ok, I got an error box that says:

There was an error exciting the subform! Please contact you Database Administrator.

When I clicked ok, went and attempted to change an entry and still wont "send to the table(Clicking sidebar).
Mar 12 '18 #6

twinnyfo
Expert Mod 2.5K+
P: 2,667
Sounds like the Error handling is doing what it is supposed to do. Now we gotta figure out what the issue is.

Change lines 8-9 from post #4 to:

Expand|Select|Wrap|Line Numbers
  1.     MsgBox "There was an error exiting the Subform!  " & vbCrLf & vbCrLf & _
  2.     "Error Number: " & Err.Number & vbCrLf & _
  3.     "Description:  " & Err.Description
Let me know what the error number/description are. I think I have some ideas, but want to confirm.
Mar 12 '18 #7

P: 96
Error: 2465
Description: Microsoft Access cant find field '/1' referred to in your expression
Mar 12 '18 #8

PhilOfWalton
Expert 100+
P: 1,373
Unless this is a pure coincidence, the code submitted by DJRhino is identical to the code in a Db passed to me a couple of days ago for some help.

I have been unable to reproduce the fault mentioned above.
If it is the same person, he did report he had a corrupted record in one of his tables, but thought he had deleted it. There may still be a corruption in his BE

Phil
Mar 12 '18 #9

P: 96
I was able to get the corrupted data out, I work with Phil Stanton this weekend to help with something else and we caught what it was and took care of it.
Mar 12 '18 #10

twinnyfo
Expert Mod 2.5K+
P: 2,667
And this error only occurs when you: 1) Open the main form, 2) edit data on the subform and then 3) try to edit data on the main form?

Does the subform allow you to add/delete records from the sub form? Does the same thing happen when you do?

Are the main and sub forms using data from the same table?

Any VBA on the subform afte any data updates?
Mar 12 '18 #11

P: 96
Main form and sub form have two different tables. the subform does allow me to Add/Delete with no issues. The only links are the ID #'s
Here is all the code listed for After Update:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Apprentice_BeforeUpdate(Cancel As Integer)
  5.  
  6.     If Nz(Apprentice) = "" Then
  7.         MsgBox "Please enter the Apprentice name, Select *None* if you do not have one!", vbCritical
  8.         Apprentice.SetFocus
  9.         Cancel = True
  10.         Exit Sub
  11.     End If
  12.  
  13. End Sub
  14.  
  15. Private Sub Form_AfterUpdate()
  16.  
  17.     If Shift = 3 Then
  18.  
  19.         If LineDate = Date And Hour(Now) >= 18 And Minute(Now) >= 30 Then
  20.             LineDate = DateAdd("d", 1, Date)
  21.         Else
  22.             LineDate = Date
  23.         End If
  24.     End If
  25.  
  26. End Sub
  27.  
  28. Private Sub Form_BeforeUpdate(Cancel As Integer)
  29.  
  30.     If Nz(GaurdsInPlace) = False Then
  31.         MsgBox "Please verify your guards are in place and check off this box!", vbCritical
  32.         Cancel = True
  33.         GaurdsInPlace.SetFocus
  34.         Exit Sub
  35.     End If
  36.  
  37.     If Nz(LineDate) = 0 Then
  38.         MsgBox "Please today's date!", vbCritical
  39.         Cancel = True
  40.         LineDate.SetFocus
  41.         Exit Sub
  42.     End If
  43.  
  44.     If Nz(Shift) = 0 Then
  45.         MsgBox "Enter Your Shift Number", vbCritical
  46.         Cancel = True
  47.         Shift.SetFocus
  48.         Exit Sub
  49.     End If
  50.  
  51.    If Nz(CboLineNoId) = 0 Then
  52.         MsgBox "Enter the Line Number", vbCritical
  53.         Cancel = True
  54.         CboLineNoId.SetFocus
  55.         Exit Sub
  56.     End If
  57.  
  58.    If Nz(TechID) = 0 Then
  59.         MsgBox "Enter Tech's name", vbCritical
  60.         Cancel = True
  61.         TechID.SetFocus
  62.         Exit Sub
  63.     End If
  64.  
  65.     If Nz(Apprentice) = "" Then
  66.         MsgBox "Please enter the Apprentice name, Select *None* if you do not have one!", vbCritical
  67.         Apprentice.SetFocus
  68.         Cancel = True
  69.         Exit Sub
  70.     End If
  71.  
  72.    If Nz(SupervisorID) = 0 Then
  73.         MsgBox "Enter Supervisor's name", vbCritical
  74.         Cancel = True
  75.         SupervisorID.SetFocus
  76.         Exit Sub
  77.     End If
  78.  
  79.     If Nz(ScheduledHrs) <= 0 Then
  80.         MsgBox "Please Enter the hours you are scheduled to run on this line!", vbCritical
  81.         Cancel = True
  82.         ScheduledHrs.SetFocus
  83.         Exit Sub
  84.     End If
  85.  
  86. End Sub
  87.  
  88. Private Sub Form_Current()
  89.  
  90.     ReSizeSubform Me
  91.  
  92. End Sub
  93.  
  94. Private Sub form_Open(Cancel As Integer)
  95.  
  96.     DoCmd.Maximize
  97.  
  98. End Sub
  99.  
  100. Private Sub ScheduledHrs_BeforeUpdate(Cancel As Integer)
  101.  
  102.     If Nz(ScheduledHrs) <= 0 Then
  103.         MsgBox "Please Enter the hours you are scheduled to run on this line!", vbCritical
  104.         Cancel = True
  105.         ScheduledHrs.SetFocus
  106.  
  107.     End If
  108.  
  109.     If Nz(ScheduledHrs) > 720 Then
  110.         MsgBox "Please Enter the Correct hours you are scheduled to run on this line!", vbCritical
  111.         Cancel = True
  112.         ScheduledHrs.Undo
  113.  
  114.     End If
  115.  
  116. End Sub
  117.  
  118. Private Sub Shift_BeforeUpdate(Cancel As Integer)
  119.  
  120.     If Nz(Shift) <= 0 Or Nz(Shift) > 3 Then
  121.         MsgBox "Please Enter Your Shift!", vbCritical
  122.         Cancel = True
  123.         Shift.SetFocus
  124.         Exit Sub
  125.     End If
  126.  
  127. End Sub
  128.  
  129. Private Sub subfrmDrawing_Exit(Cancel As Integer)
  130.  
  131. On Error GoTo EH
  132.  
  133.     Me.[SubFormName].Form.Refresh
  134.  
  135.     Exit Sub
  136. EH:
  137.      MsgBox "There was an error exiting the Subform!  " & vbCrLf & vbCrLf & _
  138.     "Error Number: " & Err.Number & vbCrLf & _
  139.     "Description:  " & Err.Description
  140.     Exit Sub
  141. End Sub
  142.  
Mar 12 '18 #12

PhilOfWalton
Expert 100+
P: 1,373
Sorry to but in, but I think the problem is on the AfterUpdate of the main form where you immediately dirty the record by altering the LineDate. Hence the continual pencil mark.

There are 2 alternatives to try.
1) Play with the LineDate on the BeforeUpdate
2) Add the following code after the final "End If" in the AfterUpdate
Expand|Select|Wrap|Line Numbers
  1.    Me.Dirty = false
  2.  
That should save the record

Phil
Mar 12 '18 #13

P: 96
Phil
I added the Me/Dirty=False to the after update and went in and changed several records and each one took. I think this fixed it. I fixed the message in the before update so now it makes since.
Mar 12 '18 #14

twinnyfo
Expert Mod 2.5K+
P: 2,667
Good catch, Phil! I hadn't even had a chance to review the code.
Mar 12 '18 #15

PhilOfWalton
Expert 100+
P: 1,373
I personally think Option 1 is better, as the second option requires 2 write statements (twice the chance of an error an a longer operation)

Phil
Mar 12 '18 #16

P: 96
Turns out this started causing issues also. So now I'm back to the drawing board. Going to look at option 1 now, but not sure what I can play with here. I do have the table putting in a date here automatically through the default function as Date(). So I tried 'blocking out this code, and if I made a change to a record it was causing an error that states "Run-time error '2115':
The macro or function set to beforeUpdate or Validation rule property for this field is preventing Microsoft Access from saving the data field.
So I click on debug and it takes me to Me.Dirty = False.

This issue only seems to effect data entered when the "3" is entered in the shift box, which ids controlled by the afterupdate portion that the Me.Dirty = False was entered. Any ideas?
Mar 13 '18 #17

PhilOfWalton
Expert 100+
P: 1,373
OK, assuming that the Shift Number always has to be entered, run the date calculation on the AfterUpdate of the Shift

Phil
Mar 13 '18 #18

P: 96
Sweet, so far that is working. I had to remove the Me.dirty=False, because once I left the shift box it triggered the error. Once I did that I went through the whole process, closed the database down, then changed the info in the record and it excepted all changes with no issue.

Thanks again.
Mar 13 '18 #19

Post your reply

Sign in to post your reply or Sign up for a free account.