By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,308 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Passing fieldname to a subroutine

P: n/a
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

Jul 16 '06 #1
Share this Question
Share on Google+
14 Replies


P: n/a
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?

Jul 16 '06 #2

P: n/a
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

Jul 16 '06 #3

P: n/a
"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/
Jul 16 '06 #4

P: n/a
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/

Jul 16 '06 #5

P: n/a
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

Jul 16 '06 #6

P: n/a
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.

Jul 16 '06 #7

P: n/a
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/
Jul 17 '06 #8

P: n/a
* 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.
Jul 17 '06 #9

P: n/a
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.

Jul 17 '06 #10

P: n/a
>
http://www.mvps.org/access/forms/frm0031.htm
The reference is exactly what I was looking for. Thanks once again.
- Bernie

Jul 17 '06 #11

P: n/a
Lyle Fairfield wrote:
I have not used a bang (!) for years and years.
You don't say! Surely you emphasize statements! Emphasis requires bangs!

:-)
Jul 17 '06 #12

P: n/a

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".

Jul 17 '06 #13

P: n/a
"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/
Jul 17 '06 #14

P: n/a
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.

Jul 18 '06 #15

This discussion thread is closed

Replies have been disabled for this discussion.