Simple ?-how to get the doc_descrip field with the doc_type_no

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

Guest

I have a simple question. If I have 2 fields: 1) doc_type_no (i.e. "OA") and
2) doc_descrip ("Original Agreement") on a form, how do I get the doc_descrip
to show up on my form by using the doc_type_no field?

If I change the properties using the "CHANGE TO" a combo box then it works
through a Select Distinct statement, however, if I just display the
doc_type_no and try to pull the doc_descrip, there is no table/query area in
the properties section to add the select statement. Do I need to write a
query first?

Will appreciate any help!

Thank you!

gg :)
 
gg said:
I have a simple question. If I have 2 fields: 1) doc_type_no (i.e.
"OA") and 2) doc_descrip ("Original Agreement") on a form, how do I
get the doc_descrip to show up on my form by using the doc_type_no
field?

If I change the properties using the "CHANGE TO" a combo box then it
works through a Select Distinct statement, however, if I just display
the doc_type_no and try to pull the doc_descrip, there is no
table/query area in the properties section to add the select
statement. Do I need to write a query first?

Will appreciate any help!

Thank you!

gg :)
Click to expand...

There could be two approaches to this. One is to change the form's
recordsource to a query that includes the table matching doc_type_no
with doc_descrip, and join that table with the form's original
recordsource table on the doc_type_no field. Then, by including the
doc_descrip field in the query's field list, you'll make that field
available in the form's field list. I prefer this approach.

The other approach that springs to mind is to have a text box on the
form with a controlsource that uses the DLookup() function to look up
the doc_descrip value that matches the current record's doc_type_no.
For example,

=DLookup("doc_descrip", "tblDocTypes",
"doc_type_no='" & [doc_type_no] & "'")
 
Hi Dirk,

Thank you for your help.

Does this mean that whenever you need to link fields, the best way is to
build a query and pull the fields from the Query? Is it better to build
specific queries or more general queries that access several fields from many
tables so that one query can be used as a source for multiple fields on a
form?

Also, why am I getting a Name? error? I built a query using 2 tables:
doc_types (doc_type_no, doc_descrip) and contract_history (doc_type_no). I
linked on doc_type_no, and pulled the doc_descrip in the fieldlist.

Thank you!

gg

Dirk Goldgar said:
gg said:
I have a simple question. If I have 2 fields: 1) doc_type_no (i.e.
"OA") and 2) doc_descrip ("Original Agreement") on a form, how do I
get the doc_descrip to show up on my form by using the doc_type_no
field?

If I change the properties using the "CHANGE TO" a combo box then it
works through a Select Distinct statement, however, if I just display
the doc_type_no and try to pull the doc_descrip, there is no
table/query area in the properties section to add the select
statement. Do I need to write a query first?

Will appreciate any help!

Thank you!

gg :)
Click to expand...

There could be two approaches to this. One is to change the form's
recordsource to a query that includes the table matching doc_type_no
with doc_descrip, and join that table with the form's original
recordsource table on the doc_type_no field. Then, by including the
doc_descrip field in the query's field list, you'll make that field
available in the form's field list. I prefer this approach.

The other approach that springs to mind is to have a text box on the
form with a controlsource that uses the DLookup() function to look up
the doc_descrip value that matches the current record's doc_type_no.
For example,

=DLookup("doc_descrip", "tblDocTypes",
"doc_type_no='" & [doc_type_no] & "'")

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
gg said:
Does this mean that whenever you need to link fields, the best way is
to build a query and pull the fields from the Query? Is it better to
build specific queries or more general queries that access several
fields from many tables so that one query can be used as a source for
multiple fields on a form?
Click to expand...

That will depend on how many tables you need to link to get the data for
the form, what their relationships are, and whether you the form should
be updatable or not. The more tables you link together in a query, the
greater the chance that the resulting query will be non-updatable. It's
not just a matter of the number of tables, though; it's more about the
types of links and the relationships between the tables. A query that
joins two tables will usually be updatable; more than that, and there
are various things that *may* prevent the query from being updatable.
See the help topic, "When can I updata data from a query?", for a
discussion.

Quite often, the need to link more than two tables to get all the data
you want to show on a form suggests that the tables are related in a way
that makes a form/subform arrangement a better choice. There definitely
are exceptions to this, though.

Also, why am I getting a Name? error? I built a query using 2 tables:
doc_types (doc_type_no, doc_descrip) and contract_history
(doc_type_no). I linked on doc_type_no, and pulled the doc_descrip in
the fieldlist.
Click to expand...

Post the SQL view of the form's recordsource query, and post the name
and controlsource property of the text box that is giving the error.
 
Hi Dirk,
Here's the SQL view of the query:
SELECT Contract_History.Contract_no, Contract_History.Doc_type,
Doc_types.doc_descrip, Contract_History.Effective_date, Doc_types.order,
Contract_History.Timedatestamp
FROM Doc_types INNER JOIN Contract_History ON Doc_types.doc_type =
Contract_History.Doc_type;

I think I should be pulling in the fields that were built vs. this query
rather than the contract_history table, correct? Is it inconsistent to build
my Main Contract form and detailed Contract form, using tables, and then pull
in this other subform, using the query?

Thank you, Dirk!

gg
 
gg said:
Hi Dirk,
Here's the SQL view of the query:
SELECT Contract_History.Contract_no, Contract_History.Doc_type,
Doc_types.doc_descrip, Contract_History.Effective_date,
Doc_types.order, Contract_History.Timedatestamp
FROM Doc_types INNER JOIN Contract_History ON Doc_types.doc_type =
Contract_History.Doc_type;

I think I should be pulling in the fields that were built vs. this
query rather than the contract_history table, correct?
Correct.

Is it
inconsistent to build my Main Contract form and detailed Contract
form, using tables, and then pull in this other subform, using the
query?
Click to expand...

I'm not sure what you mean, since this is the first time you mentioned a
subform. But there's nothing inconsistent in using a query as the
recordsource for one form, and a table as the recordsource for another
form. Some people follow a practice of using queries as the
recordsources for all forms, even when the query just selects all the
fields in a table, but I've never seen any real need for that.
 
Thanks for your patience, Dirk. One last question. How can I get a
time-date stamp on this record when the user enters the doc type in this
subform? I've already created a field called datetimestamp (datetime format)
in the doc_types table. See my dilemma. I'm pulling the doc_descrip from
the query but trying to write a date-time stamp to the table. Help again!

Thank you!

Geri
 
gg said:
Thanks for your patience, Dirk. One last question. How can I get a
time-date stamp on this record when the user enters the doc type in
this subform? I've already created a field called datetimestamp
(datetime format) in the doc_types table. See my dilemma. I'm
pulling the doc_descrip from the query but trying to write a
date-time stamp to the table. Help again!

Thank you!

Geri
Click to expand...

Now I'm confused, Geri. I thought the idea behind this form was to
display and update the Contract_History table, and you were just pulling
in the Doc_types table to get the doc_descrip field. So I'd have
thought that you would be time-stamping the Contract_History record, not
the Doc_types record. If that's the case, it's the Contract_History
table that should have the datetimestamp field, and you would put code
in the form's BeforeUpdate event to assign the value of Now() to that
field. What you say about creating the field in Doc_types makes me
wonder if I've completely misunderstood what you're doing.

Please explain.
 
Hi Dirk,
Oops...you're right. Here are my 2 tables:
I. Contract_history-
1)contract_no - number (i.e. contract=27)
2)doc_type - text (i.e. OA)
3)doc_descrip - text (i.e. Original Agreement)
4)effective_date - text (i.e.user enters the date 6/1/2005)
5)timedatestamp-date/time (the system generated datetime stamp)
II. Doc_types-
1)doc_type-text (i.e. OA)
2)doc_descrip-text (i.e. Original Agreement)
3)order-number (i.e.1)

I should not be storing the doc_descrip in the contract_history table since
it is already in the doc_types table, correct?

Can you please explain how I would get the timedate stamp to load into the
Contract_history table? I've spent all day trying to put it in an event
procedure- after update but it hasn't worked yet.

Thank you so much for your patience!

geri
 
gg said:
Hi Dirk,
Oops...you're right. Here are my 2 tables:
I. Contract_history-
1)contract_no - number (i.e. contract=27)
2)doc_type - text (i.e. OA)
3)doc_descrip - text (i.e. Original Agreement)
4)effective_date - text (i.e.user enters the date 6/1/2005)
5)timedatestamp-date/time (the system generated datetime stamp)
II. Doc_types-
1)doc_type-text (i.e. OA)
2)doc_descrip-text (i.e. Original Agreement)
3)order-number (i.e.1)

I should not be storing the doc_descrip in the contract_history table
since it is already in the doc_types table, correct?
Click to expand...

Correct. As a rule, don't store what can be looked up.
Can you please explain how I would get the timedate stamp to load
into the Contract_history table? I've spent all day trying to put it
in an event procedure- after update but it hasn't worked yet.
Click to expand...

You can't do it in the form's AfterUpdate event, because then the record
has already been saved. You must use the form's BeforeUpdate event.
Here's an example of the event procedure you need:

'----- start of code -----
Private Sub Form_BeforeUpdate(Cancel As Integer)

Me!timedatestamp = Now()

End Sub
'----- end of code -----

That really should be all you need.
Thank you so much for your patience!
Click to expand...

Glad to help.
 
Dirk,

It worked...SO cool!!!! Thank you! Thank you!!!

If I want to always default to the most current datetime, would I add the
sort descending to the datetimestamp field within the Before Update routine?

One last question for today...
I have 2 tables-
I. Main Contract table - contract_no field (i.e. 27)
II. Contract Detail table - line_item_no (i.e. 1), doc_type (i.e. OA)
and I can have the following possible records:

27,1-OA original agreement, $500
27,1-A1 amendment 1, $1000
27,1-A2 amendment 2, $2000

Should I also have an auto-numbered line_item# in addition to the user input
line_item_no and user input doc_type? These would be the fields I'd use to
search the contract detail records on.

Thank you SO much!
geri
 
gg said:
Dirk,

It worked...SO cool!!!! Thank you! Thank you!!!

If I want to always default to the most current datetime, would I add
the sort descending to the datetimestamp field within the Before
Update routine?
Click to expand...

I'm afraid I don't understand what you're asking, but I don't think that
would be the way to do it, because it makes no sense to me. You can't
sort within a BeforeUpdate procedure. What do you mean, "default to the
most current datetime"? If you can explain what you want to do in words
that I can understand, I'll try to suggest something.
One last question for today...
I have 2 tables-
I. Main Contract table - contract_no field (i.e. 27)
II. Contract Detail table - line_item_no (i.e. 1), doc_type (i.e. OA)
and I can have the following possible records:

27,1-OA original agreement, $500
27,1-A1 amendment 1, $1000
27,1-A2 amendment 2, $2000

Should I also have an auto-numbered line_item# in addition to the
user input line_item_no and user input doc_type? These would be the
fields I'd use to search the contract detail records on.
Click to expand...

If you can't logically have duplicate records with the same values for
the combination of contract_no, line_item_no, and doc_type, then there's
no point that I can see in adding an autonumber field to the table.
Those three fields combined would make up the (composite) primary key of
the table. On the other hand, if you could have duplicate records on
those three fields, then you either need to add another field to the
primary key, or use an autonumber as the PK.

The only other reason I can think of for adding an autonumber field is
to assist in ordering the records for display purposes, but that's not
really reliable. Did I see in an earlier post that you had an "order"
field in the Doc_types table? Is that for the purpose of ordering line
items according to their doc_type?
 
Dirk,

I. To clarify on my "1st" question-on my Main Contract screen, I am
displaying the Contract_history form (the fields - 1) doc_type, 2)
doc_descrip and 2) effective_date 3) the timedatestamp is hidden but I'd like
the most current record to show up first (i.e. Amendment1
w/timedatestamp=11/1/2004 2 pm) to show up 1st and then the Original
Agreement-w/a timedatestamp=10/1/2004 1 pm). Where is the most logical place
to code a sort (Contract_history table or on the main Contract screen)?

II. Yes, I just added a field called "order" so that the records would not
sort alphabetically.

III. Yes, the 3 fields together would be unique- 1)contract_no and
2)line_item_no and 3) doc_type. However, my contract_no is autonumbered and
the other 2 fields are text. I think I will need to convert the number to a
string and then concatenate to make them all PK's??

Appreciate all of your help! :-)

geri
 
gg said:
Dirk,

I. To clarify on my "1st" question-on my Main Contract screen, I am
displaying the Contract_history form (the fields - 1) doc_type, 2)
doc_descrip and 2) effective_date 3) the timedatestamp is hidden but
I'd like the most current record to show up first (i.e. Amendment1
w/timedatestamp=11/1/2004 2 pm) to show up 1st and then the Original
Agreement-w/a timedatestamp=10/1/2004 1 pm). Where is the most
logical place to code a sort (Contract_history table or on the main
Contract screen)?
Click to expand...

Neither. The Contract History subform is based on a query, right?
(Note: that may be a stored query or a SQL statement placed right there
in the RecordSource property. You can sort that query by Contract_no
(ascending) and Datetimestamp (descending). Then that's the way they're
going to show up on the form.
III. Yes, the 3 fields together would be unique- 1)contract_no and
2)line_item_no and 3) doc_type. However, my contract_no is
autonumbered and the other 2 fields are text. I think I will need to
convert the number to a string and then concatenate to make them all
PK's??
Click to expand...

No. First let me point out that contract_no, in the *Contract History*
table, is not an autonumber. Or at least, it shouldn't be, if this
table is related by contract_no, many-to-one, to the Contracts table (or
whatever you call it). If contract_no in Contracts is an autonumber
field, then in the Contract History table, contract_no must be a
Number/Long Integer field.

Second, you do *not* need to concatenate fields together to create a
composite primary key. A key can contain multiple fields. In the table
design view, select all three fields at once, and then click the little
"key" icon on the toolbar, to make them into a composite (also called
"compound") primary key.
 
Dirk,

Thank you for ALL of your help! You have been MOST helpful!!!

Until tomorrow... :)

Thanks again,
Geri
 
Back
Top