Field name as a parameter

J

JK

Hi to all,

I am struggling with 2 problems which I'm sure there are simple solutions to
them.

Question1
-----------
I'm trying to write a function which reads an object whereby the object name
and the field name are supplied parameters:

Public Function MyFunction(ValueX as variant, FieldName as ????, ObjectName
as string, optional ObjectType as Byte=acQuery) as variant

Dim X as Double, ......

Select Case ObjectType
.......
Case 1
DoCmd.OpenQuery ObjectName
.......
End Select

DoCmd.GoToRecord, ,acFirst

Do

'Read Filled "FieldName"
X= ?????? 'How do I do it ???? ********
...
DoCmd.GoToRecord, ,acNext
Loop
.............
..
End Function

I have no problem with forms, I use X= Forms(ObjectName)(FieldName) but I
am struggling with tables and queries, I'm looking for something like
&ObjectName in the good old dBase. I tried "[" & FieldName & "]" but it does
not work

How do I do it?



Question 2
-------------
How to I get out of the Do/Loop when I reach the last record in the above
example e.g:
Do While ?????

Regards/JK
 
S

strive4peace

Hi JK,

I believe this code answers both of your questions...

'~~~~~~~~~~~~~~~~

Function LoopAndCombine( _
pTablename As String, _
pIDFieldname As String, _
pTextFieldname As String, _
pValueID As Long, _
Optional pWhere As String, _
Optional pDeli As String, _
Optional pNoValue as string) As String

'crystal 8-3-06
'strive4peace2006 at yahoo dot com

'NEEDS REFERENCE
'Microsoft DAO Library

'PARAMETERS
'pTablename --> tablename to get list from
'pIDFieldname --> fieldname to link on (ie: "BookID")
'pTextFieldname --> fieldname to combine (ie: "PageNumber")
'pValueID --> actual value of ID for this iteration ( ie: [BookID])
'pWhere, Optional --> more criteria (ie: "Year(PubDate) = 2006")
'pDeli, Optional --> delimiter other than comma (ie: ";")
'pNoValue, Optional --> value to use if no data (ie: "No Pages")

'Set up error handler
On Error GoTo Proc_Err

'dimension variables
Dim r As dao.Recordset, mAllValues As String, S As String
Dim mValueDeli As String

If Len(nz(pDeli,"")) > 0 Then _
mValueDeli = pDeli Else mValueDeli = ","

mAllValues = ""

S = "SELECT [" & pTextFieldname & "] " _
& " FROM [" & pTablename & "]" _
& " WHERE [" & pIDFieldname _
& "] = " & pValueID _
& IIf(Len(pWhere) > 0, " AND " & pWhere, "") _
& ";"

'open the recordset
Set r = CurrentDb.OpenRecordset(S, dbOpenSnapshot)

'loop through the recordset until the end
Do While Not r.EOF
If Not IsNull(r(pTextFieldname)) Then
mAllValues = mAllValues _
& " " & r(pTextFieldname) & mValueDeli
End If
r.MoveNext
Loop

If len(mAllValues) = 0 then
mAllValues = nz(pNoValue,"")
end if


Proc_Exit:
'close the recordset
r.Close
'release the recordset variable
Set r = Nothing

LoopAndCombine = Trim(mAllValues)
Exit Function

'if there is an error, the following code will execute
Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " LoopAndCombine"

'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume
Resume Proc_Exit
End Function

'~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Hi to all,

I am struggling with 2 problems which I'm sure there are simple solutions to
them.

Question1
-----------
I'm trying to write a function which reads an object whereby the object name
and the field name are supplied parameters:

Public Function MyFunction(ValueX as variant, FieldName as ????, ObjectName
as string, optional ObjectType as Byte=acQuery) as variant

Dim X as Double, ......

Select Case ObjectType
.......
Case 1
DoCmd.OpenQuery ObjectName
.......
End Select

DoCmd.GoToRecord, ,acFirst

Do

'Read Filled "FieldName"
X= ?????? 'How do I do it ???? ********
...
DoCmd.GoToRecord, ,acNext
Loop
.............
.
End Function

I have no problem with forms, I use X= Forms(ObjectName)(FieldName) but I
am struggling with tables and queries, I'm looking for something like
&ObjectName in the good old dBase. I tried "[" & FieldName & "]" but it does
not work

How do I do it?



Question 2
-------------
How to I get out of the Do/Loop when I reach the last record in the above
example e.g:
Do While ?????

Regards/JK
Click to expand...
 
J

JK

Wahoo Crystal

And I thought that there is a simple solution :)
Going to work on it and will report back

Many Thanks/JK



strive4peace said:
Hi JK,

I believe this code answers both of your questions...

'~~~~~~~~~~~~~~~~

Function LoopAndCombine( _
pTablename As String, _
pIDFieldname As String, _
pTextFieldname As String, _
pValueID As Long, _
Optional pWhere As String, _
Optional pDeli As String, _
Optional pNoValue as string) As String

'crystal 8-3-06
'strive4peace2006 at yahoo dot com

'NEEDS REFERENCE
'Microsoft DAO Library

