Referencing a field in a subform (MS Access)

Closed

PNyiam Posts 21 Registration date Tuesday July 17, 2018 Status Member Last seen September 5, 2018 - Updated on Aug 6, 2018 at 10:58 AM
PNyiam Posts 21 Registration date Tuesday July 17, 2018 Status Member Last seen September 5, 2018 - Aug 14, 2018 at 09:45 AM

Hello,

In MS Access, I have embedded a subform query into a task closure form that is linked to a task table. I was able to set the form so that any open task could be scrolled through and completed through the subform, however I would like to ensure that a user can't move on until they have completed all fields. The big challenge I am having is referencing the appropriate fields in the subform.

So, the behavior I am trying to get is as follows:
- If [Completion Date], [Time Worked (hours)], [Number of Employees Work] or [Completed By 1] = 0 or " "
- MsgBox "Please complete all fields."
- Else Save Record and Close Form

Code I am using all works except the if statement, (below):

Would really appreciate any help you can give on this. Also, ideally I would have the user select a record then input the info on text boxes in the main form rather than direct to the subform, if you can think of any way to incorporate this.

Note: I can't just make the fields required fields because the table is accessed by another form, where these fields will need to stay blank and would therefore error.

Thanks,

Referencing a field in a subform (MS Access)
Ms access download - Download - Databases
Phpmyadmin access denied - Guide
How to access google usa - Guide
Attrib access denied ✓ - Hard Drive & SSD Forum
Access and downloading - Guide

1 response

Answer 1 / 1

Blocked Profile
Updated on Aug 6, 2018 at 05:09 PM

OK, try this.

Initiate a variable called "mandotoryWorkTime", and set it to the the field you wish:

mandotoryWorkTime = forms!blahblahblah.textfield

Then check for mandotoryWorkTime in your logic statement.

If you wish to what the variable is set up as to troubleshoot programming, place this in place before the logic statement so you can see what the variable is set to:
msgbox(mandotoryWorkTime)

Then after you have verified the variable mandotoryWorkTime is actually what you expected, REM out the msgbox for later troubleshooting if needed.

Try that.

BTW, go ahead and place the HandleExit at the end, so you do not have to publish EXIT sub twice, the handle exit will just flow end, ending the sub.

PNyiam Posts 21 Registration date Tuesday July 17, 2018 Status Member Last seen September 5, 2018
Aug 7, 2018 at 04:39 AM

So I have added mandatoryWorkTime as a String variable and called it in a messagebox, I think I am going about referencing the cell all wrong though, can you tell me what this should look like? All of the names listed below.

Form = CompleteTaskForm
Subform = CompleteTaskSubform
Control? = Tasks subform
Table = Tasks
Field = Completed By 1 (Short text data type)

This is what the VBA currently looks like...

Thanks

Blocked Profile
Aug 7, 2018 at 04:31 PM

IN order to troubleshoot, make the form Field of "Completed By 1" into "CompletedByOne", or "Completed_By_1".

The messagebox will display what ever you have loaded into that new variable.

PNyiam Posts 21 Registration date Tuesday July 17, 2018 Status Member Last seen September 5, 2018
Aug 14, 2018 at 09:45 AM

Thanks for the suggestions...I managed to muddle through and figured out the error in my referencing with help from some guidance notes posted online by Allen Browne http://allenbrowne.com/casu-04.html This grew quite a bit due to unexpected behavior but have added my code below for reference...Perhaps you know a better way?

Private Sub CompleteTaskButton_Click()
On Error GoTo HandleError

If IsNull(Forms.CompleteTaskForm.CompleteTaskQuerySubform.Form.[Completion Date]) = True Then
MsgBox ("Please enter a completion date.")
Else
If IsNull(Forms.CompleteTaskForm.CompleteTaskQuerySubform.Form.[Time Worked (hours)]) = True Then
MsgBox ("Please enter number of hours worked.")
Else
If IsNull(Forms.CompleteTaskForm.CompleteTaskQuerySubform.Form.[Number of Employees Worked]) = True Then
MsgBox ("Please enter number of employees worked.")
Else
If IsNull(Forms.CompleteTaskForm.CompleteTaskQuerySubform.Form.[Completed By 1]) = True Then
MsgBox ("Please enter name or initials of all employees that worked on PM.")
Else
'Save the record
DoCmd.RunCommand acCmdSaveRecord

'Close the form
DoCmd.Close ObjectType:=acForm, ObjectName:=Me.Name, Save:=acSavePrompt
End If
End If
End If
End If

HandleExit:
Exit Sub

HandleError:
MsgBox Err.Description
Resume HandleExit
End Sub
_____________________________________________________________________________________________________________
Private Sub Form_Unload(Cancel As Integer)
If IsNull(Forms.CompleteTaskForm.CompleteTaskQuerySubform.Form.[Completion Date]) = True And IsNull(Forms.CompleteTaskForm.CompleteTaskQuerySubform.Form.[Time Worked (hours)]) = True And IsNull(Forms.CompleteTaskForm.CompleteTaskQuerySubform.Form.[Number of Employees Worked]) = True And IsNull(Forms.CompleteTaskForm.CompleteTaskQuerySubform.Form.[Completed By 1]) = True Then
userresponse = MsgBox("Are you sure you want close?", vbYesNo, "Database Information")
Select Case userresponse
Case 6
Cancel = False
Case 7
Cancel = True
'this line keeps my own form open in my own case
DoCmd.OpenForm "CompleteTaskForm"
End Select
Else
If IsNull(Forms.CompleteTaskForm.CompleteTaskQuerySubform.Form.[Completion Date]) = True Or IsNull(Forms.CompleteTaskForm.CompleteTaskQuerySubform.Form.[Time Worked (hours)]) = True Or IsNull(Forms.CompleteTaskForm.CompleteTaskQuerySubform.Form.[Number of Employees Worked]) = True Or IsNull(Forms.CompleteTaskForm.CompleteTaskQuerySubform.Form.[Completed By 1]) = True Then
MsgBox ("Please either complete Completion Date, Hours Worked, Employees Worked and Completed By, or clear any partial info added to these before closing.")
Cancel = True
DoCmd.OpenForm "CompleteTaskForm"
Else
'Save the record
DoCmd.RunCommand acCmdSaveRecord

Cancel = False
End If
End If
End Sub

Similar discussions

My facebook account is unable to access i need my account

How to gain access to my hacked facebook account

Can’t access settings for a few days lock out

HACKED FACEBOOK ACCOUNT

Hello, I have a problems on accessing to my Facebook account

Similar discussions

Subscribe To Our Newsletter!