Passing fieldname to a subroutine

[bgreenspan]

I have a form with a subform. In the subform I have several fields to
which I am assigning links to documents on the file server. I learned
(from this list :-) how to add the links. Because I will be executing
the code for four or five fields, I would like to have a generic
subroutine to pass the field name parameters to. I have searched and
tried many formats and syntaxes for this with no success.

Here are the principle lines from the routine that I wish to make more
general

lnkApplication = varFile & "#" & varFile ' puts the hyperlink in the
field.

lnkApplication.Visible = Not (IsNull(lnkApplication.Value)) ' only
shows the field on the form if it has a path in it

lnkApplication is the actual name of the field in a table called
tblFamilyMembers.

Please help me with the syntax to pass the fieldnames to a general
subroutine and to modify the lines above. Also, please let me know if
there are any additional lines of code needed to make it work.

Thanks,
Bernie

[pietlinden]

haven't a clue what your example is on about, but the way to pass info
into a subroutine is pretty simple...

Sub MySubroutine(byval strFieldName as string)
dim strSQL as string
strSQL = "SELECT * FROM MyTable WHERE [" & strFieldName & "] =
'X';"
DBEngine(0)(0).Querydefs("qdfTemp").SQL = strSQL
End Sub

Does that help?

[Terry Kreft]

I'm guessing that lnkApplication is also the control on your form, if so
you can pass a reference to the control

e.g.

Sub wibble(MyControl as Ontrol)
' Stuff you've already got

With MyControl
.Value = varFile & "#" & varFile
.Visible = Not (IsNull(.Value))
End With
End Sub
And you'd call it with
Call wibble(Me.lnkApplication)


--

Terry Kreft
<bg********@gmail.comwrote in message
news:11*********************@m73g2000cwd.googlegro ups.com...

I have a form with a subform. In the subform I have several fields to
which I am assigning links to documents on the file server. I learned
(from this list :-) how to add the links. Because I will be executing
the code for four or five fields, I would like to have a generic
subroutine to pass the field name parameters to. I have searched and
tried many formats and syntaxes for this with no success.

Here are the principle lines from the routine that I wish to make more
general

lnkApplication = varFile & "#" & varFile ' puts the hyperlink in the
field.

lnkApplication.Visible = Not (IsNull(lnkApplication.Value)) ' only
shows the field on the form if it has a path in it

lnkApplication is the actual name of the field in a table called
tblFamilyMembers.

Please help me with the syntax to pass the fieldnames to a general
subroutine and to modify the lines above. Also, please let me know if
there are any additional lines of code needed to make it work.

Thanks,
Bernie

[David W. Fenton]

"Terry Kreft" <te*********@mps.co.ukwrote in
news:Ky********************@karoo.co.uk:

Sub wibble(MyControl as Ontrol)
' Stuff you've already got

With MyControl
.Value = varFile & "#" & varFile
.Visible = Not (IsNull(.Value))
End With
End Sub

The function declaration should be:

Sub wibble(MyControl as Control)

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Terry Kreft]

Whoops, thanks David.

I hate this laptop.

--

Terry Kreft
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...

"Terry Kreft" <te*********@mps.co.ukwrote in
news:Ky********************@karoo.co.uk:

Sub wibble(MyControl as Ontrol)
' Stuff you've already got

With MyControl
.Value = varFile & "#" & varFile
.Visible = Not (IsNull(.Value))
End With
End Sub

The function declaration should be:

Sub wibble(MyControl as Control)

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[bgreenspan]

I ended up passing the field name as a string and using

Me(strFieldName) = varFile & "#" & varFile

and later on

Me(strFieldName).Visible = Not (IsNull(Me(strFieldName).Value))

I'll try the code you have suggested because I think it will be simpler
than all of the code surrounding the lines I showed you.

I have been using straight Visual Basic for about 12 years and have
written VBA for Excel in the past. This is my first attempt at VBA
with Access and I find myself needing to lookup the syntax for
database-specific routines.

Is there a nice concise (not-written-by-MS) summary of how to write
syntax for Forms, Tables, Fields, etc.? My biggest hang up seems to be
choosing between ! and .

Thank you for your prompt and helpful responses above!

-Bernie

[Lyle Fairfield]

Is there a nice concise (not-written-by-MS) summary of how to write

syntax for Forms, Tables, Fields, etc.? My biggest hang up seems to be
choosing between ! and .

Simple Solution. Don't choose. I have used a bang (!) for years and
years.

[David W. Fenton]

bg********@gmail.com wrote in
news:11**********************@75g2000cwc.googlegro ups.com:

Is there a nice concise (not-written-by-MS) summary of how to
write syntax for Forms, Tables, Fields, etc.? My biggest hang up
seems to be choosing between ! and .

