Passing fieldname to a subroutine

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

re: Passing fieldname to a subroutine

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?

re: Passing fieldname to a subroutine

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


<bgreenspan@gmail.comwrote in message
news:1153018677.217863.54460@m73g2000cwd.googlegro ups.com...

re: Passing fieldname to a subroutine

"Terry Kreft" <terry.kreft@mps.co.ukwrote in
news:Ky-dnSjBx66igCfZSa8jmw@karoo.co.uk:
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/

re: Passing fieldname to a subroutine

Whoops, thanks David.

I hate this laptop.



--

Terry Kreft


"David W. Fenton" <XXXusenet@dfenton.com.invalidwrote in message
news:Xns98024E5C923C7f99a49ed1d0c49c5bbb2@127.0.0. 1...

re: Passing fieldname to a subroutine

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

re: Passing fieldname to a subroutine

Is there a nice concise (not-written-by-MS) summary of how to writeSimple Solution. Don't choose. I have used a bang (!) for years and
years.

re: Passing fieldname to a subroutine

bgreenspan@gmail.com wrote in
news:1153081084.549122.165080@75g2000cwc.googlegro ups.com:
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/

re: Passing fieldname to a subroutine

* bgreenspan@gmail.com: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.

re: Passing fieldname to a subroutine

Lyle Fairfield wrote:Correction:

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

re: Passing fieldname to a subroutine

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

re: Passing fieldname to a subroutine

Lyle Fairfield wrote:
You don't say! Surely you emphasize statements! Emphasis requires bangs!

:-)

re: Passing fieldname to a subroutine

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

re: Passing fieldname to a subroutine

"Lyle Fairfield" <lylefairfield@aim.comwrote in
news:1153135195.840288.226220@b28g2000cwb.googlegr oups.com:
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/

re: Passing fieldname to a subroutine

David W. Fenton wrote: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.