Auto Fill/Auto Lookup??

[raymondp]

I have created a form to input new data into the database. The data table
for the database is called "TAODailyLog". In this table the column headings
are Date, Site ID, Buoy ID, WMO ID, and Deploy Date (there are others, but
these are the important ones). I have another table called "siteid". This
table I use primarily for looking up values. In the "siteid" table, the
columns are Order, SiteID, pmID, wmoID, and juliandeployed. With each new
record that is placed into the database, the user must put in the Site ID,
Buoy ID, WMO ID and the Deploy Date. Currently, I have the form setup with
combo boxes for each field. For example, I have the combo box to look up the
values for the Buoy ID (to fill in that field for the "TAODailyLog") from the
"siteid" table from the pmID field in that table. Although this works well
it can be a bit cumbersome. What I would like to do is have this process
automated in some fashion. For example, have the user fill in the Site ID
field on the form and then have the other fields (Buoy ID, WMO ID, and Deploy
Date) fill in automatically using the value in the Site ID as the look up
reference. The alternative is to have a combo box that can return more than
one value and I haven't figured out how to do that.

For each Site ID there is a unique Buoy ID, WMO ID, and Deploy Date.
Inputting these are really a way to help sort and find data later. The only
thing that changes is the description that changes everyday.

Any help would be greatly appreciated.

 

[raymondp]

Ok, I figured it out. Of course I would since i sent this. Nice huh?

 

[John W. Vinson]

For example, have the user fill in the Site ID
field on the form and then have the other fields (Buoy ID, WMO ID, and Deploy
Date) fill in automatically using the value in the Site ID as the look up
reference.

What I'm wondering is why you would want to store the same data redundantly in
two tables! Is the Deploy Date an attribute of the site? If so it should be
only in the site table. How are the Buoy and the WMO (whatever that is)
related to a site? or to a log entry?

If the SiteID uniquely determines the Buoy and the WMO ID then the BuoyID and
WMO ID fields should NOT EXIST in the log table - only the site ID.

John W. Vinson [MVP]

 

[raymondp]

ah, you hit the nail on the head with that. I ask the same thing. However,
since I didn't come up with the naming conventions and there are people here
who are set in there ways, all the IDs must be present so different people
can do their searches according to the ID they wish to use.
The Site ID refers to the Lat and Long of the buoy in question, such as
8n165e, this ID never changes and was incorporated when the NDBC took over
this program. The Buoy ID or pmID is the historical convention given by the
Pacific Marine Environmental Lab (PMEL) where the ID changes everytime the
buoy is serviced or exchanged. These IDs are unique to a specific deployment
period, so this ID must be there. The WMO ID is another convention that
uniquely identifies the buoy world wide, and this ID never changes. The
Deploy Date is a way to help identify buoys that have been in service for a
year so that we know when they must be serviced or exchanged. This date
changes along with the pmID.

 

[Douglas J. Steele]

Create a query that joins the tables so that all the IDs are present in the
query.

Use the query rather than the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[raymondp]

Douglas, I'm not quite sure what you're saying. I have created the fields to
look up the correct data that I need, so I'm not sure a query is needed. I
may not have emphasized enough that the siteid table is used to store the
data for the specific site which does change. No new records are added, just
updated, and the table is only used to look up the info that I need to put
into the database. The hitch, that I may not be conveying effectively, is
that once the data in in the database, I don't want to change the IDs even
though the siteid table gets updated. So, if someone comes along and asks me
to get them all the info for a Site ID, all I have to do is a query on the
site. They may also ask me for all the info from a particular deployment.
For example, site 4n125w has the PM IDs of 644a, 644b, 652a, 652b which mean
that 644a and 652a are when the new buoy was deployed and 644b and 652b are
when the buoy was serviced. Although 644b and 652b are the same buoy, they
are referred to as different deployments. So, I may be asked for only the
info for 644a. That is why I can't have this data change.

I hope this explains the problems and reasoning behind things.

The problem that I'm running into now is that I have the 3 fields to look up
the pmid, wmoID, and the juliandeployed working, which is great. However, in
order to get it to work the way I want it to, I had to put the expression
(see below) in the control source which doesn't input the value into the
database.

=DLookUp("[pmID]","[siteid]","[SiteID] = Forms![Tao Daily Exp]![Site ID]")

I tried putting the expression in the default and having the control source
be the Buoy ID (where I want the value stored in the database), but it did
not return any value when I entered a new record. I was able to copy and
paste within the form having separate fields for looking up the values from
the table and storing the values in the database, but I would like to create
a button that would do that for me for all 3 fields.

Can you help me out with this?

Thanks,
-Ray

 

[Douglas J. Steele]

Rather than setting the control source to the function, you'll have bind the
text box to the field in the recordsource, and set its value in VBA code.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas, I'm not quite sure what you're saying. I have created the fields
to
look up the correct data that I need, so I'm not sure a query is needed.
I
may not have emphasized enough that the siteid table is used to store the
data for the specific site which does change. No new records are added,
just
updated, and the table is only used to look up the info that I need to put
into the database. The hitch, that I may not be conveying effectively, is
that once the data in in the database, I don't want to change the IDs even
though the siteid table gets updated. So, if someone comes along and asks
me
to get them all the info for a Site ID, all I have to do is a query on the
site. They may also ask me for all the info from a particular deployment.
For example, site 4n125w has the PM IDs of 644a, 644b, 652a, 652b which
mean
that 644a and 652a are when the new buoy was deployed and 644b and 652b
are
when the buoy was serviced. Although 644b and 652b are the same buoy,
they
are referred to as different deployments. So, I may be asked for only the
info for 644a. That is why I can't have this data change.