If it's a control or a field, use !. If it's a property or a method,
use .

Or, if you want to depend on undocumented wrapper properties created
for you behind the scenes in Access, you can use . too for controls
and fields in forms and reports. But you can't use it for fields in
a recordset.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Randy Harris]

* bg********@gmail.com:

I ended up passing the field name as a string and using

Me(strFieldName) = varFile & "#" & varFile

and later on

Me(strFieldName).Visible = Not (IsNull(Me(strFieldName).Value))

I'll try the code you have suggested because I think it will be simpler
than all of the code surrounding the lines I showed you.

I have been using straight Visual Basic for about 12 years and have
written VBA for Excel in the past. This is my first attempt at VBA
with Access and I find myself needing to lookup the syntax for
database-specific routines.

Is there a nice concise (not-written-by-MS) summary of how to write
syntax for Forms, Tables, Fields, etc.? My biggest hang up seems to be
choosing between ! and .

Thank you for your prompt and helpful responses above!

-Bernie

I'm not entirely certain this is what you had in mind, but this is what
I use:

http://www.mvps.org/access/forms/frm0031.htm

HTH
--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

[Lyle Fairfield]

Lyle Fairfield wrote:

Is there a nice concise (not-written-by-MS) summary of how to write
syntax for Forms, Tables, Fields, etc.? My biggest hang up seems to be
choosing between ! and .

Simple Solution. Don't choose. I have used a bang (!) for years and
years.

Correction:

I have not used a bang (!) for years and years.

[bgreenspan]

>

http://www.mvps.org/access/forms/frm0031.htm

The reference is exactly what I was looking for. Thanks once again.
- Bernie

[Larry Linson]

Lyle Fairfield wrote:

I have not used a bang (!) for years and years.

You don't say! Surely you emphasize statements! Emphasis requires bangs!

:-)

[Lyle Fairfield]

Larry Linson wrote:

Lyle Fairfield wrote:

I have not used a bang (!) for years and years.

You don't say! Surely you emphasize statements! Emphasis requires bangs!

Naah ... in these days of rtf, html etc, I use a small image of your
face, Larry, superimposed with the words, "44 Magnum".

[David W. Fenton]

"Lyle Fairfield" <ly***********@aim.comwrote in
news:11**********************@b28g2000cwb.googlegr oups.com:

Lyle Fairfield wrote:

Is there a nice concise (not-written-by-MS) summary of how to
write syntax for Forms, Tables, Fields, etc.? My biggest hang
up seems to be choosing between ! and .

Simple Solution. Don't choose. I have used a bang (!) for years
and years.

Correction:

I have not used a bang (!) for years and years.

Do ADO recordsets allow the use of the . for fields?

If so, that's another reason I'm glad I've never embarked on more
than just dabbling in ADO.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Lyle Fairfield]

David W. Fenton wrote:

Do ADO recordsets allow the use of the . for fields?

Well I'm not sure what you mean. We can use any of these to get an
object pointer to a ADO field:

objField = objRecordset.Fields.Item("LastName")
objField = objRecordset.Fields("LastName")
objField = objRecordset.Fields.Item(1)
objField = objRecordset.Fields(1)

and in VBA it's likely we will need to use the SET operator as in
SET objField = objRecordset.Fields.Item("LastName")
but this is a peculiarity of VBA and not ADO.

There are many ways to retrieve the value of an ADO field:

Dim objRecordset As ADODB.Recordset
Set objRecordset = CurrentProject.Connection.Execute("SELECT * FROM
Employees")
Debug.Print objRecordset.Fields("LastName")
Debug.Print objRecordset.Fields("LastName").Value
Debug.Print objRecordset.Fields(1)
Debug.Print objRecordset.Fields(1).Value
Debug.Print objRecordset.Collect("LastName")
Debug.Print objRecordset!LastName

I frequently use
..Collect("LastName")
TTBOMK this is the fastest of all showed above.

But Dimitri Furman has pointed out that this is faster (tested by
several in a thread some years ago):
Dim objRecordset As ADODB.Recordset
Dim objField As ADODB.Field
Set objRecordset = CurrentProject.Connection.Execute("SELECT * FROM
Employees")
Set objField = objRecordset.Fields("LastName")
Debug.Print objField.Value
and I use it if there are going to be enough uses of the objField
pointer to offset the times and resources needed for its creation.

I never use
objRecordset!LastName
I never have to choose; my code is consistent;
[!] like [.Collect] short circuits and obscures the Field(Index).Value
OOP reference
TTBOMR Intellisense does not help with !
MS wizards, help files and kb articles write some bizarre and lengthy
combinations of [.] and [!]. I think these are confusing to many.

TTBOMK all of the above (with the exception of loading the recordset)
is identical in DAO.