Drill Down Function

[oldblindpew]

I'm trying to create a function that will open a detail form based on a field
value in a master form. I can do this using a private sub, but I hate
replicating the same private sub over and over again each time I want to
drill down from a different form or control. I also like being able to run a
function directly from the On Double Click event of any given control and
form without first having to go to a private sub.

The function would have to be told the name of the detail form to open, the
name of the key field in the detail form, and the value to search for.

Here's the event and the function that don't work:

=Drilldown("formInsPolGL",[GLpolID],"[PolicyID]=" & [Me].[GLpolID])

Function Drilldown(strFormName As String, FieldValue As Long, strWhereCond
As String)
If IsNull(FieldValue) Then
GoTo Exit_Drilldown
End If
DoCmd.OpenForm strFormName, strWhereCond
Exit_Drilldown:
On Error Resume Next
Exit Function

End Function

I apoligize for having to ask for help on what should be such a simple bit
of programming.

Thanks,
oldblindpew

 

[fredg]

I'm trying to create a function that will open a detail form based on a field
value in a master form. I can do this using a private sub, but I hate
replicating the same private sub over and over again each time I want to
drill down from a different form or control. I also like being able to run a
function directly from the On Double Click event of any given control and
form without first having to go to a private sub.

The function would have to be told the name of the detail form to open, the
name of the key field in the detail form, and the value to search for.

Here's the event and the function that don't work:

=Drilldown("formInsPolGL",[GLpolID],"[PolicyID]=" & [Me].[GLpolID])

Function Drilldown(strFormName As String, FieldValue As Long, strWhereCond
As String)
If IsNull(FieldValue) Then
GoTo Exit_Drilldown
End If
DoCmd.OpenForm strFormName, strWhereCond
Exit_Drilldown:
On Error Resume Next
Exit Function

End Function

I apoligize for having to ask for help on what should be such a simple bit
of programming.

Thanks,
oldblindpew

For one thing, you don't need to pass the separate value of [GLpolID]
(in the second argument) as you have already included it in the third
argument ... ("[PolicyID]=" & [Me].[GLpolID]).
Then you have placed the strWhereCond clause in the wrong position.

The where condition clause is the fourth argument not the second.

DoCmd.OpenForm strFormName, , ,strWhereCond

Your Where condition, as written, expects that [PolicyID] is a Number
datatype.

 

[oldblindpew]

Thanks for replying. I corrected the position of the strWhereCond argument,
but there was still a problem with 'Me'. I'm got an error message:

The expression On Dbl Click you entered as the event property setting
produced the following error: The object doesn't contain the Automation
object 'Me.'.

The third argument of the function was an attempt to pass the entire Where
condition as a string, complete with the name of the search field and its
value.

Next I tried this:

=Drilldown("formInsPolGL","[PolicyID]",[GLpolID])

Function Drilldown(strFormName As String, strFieldName As String, FieldValue
As Long)
' This function opens a detail form based on a field value in the main form.
Dim strWhereCond As String
If IsNull(FieldValue) Then
GoTo Exit_Drilldown
End If
strWhereCond = strFieldName & "=" & Me.FieldValue
DoCmd.OpenForm strFormName, , , strWhereCond
Exit_Drilldown:
On Error Resume Next
Exit Function

Of course this doesn't work either, because of improper use of Me. I'm
guessing at syntax, and guessing wrong, as usual.

Pew

I'm trying to create a function that will open a detail form based on a field
value in a master form. I can do this using a private sub, but I hate
replicating the same private sub over and over again each time I want to
drill down from a different form or control. I also like being able to run a
function directly from the On Double Click event of any given control and
form without first having to go to a private sub.

The function would have to be told the name of the detail form to open, the
name of the key field in the detail form, and the value to search for.

Here's the event and the function that don't work:

=Drilldown("formInsPolGL",[GLpolID],"[PolicyID]=" & [Me].[GLpolID])

Function Drilldown(strFormName As String, FieldValue As Long, strWhereCond
As String)
If IsNull(FieldValue) Then
GoTo Exit_Drilldown
End If
DoCmd.OpenForm strFormName, strWhereCond
Exit_Drilldown:
On Error Resume Next
Exit Function

End Function

I apoligize for having to ask for help on what should be such a simple bit
of programming.

Thanks,
oldblindpew