I hope this explains the problems and reasoning behind things.

The problem that I'm running into now is that I have the 3 fields to look
up
the pmid, wmoID, and the juliandeployed working, which is great. However,
in
order to get it to work the way I want it to, I had to put the expression
(see below) in the control source which doesn't input the value into the
database.

=DLookUp("[pmID]","[siteid]","[SiteID] = Forms![Tao Daily Exp]![Site ID]")

I tried putting the expression in the default and having the control
source
be the Buoy ID (where I want the value stored in the database), but it did
not return any value when I entered a new record. I was able to copy and
paste within the form having separate fields for looking up the values
from
the table and storing the values in the database, but I would like to
create
a button that would do that for me for all 3 fields.

Can you help me out with this?

Thanks,
-Ray

Create a query that joins the tables so that all the IDs are present in
the
query.

Use the query rather than the table.

 

[John W. Vinson]

However,
since I didn't come up with the naming conventions and there are people here
who are set in there ways, all the IDs must be present so different people
can do their searches according to the ID they wish to use.

If people are opening the Table to do their searching, then they a) probably
shouldn't be doing so and b) if the ARE doing so anyway, they should
understand the structure of the database.

An alternative would be to provide them with a user-friendly Form which would
search *in a Query* joining the tables. It is not necessary to have all the
fields in the same table in order to search them!!!

John W. Vinson [MVP]

 

[raymondp]

Thanks.
I forgot to mention that I am novice to VBA, so could you elaborate further?

Rather than setting the control source to the function, you'll have bind the
text box to the field in the recordsource, and set its value in VBA code.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas, I'm not quite sure what you're saying. I have created the fields
to
look up the correct data that I need, so I'm not sure a query is needed.
I
may not have emphasized enough that the siteid table is used to store the
data for the specific site which does change. No new records are added,
just
updated, and the table is only used to look up the info that I need to put
into the database. The hitch, that I may not be conveying effectively, is
that once the data in in the database, I don't want to change the IDs even
though the siteid table gets updated. So, if someone comes along and asks
me
to get them all the info for a Site ID, all I have to do is a query on the
site. They may also ask me for all the info from a particular deployment.
For example, site 4n125w has the PM IDs of 644a, 644b, 652a, 652b which
mean
that 644a and 652a are when the new buoy was deployed and 644b and 652b
are
when the buoy was serviced. Although 644b and 652b are the same buoy,
they
are referred to as different deployments. So, I may be asked for only the
info for 644a. That is why I can't have this data change.

I hope this explains the problems and reasoning behind things.

The problem that I'm running into now is that I have the 3 fields to look
up
the pmid, wmoID, and the juliandeployed working, which is great. However,
in
order to get it to work the way I want it to, I had to put the expression
(see below) in the control source which doesn't input the value into the
database.

=DLookUp("[pmID]","[siteid]","[SiteID] = Forms![Tao Daily Exp]![Site ID]")

I tried putting the expression in the default and having the control
source
be the Buoy ID (where I want the value stored in the database), but it did
not return any value when I entered a new record. I was able to copy and
paste within the form having separate fields for looking up the values
from
the table and storing the values in the database, but I would like to
create
a button that would do that for me for all 3 fields.

Can you help me out with this?

Thanks,
-Ray

Create a query that joins the tables so that all the IDs are present in
the
query.

Use the query rather than the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


ah, you hit the nail on the head with that. I ask the same thing.
However,
since I didn't come up with the naming conventions and there are people
here
who are set in there ways, all the IDs must be present so different
people
can do their searches according to the ID they wish to use.
The Site ID refers to the Lat and Long of the buoy in question, such as
8n165e, this ID never changes and was incorporated when the NDBC took
over
this program. The Buoy ID or pmID is the historical convention given
by
the
Pacific Marine Environmental Lab (PMEL) where the ID changes everytime
the
buoy is serviced or exchanged. These IDs are unique to a specific
deployment
period, so this ID must be there. The WMO ID is another convention
that
uniquely identifies the buoy world wide, and this ID never changes.
The
Deploy Date is a way to help identify buoys that have been in service
for
a
year so that we know when they must be serviced or exchanged. This
date
changes along with the pmID.

:

On Tue, 3 Jul 2007 12:14:01 -0700, raymondp

For example, have the user fill in the Site ID
field on the form and then have the other fields (Buoy ID, WMO ID,
and
Deploy
Date) fill in automatically using the value in the Site ID as the
look
up
reference.

What I'm wondering is why you would want to store the same data
redundantly in
two tables! Is the Deploy Date an attribute of the site? If so it
should
be
only in the site table. How are the Buoy and the WMO (whatever that
is)
related to a site? or to a log entry?

If the SiteID uniquely determines the Buoy and the WMO ID then the
BuoyID
and
WMO ID fields should NOT EXIST in the log table - only the site ID.

John W. Vinson [MVP]

 

[raymondp]

John,
it's not me, it's them. I'm just doing what they want.

 

[John W. Vinson]

John,
it's not me, it's them. I'm just doing what they want.

So if they want to misuse Access, corrupt their data, and make their own job
harder...?

<shrug> All I can say is that I wish you luck.

John W. Vinson [MVP]