MS Access subform not displaying records and not navigating

Question

I have a one-to-one relationship between an SLD table and an ORDER table. The SLD table is the main form, ORDER table is the subform. The main form's table has more records than the subform table. Master and child links are set. I have decompiled and have done compact and repair. I suspect my application is malfunctioning. In that case it is not the first time (I have fixed it before). Nothing seems to be working this time. I have also imported into a new database.

On form-load both forms and fields are working fine. I continue to the next records using a ['NEXT'] button and reach the end of the sub-forms records therefore having blanks fields. I try to navigate to the previous records (also using a button) but the subform does not move/navigate. Nothing happens to the subform.

It seems like it has gotten worse because first it was navigating but it was not obeying code saying when text box is filled disable checkbox and vise versa.

What is malfunctioning exactly? Is the subform showing no data? Is it showing data but showing blank records? Try getting the subform on the parent form without any links. Once you have it working, then add the links to delimit the data. — Pants, May 15 '17 at 12:41
The subform shows data only until it goes blank (where I have to add a new record). When I try and navigate back to that data, it does not navigate at all - it stays blank. — Marcus Mackaku, May 15 '17 at 13:01
I see that you are using a custom next button, try using the default record navigation tools at the bottom of the subform, does this result in the same issue occurring? If not, then your issue lies within your next button — Pants, May 15 '17 at 14:16
Yes "the default record navigation tools" work fine. I was concerned about the users experience when using them so I set them to 'No'. Do they hamper users experience? — Marcus Mackaku, May 16 '17 at 07:01
It seems like this code is disturbing my subforms navigation: If (IsNull(Forms!Order!OrderSubform.Form!txtDate_Of_Order.Value)) Then Forms!Order!OrderSubform.Form!txtDate_Of_Order.Enabled = False Forms!Order!OrderSubform.Form!chkbxOrder_Cancelled.Enabled = True Forms!Order!OrderSubform.Form!chkbxOrder_Cancelled.Value = True Else Forms!Order!OrderSubform.Form!txtDate_Of_Order.Enabled = True Forms!Order!OrderSubform.Form!chkbxOrder_Cancelled.Enabled = False Forms!Order!OrderSubform.Form!chkbxOrder_Cancelled.Value = False End If — Marcus Mackaku, May 16 '17 at 07:17
They only hamper the User's experience if you don't provide the appropriate alternative to the feature. in some use cases you truly don't need them. — Pants, May 16 '17 at 12:52

score 0 · Answer 1 · answered May 16 '17 at 13:02

From the sounds of it the issue is in your Next button rather than it being a subform issue. As you stated in the comments the default navigation allows the form to work as expected.

Is this logic out in your Previous button OnClick Event? Also to be safe i would add in the OR statement in the below code to be extra sure that its catching nulls or blanks

If (IsNull(Forms!Order!OrderSubform.Form!txtDate_Of_Order.Value‌)) OR Forms!Order!OrderSubform.Form!txtDate_Of_Order.Value‌ <> "" Then 
    Forms!Order!OrderSubform.Form!txtDate_Of_Order.Enabled = False 
    Forms!Order!OrderSubform.Form!chkbxOrder_Cancelled.Enabled = True 
    Forms!Order!OrderSubform.Form!chkbxOrder_Cancelled.Value = True 
Else 
    Forms!Order!OrderSubform.Form!txtDate_Of_Order.Enabled = True 
    Forms!Order!OrderSubform.Form!chkbxOrder_Cancelled.Enabled = False 
    Forms!Order!OrderSubform.Form!chkbxOrder_Cancelled.Value = False 
End If

score 0 · Accepted Answer · answered May 18 '17 at 12:40

I found out that the If statement belonged in Form_Current event. I am not experiencing the problem anymore after this code:

Private Sub Form_Current()

If (IsNull(Forms!Order!OrderSubform.Form!txtOrder_Number.Value) Or _
    Forms!Order!OrderSubform.Form!txtOrder_Number.Value = "") Then
        Forms!Order!OrderSubform.Form!txtDate_Of_Order.Enabled = True
        Forms!Order!OrderSubform.Form!chkbxOrder_Cancelled.Enabled = True
ElseIf (IsNull(Forms!Order!OrderSubform.Form!txtDate_Of_Order.Value) Or _
    Forms!Order!OrderSubform.Form!txtDate_Of_Order.Value = "") Then
        Forms!Order!OrderSubform.Form!txtDate_Of_Order.Enabled = False
        Forms!Order!OrderSubform.Form!chkbxOrder_Cancelled.Enabled = True
        Forms!Order!OrderSubform.Form!chkbxOrder_Cancelled.Value = True
Else
    Forms!Order!OrderSubform.Form!txtDate_Of_Order.Enabled = True
    Forms!Order!OrderSubform.Form!chkbxOrder_Cancelled.Enabled = False
    Forms!Order!OrderSubform.Form!chkbxOrder_Cancelled.Value = False
End If

End Sub

MS Access subform not displaying records and not navigating

2 Answers2