For one thing, you don't need to pass the separate value of [GLpolID]
(in the second argument) as you have already included it in the third
argument ... ("[PolicyID]=" & [Me].[GLpolID]).
Then you have placed the strWhereCond clause in the wrong position.

The where condition clause is the fourth argument not the second.

DoCmd.OpenForm strFormName, , ,strWhereCond

Your Where condition, as written, expects that [PolicyID] is a Number
datatype.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
.

 

[oldblindpew]

I've got it working, like this:

Dbl Click event property set to:
=Drilldown("formInsPolGL",[GLpolID],"[PolicyID]=" & [Screen].[ActiveControl])

Coding in Public Functions module (error handling not shown):
Function Drilldown(strFormName As String, FieldValue As Long, strWhereCond
As String)
' This function opens a detail form based on a field value in the main form.
If IsNull(FieldValue) Then
GoTo Exit_Drilldown
End If
DoCmd.OpenForm strFormName, , , strWhereCond
Exit_Drilldown:
On Error Resume Next
Exit Function
End Function

--Pew

 

[Steve Sanford]

Because you have defined FieldValue as a type long, it cannot be null. Well,
it can, but it will throw an error.

Try this:
in the double click event of a control:

=Drilldown("formInsPolGL",[ActiveControl].[Name],[ActiveControl])


The function:
'----------beg code-----------------
Function Drilldown(pstrFormName As String, pstrFieldName As String,
pFieldValue As Variant)

' This function opens a detail form based on a field value in the main form.
Dim strWhereCond As String

'check for text or null
If IsNull(pFieldValue) Or Not IsNumeric(pFieldValue) Then
Exit Function
End If

strWhereCond = "[" & pstrFieldName & "]" & "=" & pFieldValue
DoCmd.OpenForm pstrFormName, , , strWhereCond

Exit_Drilldown:
On Error Resume Next
Exit Function
End Function
'----------end code-----------------

Note: FieldValue ("FieldValue") is now a variant type

Note: I prefixed the arguments with "p" to know which variables are
arguments and which are defined within the function.


HTH

 

[oldblindpew]

Thanks for replying, Steve.

I had already discovered the problem with testing a long integer variable
for Null. Like much of Access, it doesn't make a lot of sense to me; I'm
just happy if it works. As I see it, I have a table with a long integer
foreign key field that starts out empty, so assume it must be Null. I want
to pass its value to a function so I define the argument variable as same
type. Once in the function, I test for Null, and get an error because the
type isn't Variant. Who decided a long integer field cannot be Null?

In your solution, it looks like the name of the active control is passed to
the function for use as the name of the field to search in the detail form's
recordset. I wouldn't expect these names to be identical. Many people use
the same field name for a primary key in one table and the foreign key in
another, in which case maybe you could pass the Control Source of the Active
Control?

In my case the field names are not identical. My arguments are 1) the name
of the form to open, 2) the value of the active control (the value to be
searched for), and 3) the name of the field to be searched. The Dbl-Click
event now looks like this:

=Drilldown("formInsPolGL",[Screen].[ActiveControl],"[PolicyID]")

and the code like this:

Function Drilldown(strFormName As String, FieldValue As Variant,
strFieldName As String)
' This function opens a detail form based on a field value in the main form.
On Error GoTo Err_Drilldown
Dim strWhereCond As String
If IsNull(FieldValue) Then
GoTo Exit_Drilldown
End If
strWhereCond = strFieldName & "=" & FieldValue
DoCmd.OpenForm strFormName, , , strWhereCond
Exit_Drilldown:
On Error Resume Next
Exit Function
Err_Drilldown:
MsgBox "Error #: " & Err & Chr(13) & Err.Description, _
Buttons:=vbCritical, title:="Contract Admin Database"
Resume Exit_Drilldown
Resume

End Function

--Pew

Because you have defined FieldValue as a type long, it cannot be null. Well,
it can, but it will throw an error.

Try this:
in the double click event of a control:

=Drilldown("formInsPolGL",[ActiveControl].[Name],[ActiveControl])


The function:
'----------beg code-----------------
Function Drilldown(pstrFormName As String, pstrFieldName As String,
pFieldValue As Variant)

' This function opens a detail form based on a field value in the main form.
Dim strWhereCond As String

'check for text or null
If IsNull(pFieldValue) Or Not IsNumeric(pFieldValue) Then
Exit Function
End If