'PARAMETERS
'pTablename --> tablename to get list from
'pIDFieldname --> fieldname to link on (ie: "BookID")
'pTextFieldname --> fieldname to combine (ie: "PageNumber")
'pValueID --> actual value of ID for this iteration ( ie: [BookID])
'pWhere, Optional --> more criteria (ie: "Year(PubDate) = 2006")
'pDeli, Optional --> delimiter other than comma (ie: ";")
'pNoValue, Optional --> value to use if no data (ie: "No Pages")

'Set up error handler
On Error GoTo Proc_Err

'dimension variables
Dim r As dao.Recordset, mAllValues As String, S As String
Dim mValueDeli As String

If Len(nz(pDeli,"")) > 0 Then _
mValueDeli = pDeli Else mValueDeli = ","

mAllValues = ""

S = "SELECT [" & pTextFieldname & "] " _
& " FROM [" & pTablename & "]" _
& " WHERE [" & pIDFieldname _
& "] = " & pValueID _
& IIf(Len(pWhere) > 0, " AND " & pWhere, "") _
& ";"

'open the recordset
Set r = CurrentDb.OpenRecordset(S, dbOpenSnapshot)

'loop through the recordset until the end
Do While Not r.EOF
If Not IsNull(r(pTextFieldname)) Then
mAllValues = mAllValues _
& " " & r(pTextFieldname) & mValueDeli
End If
r.MoveNext
Loop

If len(mAllValues) = 0 then
mAllValues = nz(pNoValue,"")
end if


Proc_Exit:
'close the recordset
r.Close
'release the recordset variable
Set r = Nothing

LoopAndCombine = Trim(mAllValues)
Exit Function

'if there is an error, the following code will execute
Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " LoopAndCombine"

'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume
Resume Proc_Exit
End Function

'~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Hi to all,

I am struggling with 2 problems which I'm sure there are simple solutions
to them.

Question1
-----------
I'm trying to write a function which reads an object whereby the object
name and the field name are supplied parameters:

Public Function MyFunction(ValueX as variant, FieldName as ????,
ObjectName as string, optional ObjectType as Byte=acQuery) as variant

Dim X as Double, ......

Select Case ObjectType
.......
Case 1
DoCmd.OpenQuery ObjectName
.......
End Select

DoCmd.GoToRecord, ,acFirst

Do

'Read Filled "FieldName"
X= ?????? 'How do I do it ???? ********
...
DoCmd.GoToRecord, ,acNext
Loop
.............
.
End Function

I have no problem with forms, I use X= Forms(ObjectName)(FieldName) but
I am struggling with tables and queries, I'm looking for something like
&ObjectName in the good old dBase. I tried "[" & FieldName & "]" but it
does not work

How do I do it?



Question 2
-------------
How to I get out of the Do/Loop when I reach the last record in the
above example e.g:
Do While ?????

Regards/JK
Click to expand...
Click to expand...
 
J

JK

Works like a charm Crystal
Many thanks
JK

strive4peace said:
Hi JK,

I believe this code answers both of your questions...

'~~~~~~~~~~~~~~~~

Function LoopAndCombine( _
pTablename As String, _
pIDFieldname As String, _
pTextFieldname As String, _
pValueID As Long, _
Optional pWhere As String, _
Optional pDeli As String, _
Optional pNoValue as string) As String

'crystal 8-3-06
'strive4peace2006 at yahoo dot com

'NEEDS REFERENCE
'Microsoft DAO Library

'PARAMETERS
'pTablename --> tablename to get list from
'pIDFieldname --> fieldname to link on (ie: "BookID")
'pTextFieldname --> fieldname to combine (ie: "PageNumber")
'pValueID --> actual value of ID for this iteration ( ie: [BookID])
'pWhere, Optional --> more criteria (ie: "Year(PubDate) = 2006")
'pDeli, Optional --> delimiter other than comma (ie: ";")
'pNoValue, Optional --> value to use if no data (ie: "No Pages")

'Set up error handler
On Error GoTo Proc_Err

'dimension variables
Dim r As dao.Recordset, mAllValues As String, S As String
Dim mValueDeli As String

If Len(nz(pDeli,"")) > 0 Then _
mValueDeli = pDeli Else mValueDeli = ","

mAllValues = ""

S = "SELECT [" & pTextFieldname & "] " _
& " FROM [" & pTablename & "]" _
& " WHERE [" & pIDFieldname _
& "] = " & pValueID _
& IIf(Len(pWhere) > 0, " AND " & pWhere, "") _
& ";"

'open the recordset
Set r = CurrentDb.OpenRecordset(S, dbOpenSnapshot)

'loop through the recordset until the end
Do While Not r.EOF
If Not IsNull(r(pTextFieldname)) Then
mAllValues = mAllValues _
& " " & r(pTextFieldname) & mValueDeli
End If
r.MoveNext
Loop

If len(mAllValues) = 0 then
mAllValues = nz(pNoValue,"")
end if


Proc_Exit:
'close the recordset
r.Close
'release the recordset variable
Set r = Nothing

