SUBFORM not DISPLAYED when MAINFORM is opened with CODE

[ZaZa]

I have a database that tracks the amount of open technical issues in an engineering department. Open issues are called PunchItems.

I have a MAINFORM based on a table tblPUNCHITEMS with the following filed:

tblPunchItems
---------------
PunchItemID (AutoNumber)
Title (Text)
PersonAssignedTo (Number)

On the main form, there is a subform called COMMENTS that records comments from various engineers about the PunchItems. The COMMENTS subform is based on the table tblCOMMENTS with the following fields:

tblComments
-------------

CommentID (AutoNumber)
PunchItemID (Number)
PersonCommentingID (Number)
Comments (Memo)

Now when I open the mainform directly (without code) the subform comes up displaying the correct comments for a particular punchitem.

PROBLEM is when I created a form called DIALOGUEFORM so that a user could select an employee from a combo box called EMPLOYEENAME and then click on a command button called ShowHisIssues to open the MIANFORM. The MAIN FORM would be filtered to show only the punch items assigned to the selected employee.


PROBLEM:
After selecting the employee and clicking on the ShowHisIssues button, the main form comes up, correctly filtered BUT WITH THE SUBFORM NOT DISPLAYED ie BLANK.

I have pasted the code for the on clock event of the ShowHisIssues button.

WHERE HAVE I GONE WRONG?? Never had this problem before.
Any help/Advice will be appreciated.
--------------------------------------
Private Sub ShowHisIssues_Click()

On Error GoTo Err_ShowHisIssues_Click

Dim stDocName As String
Dim stLinkCriteria As Variant

' Check if user entered a name.
If IsNull(EmployeeName) Then
DisplayMessage "You must select or enter your name."
EmployeeName.SetFocus
Exit Sub
End If

stDocName = "MAINFORM"
stLinkCriteria = "[PersonAssignedTo]=" & Me![EmployeeName]

' Check whether the user has any issues. If they do, open the Issues
' form with a filter; otherwise, open it without a filter.
If DCount("PunchItemID", "tblPunchItems", stLinkCriteria) Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
DisplayMessage "No issues assigned to you -- displaying all issues."
DoCmd.OpenForm stDocName
End If

' Close the dialog box.
DoCmd.Close acForm, "DialogueForm"

Exit_ShowHisIssues_Click:
Exit Sub

Err_ShowHisIssues_Click:
MsgBox Err.Description
Resume Exit_ShowHisIssues_Click

End Sub

------------------------------------

Thanks,
ZaZa

 

[ZaZa]

Anyone?? :-(

ZaZa

 

[Lightning]

When a form/subform combination is first loaded, the subform actually loads FIRST, then the main form. Since your criteria is set for the main form, the subform does not have any criteria to display when it first loads.

The solution is to requery the main form after it loads. This should then display the correct information in the subform.

HTH

Lightning

 

[ZaZa]

Lightning,

Thanks for responding. You say to requery the main form AFTER it Loads.

I put the command:
MAINFORM.requery
on the ONLOAD event for the main form and also on the line of code just after the follow code:
DoCmd.OpenForm stDocName, , , stLinkCriteria

The subform still comes up blank. But maybe I am not understanding how to requery the form itself. CAn you explain more?

Thanks,
ZaZa

 

[ZaZa]

Everyone,

I just tried the openform command without using the string criteria and It worked fine - the subform was displayed with the subform.When I put back in the stCriteria, the subform was blank.

So this does suggest that the problem involves the strCritreria.That all the progress I have made so far.

Anymore help will be greatly appreciated.
ZAZA

 

[Lightning]

Use the format

Forms!MainForm.Requery

for the requery command after the OpenForm command line.

HTH

Lightning

 

[ZaZa]

Hi

I tried the format:
Forms!MainForm.Requery
as lightning suggested but to no avail. ANy more ideas anyone??

Thanks,
ZaZa

 

[ZaZa]

Anyone?

ZaZa

 

[Lightning]

Hi ZaZa

That code should work. I use this tecnique in a database that tracks legal matters, and it works without problem.

So, what else could be causing this behaviour? You say that the subform displays correctly without the filter criteria. The only thing I can see in your criteria that might be causing a problem is the Me keyword. Try replacing me with the full form name

stLinkCriteria = "[PersonAssignedTo]=" & Forms!DialogueForm![EmployeeName]

The only other thing I can suggest at this stage is to double-check your controlnames to ensure there are no typos.

Never Give Up!

Lightning

 

[ZaZa]

Hi Lightninig!!!!!!!! :-D :-D


I finally figured out the problem.

I created a new mainform with subform and tried to apply the stLinkCriteria. I got a message saying that it could not apply the filter because all the records were locked.( Now I didn't get that message with the original forms)

I went back to my original main form and set the records locking to NO LOCKS and also in the advanced tab in the options menus.

After that my subform was displayed.

I STILL DON'T UNDERSTAND WHY THE RECORD LOCKING WOULDS WAS MESSING THINGS UP. If anyone knows, please share.

Lightninig, thanks for responding and giving your advice,
ZaZa