strWhereCond = "[" & pstrFieldName & "]" & "=" & pFieldValue
DoCmd.OpenForm pstrFormName, , , strWhereCond

Exit_Drilldown:
On Error Resume Next
Exit Function
End Function
'----------end code-----------------

Note: FieldValue ("FieldValue") is now a variant type

Note: I prefixed the arguments with "p" to know which variables are
arguments and which are defined within the function.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

I've got it working, like this:

Dbl Click event property set to:
=Drilldown("formInsPolGL",[GLpolID],"[PolicyID]=" & [Screen].[ActiveControl])

Coding in Public Functions module (error handling not shown):
Function Drilldown(strFormName As String, FieldValue As Long, strWhereCond
As String)
' This function opens a detail form based on a field value in the main form.
If IsNull(FieldValue) Then
GoTo Exit_Drilldown
End If
DoCmd.OpenForm strFormName, , , strWhereCond
Exit_Drilldown:
On Error Resume Next
Exit Function
End Function

--Pew

 

[oldblindpew]

Thanks for your post, Steve.

I tried to reply, but it never showed up in the forum, so I'm trying again.

Actually I'd already discovered the problem of testing for Null in a Long
Integer variable, and "fixed" it by changing the variable type to Variant. I
don't understand this, why an integer cannot be Null. The field in the table
is long integer. It's a foreign key that matches an Autonumber key in the
parent table. Before any data is written to it, I assume it is Null. Maybe
the problem is not that an integer cannot be Null, but that Access doesn't
know how to pass a Null value to a function?? I encountered this same problem
in the past when testing Y/N fields or Check Boxes for Null, but never
thought the same thing would happen for numeric fields.

On another note, in your solution it looks like the name of the Active
Control is being passed to the function as the field to be searched in the
detail form's recordset. I don't see how this could work since these names
are not the same.

My arguments are now 1) the name of the detail form to be opened, 2) the
value of the Active Control (which is the value to search for), and 3) the
name of the field to search. I changed [Screen].[ActiveControl] to just
[ActiveControl]. Either way seems to work. The Dbl-Click event now looks
like this:

=Drilldown("formInsPolGL",[ActiveControl],"[PolicyID]")

and the function coding now looks like this:

Function Drilldown(strFormName As String, FieldValue As Variant,
strFieldName As String)
' This function opens a detail form based on a field value in the main form.
On Error GoTo Err_Drilldown
Dim strWhereCond As String
If IsNull(FieldValue) Then
GoTo Exit_Drilldown
End If
strWhereCond = strFieldName & "=" & FieldValue
DoCmd.OpenForm strFormName, , , strWhereCond
Exit_Drilldown:
On Error Resume Next
Exit Function
Err_Drilldown:
MsgBox "Error #: " & Err & Chr(13) & Err.Description, _
Buttons:=vbCritical, title:="Contract Admin Database"
Resume Exit_Drilldown
Resume

End Function

--Pew

Because you have defined FieldValue as a type long, it cannot be null. Well,
it can, but it will throw an error.

Try this:
in the double click event of a control:

=Drilldown("formInsPolGL",[ActiveControl].[Name],[ActiveControl])


The function:
'----------beg code-----------------
Function Drilldown(pstrFormName As String, pstrFieldName As String,
pFieldValue As Variant)

' This function opens a detail form based on a field value in the main form.
Dim strWhereCond As String

'check for text or null
If IsNull(pFieldValue) Or Not IsNumeric(pFieldValue) Then
Exit Function
End If

strWhereCond = "[" & pstrFieldName & "]" & "=" & pFieldValue
DoCmd.OpenForm pstrFormName, , , strWhereCond

Exit_Drilldown:
On Error Resume Next
Exit Function
End Function
'----------end code-----------------

Note: FieldValue ("FieldValue") is now a variant type

Note: I prefixed the arguments with "p" to know which variables are
arguments and which are defined within the function.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

I've got it working, like this:

Dbl Click event property set to:
=Drilldown("formInsPolGL",[GLpolID],"[PolicyID]=" & [Screen].[ActiveControl])

Coding in Public Functions module (error handling not shown):
Function Drilldown(strFormName As String, FieldValue As Long, strWhereCond
As String)
' This function opens a detail form based on a field value in the main form.
If IsNull(FieldValue) Then
GoTo Exit_Drilldown
End If
DoCmd.OpenForm strFormName, , , strWhereCond
Exit_Drilldown:
On Error Resume Next
Exit Function
End Function

