G
Gary Miller
OK. Your last post referred to...
now
Is the table actually "Ground_Cover_Type" when the code has
"tblGround_Cover_Type"? If so, just remove the "tbl" from
that OpenRecordset line.
Gary
now
Is the table actually "Ground_Cover_Type" when the code has
"tblGround_Cover_Type"? If so, just remove the "tbl" from
that OpenRecordset line.
Gary
Jonathan said: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
useJonathan said: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
tostraight.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
message
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
Ifrunningit
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
itassignsas 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")whichwilla
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
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
beable
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
.
.
.
.
.
.
.
.