Referencing values in a subform from the main form. 1

[fiel]

I have a main that the user can choose a number from a combo box and the form will then only show records for a task relating to the selected number. The records displayed are shown is the category 'SUBTASK' in the subform 'sfrmAllTasks' and 'frmEIStaff'. Clicking on a command button will preview a report of the currently listed task. It works fine opening a task with filled records, but in tasks without a filled record, I won't want any preview to take place. Part of my code is as follows and is actually all on one line.

Is there a way to use the NZ and DLookup to find if there are any current records available? I'm not sure if I'm on the right track here. Perhaps my syntax is not the only issue?

If Nz(
DLookup(
"[sfrmAllTasks.SUBTASK]",
"tblRoleInfo",
"[sfrmAllTasks.SUBTASK]='" &
[frmEIStaff1.SUBTASK] & "'"), "0") = "0" Then
Result =
MsgBox("There is no record for this
subtask. Total Hours for all Staff Members
is 0.", vbOKOnly, "No Records Exist")

 

[PHV]

Does tblRoleInfo really have a field named sfrmAllTasks.SUBTASK ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fiel]

tblRoleInfo has a field name SUBTASK, but the subform sfrmAllTasks used a textbox with the same name.

 

[PHV]

DLookUp read field values from table

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fiel]

I tried the following but am still getting errors, my code is as follows:

DLookup("SUBTASK", "tblRoleInfo", "[sfrmAllTasks.SUBTASK]='" & [frmEIStaff1.SUBTASK] & "'"), "0"

I tried googling other ways and tried this:

Dim isEmpty As Integer

isEmpty = sfrmEIStaff1.NewRecord

If isEmpty = True Then
Result = MsgBox("There is no record for this
subtask. Total Hours for all Staff Members
is
0.", vbOKOnly, "No Records Exist")

End If
End Sub

I get errors on this too regarding an object reference. Is there something I could do with this in the coding for my command button?

 

[PHV]

Again, DLookUp looks at fields in table, even for the criteria !
What about this ?
DLookup("SUBTASK", "tblRoleInfo", "SUBTASK='" & Me![SUBTASK] & "'")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fiel]

Since the values for SUBTASK are listed in the subform, does the last part of the DLookup need to be changed for it instead of Me![SUBTASK]?

 

[fiel]

Oh wait I got it, just changed me to the sub form's name.

Thanks PVH!