--Pew

 

[Mike Painter]

Who
decided a long integer field cannot be Null?

Setting a number equal to Null is the same as setting a number equal to
"Betty"
Neither are numbers (at least in the computing world, Betty may be a hot
number in the real world.)

In general Basic is far to forgiving in many aspects. Most languages will
not let you say A=B if the types are different.
It requires an extra step to convert one to the other.

I like it, I live with it and I've learned the hard way what kind of poblems
this can cause.
It works for me in this environment but if I was in charge of a large group
of programmers Asic would be low on my list of languages.

 

[Steve]

In the function I was trying to get the control name to get the value to
search on. But if you got it to work, its good.

BTW, instead of using a "GoTo" (> GoTo Exit_Drilldown), just use
"Exit Function".

And in case that there are spaces in the field name, you should add
brackets in strWhere:

strWhereCond = "[" & pstrFieldName & "]" & "=" & pFieldValue

Just my 2 cents worth.....

Glad its working

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Thanks for replying, Steve.

I had already discovered the problem with testing a long integer variable
for Null. Like much of Access, it doesn't make a lot of sense to me; I'm
just happy if it works. As I see it, I have a table with a long integer
foreign key field that starts out empty, so assume it must be Null. I want
to pass its value to a function so I define the argument variable as same
type. Once in the function, I test for Null, and get an error because the
type isn't Variant. Who decided a long integer field cannot be Null?

In your solution, it looks like the name of the active control is passed to
the function for use as the name of the field to search in the detail form's
recordset. I wouldn't expect these names to be identical. Many people use
the same field name for a primary key in one table and the foreign key in
another, in which case maybe you could pass the Control Source of the Active
Control?

In my case the field names are not identical. My arguments are 1) the name
of the form to open, 2) the value of the active control (the value to be
searched for), and 3) the name of the field to be searched. The Dbl-Click
event now looks like this:

=Drilldown("formInsPolGL",[Screen].[ActiveControl],"[PolicyID]")

and the code like this:

Function Drilldown(strFormName As String, FieldValue As Variant,
strFieldName As String)
' This function opens a detail form based on a field value in the main form.
On Error GoTo Err_Drilldown
Dim strWhereCond As String
If IsNull(FieldValue) Then
GoTo Exit_Drilldown
End If
strWhereCond = strFieldName & "=" & FieldValue
DoCmd.OpenForm strFormName, , , strWhereCond
Exit_Drilldown:
On Error Resume Next
Exit Function
Err_Drilldown:
MsgBox "Error #: " & Err & Chr(13) & Err.Description, _
Buttons:=vbCritical, title:="Contract Admin Database"
Resume Exit_Drilldown
Resume

End Function

--Pew

Because you have defined FieldValue as a type long, it cannot be null. Well,
it can, but it will throw an error.

Try this:
in the double click event of a control:

=Drilldown("formInsPolGL",[ActiveControl].[Name],[ActiveControl])


The function:
'----------beg code-----------------
Function Drilldown(pstrFormName As String, pstrFieldName As String,
pFieldValue As Variant)

' This function opens a detail form based on a field value in the main form.
Dim strWhereCond As String

'check for text or null
If IsNull(pFieldValue) Or Not IsNumeric(pFieldValue) Then
Exit Function
End If

strWhereCond = "[" & pstrFieldName & "]" & "=" & pFieldValue
DoCmd.OpenForm pstrFormName, , , strWhereCond

Exit_Drilldown:
On Error Resume Next
Exit Function
End Function
'----------end code-----------------

Note: FieldValue ("FieldValue") is now a variant type

Note: I prefixed the arguments with "p" to know which variables are
arguments and which are defined within the function.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

I've got it working, like this:

Dbl Click event property set to:
=Drilldown("formInsPolGL",[GLpolID],"[PolicyID]=" & [Screen].[ActiveControl])

Coding in Public Functions module (error handling not shown):
Function Drilldown(strFormName As String, FieldValue As Long, strWhereCond
As String)
' This function opens a detail form based on a field value in the main form.
If IsNull(FieldValue) Then
GoTo Exit_Drilldown
End If
DoCmd.OpenForm strFormName, , , strWhereCond
Exit_Drilldown:
On Error Resume Next
Exit Function
End Function

--Pew