Simple Design creating Big Problem

[Jonathan]

I have created too simple a database. tbl_Main contains
basic fields such as [Site_ID], [Waypoint_Lat],
[Waypoint_Lon]Etc... and contains basic info on the sites
we visit.

There are several other tables, such as
tbl_Ground_Cover_Type, and this has [Site_ID](which is
linked to site_ID in tbl_Main) with 15 other Yes/No-Data
type fields, each with descriptions about the ground cover
type such as coastal flat, bedrock outcrop, lichen upland
etc...

My problem is with the reporting of the sites. As it
stands now, I am able to display the results of the sites
with all of the possible ground cover types either with a
yes or no value. I wish to display each site with ONLY
the ground cover characteristics marked with a Yes.
Please help me as I have stumbled on this because of my
shortcut when I designed this database in the first
place.

Thanks in advance

Jonathan

 

[Gary Miller]

Jonathan,

This is why good database design starts from the reporting
requirements and works backwards to the table designs.

Is there only one ground cover type per site? If so, your
Ground Cover table should have two fields, GroundCoverID and
GroundCover
1 Coastal Flat
2 Bedrock Outcrop
etc...

and you would store this value in a GroundCoverID field in
the site table and populate it through the use of a combobox
that would look up the value from this table.

If you have more than one per site, you need to now have two
tables. The above would be your tblGroundCoverTypes table
and you would need one more that would store the multiple
types for each site, maybe called tblGroundCovers. This
would have an Autonumber Primary Key field SiteGCID(??), a
Foreign Key Field to hold the SiteID (LongInteger, and
another Foreign Key to hold the GroundCoverID from the above
table.

Now you will populate this table by using a small subform on
your main form that would allow you to pick multiple ground
covers for the site.

When you do your reporting you would do it based on a query
that will bring in the Ground Cover name based on linking
the ID's together and you would just show the applicable
cover(s).

Does this make sense?

Gary Miller
Sisters, OR

 

[Guest]

Hi Gary,

I have done it the way you have suggested, and I will
continue to do it after this one for the next databases
that I create. I appreciate your input, but you only
proposed a solution for "next time". I have already
completed entering data into this, which took about three
months.

That does not help me with my current problem. Since I did
not design it as it should have, I am left with presenting
a report that shows every detail, even the ground cover
types that are not marked yes. This is a waste of space
on my single page that I am trying to fit everything on.

So what can I do to pull out the ground cover types marked
yes in my ground cover types table for each site? I can't
imagine how it would work, unless there is a way of
creating a complex formula in a query. (ie. If
[Ground_Cover_Type.Coastal_Flat]=true, then magically pull
out and apply it somewhere) or something like that.

I might have to apply your suggestion and re-enter the
data all over again.

Jonathan

-----Original Message-----
Jonathan,

This is why good database design starts from the reporting
requirements and works backwards to the table designs.

Is there only one ground cover type per site? If so, your
Ground Cover table should have two fields, GroundCoverID and
GroundCover
1 Coastal Flat
2 Bedrock Outcrop
etc...

and you would store this value in a GroundCoverID field in
the site table and populate it through the use of a combobox
that would look up the value from this table.

If you have more than one per site, you need to now have two
tables. The above would be your tblGroundCoverTypes table
and you would need one more that would store the multiple
types for each site, maybe called tblGroundCovers. This
would have an Autonumber Primary Key field SiteGCID(??), a
Foreign Key Field to hold the SiteID (LongInteger, and
another Foreign Key to hold the GroundCoverID from the above
table.

Now you will populate this table by using a small subform on
your main form that would allow you to pick multiple ground
covers for the site.

When you do your reporting you would do it based on a query
that will bring in the Ground Cover name based on linking
the ID's together and you would just show the applicable
cover(s).

Does this make sense?

Gary Miller
Sisters, OR

I have created too simple a database. tbl_Main contains
basic fields such as [Site_ID], [Waypoint_Lat],
[Waypoint_Lon]Etc... and contains basic info on the sites
we visit.

There are several other tables, such as
tbl_Ground_Cover_Type, and this has [Site_ID](which is
linked to site_ID in tbl_Main) with 15 other Yes/No-Data
type fields, each with descriptions about the ground cover
type such as coastal flat, bedrock outcrop, lichen upland
etc...

My problem is with the reporting of the sites. As it
stands now, I am able to display the results of the sites
with all of the possible ground cover types either with a
yes or no value. I wish to display each site with ONLY
the ground cover characteristics marked with a Yes.
Please help me as I have stumbled on this because of my
shortcut when I designed this database in the first
place.

Thanks in advance

Jonathan

.

 

[Gary Miller]

Jonathan,

If you agree that the design change would be the best way to
go in the long run, I am sure that we can brainstorm a way
to automate the data transfer to the new format. I am
thinking of a recordset loop on your existing table that
could check the value of each field and if it was yes poke
in a record into the new table with that field name or
GCCode and the siteID. It will take longer to write the code
than it will to run it. You may be able to convert the three
months work in about 10 seconds.

If you want to do this, I will need a list of all of the Y/N
field names that you are using. On second thought, how about
this approach. You create the tblGroundCover with the
autonumber ID and the cover name. Now enter your ground
covers into the table and print your table showing the names
and ID.

Next make a copy of your original table and rename all of
the Y/N fields to match the ID for that cover that Access
generated in the new table so that the field name for the
first one is just '1'. Now I can easily create a loop that
will check every field for a yes and then poke the name of
the field and the siteID into the intermediary table as a
new record.

What do you think? You may have to redesign a report or two
as well as your entry method, but they will give you what
you want finally and the code should be pretty easy on my
end. I will need the exact names of your tables and the new
fields as you end up naming them.

Gary Miller
Sisters, OR

Hi Gary,

I have done it the way you have suggested, and I will
continue to do it after this one for the next databases
that I create. I appreciate your input, but you only
proposed a solution for "next time". I have already
completed entering data into this, which took about three
months.

That does not help me with my current problem. Since I did
not design it as it should have, I am left with presenting
a report that shows every detail, even the ground cover
types that are not marked yes. This is a waste of space
on my single page that I am trying to fit everything on.

So what can I do to pull out the ground cover types marked
yes in my ground cover types table for each site? I can't
imagine how it would work, unless there is a way of
creating a complex formula in a query. (ie. If
[Ground_Cover_Type.Coastal_Flat]=true, then magically pull
out and apply it somewhere) or something like that.

I might have to apply your suggestion and re-enter the
data all over again.

Jonathan

-----Original Message-----
Jonathan,

This is why good database design starts from the reporting
requirements and works backwards to the table designs.

Is there only one ground cover type per site? If so, your
Ground Cover table should have two fields, GroundCoverID and
GroundCover
1 Coastal Flat
2 Bedrock Outcrop
etc...

and you would store this value in a GroundCoverID field in
the site table and populate it through the use of a combobox
that would look up the value from this table.

If you have more than one per site, you need to now have two
tables. The above would be your tblGroundCoverTypes table
and you would need one more that would store the multiple
types for each site, maybe called tblGroundCovers. This
would have an Autonumber Primary Key field SiteGCID(??), a
Foreign Key Field to hold the SiteID (LongInteger, and
another Foreign Key to hold the GroundCoverID from the above
table.

Now you will populate this table by using a small subform on
your main form that would allow you to pick multiple ground
covers for the site.

When you do your reporting you would do it based on a query
that will bring in the Ground Cover name based on linking
the ID's together and you would just show the applicable
cover(s).

Does this make sense?

Gary Miller
Sisters, OR

I have created too simple a database. tbl_Main contains
basic fields such as [Site_ID], [Waypoint_Lat],
[Waypoint_Lon]Etc... and contains basic info on the sites
we visit.

There are several other tables, such as
tbl_Ground_Cover_Type, and this has [Site_ID](which is
linked to site_ID in tbl_Main) with 15 other Yes/No-Data
type fields, each with descriptions about the ground cover
type such as coastal flat, bedrock outcrop, lichen upland
etc...

My problem is with the reporting of the sites. As it
stands now, I am able to display the results of the sites
with all of the possible ground cover types either with a
yes or no value. I wish to display each site with ONLY
the ground cover characteristics marked with a Yes.
Please help me as I have stumbled on this because of my
shortcut when I designed this database in the first
place.

Thanks in advance

Jonathan

.

 

[Jonathan]

Thanks gerry, here is what I have

tblGround_Cover_Type
Gound_Cover_Type_ID Ground_Cover_Name
1 Coastal Flat
2 Wet Sedge Meadow
3 Hummock Graminoide Tundra
4 Tussock Graminoide Tundra
5 Low Shrub Tundra
6 Closed Shrub Thicket (>40% Cover)
7 Open Shrub Thicket (25-40% Cover)
8 Moss-Lichen Upland
9 Lichen-Heath Upland
10 Gravel/Sand Ridge
11 Rock Outcrop
12 Boulder Field
13 Exposed Lake Bottom
14 Other
15 Other, including complexes

So I will make a copy of the orginal Database so that
Coastal Flat field will be 1 and so on. Important note,
there is more than one possible type of ground cover
marked yes for each site.

The exact name in the original database for the GCT table
is Ground_Cover_Type. 4 other tables, each with similar
problems that I am facing. Let's start with GCT and see
if we can carry on to the rest.

I eagerly wait your loop thing you got going there, You
guys are amazing.

Jonathan

-----Original Message-----
Jonathan,

If you agree that the design change would be the best way to
go in the long run, I am sure that we can brainstorm a way
to automate the data transfer to the new format. I am
thinking of a recordset loop on your existing table that
could check the value of each field and if it was yes poke
in a record into the new table with that field name or
GCCode and the siteID. It will take longer to write the code
than it will to run it. You may be able to convert the three
months work in about 10 seconds.

If you want to do this, I will need a list of all of the Y/N
field names that you are using. On second thought, how about
this approach. You create the tblGroundCover with the
autonumber ID and the cover name. Now enter your ground
covers into the table and print your table showing the names
and ID.

Next make a copy of your original table and rename all of
the Y/N fields to match the ID for that cover that Access
generated in the new table so that the field name for the
first one is just '1'. Now I can easily create a loop that
will check every field for a yes and then poke the name of
the field and the siteID into the intermediary table as a
new record.

What do you think? You may have to redesign a report or two
as well as your entry method, but they will give you what
you want finally and the code should be pretty easy on my
end. I will need the exact names of your tables and the new
fields as you end up naming them.

Gary Miller
Sisters, OR

Hi Gary,

I have done it the way you have suggested, and I will
continue to do it after this one for the next databases
that I create. I appreciate your input, but you only
proposed a solution for "next time". I have already
completed entering data into this, which took about three
months.

That does not help me with my current problem. Since I did
not design it as it should have, I am left with presenting
a report that shows every detail, even the ground cover
types that are not marked yes. This is a waste of space
on my single page that I am trying to fit everything on.

So what can I do to pull out the ground cover types marked
yes in my ground cover types table for each site? I can't
imagine how it would work, unless there is a way of
creating a complex formula in a query. (ie. If
[Ground_Cover_Type.Coastal_Flat]=true, then magically pull
out and apply it somewhere) or something like that.

I might have to apply your suggestion and re-enter the
data all over again.

Jonathan

-----Original Message-----
Jonathan,

This is why good database design starts from the reporting
requirements and works backwards to the table designs.

Is there only one ground cover type per site? If so, your
Ground Cover table should have two fields,

GroundCoverID
and

GroundCover
1 Coastal Flat
2 Bedrock Outcrop
etc...

and you would store this value in a GroundCoverID field in
the site table and populate it through the use of a combobox
that would look up the value from this table.

If you have more than one per site, you need to now

have
two

tables. The above would be your tblGroundCoverTypes table
and you would need one more that would store the multiple
types for each site, maybe called tblGroundCovers. This
would have an Autonumber Primary Key field SiteGCID

(??),

a

Foreign Key Field to hold the SiteID (LongInteger, and
another Foreign Key to hold the GroundCoverID from the above
table.

Now you will populate this table by using a small

subform
on

your main form that would allow you to pick multiple ground
covers for the site.

When you do your reporting you would do it based on a query
that will bring in the Ground Cover name based on linking
the ID's together and you would just show the applicable
cover(s).

Does this make sense?

Gary Miller
Sisters, OR

I have created too simple a database. tbl_Main contains
basic fields such as [Site_ID], [Waypoint_Lat],
[Waypoint_Lon]Etc... and contains basic info on the sites
we visit.

There are several other tables, such as
tbl_Ground_Cover_Type, and this has [Site_ID](which is
linked to site_ID in tbl_Main) with 15 other Yes/No-Data
type fields, each with descriptions about the ground cover
type such as coastal flat, bedrock outcrop, lichen upland
etc...

My problem is with the reporting of the sites. As it
stands now, I am able to display the results of the sites
with all of the possible ground cover types either

with
a

yes or no value. I wish to display each site with ONLY
the ground cover characteristics marked with a Yes.
Please help me as I have stumbled on this because of my
shortcut when I designed this database in the first
place.

Thanks in advance

Jonathan


.

.

 

[Gary Miller]

Got it and I think that you gave me almost enough to work
with. I only now need the name of the field that holds the
SiteID in this table. There wouldn't be any other Yes/No
fields besides these in this table are there?

Also, what version of Access are you using?

We do need the one other table. Use the field names below as
I will put these in my code...

you would need one more that would store the multiple
types for each site, maybe called tblGroundCovers. This
would have an Autonumber Primary Key field Site_Cover_ID, a
Foreign Key Field to hold the Site_FK (LongInteger, and
another Foreign Key to hold the Ground_Cover_Type_FK
(LongInteger) from the above
table.

Thanks gerry, here is what I have

tblGround_Cover_Type
Gound_Cover_Type_ID Ground_Cover_Name
1 Coastal Flat
2 Wet Sedge Meadow
3 Hummock Graminoide Tundra
4 Tussock Graminoide Tundra
5 Low Shrub Tundra
6 Closed Shrub Thicket (>40% Cover)
7 Open Shrub Thicket (25-40% Cover)
8 Moss-Lichen Upland
9 Lichen-Heath Upland
10 Gravel/Sand Ridge
11 Rock Outcrop
12 Boulder Field
13 Exposed Lake Bottom
14 Other
15 Other, including complexes

So I will make a copy of the orginal Database so that
Coastal Flat field will be 1 and so on. Important note,
there is more than one possible type of ground cover
marked yes for each site.

The exact name in the original database for the GCT table
is Ground_Cover_Type. 4 other tables, each with similar
problems that I am facing. Let's start with GCT and see
if we can carry on to the rest.

I eagerly wait your loop thing you got going there, You
guys are amazing.

Jonathan

-----Original Message-----
Jonathan,

If you agree that the design change would be the best way to
go in the long run, I am sure that we can brainstorm a way
to automate the data transfer to the new format. I am
thinking of a recordset loop on your existing table that
could check the value of each field and if it was yes poke
in a record into the new table with that field name or
GCCode and the siteID. It will take longer to write the code
than it will to run it. You may be able to convert the three
months work in about 10 seconds.

If you want to do this, I will need a list of all of the Y/N
field names that you are using. On second thought, how about
this approach. You create the tblGroundCover with the
autonumber ID and the cover name. Now enter your ground
covers into the table and print your table showing the names
and ID.

Next make a copy of your original table and rename all of
the Y/N fields to match the ID for that cover that Access
generated in the new table so that the field name for the
first one is just '1'. Now I can easily create a loop that
will check every field for a yes and then poke the name of
the field and the siteID into the intermediary table as a
new record.

What do you think? You may have to redesign a report or two
as well as your entry method, but they will give you what
you want finally and the code should be pretty easy on my
end. I will need the exact names of your tables and the new
fields as you end up naming them.

Gary Miller
Sisters, OR

Hi Gary,

I have done it the way you have suggested, and I will
continue to do it after this one for the next databases
that I create. I appreciate your input, but you only
proposed a solution for "next time". I have already
completed entering data into this, which took about three
months.

That does not help me with my current problem. Since I did
not design it as it should have, I am left with presenting
a report that shows every detail, even the ground cover
types that are not marked yes. This is a waste of space
on my single page that I am trying to fit everything on.

So what can I do to pull out the ground cover types marked
yes in my ground cover types table for each site? I can't
imagine how it would work, unless there is a way of
creating a complex formula in a query. (ie. If
[Ground_Cover_Type.Coastal_Flat]=true, then magically pull
out and apply it somewhere) or something like that.

I might have to apply your suggestion and re-enter the
data all over again.

Jonathan
-----Original Message-----
Jonathan,

This is why good database design starts from the reporting
requirements and works backwards to the table designs.

Is there only one ground cover type per site? If so, your
Ground Cover table should have two fields, GroundCoverID
and
GroundCover
1 Coastal Flat
2 Bedrock Outcrop
etc...

and you would store this value in a GroundCoverID

field
in

the site table and populate it through the use of a
combobox
that would look up the value from this table.

If you have more than one per site, you need to now have
two
tables. The above would be your tblGroundCoverTypes table
and you would need one more that would store the multiple
types for each site, maybe called tblGroundCovers. This
would have an Autonumber Primary Key field SiteGCID

(??),

a
Foreign Key Field to hold the SiteID (LongInteger, and
another Foreign Key to hold the GroundCoverID from the
above
table.

Now you will populate this table by using a small subform
on
your main form that would allow you to pick multiple
ground
covers for the site.

When you do your reporting you would do it based on a
query
that will bring in the Ground Cover name based on linking
the ID's together and you would just show the applicable
cover(s).

Does this make sense?

Gary Miller
Sisters, OR

I have created too simple a database. tbl_Main contains
basic fields such as [Site_ID], [Waypoint_Lat],
[Waypoint_Lon]Etc... and contains basic info on the
sites
we visit.

There are several other tables, such as
tbl_Ground_Cover_Type, and this has [Site_ID](which is
linked to site_ID in tbl_Main) with 15 other Yes/No-Data
type fields, each with descriptions about the ground
cover
type such as coastal flat, bedrock outcrop, lichen
upland
etc...

My problem is with the reporting of the sites. As it
stands now, I am able to display the results of the
sites
with all of the possible ground cover types either with
a
yes or no value. I wish to display each site with ONLY
the ground cover characteristics marked with a Yes.
Please help me as I have stumbled on this because of my
shortcut when I designed this database in the first
place.

Thanks in advance

Jonathan


.

.

 

[Gary Miller]

Ok, here is the code routine to loop through the table and
do a conversion and dump to the new table. We may need to
make some adjustments as I don't have all the answers back
on table and field names yet, but they should be minor. I am
leaving all error handling out because if you have something
unusual in the table that bombs the code, I will want to
know exactly which line caused the error. I did put in a lot
of comments to explain what is going on.

We will create a new form with a command button on it to run
it when we are ready to go. Let me know when you have it and
are ready. I believe that we are working with a bit of a
time zone difference here.

Gary Miller
Sisters, OR
*****************************************
Sub TransferGroundCover()

Dim db As Database, rsOld As Recordset, rsNew As Recordset
Dim fld As Field
Dim lngSite As Long, lngGCID As Long

Set db = CurrentDb
' Open up the original table behind the scenes
Set rsOld = db.OpenRecordset("tblGroundCovTest")
' Open up the new table behind the scenes
Set rsNew = db.OpenRecordset("tblGroundCovers")

' 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
lngSite = !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
lngGCID = fld.Name
' Go to the new table
With rsNew
' Poke in a new record with these values
.AddNew
!Site_FK = lngSite
!Ground_Cover_Type_FK = lngGCID
.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

*****************************************

 

[Jonathan]

Hi Gary,

The tblGround_Cover_Type I created below is new, following
your suggestion. The layout for my ground cover type
table in the original database is this....
tblGround_Cover_Type [Site_ID] Text and the 15 Ground
Cover Types each with Yes/No Field (Except for the last
two, other, which are text fields. I will be able to
export those and re-import them into the new database with
relative ease).

The name of the field that holds siteID in the table is
[Site_ID].

I am running Access 2000.

Thank you again for your help.

Jonathan

 

[Gary Miller]

Jonathan,

I am here and around for a bit so timing is good for me.

I assume that you have the routine that I posted. What you
will want to do with it is to paste it into a global module,
either existing or a new one. Then you will want to examine
it and make adjustments to any of the field/table names to
match yours. The new table that will hold the site ground
covers I think I named to tblGroundCovers, but to match your
naming conventions, maybe you should name it
tblSite_Ground_Covers. The table name will need to be
changed in the code to whatever you end up using. The fields
that I used were Site_Cover_ID (PK, Autonumber),
Site_FK(LongInteger) to hold the Site ID and
Ground_Cover_Type_FK (LongInteger) to hold the ground cover
ID.

You will need to create a new unbound form that will hold
one command button that we will put the command to run the
routine in. Let me know when all this is ready and I'll help
you step through the code to make sure it is working right
before we do the whole run.

Gary Miller

Hi Gary,

The tblGround_Cover_Type I created below is new, following
your suggestion. The layout for my ground cover type
table in the original database is this....
tblGround_Cover_Type [Site_ID] Text and the 15 Ground
Cover Types each with Yes/No Field (Except for the last
two, other, which are text fields. I will be able to
export those and re-import them into the new database with
relative ease).

The name of the field that holds siteID in the table is
[Site_ID].

I am running Access 2000.

Thank you again for your help.

Jonathan

-----Original Message-----
Hi Gary,

Wednesday morning, and I am reading over your posts and I
will reply asap.

Jonathan I
.

 

[Jonathan]

I think the only modification I need to make would be to
rename, in your loop, the name you assigned for my
original table to "tblGround_Cover_Type".

I did rename the fields in that table (1,2,3 and so on)
but left out the last two as others as they are text
fields. I will cut and paste them to a new table to be
applied later on in the report, for all intensive
purposes, this should be fine.

Now, I am ready to test this and see if it works, I'll get
back to you in a few minutes.

Jonathan

-----Original Message-----
Jonathan,

I am here and around for a bit so timing is good for me.

I assume that you have the routine that I posted. What you
will want to do with it is to paste it into a global module,
either existing or a new one. Then you will want to examine
it and make adjustments to any of the field/table names to
match yours. The new table that will hold the site ground
covers I think I named to tblGroundCovers, but to match your
naming conventions, maybe you should name it
tblSite_Ground_Covers. The table name will need to be
changed in the code to whatever you end up using. The fields
that I used were Site_Cover_ID (PK, Autonumber),
Site_FK(LongInteger) to hold the Site ID and
Ground_Cover_Type_FK (LongInteger) to hold the ground cover
ID.

You will need to create a new unbound form that will hold
one command button that we will put the command to run the
routine in. Let me know when all this is ready and I'll help
you step through the code to make sure it is working right
before we do the whole run.

Gary Miller

Hi Gary,

The tblGround_Cover_Type I created below is new, following
your suggestion. The layout for my ground cover type
table in the original database is this....
tblGround_Cover_Type [Site_ID] Text and the 15 Ground
Cover Types each with Yes/No Field (Except for the last
two, other, which are text fields. I will be able to
export those and re-import them into the new database with
relative ease).

The name of the field that holds siteID in the table is
[Site_ID].

I am running Access 2000.

Thank you again for your help.

Jonathan

-----Original Message-----
Hi Gary,

Wednesday morning, and I am reading over your posts and I
will reply asap.

Jonathan
-----Original Message-----
Ok, here is the code routine to loop through the table and
do a conversion and dump to the new table. We may need to
make some adjustments as I don't have all the answers back
on table and field names yet, but they should be

minor.
I

am
leaving all error handling out because if you have
something
unusual in the table that bombs the code, I will want to
know exactly which line caused the error. I did put in a
lot
of comments to explain what is going on.

We will create a new form with a command button on it to
run
it when we are ready to go. Let me know when you have it
and
are ready. I believe that we are working with a bit of a
time zone difference here.

Gary Miller
Sisters, OR
*****************************************
Sub TransferGroundCover()

Dim db As Database, rsOld As Recordset, rsNew As Recordset
Dim fld As Field
Dim lngSite As Long, lngGCID As Long

Set db = CurrentDb
' Open up the original table behind the scenes
Set rsOld = db.OpenRecordset("tblGroundCovTest")
' Open up the new table behind the scenes
Set rsNew = db.OpenRecordset("tblGroundCovers")

' 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
lngSite = !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
lngGCID = fld.Name
' Go to the new table
With rsNew
' Poke in a new record with these
values
.AddNew
!Site_FK = lngSite
!Ground_Cover_Type_FK = lngGCID
.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

*****************************************
Thanks gerry, here is what I have


.

.

.

 

[Gary Miller]

If you want to add the two fields, it would be easy to
include them in the loop although it would result in the
fields being included for each ground cover. May be better
to set them up in the site table and append them there if
there is only one entry for them per site.

Gary

I think the only modification I need to make would be to
rename, in your loop, the name you assigned for my
original table to "tblGround_Cover_Type".

I did rename the fields in that table (1,2,3 and so on)
but left out the last two as others as they are text
fields. I will cut and paste them to a new table to be
applied later on in the report, for all intensive
purposes, this should be fine.

Now, I am ready to test this and see if it works, I'll get
back to you in a few minutes.

Jonathan

-----Original Message-----
Jonathan,

I am here and around for a bit so timing is good for me.

I assume that you have the routine that I posted. What you
will want to do with it is to paste it into a global module,
either existing or a new one. Then you will want to examine
it and make adjustments to any of the field/table names to
match yours. The new table that will hold the site ground
covers I think I named to tblGroundCovers, but to match your
naming conventions, maybe you should name it
tblSite_Ground_Covers. The table name will need to be
changed in the code to whatever you end up using. The fields
that I used were Site_Cover_ID (PK, Autonumber),
Site_FK(LongInteger) to hold the Site ID and
Ground_Cover_Type_FK (LongInteger) to hold the ground cover
ID.

You will need to create a new unbound form that will hold
one command button that we will put the command to run the
routine in. Let me know when all this is ready and I'll help
you step through the code to make sure it is working right
before we do the whole run.

Gary Miller

Hi Gary,

The tblGround_Cover_Type I created below is new, following
your suggestion. The layout for my ground cover type
table in the original database is this....
tblGround_Cover_Type [Site_ID] Text and the 15 Ground
Cover Types each with Yes/No Field (Except for the last
two, other, which are text fields. I will be able to
export those and re-import them into the new database with
relative ease).

The name of the field that holds siteID in the table is
[Site_ID].

I am running Access 2000.

Thank you again for your help.

Jonathan
-----Original Message-----
Hi Gary,

Wednesday morning, and I am reading over your posts and I
will reply asap.

Jonathan
-----Original Message-----
Ok, here is the code routine to loop through the table
and
do a conversion and dump to the new table. We may

need
to

make some adjustments as I don't have all the answers
back
on table and field names yet, but they should be minor.
I
am
leaving all error handling out because if you have
something
unusual in the table that bombs the code, I will want to
know exactly which line caused the error. I did put in a
lot
of comments to explain what is going on.

We will create a new form with a command button on it to
run
it when we are ready to go. Let me know when you have it
and
are ready. I believe that we are working with a bit of a
time zone difference here.

Gary Miller
Sisters, OR
*****************************************
Sub TransferGroundCover()

Dim db As Database, rsOld As Recordset, rsNew As
Recordset
Dim fld As Field
Dim lngSite As Long, lngGCID As Long

Set db = CurrentDb
' Open up the original table behind the scenes
Set rsOld = db.OpenRecordset("tblGroundCovTest")
' Open up the new table behind the scenes
Set rsNew = db.OpenRecordset("tblGroundCovers")

' 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
lngSite = !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
lngGCID = fld.Name
' Go to the new table
With rsNew
' Poke in a new record with these
values
.AddNew
!Site_FK = lngSite
!Ground_Cover_Type_FK = lngGCID
.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

*****************************************
Thanks gerry, here is what I have


.

.

.

 

[Jonathan]

Hi Gary,

I have the loop in Module1 now, I tried running it as a
command in a unbound form, clicked on the command and
nothing happened.

Few questions for you, I have three tables, the original
table (tblGround_Cover_Type), a table I created yesterday
(tblGround_Cover_Types) (Notes "s")which assigns a number
to each cover type, Finally, the third table,
tblGroundCovers in which you suggested also. Does the
loop work with all three tables? or just the two. I think
it just works with the original and the last table
mentioned above.

I am also confused with the field names you have in the
loop.

Jonathan

-----Original Message-----
If you want to add the two fields, it would be easy to
include them in the loop although it would result in the
fields being included for each ground cover. May be better
to set them up in the site table and append them there if
there is only one entry for them per site.

Gary

I think the only modification I need to make would be to
rename, in your loop, the name you assigned for my
original table to "tblGround_Cover_Type".

I did rename the fields in that table (1,2,3 and so on)
but left out the last two as others as they are text
fields. I will cut and paste them to a new table to be
applied later on in the report, for all intensive
purposes, this should be fine.

Now, I am ready to test this and see if it works, I'll get
back to you in a few minutes.

Jonathan

-----Original Message-----
Jonathan,

I am here and around for a bit so timing is good for me.

I assume that you have the routine that I posted. What you
will want to do with it is to paste it into a global module,
either existing or a new one. Then you will want to examine
it and make adjustments to any of the field/table names to
match yours. The new table that will hold the site ground
covers I think I named to tblGroundCovers, but to match your
naming conventions, maybe you should name it
tblSite_Ground_Covers. The table name will need to be
changed in the code to whatever you end up using. The fields
that I used were Site_Cover_ID (PK, Autonumber),
Site_FK(LongInteger) to hold the Site ID and
Ground_Cover_Type_FK (LongInteger) to hold the ground cover
ID.

You will need to create a new unbound form that will hold
one command button that we will put the command to run the
routine in. Let me know when all this is ready and I'll help
you step through the code to make sure it is working right
before we do the whole run.

Gary Miller

Hi Gary,

The tblGround_Cover_Type I created below is new, following
your suggestion. The layout for my ground cover type
table in the original database is this....
tblGround_Cover_Type [Site_ID] Text and the 15 Ground
Cover Types each with Yes/No Field (Except for the last
two, other, which are text fields. I will be able to
export those and re-import them into the new database with
relative ease).

The name of the field that holds siteID in the table is
[Site_ID].

I am running Access 2000.

Thank you again for your help.

Jonathan
-----Original Message-----
Hi Gary,

Wednesday morning, and I am reading over your posts and I
will reply asap.

Jonathan
-----Original Message-----
Ok, here is the code routine to loop through the table
and
do a conversion and dump to the new table. We may need
to
make some adjustments as I don't have all the answers
back
on table and field names yet, but they should be minor.
I
am
leaving all error handling out because if you have
something
unusual in the table that bombs the code, I will

want
to

know exactly which line caused the error. I did put in a
lot
of comments to explain what is going on.

We will create a new form with a command button on

it
to

run
it when we are ready to go. Let me know when you

have
it

and
are ready. I believe that we are working with a bit of a
time zone difference here.

Gary Miller
Sisters, OR
*****************************************
Sub TransferGroundCover()

Dim db As Database, rsOld As Recordset, rsNew As
Recordset
Dim fld As Field
Dim lngSite As Long, lngGCID As Long

Set db = CurrentDb
' Open up the original table behind the scenes
Set rsOld = db.OpenRecordset("tblGroundCovTest")
' Open up the new table behind the scenes
Set rsNew = db.OpenRecordset("tblGroundCovers")

' 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
lngSite = !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
lngGCID = fld.Name
' Go to the new table
With rsNew
' Poke in a new record with these
values
.AddNew
!Site_FK = lngSite
!Ground_Cover_Type_FK = lngGCID
.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

*****************************************
Thanks gerry, here is what I have


.

.



.

.

 

[Gary Miller]

It is intended to work with the version of the
tblGround_Cover_Type that you renamed the type Y/N's to 1,
2, 3, etc... and dump the records into the tblGroundCovers.
The new second table you will use as a combobox lookup table
to select your GroundCovers in the future on your subform
that you will put on your site form.

I was guessing on your field names when I created the loop
code so they may not match yours. If you set up
tblGroundCovers with the names I gave you, that part should
be fine. You may need to adjust the name in the code to
match your SiteID field name. If you are still confused post
the part that is confusing.

To get the command button to run the code go in design view
doubleclick on the button to get the properties window for
it open. Go to the Events tab and find the line for the
OnClick event. Click into that line and then click on the
grey to the right of that line and you will get a builder
button. Click on that and you will get a window with three
options. Select 'Code' from that window and you will get
dumped into the OnClick sub of the form code module. Type in
the following...

Call TransferGroundCover

Close the code window and hit the save button. You will now
be ready to run it when you put the form back into form
view. If there is still a problem with getting the field and
table names right you will get an error message and options
to Debug or End. Hit the Debug button and it will take you
to the line of code that is generating the problem. The line
should be highlighted in yellow. If that happens copy the
line into a post to me and we'll get it straight. If it
works, you will get a message box telling you that it is
done.

You should make a backup of the db just for safety of
course, although I am not changing any of your data.

Gary Miller

Hi Gary,

I have the loop in Module1 now, I tried running it as a
command in a unbound form, clicked on the command and
nothing happened.

Few questions for you, I have three tables, the original
table (tblGround_Cover_Type), a table I created yesterday
(tblGround_Cover_Types) (Notes "s")which assigns a number
to each cover type, Finally, the third table,
tblGroundCovers in which you suggested also. Does the
loop work with all three tables? or just the two. I think
it just works with the original and the last table
mentioned above.

I am also confused with the field names you have in the
loop.

Jonathan

-----Original Message-----
If you want to add the two fields, it would be easy to
include them in the loop although it would result in the
fields being included for each ground cover. May be better
to set them up in the site table and append them there if
there is only one entry for them per site.

Gary

I think the only modification I need to make would be to
rename, in your loop, the name you assigned for my
original table to "tblGround_Cover_Type".

I did rename the fields in that table (1,2,3 and so on)
but left out the last two as others as they are text
fields. I will cut and paste them to a new table to be
applied later on in the report, for all intensive
purposes, this should be fine.

Now, I am ready to test this and see if it works, I'll get
back to you in a few minutes.

Jonathan
-----Original Message-----
Jonathan,

I am here and around for a bit so timing is good for me.

I assume that you have the routine that I posted. What you
will want to do with it is to paste it into a global
module,
either existing or a new one. Then you will want to
examine
it and make adjustments to any of the field/table

names
to

match yours. The new table that will hold the site ground
covers I think I named to tblGroundCovers, but to match
your
naming conventions, maybe you should name it
tblSite_Ground_Covers. The table name will need to be
changed in the code to whatever you end up using. The
fields
that I used were Site_Cover_ID (PK, Autonumber),
Site_FK(LongInteger) to hold the Site ID and
Ground_Cover_Type_FK (LongInteger) to hold the ground
cover
ID.

You will need to create a new unbound form that will hold
one command button that we will put the command to run the
routine in. Let me know when all this is ready and I'll
help
you step through the code to make sure it is working right
before we do the whole run.

Gary Miller

Hi Gary,

The tblGround_Cover_Type I created below is new,
following
your suggestion. The layout for my ground cover type
table in the original database is this....
tblGround_Cover_Type [Site_ID] Text and the 15 Ground
Cover Types each with Yes/No Field (Except for the last
two, other, which are text fields. I will be able to
export those and re-import them into the new database
with
relative ease).

The name of the field that holds siteID in the table is
[Site_ID].

I am running Access 2000.

Thank you again for your help.

Jonathan
-----Original Message-----
Hi Gary,

Wednesday morning, and I am reading over your posts
and I
will reply asap.

Jonathan
-----Original Message-----
Ok, here is the code routine to loop through the table
and
do a conversion and dump to the new table. We may need
to
make some adjustments as I don't have all the answers
back
on table and field names yet, but they should be
minor.
I
am
leaving all error handling out because if you have
something
unusual in the table that bombs the code, I will want
to
know exactly which line caused the error. I did put
in a
lot
of comments to explain what is going on.

We will create a new form with a command button on it
to
run
it when we are ready to go. Let me know when you have
it
and
are ready. I believe that we are working with a bit
of a
time zone difference here.

Gary Miller
Sisters, OR
*****************************************
Sub TransferGroundCover()

Dim db As Database, rsOld As Recordset, rsNew As
Recordset
Dim fld As Field
Dim lngSite As Long, lngGCID As Long

Set db = CurrentDb
' Open up the original table behind the scenes
Set rsOld = db.OpenRecordset("tblGroundCovTest")
' Open up the new table behind the scenes
Set rsNew = db.OpenRecordset("tblGroundCovers")

' 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
lngSite = !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
lngGCID = fld.Name
' Go to the new table
With rsNew
' Poke in a new record with these
values
.AddNew
!Site_FK = lngSite
!Ground_Cover_Type_FK = lngGCID
.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

*****************************************
Thanks gerry, here is what I have


.

.



.

.

 

[Jonathan]

Hi Gary,

The part I am confused about are lngSite and lngGCID where
it asks for initially in DIM and when it does the search
and put in part. My field name for Site ID in the
original table is [Site_ID]. I am not sure if I should
make changes to lngGCID.

I't that time of the day again, I'll keep working on this
this evening and provide an update in the AM.

Jonathan

-----Original Message-----
It is intended to work with the version of the
tblGround_Cover_Type that you renamed the type Y/N's to 1,
2, 3, etc... and dump the records into the tblGroundCovers.
The new second table you will use as a combobox lookup table
to select your GroundCovers in the future on your subform
that you will put on your site form.

I was guessing on your field names when I created the loop
code so they may not match yours. If you set up
tblGroundCovers with the names I gave you, that part should
be fine. You may need to adjust the name in the code to
match your SiteID field name. If you are still confused post
the part that is confusing.

To get the command button to run the code go in design view
doubleclick on the button to get the properties window for
it open. Go to the Events tab and find the line for the
OnClick event. Click into that line and then click on the
grey to the right of that line and you will get a builder
button. Click on that and you will get a window with three
options. Select 'Code' from that window and you will get
dumped into the OnClick sub of the form code module. Type in
the following...

Call TransferGroundCover

Close the code window and hit the save button. You will now
be ready to run it when you put the form back into form
view. If there is still a problem with getting the field and
table names right you will get an error message and options
to Debug or End. Hit the Debug button and it will take you
to the line of code that is generating the problem. The line
should be highlighted in yellow. If that happens copy the
line into a post to me and we'll get it straight. If it
works, you will get a message box telling you that it is
done.

You should make a backup of the db just for safety of
course, although I am not changing any of your data.

Gary Miller

Hi Gary,

I have the loop in Module1 now, I tried running it as a
command in a unbound form, clicked on the command and
nothing happened.

Few questions for you, I have three tables, the original
table (tblGround_Cover_Type), a table I created yesterday
(tblGround_Cover_Types) (Notes "s")which assigns a number
to each cover type, Finally, the third table,
tblGroundCovers in which you suggested also. Does the
loop work with all three tables? or just the two. I think
it just works with the original and the last table
mentioned above.

I am also confused with the field names you have in the
loop.

Jonathan

-----Original Message-----
If you want to add the two fields, it would be easy to
include them in the loop although it would result in the
fields being included for each ground cover. May be better
to set them up in the site table and append them there if
there is only one entry for them per site.

Gary

I think the only modification I need to make would be to
rename, in your loop, the name you assigned for my
original table to "tblGround_Cover_Type".

I did rename the fields in that table (1,2,3 and so on)
but left out the last two as others as they are text
fields. I will cut and paste them to a new table to be
applied later on in the report, for all intensive
purposes, this should be fine.

Now, I am ready to test this and see if it works,

I'll
get

back to you in a few minutes.

Jonathan
-----Original Message-----
Jonathan,

I am here and around for a bit so timing is good for me.

I assume that you have the routine that I posted. What
you
will want to do with it is to paste it into a global
module,
either existing or a new one. Then you will want to
examine
it and make adjustments to any of the field/table names
to
match yours. The new table that will hold the site ground
covers I think I named to tblGroundCovers, but to match
your
naming conventions, maybe you should name it
tblSite_Ground_Covers. The table name will need to be
changed in the code to whatever you end up using. The
fields
that I used were Site_Cover_ID (PK, Autonumber),
Site_FK(LongInteger) to hold the Site ID and
Ground_Cover_Type_FK (LongInteger) to hold the ground
cover
ID.

You will need to create a new unbound form that will hold
one command button that we will put the command to run
the
routine in. Let me know when all this is ready and I'll
help
you step through the code to make sure it is working
right
before we do the whole run.

Gary Miller

Hi Gary,

The tblGround_Cover_Type I created below is new,
following
your suggestion. The layout for my ground cover type
table in the original database is this....
tblGround_Cover_Type [Site_ID] Text and the 15 Ground
Cover Types each with Yes/No Field (Except for the last
two, other, which are text fields. I will be able to
export those and re-import them into the new database
with
relative ease).

The name of the field that holds siteID in the

table
is

[Site_ID].

I am running Access 2000.

Thank you again for your help.

Jonathan
-----Original Message-----
Hi Gary,

Wednesday morning, and I am reading over your posts
and I
will reply asap.

Jonathan
-----Original Message-----
Ok, here is the code routine to loop through the
table
and
do a conversion and dump to the new table. We may
need
to
make some adjustments as I don't have all the answers
back
on table and field names yet, but they should be
minor.
I
am
leaving all error handling out because if you have
something
unusual in the table that bombs the code, I will want
to
know exactly which line caused the error. I did put
in a
lot
of comments to explain what is going on.

We will create a new form with a command button

on
it

to
run
it when we are ready to go. Let me know when you have
it
and
are ready. I believe that we are working with a bit
of a
time zone difference here.

Gary Miller
Sisters, OR
*****************************************
Sub TransferGroundCover()

Dim db As Database, rsOld As Recordset, rsNew As
Recordset
Dim fld As Field
Dim lngSite As Long, lngGCID As Long

Set db = CurrentDb
' Open up the original table behind the scenes
Set rsOld = db.OpenRecordset("tblGroundCovTest")
' Open up the new table behind the scenes
Set rsNew = db.OpenRecordset("tblGroundCovers")

' 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
lngSite = !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
lngGCID = fld.Name
' Go to the new table
With rsNew
' Poke in a new record with these
values
.AddNew
!Site_FK = lngSite
!Ground_Cover_Type_FK = lngGCID
.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

*****************************************
Thanks gerry, here is what I have


.

.



.



.

.

 

[Gary Miller]

Jonathan,

Those are just 'variable' names for the temporary data
holders that I use in the code. The 'Dim' statement tells
access to reserve that name for a variable I can use to
shuttle data around the code. Kind of like the algebra
variables 'x + y = c'. You don't need or want to change
them.

Did you put the code line in the OnClick event of the
button? If so, nothing more to work on. Hit the button and
if there is a problem, access will tell you where and what
when it hits an error if you select Debug from the error
message. Just let me know which line it stops on.

Gary

Hi Gary,

The part I am confused about are lngSite and lngGCID where
it asks for initially in DIM and when it does the search
and put in part. My field name for Site ID in the
original table is [Site_ID]. I am not sure if I should
make changes to lngGCID.

I't that time of the day again, I'll keep working on this
this evening and provide an update in the AM.

Jonathan

-----Original Message-----
It is intended to work with the version of the
tblGround_Cover_Type that you renamed the type Y/N's to 1,
2, 3, etc... and dump the records into the tblGroundCovers.
The new second table you will use as a combobox lookup table
to select your GroundCovers in the future on your subform
that you will put on your site form.

I was guessing on your field names when I created the loop
code so they may not match yours. If you set up
tblGroundCovers with the names I gave you, that part should
be fine. You may need to adjust the name in the code to
match your SiteID field name. If you are still confused post
the part that is confusing.

To get the command button to run the code go in design view
doubleclick on the button to get the properties window for
it open. Go to the Events tab and find the line for the
OnClick event. Click into that line and then click on the
grey to the right of that line and you will get a builder
button. Click on that and you will get a window with three
options. Select 'Code' from that window and you will get
dumped into the OnClick sub of the form code module. Type in
the following...

Call TransferGroundCover

Close the code window and hit the save button. You will now
be ready to run it when you put the form back into form
view. If there is still a problem with getting the field and
table names right you will get an error message and options
to Debug or End. Hit the Debug button and it will take you
to the line of code that is generating the problem. The line
should be highlighted in yellow. If that happens copy the
line into a post to me and we'll get it straight. If it
works, you will get a message box telling you that it is
done.

You should make a backup of the db just for safety of
course, although I am not changing any of your data.

Gary Miller

Hi Gary,

I have the loop in Module1 now, I tried running it as a
command in a unbound form, clicked on the command and
nothing happened.

Few questions for you, I have three tables, the original
table (tblGround_Cover_Type), a table I created yesterday
(tblGround_Cover_Types) (Notes "s")which assigns a number
to each cover type, Finally, the third table,
tblGroundCovers in which you suggested also. Does the
loop work with all three tables? or just the two. I think
it just works with the original and the last table
mentioned above.

I am also confused with the field names you have in the
loop.

Jonathan
-----Original Message-----
If you want to add the two fields, it would be easy to
include them in the loop although it would result in the
fields being included for each ground cover. May be better
to set them up in the site table and append them there if
there is only one entry for them per site.

Gary

I think the only modification I need to make would

be
to

rename, in your loop, the name you assigned for my
original table to "tblGround_Cover_Type".

I did rename the fields in that table (1,2,3 and so on)
but left out the last two as others as they are text
fields. I will cut and paste them to a new table to be
applied later on in the report, for all intensive
purposes, this should be fine.

Now, I am ready to test this and see if it works, I'll
get
back to you in a few minutes.

Jonathan
-----Original Message-----
Jonathan,

I am here and around for a bit so timing is good for
me.

I assume that you have the routine that I posted. What
you
will want to do with it is to paste it into a global
module,
either existing or a new one. Then you will want to
examine
it and make adjustments to any of the field/table names
to
match yours. The new table that will hold the site
ground
covers I think I named to tblGroundCovers, but to match
your
naming conventions, maybe you should name it
tblSite_Ground_Covers. The table name will need to be
changed in the code to whatever you end up using. The
fields
that I used were Site_Cover_ID (PK, Autonumber),
Site_FK(LongInteger) to hold the Site ID and
Ground_Cover_Type_FK (LongInteger) to hold the ground
cover
ID.

You will need to create a new unbound form that will
hold
one command button that we will put the command to run
the
routine in. Let me know when all this is ready and I'll
help
you step through the code to make sure it is working
right
before we do the whole run.

Gary Miller

message
Hi Gary,

The tblGround_Cover_Type I created below is new,
following
your suggestion. The layout for my ground cover type
table in the original database is this....
tblGround_Cover_Type [Site_ID] Text and the 15 Ground
Cover Types each with Yes/No Field (Except for the
last
two, other, which are text fields. I will be able to
export those and re-import them into the new database
with
relative ease).

The name of the field that holds siteID in the table
is
[Site_ID].

I am running Access 2000.

Thank you again for your help.

Jonathan
-----Original Message-----
Hi Gary,

Wednesday morning, and I am reading over your posts
and I
will reply asap.

Jonathan
-----Original Message-----
Ok, here is the code routine to loop through the
table
and
do a conversion and dump to the new table. We may
need
to
make some adjustments as I don't have all the
answers
back
on table and field names yet, but they should be
minor.
I
am
leaving all error handling out because if you have
something
unusual in the table that bombs the code, I will
want
to
know exactly which line caused the error. I did put
in a
lot
of comments to explain what is going on.

We will create a new form with a command button on
it
to
run
it when we are ready to go. Let me know when you
have
it
and
are ready. I believe that we are working with a bit
of a
time zone difference here.

Gary Miller
Sisters, OR
*****************************************
Sub TransferGroundCover()

Dim db As Database, rsOld As Recordset, rsNew As
Recordset
Dim fld As Field
Dim lngSite As Long, lngGCID As Long

Set db = CurrentDb
' Open up the original table behind the scenes
Set rsOld = db.OpenRecordset("tblGroundCovTest")
' Open up the new table behind the scenes
Set rsNew = db.OpenRecordset("tblGroundCovers")

' 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
lngSite = !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
lngGCID = fld.Name
' Go to the new table
With rsNew
' Poke in a new record with
these
values
.AddNew
!Site_FK = lngSite
!Ground_Cover_Type_FK = lngGCID
.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

*****************************************
Thanks gerry, here is what I have


.

.



.



.

.

 

[Jonathan]

I have recieved an error message - Compile Error: Usder
defined type not defined and highlights db As Database in
blue.

Jonathan

-----Original Message-----
Jonathan,

Those are just 'variable' names for the temporary data
holders that I use in the code. The 'Dim' statement tells
access to reserve that name for a variable I can use to
shuttle data around the code. Kind of like the algebra
variables 'x + y = c'. You don't need or want to change
them.

Did you put the code line in the OnClick event of the
button? If so, nothing more to work on. Hit the button and
if there is a problem, access will tell you where and what
when it hits an error if you select Debug from the error
message. Just let me know which line it stops on.

Gary

Hi Gary,

The part I am confused about are lngSite and lngGCID where
it asks for initially in DIM and when it does the search
and put in part. My field name for Site ID in the
original table is [Site_ID]. I am not sure if I should
make changes to lngGCID.

I't that time of the day again, I'll keep working on this
this evening and provide an update in the AM.

Jonathan

-----Original Message-----
It is intended to work with the version of the
tblGround_Cover_Type that you renamed the type Y/N's to 1,
2, 3, etc... and dump the records into the tblGroundCovers.
The new second table you will use as a combobox lookup table
to select your GroundCovers in the future on your subform
that you will put on your site form.

I was guessing on your field names when I created the loop
code so they may not match yours. If you set up
tblGroundCovers with the names I gave you, that part should
be fine. You may need to adjust the name in the code to
match your SiteID field name. If you are still confused post
the part that is confusing.

To get the command button to run the code go in design view
doubleclick on the button to get the properties window for
it open. Go to the Events tab and find the line for the
OnClick event. Click into that line and then click on the
grey to the right of that line and you will get a builder
button. Click on that and you will get a window with three
options. Select 'Code' from that window and you will get
dumped into the OnClick sub of the form code module.

Type
in

the following...

Call TransferGroundCover

Close the code window and hit the save button. You will now
be ready to run it when you put the form back into form
view. If there is still a problem with getting the

field
and

table names right you will get an error message and options
to Debug or End. Hit the Debug button and it will take you
to the line of code that is generating the problem. The line
should be highlighted in yellow. If that happens copy the
line into a post to me and we'll get it straight. If it
works, you will get a message box telling you that it is
done.

You should make a backup of the db just for safety of
course, although I am not changing any of your data.

Gary Miller


Hi Gary,

I have the loop in Module1 now, I tried running it as a
command in a unbound form, clicked on the command and
nothing happened.

Few questions for you, I have three tables, the original
table (tblGround_Cover_Type), a table I created yesterday
(tblGround_Cover_Types) (Notes "s")which assigns a number
to each cover type, Finally, the third table,
tblGroundCovers in which you suggested also. Does the
loop work with all three tables? or just the two. I think
it just works with the original and the last table
mentioned above.

I am also confused with the field names you have in the
loop.

Jonathan
-----Original Message-----
If you want to add the two fields, it would be easy to
include them in the loop although it would result in the
fields being included for each ground cover. May be
better
to set them up in the site table and append them

there
if

there is only one entry for them per site.

Gary

I think the only modification I need to make would be
to
rename, in your loop, the name you assigned for my
original table to "tblGround_Cover_Type".

I did rename the fields in that table (1,2,3 and

so
on)

but left out the last two as others as they are text
fields. I will cut and paste them to a new table

to
be

applied later on in the report, for all intensive
purposes, this should be fine.

Now, I am ready to test this and see if it works, I'll
get
back to you in a few minutes.

Jonathan
-----Original Message-----
Jonathan,

I am here and around for a bit so timing is good for
me.

I assume that you have the routine that I posted. What
you
will want to do with it is to paste it into a global
module,
either existing or a new one. Then you will want to
examine
it and make adjustments to any of the field/table
names
to
match yours. The new table that will hold the site
ground
covers I think I named to tblGroundCovers, but to
match
your
naming conventions, maybe you should name it
tblSite_Ground_Covers. The table name will need

to
be

changed in the code to whatever you end up using. The
fields
that I used were Site_Cover_ID (PK, Autonumber),
Site_FK(LongInteger) to hold the Site ID and
Ground_Cover_Type_FK (LongInteger) to hold the ground
cover
ID.

You will need to create a new unbound form that will
hold
one command button that we will put the command

to
run

the
routine in. Let me know when all this is ready and
I'll
help
you step through the code to make sure it is working
right
before we do the whole run.

Gary Miller

message
Hi Gary,

The tblGround_Cover_Type I created below is new,
following
your suggestion. The layout for my ground cover
type
table in the original database is this....
tblGround_Cover_Type [Site_ID] Text and the 15
Ground
Cover Types each with Yes/No Field (Except for the
last
two, other, which are text fields. I will be

able
to

export those and re-import them into the new
database
with
relative ease).

The name of the field that holds siteID in the table
is
[Site_ID].

I am running Access 2000.

Thank you again for your help.

Jonathan
-----Original Message-----
Hi Gary,

Wednesday morning, and I am reading over your posts
and I
will reply asap.

Jonathan
-----Original Message-----
Ok, here is the code routine to loop through the
table
and
do a conversion and dump to the new table. We may
need
to
make some adjustments as I don't have all the
answers
back
on table and field names yet, but they should be
minor.
I
am
leaving all error handling out because if you have
something
unusual in the table that bombs the code, I will
want
to
know exactly which line caused the error. I did
put
in a
lot
of comments to explain what is going on.

We will create a new form with a command

button
on

it
to
run
it when we are ready to go. Let me know when you
have
it
and
are ready. I believe that we are working with a
bit
of a
time zone difference here.

Gary Miller
Sisters, OR
*****************************************
Sub TransferGroundCover()

Dim db As Database, rsOld As Recordset, rsNew As
Recordset
Dim fld As Field
Dim lngSite As Long, lngGCID As Long

Set db = CurrentDb
' Open up the original table behind the scenes
Set rsOld = db.OpenRecordset("tblGroundCovTest")
' Open up the new table behind the scenes
Set rsNew = db.OpenRecordset ("tblGroundCovers")

' 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
lngSite = !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
lngGCID = fld.Name
' Go to the new table
With rsNew
' Poke in a new record with
these
values
.AddNew
!Site_FK = lngSite
!Ground_Cover_Type_FK =
lngGCID
.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

*****************************************
[email protected]...
Thanks gerry, here is what I have


.

.



.



.



.

.

 

[Gary Miller]

Access 2000 right. It needs a reference to the DAO library.
Change that line to...

Dim db as DAO.Database.

If you haven't already reset the code, do that by clicking
on the black rectangle on the toolbar that will say 'Reset'
when you hold your mouse over it. Now in the same window, go
to Tools/References and see if there is a 'checked'
reference that refers to DAO, probably 3.6. If not we will
need to add that.

Gary Miller

I have recieved an error message - Compile Error: Usder
defined type not defined and highlights db As Database in
blue.

Jonathan

-----Original Message-----
Jonathan,

Those are just 'variable' names for the temporary data
holders that I use in the code. The 'Dim' statement tells
access to reserve that name for a variable I can use to
shuttle data around the code. Kind of like the algebra
variables 'x + y = c'. You don't need or want to change
them.

Did you put the code line in the OnClick event of the
button? If so, nothing more to work on. Hit the button and
if there is a problem, access will tell you where and what
when it hits an error if you select Debug from the error
message. Just let me know which line it stops on.

Gary

Hi Gary,

The part I am confused about are lngSite and lngGCID where
it asks for initially in DIM and when it does the search
and put in part. My field name for Site ID in the
original table is [Site_ID]. I am not sure if I should
make changes to lngGCID.

I't that time of the day again, I'll keep working on this
this evening and provide an update in the AM.

Jonathan
-----Original Message-----
It is intended to work with the version of the
tblGround_Cover_Type that you renamed the type Y/N's

to
1,

2, 3, etc... and dump the records into the
tblGroundCovers.
The new second table you will use as a combobox lookup
table
to select your GroundCovers in the future on your subform
that you will put on your site form.

I was guessing on your field names when I created the loop
code so they may not match yours. If you set up
tblGroundCovers with the names I gave you, that part
should
be fine. You may need to adjust the name in the code to
match your SiteID field name. If you are still confused
post
the part that is confusing.

To get the command button to run the code go in design
view
doubleclick on the button to get the properties window for
it open. Go to the Events tab and find the line for the
OnClick event. Click into that line and then click on the
grey to the right of that line and you will get a builder
button. Click on that and you will get a window with three
options. Select 'Code' from that window and you will get
dumped into the OnClick sub of the form code module. Type
in
the following...

Call TransferGroundCover

Close the code window and hit the save button. You will
now
be ready to run it when you put the form back into form
view. If there is still a problem with getting the field
and
table names right you will get an error message and
options
to Debug or End. Hit the Debug button and it will take you
to the line of code that is generating the problem. The
line
should be highlighted in yellow. If that happens copy the
line into a post to me and we'll get it straight. If it
works, you will get a message box telling you that it is
done.

You should make a backup of the db just for safety of
course, although I am not changing any of your data.

Gary Miller


Hi Gary,

I have the loop in Module1 now, I tried running it as a
command in a unbound form, clicked on the command and
nothing happened.

Few questions for you, I have three tables, the original
table (tblGround_Cover_Type), a table I created
yesterday
(tblGround_Cover_Types) (Notes "s")which assigns a
number
to each cover type, Finally, the third table,
tblGroundCovers in which you suggested also. Does the
loop work with all three tables? or just the two. I
think
it just works with the original and the last table
mentioned above.

I am also confused with the field names you have in the
loop.

Jonathan
-----Original Message-----
If you want to add the two fields, it would be easy to
include them in the loop although it would result in
the
fields being included for each ground cover. May be
better
to set them up in the site table and append them there
if
there is only one entry for them per site.

Gary

message
I think the only modification I need to make

would
be

to
rename, in your loop, the name you assigned for my
original table to "tblGround_Cover_Type".

I did rename the fields in that table (1,2,3 and so
on)
but left out the last two as others as they are text
fields. I will cut and paste them to a new table to
be
applied later on in the report, for all intensive
purposes, this should be fine.

Now, I am ready to test this and see if it works,
I'll
get
back to you in a few minutes.

Jonathan
-----Original Message-----
Jonathan,

I am here and around for a bit so timing is good for
me.

I assume that you have the routine that I posted.
What
you
will want to do with it is to paste it into a global
module,
either existing or a new one. Then you will want to
examine
it and make adjustments to any of the field/table
names
to
match yours. The new table that will hold the site
ground
covers I think I named to tblGroundCovers, but to
match
your
naming conventions, maybe you should name it
tblSite_Ground_Covers. The table name will need to
be
changed in the code to whatever you end up using.
The
fields
that I used were Site_Cover_ID (PK, Autonumber),
Site_FK(LongInteger) to hold the Site ID and
Ground_Cover_Type_FK (LongInteger) to hold the
ground
cover
ID.

You will need to create a new unbound form that will
hold
one command button that we will put the command to
run
the
routine in. Let me know when all this is ready and
I'll
help
you step through the code to make sure it is working
right
before we do the whole run.

Gary Miller

message
Hi Gary,

The tblGround_Cover_Type I created below is new,
following
your suggestion. The layout for my ground cover
type
table in the original database is this....
tblGround_Cover_Type [Site_ID] Text and the 15
Ground
Cover Types each with Yes/No Field (Except for the
last
two, other, which are text fields. I will be able
to
export those and re-import them into the new
database
with
relative ease).

The name of the field that holds siteID in the
table
is
[Site_ID].

I am running Access 2000.

Thank you again for your help.

Jonathan
-----Original Message-----
Hi Gary,

Wednesday morning, and I am reading over your
posts
and I
will reply asap.

Jonathan
-----Original Message-----
Ok, here is the code routine to loop through the
table
and
do a conversion and dump to the new table. We
may
need
to
make some adjustments as I don't have all the
answers
back
on table and field names yet, but they

should
be

minor.
I
am
leaving all error handling out because if you
have
something
unusual in the table that bombs the code, I will
want
to
know exactly which line caused the error. I did
put
in a
lot
of comments to explain what is going on.

We will create a new form with a command button
on
it
to
run
it when we are ready to go. Let me know when you
have
it
and
are ready. I believe that we are working with a
bit
of a
time zone difference here.

Gary Miller
Sisters, OR
*****************************************
Sub TransferGroundCover()

Dim db As Database, rsOld As Recordset,

rsNew
As

Recordset
Dim fld As Field
Dim lngSite As Long, lngGCID As Long

Set db = CurrentDb
' Open up the original table behind the scenes
Set rsOld = db.OpenRecordset("tblGroundCovTest")
' Open up the new table behind the scenes
Set rsNew = db.OpenRecordset ("tblGroundCovers")

' 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
lngSite = !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
lngGCID = fld.Name
' Go to the new table
With rsNew
' Poke in a new record with
these
values
.AddNew
!Site_FK = lngSite
!Ground_Cover_Type_FK =
lngGCID
.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

*****************************************
message
[email protected]...
Thanks gerry, here is what I have


.

.



.



.



.

.

 

[Jonathan]

Almost got it now I think. Got past the DAO trouble, now
it says that it cannot find the table Ground_Cover_Type.

The spelling is the same, this is for the original table
right? I don't understand it, the table is there, but VBA
is saying it's not.

Jonathan

-----Original Message-----
Access 2000 right. It needs a reference to the DAO library.
Change that line to...

Dim db as DAO.Database.

If you haven't already reset the code, do that by clicking
on the black rectangle on the toolbar that will say 'Reset'
when you hold your mouse over it. Now in the same window, go
to Tools/References and see if there is a 'checked'
reference that refers to DAO, probably 3.6. If not we will
need to add that.

Gary Miller

I have recieved an error message - Compile Error: Usder
defined type not defined and highlights db As Database in
blue.

Jonathan

-----Original Message-----
Jonathan,

Those are just 'variable' names for the temporary data
holders that I use in the code. The 'Dim' statement tells
access to reserve that name for a variable I can use to
shuttle data around the code. Kind of like the algebra
variables 'x + y = c'. You don't need or want to change
them.

Did you put the code line in the OnClick event of the
button? If so, nothing more to work on. Hit the button and
if there is a problem, access will tell you where and what
when it hits an error if you select Debug from the error
message. Just let me know which line it stops on.

Gary

Hi Gary,

The part I am confused about are lngSite and lngGCID where
it asks for initially in DIM and when it does the search
and put in part. My field name for Site ID in the
original table is [Site_ID]. I am not sure if I should
make changes to lngGCID.

I't that time of the day again, I'll keep working on this
this evening and provide an update in the AM.

Jonathan
-----Original Message-----
It is intended to work with the version of the
tblGround_Cover_Type that you renamed the type Y/N's to
1,
2, 3, etc... and dump the records into the
tblGroundCovers.
The new second table you will use as a combobox lookup
table
to select your GroundCovers in the future on your subform
that you will put on your site form.

I was guessing on your field names when I created the
loop
code so they may not match yours. If you set up
tblGroundCovers with the names I gave you, that part
should
be fine. You may need to adjust the name in the code to
match your SiteID field name. If you are still confused
post
the part that is confusing.

To get the command button to run the code go in design
view
doubleclick on the button to get the properties window
for
it open. Go to the Events tab and find the line for the
OnClick event. Click into that line and then click

on
the

grey to the right of that line and you will get a builder
button. Click on that and you will get a window with
three
options. Select 'Code' from that window and you will get
dumped into the OnClick sub of the form code module. Type
in
the following...

Call TransferGroundCover

Close the code window and hit the save button. You will
now
be ready to run it when you put the form back into form
view. If there is still a problem with getting the field
and
table names right you will get an error message and
options
to Debug or End. Hit the Debug button and it will take
you
to the line of code that is generating the problem. The
line
should be highlighted in yellow. If that happens

copy
the

line into a post to me and we'll get it straight. If it
works, you will get a message box telling you that

it
is

done.

You should make a backup of the db just for safety of
course, although I am not changing any of your data.

Gary Miller


Hi Gary,

I have the loop in Module1 now, I tried running it as a
command in a unbound form, clicked on the command and
nothing happened.

Few questions for you, I have three tables, the
original
table (tblGround_Cover_Type), a table I created
yesterday
(tblGround_Cover_Types) (Notes "s")which assigns a
number
to each cover type, Finally, the third table,
tblGroundCovers in which you suggested also. Does the
loop work with all three tables? or just the two. I
think
it just works with the original and the last table
mentioned above.

I am also confused with the field names you have

in
the

loop.

Jonathan
-----Original Message-----
If you want to add the two fields, it would be

easy
to

include them in the loop although it would result in
the
fields being included for each ground cover. May be
better
to set them up in the site table and append them there
if
there is only one entry for them per site.

Gary

message
I think the only modification I need to make would
be
to
rename, in your loop, the name you assigned for my
original table to "tblGround_Cover_Type".

I did rename the fields in that table (1,2,3

and
so

on)
but left out the last two as others as they are text
fields. I will cut and paste them to a new

table
to

be
applied later on in the report, for all intensive
purposes, this should be fine.

Now, I am ready to test this and see if it works,
I'll
get
back to you in a few minutes.

Jonathan
-----Original Message-----
Jonathan,

I am here and around for a bit so timing is good
for
me.

I assume that you have the routine that I posted.
What
you
will want to do with it is to paste it into a
global
module,
either existing or a new one. Then you will

want
to

examine
it and make adjustments to any of the field/table
names
to
match yours. The new table that will hold the site
ground
covers I think I named to tblGroundCovers, but to
match
your
naming conventions, maybe you should name it
tblSite_Ground_Covers. The table name will

need
to

be
changed in the code to whatever you end up using.
The
fields
that I used were Site_Cover_ID (PK, Autonumber),
Site_FK(LongInteger) to hold the Site ID and
Ground_Cover_Type_FK (LongInteger) to hold the
ground
cover
ID.

You will need to create a new unbound form that
will
hold
one command button that we will put the

command
to

run
the
routine in. Let me know when all this is ready and
I'll
help
you step through the code to make sure it is
working
right
before we do the whole run.

Gary Miller

message
Hi Gary,

The tblGround_Cover_Type I created below is new,
following
your suggestion. The layout for my ground cover
type
table in the original database is this....
tblGround_Cover_Type [Site_ID] Text and the 15
Ground
Cover Types each with Yes/No Field (Except for
the
last
two, other, which are text fields. I will be able
to
export those and re-import them into the new
database
with
relative ease).

The name of the field that holds siteID in the
table
is
[Site_ID].

I am running Access 2000.

Thank you again for your help.

Jonathan
-----Original Message-----
Hi Gary,

Wednesday morning, and I am reading over your
posts
and I
will reply asap.

Jonathan
-----Original Message-----
Ok, here is the code routine to loop through
the
table
and
do a conversion and dump to the new table. We
may
need
to
make some adjustments as I don't have all the
answers
back
on table and field names yet, but they should
be
minor.
I
am
leaving all error handling out because if you
have
something
unusual in the table that bombs the code, I
will
want
to
know exactly which line caused the error.

I
did

put
in a
lot
of comments to explain what is going on.

We will create a new form with a command button
on
it
to
run
it when we are ready to go. Let me know when
you
have
it
and
are ready. I believe that we are working with a
bit
of a
time zone difference here.

Gary Miller
Sisters, OR
*****************************************
Sub TransferGroundCover()

Dim db As Database, rsOld As Recordset, rsNew
As
Recordset
Dim fld As Field
Dim lngSite As Long, lngGCID As Long

Set db = CurrentDb
' Open up the original table behind the scenes
Set rsOld =
db.OpenRecordset("tblGroundCovTest")
' Open up the new table behind the scenes
Set rsNew = db.OpenRecordset ("tblGroundCovers")

' 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
lngSite = !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
lngGCID = fld.Name
' Go to the new table
With rsNew
' Poke in a new record with
these
values
.AddNew
!Site_FK = lngSite
!Ground_Cover_Type_FK =
lngGCID
.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

*****************************************
message
[email protected]...
Thanks gerry, here is what I have


.

.



.



.



.



.

.

 

[Gary Miller]

This is the original part of the code that I sent where it
would look for the table...

Set db = CurrentDb
' Open up the original table behind the scenes
Set rsOld = db.OpenRecordset("tblGroundCovTest")
' Open up the new table behind the scenes
Set rsNew = db.OpenRecordset("tblGroundCovers")

Looking back through our posts I am not totally clear. The
rsOld line should open up the table where you renamed the
field names to 1, 2, 3, 4 etc... so make sure that line
refers to that table with your spelling and syntax.

The second line, rsNew, should refer to the new table that
holds the Site_ID and the GroundCover_ID. I am not sure what
you named this, but here are the original instructions that
I sent you about that...

"The new table that will hold the site ground
covers I think I named to tblGroundCovers, but to match your
naming conventions, maybe you should name it
tblSite_Ground_Covers. The table name will need to be
changed in the code to whatever you end up using. The fields
that I used were Site_Cover_ID (PK, Autonumber),
Site_FK(LongInteger) to hold the Site ID and
Ground_Cover_Type_FK (LongInteger) to hold the ground cover
ID."

If you are still having a problem after this, post exactly
what your code is reading now.

Gary

Almost got it now I think. Got past the DAO trouble, now
it says that it cannot find the table Ground_Cover_Type.

The spelling is the same, this is for the original table
right? I don't understand it, the table is there, but VBA
is saying it's not.

Jonathan

-----Original Message-----
Access 2000 right. It needs a reference to the DAO library.
Change that line to...

Dim db as DAO.Database.

If you haven't already reset the code, do that by clicking
on the black rectangle on the toolbar that will say 'Reset'
when you hold your mouse over it. Now in the same window, go
to Tools/References and see if there is a 'checked'
reference that refers to DAO, probably 3.6. If not we will
need to add that.

Gary Miller

I have recieved an error message - Compile Error: Usder
defined type not defined and highlights db As Database in
blue.

Jonathan
-----Original Message-----
Jonathan,

Those are just 'variable' names for the temporary data
holders that I use in the code. The 'Dim' statement tells
access to reserve that name for a variable I can use to
shuttle data around the code. Kind of like the algebra
variables 'x + y = c'. You don't need or want to change
them.

Did you put the code line in the OnClick event of the
button? If so, nothing more to work on. Hit the button and
if there is a problem, access will tell you where and what
when it hits an error if you select Debug from the error
message. Just let me know which line it stops on.

Gary

Hi Gary,

The part I am confused about are lngSite and lngGCID
where
it asks for initially in DIM and when it does the search
and put in part. My field name for Site ID in the
original table is [Site_ID]. I am not sure if I should
make changes to lngGCID.

I't that time of the day again, I'll keep working on
this
this evening and provide an update in the AM.

Jonathan
-----Original Message-----
It is intended to work with the version of the
tblGround_Cover_Type that you renamed the type

Y/N's
to

1,
2, 3, etc... and dump the records into the
tblGroundCovers.
The new second table you will use as a combobox lookup
table
to select your GroundCovers in the future on your
subform
that you will put on your site form.

I was guessing on your field names when I created the
loop
code so they may not match yours. If you set up
tblGroundCovers with the names I gave you, that part
should
be fine. You may need to adjust the name in the

code
to

match your SiteID field name. If you are still confused
post
the part that is confusing.

To get the command button to run the code go in design
view
doubleclick on the button to get the properties window
for
it open. Go to the Events tab and find the line for the
OnClick event. Click into that line and then click on
the
grey to the right of that line and you will get a
builder
button. Click on that and you will get a window with
three
options. Select 'Code' from that window and you will
get
dumped into the OnClick sub of the form code module.
Type
in
the following...

Call TransferGroundCover

Close the code window and hit the save button. You will
now
be ready to run it when you put the form back into form
view. If there is still a problem with getting the
field
and
table names right you will get an error message and
options
to Debug or End. Hit the Debug button and it will take
you
to the line of code that is generating the problem. The
line
should be highlighted in yellow. If that happens copy
the
line into a post to me and we'll get it straight.

If
it

works, you will get a message box telling you that it
is
done.

You should make a backup of the db just for safety of
course, although I am not changing any of your data.

Gary Miller


message
Hi Gary,

I have the loop in Module1 now, I tried running it
as a
command in a unbound form, clicked on the command and
nothing happened.

Few questions for you, I have three tables, the
original
table (tblGround_Cover_Type), a table I created
yesterday
(tblGround_Cover_Types) (Notes "s")which assigns a
number
to each cover type, Finally, the third table,
tblGroundCovers in which you suggested also. Does
the
loop work with all three tables? or just the two. I
think
it just works with the original and the last table
mentioned above.

I am also confused with the field names you have in
the
loop.

Jonathan
-----Original Message-----
If you want to add the two fields, it would be easy
to
include them in the loop although it would

result
in

the
fields being included for each ground cover. May be
better
to set them up in the site table and append them
there
if
there is only one entry for them per site.

Gary

message
I think the only modification I need to make would
be
to
rename, in your loop, the name you assigned

for
my

original table to "tblGround_Cover_Type".

I did rename the fields in that table (1,2,3 and
so
on)
but left out the last two as others as they are
text
fields. I will cut and paste them to a new table
to
be
applied later on in the report, for all intensive
purposes, this should be fine.

Now, I am ready to test this and see if it works,
I'll
get
back to you in a few minutes.

Jonathan
-----Original Message-----
Jonathan,

I am here and around for a bit so timing is good
for
me.

I assume that you have the routine that I posted.
What
you
will want to do with it is to paste it into a
global
module,
either existing or a new one. Then you will want
to
examine
it and make adjustments to any of the field/table
names
to
match yours. The new table that will hold the
site
ground
covers I think I named to tblGroundCovers,

but
to

match
your
naming conventions, maybe you should name it
tblSite_Ground_Covers. The table name will need
to
be
changed in the code to whatever you end up using.
The
fields
that I used were Site_Cover_ID (PK, Autonumber),
Site_FK(LongInteger) to hold the Site ID and
Ground_Cover_Type_FK (LongInteger) to hold the
ground
cover
ID.

You will need to create a new unbound form that
will
hold
one command button that we will put the command
to
run
the
routine in. Let me know when all this is ready
and
I'll
help
you step through the code to make sure it is
working
right
before we do the whole run.

Gary Miller

message
Hi Gary,

The tblGround_Cover_Type I created below is
new,
following
your suggestion. The layout for my ground
cover
type
table in the original database is this....
tblGround_Cover_Type [Site_ID] Text and the 15
Ground
Cover Types each with Yes/No Field (Except for
the
last
two, other, which are text fields. I will be
able
to
export those and re-import them into the new
database
with
relative ease).

The name of the field that holds siteID in the
table
is
[Site_ID].

I am running Access 2000.

Thank you again for your help.

Jonathan
-----Original Message-----
Hi Gary,

Wednesday morning, and I am reading over your
posts
and I
will reply asap.

Jonathan
-----Original Message-----
Ok, here is the code routine to loop through
the
table
and
do a conversion and dump to the new

table.
We

may
need
to
make some adjustments as I don't have all the
answers
back
on table and field names yet, but they should
be
minor.
I
am
leaving all error handling out because if you
have
something
unusual in the table that bombs the code, I
will
want
to
know exactly which line caused the error. I
did
put
in a
lot
of comments to explain what is going on.

We will create a new form with a command
button
on
it
to
run
it when we are ready to go. Let me know when
you
have
it
and
are ready. I believe that we are working
with a
bit
of a
time zone difference here.

Gary Miller
Sisters, OR
*****************************************
Sub TransferGroundCover()

Dim db As Database, rsOld As Recordset, rsNew
As
Recordset
Dim fld As Field
Dim lngSite As Long, lngGCID As Long

Set db = CurrentDb
' Open up the original table behind the
scenes
Set rsOld =
db.OpenRecordset("tblGroundCovTest")
' Open up the new table behind the scenes
Set rsNew = db.OpenRecordset
("tblGroundCovers")

' 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
lngSite = !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
lngGCID = fld.Name
' Go to the new table
With rsNew
' Poke in a new record
with
these
values
.AddNew
!Site_FK = lngSite
!Ground_Cover_Type_FK =
lngGCID
.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

*****************************************
message
[email protected]...
Thanks gerry, here is what I have


.

.



.



.



.



.

.

 

[Jonathan]

The error reads:

Run-time error '3078':
The microsoft jet database engine could not find the input
table or query 'tblGround_Cover_Type'. Make sure that it
exists and that it is spelled correctly.

-----Original Message-----
This is the original part of the code that I sent where it
would look for the table...

Set db = CurrentDb
' Open up the original table behind the scenes
Set rsOld = db.OpenRecordset("tblGroundCovTest")
' Open up the new table behind the scenes
Set rsNew = db.OpenRecordset("tblGroundCovers")

Looking back through our posts I am not totally clear. The
rsOld line should open up the table where you renamed the
field names to 1, 2, 3, 4 etc... so make sure that line
refers to that table with your spelling and syntax.

The second line, rsNew, should refer to the new table that
holds the Site_ID and the GroundCover_ID. I am not sure what
you named this, but here are the original instructions that
I sent you about that...

"The new table that will hold the site ground
covers I think I named to tblGroundCovers, but to match your
naming conventions, maybe you should name it
tblSite_Ground_Covers. The table name will need to be
changed in the code to whatever you end up using. The fields
that I used were Site_Cover_ID (PK, Autonumber),
Site_FK(LongInteger) to hold the Site ID and
Ground_Cover_Type_FK (LongInteger) to hold the ground cover
ID."

If you are still having a problem after this, post exactly
what your code is reading now.

Gary

Almost got it now I think. Got past the DAO trouble, now
it says that it cannot find the table Ground_Cover_Type.

The spelling is the same, this is for the original table
right? I don't understand it, the table is there, but VBA
is saying it's not.

Jonathan

-----Original Message-----
Access 2000 right. It needs a reference to the DAO library.
Change that line to...

Dim db as DAO.Database.

If you haven't already reset the code, do that by clicking
on the black rectangle on the toolbar that will say 'Reset'
when you hold your mouse over it. Now in the same

window,
go

to Tools/References and see if there is a 'checked'
reference that refers to DAO, probably 3.6. If not we will
need to add that.

Gary Miller

message I have recieved an error message - Compile Error: Usder
defined type not defined and highlights db As

Database
in

blue.

Jonathan
-----Original Message-----
Jonathan,

Those are just 'variable' names for the temporary data
holders that I use in the code. The 'Dim' statement tells
access to reserve that name for a variable I can use to
shuttle data around the code. Kind of like the algebra
variables 'x + y = c'. You don't need or want to change
them.

Did you put the code line in the OnClick event of the
button? If so, nothing more to work on. Hit the button
and
if there is a problem, access will tell you where and
what
when it hits an error if you select Debug from the error
message. Just let me know which line it stops on.

Gary

Hi Gary,

The part I am confused about are lngSite and lngGCID
where
it asks for initially in DIM and when it does the
search
and put in part. My field name for Site ID in the
original table is [Site_ID]. I am not sure if I should
make changes to lngGCID.

I't that time of the day again, I'll keep working on
this
this evening and provide an update in the AM.

Jonathan
-----Original Message-----
It is intended to work with the version of the
tblGround_Cover_Type that you renamed the type Y/N's
to
1,
2, 3, etc... and dump the records into the
tblGroundCovers.
The new second table you will use as a combobox lookup
table
to select your GroundCovers in the future on your
subform
that you will put on your site form.

I was guessing on your field names when I created the
loop
code so they may not match yours. If you set up
tblGroundCovers with the names I gave you, that part
should
be fine. You may need to adjust the name in the code
to
match your SiteID field name. If you are still
confused
post
the part that is confusing.

To get the command button to run the code go in design
view
doubleclick on the button to get the properties window
for
it open. Go to the Events tab and find the line for
the
OnClick event. Click into that line and then

click
on

the
grey to the right of that line and you will get a
builder
button. Click on that and you will get a window with
three
options. Select 'Code' from that window and you will
get
dumped into the OnClick sub of the form code module.
Type
in
the following...

Call TransferGroundCover

Close the code window and hit the save button. You
will
now
be ready to run it when you put the form back into
form
view. If there is still a problem with getting the
field
and
table names right you will get an error message and
options
to Debug or End. Hit the Debug button and it will take
you
to the line of code that is generating the problem.
The
line
should be highlighted in yellow. If that happens copy
the
line into a post to me and we'll get it straight. If
it
works, you will get a message box telling you

that
it

is
done.

You should make a backup of the db just for

safety
of

course, although I am not changing any of your data.

Gary Miller


message
Hi Gary,

I have the loop in Module1 now, I tried running it
as a
command in a unbound form, clicked on the command
and
nothing happened.

Few questions for you, I have three tables, the
original
table (tblGround_Cover_Type), a table I created
yesterday
(tblGround_Cover_Types) (Notes "s")which

assigns

a

number
to each cover type, Finally, the third table,
tblGroundCovers in which you suggested also. Does
the
loop work with all three tables? or just the

two.
I

think
it just works with the original and the last table
mentioned above.

I am also confused with the field names you

have
in

the
loop.

Jonathan
-----Original Message-----
If you want to add the two fields, it would be easy
to
include them in the loop although it would result
in
the
fields being included for each ground cover.

May
be

better
to set them up in the site table and append them
there
if
there is only one entry for them per site.

Gary

message
I think the only modification I need to make
would
be
to
rename, in your loop, the name you assigned for
my
original table to "tblGround_Cover_Type".

I did rename the fields in that table (1,2,3 and
so
on)
but left out the last two as others as they are
text
fields. I will cut and paste them to a new table
to
be
applied later on in the report, for all intensive
purposes, this should be fine.

Now, I am ready to test this and see if it works,
I'll
get
back to you in a few minutes.

Jonathan
-----Original Message-----
Jonathan,

I am here and around for a bit so timing is good
for
me.

I assume that you have the routine that I
posted.
What
you
will want to do with it is to paste it into a
global
module,
either existing or a new one. Then you will want
to
examine
it and make adjustments to any of the
field/table
names
to
match yours. The new table that will hold the
site
ground
covers I think I named to tblGroundCovers, but
to
match
your
naming conventions, maybe you should name it
tblSite_Ground_Covers. The table name will need
to
be
changed in the code to whatever you end up
using.
The
fields
that I used were Site_Cover_ID (PK, Autonumber),
Site_FK(LongInteger) to hold the Site ID and
Ground_Cover_Type_FK (LongInteger) to hold the
ground
cover
ID.

You will need to create a new unbound form that
will
hold
one command button that we will put the command
to
run
the
routine in. Let me know when all this is ready
and
I'll
help
you step through the code to make sure it is
working
right
before we do the whole run.

Gary Miller

"Jonathan" <[email protected]>

wrote
in

message
Hi Gary,

The tblGround_Cover_Type I created below is
new,
following
your suggestion. The layout for my ground
cover
type
table in the original database is this....
tblGround_Cover_Type [Site_ID] Text and

the
15

Ground
Cover Types each with Yes/No Field

(Except
for

the
last
two, other, which are text fields. I will be
able
to
export those and re-import them into the new
database
with
relative ease).

The name of the field that holds siteID

in
the

table
is
[Site_ID].

I am running Access 2000.

Thank you again for your help.

Jonathan
-----Original Message-----
Hi Gary,

Wednesday morning, and I am reading over your
posts
and I
will reply asap.

Jonathan
-----Original Message-----
Ok, here is the code routine to loop through
the
table
and
do a conversion and dump to the new table.
We
may
need
to
make some adjustments as I don't have all
the
answers
back
on table and field names yet, but they
should
be
minor.
I
am
leaving all error handling out because if
you
have
something
unusual in the table that bombs the

code,
I

will
want
to
know exactly which line caused the

error.
I

did
put
in a
lot
of comments to explain what is going on.

We will create a new form with a command
button
on
it
to
run
it when we are ready to go. Let me know when
you
have
it
and
are ready. I believe that we are working
with a
bit
of a
time zone difference here.

Gary Miller
Sisters, OR
*****************************************
Sub TransferGroundCover()

Dim db As Database, rsOld As Recordset,
rsNew
As
Recordset
Dim fld As Field
Dim lngSite As Long, lngGCID As Long

Set db = CurrentDb
' Open up the original table behind the
scenes
Set rsOld =
db.OpenRecordset("tblGroundCovTest")
' Open up the new table behind the scenes
Set rsNew = db.OpenRecordset
("tblGroundCovers")

' 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
lngSite = !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
lngGCID = fld.Name
' Go to the new table
With rsNew
' Poke in a new record
with
these
values
.AddNew
!Site_FK = lngSite
!

Ground_Cover_Type_FK
=

lngGCID
.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

*****************************************
message
[email protected]...
Thanks gerry, here is what I have


.

.



.



.



.



.



.

.