Visual Basic Code

[Eskimo Going Nuts]

I have a code that converts data from a non-relational database to a relational one. The code takes yes/no values from a table that contains a record ID and a number of parameters that are either yes or no, depending on whether the parameters were present at the site we visited.

The code goes something like this...

Sub TransferMoisture_Regime()

Dim db As DAO.Database, rsOld As DAO.Recordset, rsNew As DAO.Recordset
Dim fld As DAO.Field
Dim strSite As String, lngMSID As Long

Set db = CurrentDb
' Open up the original table behind the scenes
Set rsOld = db.OpenRecordset("Moisture Regime")

' Open up the new table behind the scenes
Set rsNew = db.OpenRecordset("Moisture_Regime")

' With the old table
With rsOld
' Go to the first record
.MoveFirst
' Loop until the end of recordset
Do Until .EOF
' Grab the site ID of the record
strSite = !Site_ID
'Examine each field in the record
For Each fld In .Fields
' If it is a Yes value (True)
If fld.Value = True Then
' Grab the field name that we renamed to the
' CoverID that is in the new table
lngMSID = fld.Name
' Go to the new table
With rsNew
' Poke in a new record with these values
.AddNew
!Site_FK = strSite
!Moisture_Regime_Type_FK = lngMSID
.Update
' Leave the new table
End With
End If
' Got to the next field
Next
' Done with this record, move to next
.MoveNext
Loop
' Leave the old table, we are done.
End With
' Close up our recordsets to regain memory
rsOld.Close
rsNew.Close
' Erase our pointers
Set rsOld = Nothing
Set rsNew = Nothing

MsgBox "Done converting records!"
End Sub

I need to change the code a bit to take values out of a similiar table into a new table, but rather than check to see if the value is a yes/no value, I need this to work with a table that has numerical values.

In this case, the table where I am converting from includes the field (site_ID) and 15 numbered fields representing land cover descriptions with numerical field values(as percentages of land cover). The table I am converting to has (site_ID), (Land_Cover_Type_FK) and (LCT_Value). This table is linked to another tbl.Land_Cover_Types with a Primary Key, Land_Cover_Type_ID and Land_Cover_Name.

The tweak I am looking for is in the line...If Field Value = True then..... It should read something like, grab the value and place it in the LCT_Value in the table I am converting to.

Thanks in advance, I got this code from someone on this site. Thanks for your help again and yes your postcard is coming.

Eskimo going nuts

 

[Ron Hinds]

I have a code that converts data from a non-relational database to a

relational one. The code takes yes/no values from a table that contains a
record ID and a number of parameters that are either yes or no, depending on
whether the parameters were present at the site we visited.

The code goes something like this...

Sub TransferMoisture_Regime()

Dim db As DAO.Database, rsOld As DAO.Recordset, rsNew As DAO.Recordset
Dim fld As DAO.Field
Dim strSite As String, lngMSID As Long

Set db = CurrentDb
' Open up the original table behind the scenes
Set rsOld = db.OpenRecordset("Moisture Regime")

' Open up the new table behind the scenes
Set rsNew = db.OpenRecordset("Moisture_Regime")

' With the old table
With rsOld
' Go to the first record
.MoveFirst
' Loop until the end of recordset
Do Until .EOF
' Grab the site ID of the record
strSite = !Site_ID
'Examine each field in the record
For Each fld In .Fields
' If it is a Yes value (True)
If fld.Value = True Then
' Grab the field name that we renamed to the
' CoverID that is in the new table
lngMSID = fld.Name
' Go to the new table
With rsNew
' Poke in a new record with these values
.AddNew
!Site_FK = strSite
!Moisture_Regime_Type_FK = lngMSID
.Update
' Leave the new table
End With
End If
' Got to the next field
Next
' Done with this record, move to next
.MoveNext
Loop
' Leave the old table, we are done.
End With
' Close up our recordsets to regain memory
rsOld.Close
rsNew.Close
' Erase our pointers
Set rsOld = Nothing
Set rsNew = Nothing

MsgBox "Done converting records!"
End Sub

I need to change the code a bit to take values out of a similiar table

into a new table, but rather than check to see if the value is a yes/no
value, I need this to work with a table that has numerical values.

In this case, the table where I am converting from includes the field

(site_ID) and 15 numbered fields representing land cover descriptions with
numerical field values(as percentages of land cover). The table I am
converting to has (site_ID), (Land_Cover_Type_FK) and (LCT_Value). This
table is linked to another tbl.Land_Cover_Types with a Primary Key,
Land_Cover_Type_ID and Land_Cover_Name.

The tweak I am looking for is in the line...If Field Value = True

then..... It should read something like, grab the value and place it in the
LCT_Value in the table I am converting to.

Thanks in advance, I got this code from someone on this site. Thanks for

your help again and yes your postcard is coming.

Eskimo going nuts

How about this:

If IsNumeric(fld.Value) Then ...

From Access Help:

IsNumeric returns True if the entire expression is recognized as a number;
otherwise, it returns False.