Visual Basic Code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Eskimo Going Nuts said:
I have a code that converts data from a non-relational database to a
Click to expand...
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
Click to expand...
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
Click to expand...
(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
Click to expand...
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
Click to expand...
your help again and yes your postcard is coming.
Eskimo going nuts
Click to expand...

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.
 
Back
Top