vb coding to select fields in record and check for data

[Guest]

Is there any way to access a series of fields in a record to check for data?
for example I can assess a field using Me.[1] or any other number to Me.[65]
but cannot access anything if I use a variable as below. any suggestions
rather than have a complete list of all 65 fields in some form of long
sequential query ?

Private Sub Command9_Click()
Dim a As String, i As String, j As Integer
For j = 1 To 65
i = Str(j)
If IsNull(Me.) Then
a = ""
Else
a = i
Exit For
End If
Next

etc. for use of a

 

[Douglas J Steele]

Me.Fields(i)

I have to question the validity of a table that contains 65 fields, much
less 65 fields that are numbered, not named, though.

 

[Guest]

Me.Fields(i)

I have to question the validity of a table that contains 65 fields, much
less 65 fields that are numbered, not named, though.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Is there any way to access a series of fields in a record to check for data?
for example I can assess a field using Me.[1] or any other number to Me.[65]
but cannot access anything if I use a variable as below. any suggestions
rather than have a complete list of all 65 fields in some form of long
sequential query ?

Private Sub Command9_Click()
Dim a As String, i As String, j As Integer
For j = 1 To 65
i = Str(j)
If IsNull(Me.) Then
a = ""
Else
a = i
Exit For
End If
Next

etc. for use of a

There are some 5000 records with about 75 fields, each field has an
identifier for a specific type of license. Some records have only one field
of dat, some have 3 or or maybe 10 with data. The rest are all null, so
there are thousands of null fileds in this database (not of my design).
Licenses have valid dates. for a particular record or sequence of records,
the subject at hand is to find the licenses held by a particular record.
This can be done for any record by looking at the table, but for reasons we
wnat to automate it so we renamed the fields with numbers to allow for
automation.
So we're still seeking an easy search method. In real VB6 this is easy, but
VBA is not VB6. Also, we did this for access 97, but VB6 can't work with
Access 2003, thanks to MS.

 

[Douglas J Steele]

Is there any way to access a series of fields in a record to check for data?
for example I can assess a field using Me.[1] or any other number to Me.[65]
but cannot access anything if I use a variable as below. any suggestions
rather than have a complete list of all 65 fields in some form of long
sequential query ?

Me.Fields(i)

I have to question the validity of a table that contains 65 fields, much
less 65 fields that are numbered, not named, though.

There are some 5000 records with about 75 fields, each field has an
identifier for a specific type of license. Some records have only one field
of dat, some have 3 or or maybe 10 with data. The rest are all null, so
there are thousands of null fileds in this database (not of my design).
Licenses have valid dates. for a particular record or sequence of records,
the subject at hand is to find the licenses held by a particular record.
This can be done for any record by looking at the table, but for reasons we
wnat to automate it so we renamed the fields with numbers to allow for
automation.
So we're still seeking an easy search method. In real VB6 this is easy, but
VBA is not VB6. Also, we did this for access 97, but VB6 can't work with
Access 2003, thanks to MS.

It definitely sounds as though your table hasn't been properly normalized.
Rather than 75 fields in a single row, you should have a second table linked
to your existing table, with one row for each valid entry.

FWIW, VB6 works fine with Access 2003 (VB5 will as well). You just have to
ensure that you're using the correct version of DAO to communicate with the
database (DAO 3.6 vs DAO 3.5x), or else the correct ADO provider
(Microsoft.Jet.OLEDB.4.0, rather than Microsoft.Jet.OLEDB.3.51)

You might also want to read the following KB articles:
PRB: Error "Unrecognized Database Format" When You Upgrade to Access 2000 or
2002:
http://support.microsoft.com/?id=238401
FIX: The Data Form Wizard May Not Open an Access 2000 Database:
http://support.microsoft.com/?id=242010
HOWTO: Modify the Visual Data Manager (VISDATA) to Work with Access 2000
Databases:
http://support.microsoft.com/?id=252438
FIX: Unrecognized Database Format Error with Data Control or Data Form
Wizard:
http://support.microsoft.com/?id=257780

 

[John Vinson]

There are some 5000 records with about 75 fields, each field has an
identifier for a specific type of license. Some records have only one field
of dat, some have 3 or or maybe 10 with data. The rest are all null, so
there are thousands of null fileds in this database (not of my design).
Licenses have valid dates. for a particular record or sequence of records,
the subject at hand is to find the licenses held by a particular record.
This can be done for any record by looking at the table, but for reasons we
wnat to automate it so we renamed the fields with numbers to allow for
automation.

I have to agree with Douglas: this table is incorrectly designed.
You're "committing spreadsheet" by storing data in fieldnames (types
of license).

A better design would be a many to many relationship:

Records
RecordID <Primary key>
<record specific fields>

Licenses
LicenseType <Primary Key>
Description

LicensesUsed
RecordID <link to Records>
LicenseType <link to Licenses>
LicenseDate

You can use a series of Append queries to extract the data out of your
wide-flat table into the tall-thin - and your search will become much
easier and will require no VBA code at all.

John W. Vinson[MVP]

 

[Guest]

I have to agree with Douglas: this table is incorrectly designed.
You're "committing spreadsheet" by storing data in fieldnames (types
of license).

A better design would be a many to many relationship:

Records
RecordID <Primary key>
<record specific fields>

Licenses
LicenseType <Primary Key>
Description

LicensesUsed
RecordID <link to Records>
LicenseType <link to Licenses>
LicenseDate

You can use a series of Append queries to extract the data out of your
wide-flat table into the tall-thin - and your search will become much
easier and will require no VBA code at all.

John W. Vinson[MVP]

From GerryO
As I stated, I did not design the database, the US gov employees did. I am
just trying to use theirs rather than start again. Thanks for suggestions.

 

[John Vinson]

As I stated, I did not design the database, the US gov employees did. I am
just trying to use theirs rather than start again. Thanks for suggestions.

Well... you could migrate the data into a local table just to get this
job done. But do whatever works for you!

John W. Vinson[MVP]