LoopAndCombine = Trim(mAllValues)
Exit Function

'if there is an error, the following code will execute
Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " LoopAndCombine"

'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume
Resume Proc_Exit
End Function

'~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Hi to all,

I am struggling with 2 problems which I'm sure there are simple solutions
to them.

Question1
-----------
I'm trying to write a function which reads an object whereby the object
name and the field name are supplied parameters:

Public Function MyFunction(ValueX as variant, FieldName as ????,
ObjectName as string, optional ObjectType as Byte=acQuery) as variant

Dim X as Double, ......

Select Case ObjectType
.......
Case 1
DoCmd.OpenQuery ObjectName
.......
End Select

DoCmd.GoToRecord, ,acFirst

Do

'Read Filled "FieldName"
X= ?????? 'How do I do it ???? ********
...
DoCmd.GoToRecord, ,acNext
Loop
.............
.
End Function

I have no problem with forms, I use X= Forms(ObjectName)(FieldName) but
I am struggling with tables and queries, I'm looking for something like
&ObjectName in the good old dBase. I tried "[" & FieldName & "]" but it
does not work

How do I do it?



Question 2
-------------
How to I get out of the Do/Loop when I reach the last record in the
above example e.g:
Do While ?????

Regards/JK
Click to expand...
Click to expand...
 
S

strive4peace

you're welcome, JK ;) happy to help

Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Works like a charm Crystal
Many thanks
JK

strive4peace said:
Hi JK,

I believe this code answers both of your questions...

'~~~~~~~~~~~~~~~~

Function LoopAndCombine( _
pTablename As String, _
pIDFieldname As String, _
pTextFieldname As String, _
pValueID As Long, _
Optional pWhere As String, _
Optional pDeli As String, _
Optional pNoValue as string) As String

'crystal 8-3-06
'strive4peace2006 at yahoo dot com

'NEEDS REFERENCE
'Microsoft DAO Library

'PARAMETERS
'pTablename --> tablename to get list from
'pIDFieldname --> fieldname to link on (ie: "BookID")
'pTextFieldname --> fieldname to combine (ie: "PageNumber")
'pValueID --> actual value of ID for this iteration ( ie: [BookID])
'pWhere, Optional --> more criteria (ie: "Year(PubDate) = 2006")
'pDeli, Optional --> delimiter other than comma (ie: ";")
'pNoValue, Optional --> value to use if no data (ie: "No Pages")

'Set up error handler
On Error GoTo Proc_Err

'dimension variables
Dim r As dao.Recordset, mAllValues As String, S As String
Dim mValueDeli As String

If Len(nz(pDeli,"")) > 0 Then _
mValueDeli = pDeli Else mValueDeli = ","

mAllValues = ""

S = "SELECT [" & pTextFieldname & "] " _
& " FROM [" & pTablename & "]" _
& " WHERE [" & pIDFieldname _
& "] = " & pValueID _
& IIf(Len(pWhere) > 0, " AND " & pWhere, "") _
& ";"

'open the recordset
Set r = CurrentDb.OpenRecordset(S, dbOpenSnapshot)

'loop through the recordset until the end
Do While Not r.EOF
If Not IsNull(r(pTextFieldname)) Then
mAllValues = mAllValues _
& " " & r(pTextFieldname) & mValueDeli
End If
r.MoveNext
Loop

If len(mAllValues) = 0 then
mAllValues = nz(pNoValue,"")
end if


Proc_Exit:
'close the recordset
r.Close
'release the recordset variable
Set r = Nothing

LoopAndCombine = Trim(mAllValues)
Exit Function

'if there is an error, the following code will execute
Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " LoopAndCombine"

'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume
Resume Proc_Exit
End Function

'~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Hi to all,

I am struggling with 2 problems which I'm sure there are simple solutions
to them.

Question1
-----------
I'm trying to write a function which reads an object whereby the object
name and the field name are supplied parameters:

Public Function MyFunction(ValueX as variant, FieldName as ????,
ObjectName as string, optional ObjectType as Byte=acQuery) as variant

Dim X as Double, ......

Select Case ObjectType
.......
Case 1
DoCmd.OpenQuery ObjectName
.......
End Select

DoCmd.GoToRecord, ,acFirst

Do

'Read Filled "FieldName"
X= ?????? 'How do I do it ???? ********
...
DoCmd.GoToRecord, ,acNext
Loop
.............
.
End Function

I have no problem with forms, I use X= Forms(ObjectName)(FieldName) but
I am struggling with tables and queries, I'm looking for something like
&ObjectName in the good old dBase. I tried "[" & FieldName & "]" but it
does not work

How do I do it?



Question 2
-------------
How to I get out of the Do/Loop when I reach the last record in the
above example e.g:
Do While ?????

Regards/JK
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Printing records on Access 2000 0
apostrophe in record 6
Using Macro to ask if my table exists. 0
How to specify a fully qualified field name? 2
Import into Access from Excel, passing a variable as the field... 1
How to create a VBA function which can be used within a query 11
objectdatasource: update parameter is a object? 2
Combo box (as Parameter) to Show selected field/record 7

Top