I have a table with 14 fields and need to access a field based on an offset value.
I have named the fields <a> <b> <c01> <c02> <c03> <c04> .. <c11> <c12> and thought I could build a string to access the data using dlookup. A parameter passed to the VBA module is the offset from the third field, so a 1 means <c01>, 7 <c07>, 12<s12> etc
Is there a more elegant way to do this?
I hope I am reading your request correctly, and I'm assuming that your Table consists of only a single Record. Given this, the following code will retrieve the Value of the 6th Field in the tblEmployees Table. Simply pass to the Function your Table Name and 'Absolute' Field Offset. - Function Definition:
- Public Function fRetrieveFieldValue(strSource As String, intFldOffset As Integer)
-
Dim MyDB As DAO.Database
-
Dim rst As DAO.Recordset
-
-
Set MyDB = CurrentDb
-
Set rst = MyDB.OpenRecordset(strSource)
-
-
-
If intFldOffset > rst.Fields.Count Then
-
MsgBox "Offset Invalid", vbExclamation, "Invalid Field Offset"
-
fRetrieveFieldValue = Null
-
Else
-
fRetrieveFieldValue = rst.Fields(intFldOffset - 1)
-
End If
-
-
rst.Close
-
Set rst = Nothing
-
End Function
- Sample Function Call:
- Dim varRetVal As Variant
-
-
varRetVal = fRetrieveFieldValue("tblEmployees",6)
- Another Option (shortened Version) which will work for a Table only, would be:
- Public Function fRetrieveFieldValue2(strSource As String, intFldOffset As Integer)
-
fRetrieveFieldValue2 = DLookup(CurrentDb.TableDefs(strSource).Fields(intFldOffset - 1).Name, strSource)
-
End Function
-
- Function Call would be exactly the same, except Call fRetrieveFieldValue2()
- Any questions, please feel free to ask.
3 2047
I hope I am reading your request correctly, and I'm assuming that your Table consists of only a single Record. Given this, the following code will retrieve the Value of the 6th Field in the tblEmployees Table. Simply pass to the Function your Table Name and 'Absolute' Field Offset. - Function Definition:
- Public Function fRetrieveFieldValue(strSource As String, intFldOffset As Integer)
-
Dim MyDB As DAO.Database
-
Dim rst As DAO.Recordset
-
-
Set MyDB = CurrentDb
-
Set rst = MyDB.OpenRecordset(strSource)
-
-
-
If intFldOffset > rst.Fields.Count Then
-
MsgBox "Offset Invalid", vbExclamation, "Invalid Field Offset"
-
fRetrieveFieldValue = Null
-
Else
-
fRetrieveFieldValue = rst.Fields(intFldOffset - 1)
-
End If
-
-
rst.Close
-
Set rst = Nothing
-
End Function
- Sample Function Call:
- Dim varRetVal As Variant
-
-
varRetVal = fRetrieveFieldValue("tblEmployees",6)
- Another Option (shortened Version) which will work for a Table only, would be:
- Public Function fRetrieveFieldValue2(strSource As String, intFldOffset As Integer)
-
fRetrieveFieldValue2 = DLookup(CurrentDb.TableDefs(strSource).Fields(intFldOffset - 1).Name, strSource)
-
End Function
-
- Function Call would be exactly the same, except Call fRetrieveFieldValue2()
- Any questions, please feel free to ask.
thank you so such a detailed response so quickly!
Sign in to post your reply or Sign up for a free account.
Similar topics
by: francis70 |
last post by:
Hi,
I have these 2 problem? Is there a way in Oracle to read UNCOMMITED
data. i.e. in Oracle the normal behaviour is that a user's updates to a
table are visible to other users ONLY when the...
|
by: HK |
last post by:
Hello,
I am new to access/sql-developement and have the follwing problem:
What I finally want, is to present within a form the results of a query,
that are probably read-only PLUS a field...
|
by: Matthew Louden |
last post by:
I want to read how many records in the table, and insert a record with id
field which increment the counter by 1. However, I had the following runtime
on Dim s As Integer = CInt(dr("t")). Since "t"...
|
by: james |
last post by:
I am trying to use Filestream to read a file ( .DAT) that contains values in HEX that I want to convert to text. I know the
different offset addresses for each portion of the data I am trying to...
|
by: Rico |
last post by:
I have a data file that I converted from Access to SQL Server using the
upsizing wizard. I then attached the tables to the Access front end using an
ODBC connection. I have one table that is read...
|
by: =?Utf-8?B?ZGF2aWQ=?= |
last post by:
I try to follow Steve's paper to build a database, and store a small text
file into SQL Server database and retrieve it later. Only difference between
my table and Steve's table is that I use NTEXT...
|
by: Bobby |
last post by:
I am using access 2003. I have a form which is based on a simple
query. Previously I could edit the data on the form, but now it is
read only. The form has not changed and neither has the query....
|
by: theaybaras |
last post by:
Hi All...
I've read both of the requery discussions I could find...
Refresh ComboBox After Adding New Values via a Seperate Form
and
refresh a form
but am not quite able to get this to apply...
|
by: Michael Munch |
last post by:
Hi
I want to read the value of af text-field, create dynamic, in a form.
Se below a small test-site to do that (but readning fails):
I use the function Test_Read for reading the value from the...
|
by: beacon |
last post by:
Hi everybody,
I created a database that links one table from an ODBC data source. I saved my password and UID to the data source so neither myself nor anyone else would have to login each time...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |