Full Version: Trouble With Dlookup
UtterAccess Forums > Microsoft® Access > Access Forms
foxtrot123
In the OnLoad event of a continuous subform, I define an array of form names like so:
!--c1-->
CODE
Private Sub Form_Load()
   aSubforms = Array("", "fsubBaseUnits", "fsubPrinters", "fsubMonitors")
End Sub

I use the array later (triggered in the OnCurrent event of the continuous subform) to determine which subform to show, by assigning the desired subform name as the sourceobject to an unbound subform named "sbfSubClass":
CODE
Private Function LoadSubform()
   Dim sSubForm As String
   sSubForm = aSubforms(Nz(ItemTypeID, 0))
   With Forms!frmCompanies!sbfSubClass
      If .SourceObject <> sSubForm Then
         .SourceObject = sSubForm
      End If
      .Requery
   End With
End Function

This works fine. But when I tried to store the subform names in a table, and use that instead of the array, I get this error:
When I debug with breakpoints the error occurs on the line, .SourceObject = sSubForm, right before the End If.
The way I redefined sSubForm was like:
CODE
sSubForm = DLookup("FormName", "tblItemTypes", "ItemTypeID = " & Nz(ItemTypeID, 0))

And Debug.Print sSubForm shows that the correct form is being assigned.
Any ideas why the DLookup technique isn't working?
MikeLyons
I don't think DLookup() is the problem.
The code didn't halt on a DLookup() call.
Mike
foxtrot123
True, but the only reason it decided to halt was when I tried to call the form name using DLookup instead of an array. This makes me think that the DLookup calls the form name in a way that's different than calling it from an array (even though Debug suggests the result is the same).
o perhaps this code isn't compatible with the DLookup technique, but is with the array technique. I'm just not sure why:
CODE
With Forms!frmCompanies!sbfSubClass
      If .SourceObject <> sSubForm Then
         .SourceObject = sSubForm
      ...
MikeLyons
Keep in mind DLookup() has nothing at all to do with forms. Essentially, it's a wrapper around the process of creating a recordset from a SELECT query.
would set a breakpoint on the code immediately after the DLookup() call completes and step through the code using F8 line by line to see what values are held in the variable sSubform
Make absolutely sure the value matches exactly the name of the form that is used as the subform.
Mike
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.