Datasheet Question

[Mary Fetsch]

In my Access 2000 project, I want to show a datasheet with
these fields:

Trans # Trans Date Tenant Name Brochure # Fee

The fields come from 2 tables. All fields except Tenant
Name come from the Tenant Transaction table. Tenant Name
comes from the Tenant Header table. The two tables are
linked by Tenant #.

I want to enter 1 row into the datasheet and then
duplicate that row 10 times, changing only Tenant Name.

So, changing Tenant Name would mean the transaction should
have a different Tenant #. It does NOT mean Tenant Name
should be changed in the Tenant Header table.

For that reason, I don't want Tenant Name to be bound to
the Tenant Header table. I thought I would use SQL
statements in my VB code to pull in the Tenant Name on
unchanged records and adjust the Tenant Transaction table
for changed records.

However, I'm having a problem. I'm using a SQL statement
in the Form Current event to grab the Tenant Name. But
when I open this datasheet and show all its rows, the same
Tenant Name appears in all the rows. How can I get the
different Tenant Names to appear in the different rows?

Is there a better way to do this? I'll very much
appreciate any help anyone can give me on this.

 

[Graham Mandeno]

Hi Mary

The field you want to change is [Tenant #] not [Tenant Name]. The easiest
way to do this is to create a continuous form based on the [Tenant
Transaction] table. Add all the fields you require, but instead of a
textbox for [Tenant Name], use a combo box bound to [Tenant #]. Set the
combo box's properties as follows:
RowSource: Select [Tenant #], [Tenant Name] from [Tenant Header] order
by [Tenant Name]
Columns: 2
BoundColumn: 1
ColumnWidths: 0

You can then duplicate a record and select the new correct tenant name from
the drop down list.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

 

[Mary Fetsch]

Hi Graham,

Thanks a lot for that suggestion. It works well except for two things.
First, there are several transactions for each Tenant #. So my combo box
repeats each Tenant's Name several times. I'd rather see each Tenant's Name
only once. Second, I'd like to sort the transactions by Tenant Name. Is
there any way to accomplish all that?

Mary

Hi Mary

The field you want to change is [Tenant #] not [Tenant Name]. The easiest
way to do this is to create a continuous form based on the [Tenant
Transaction] table. Add all the fields you require, but instead of a
textbox for [Tenant Name], use a combo box bound to [Tenant #]. Set the
combo box's properties as follows:
RowSource: Select [Tenant #], [Tenant Name] from [Tenant Header] order
by [Tenant Name]
Columns: 2
BoundColumn: 1
ColumnWidths: 0

You can then duplicate a record and select the new correct tenant name from
the drop down list.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

In my Access 2000 project, I want to show a datasheet with
these fields:

Trans # Trans Date Tenant Name Brochure # Fee

The fields come from 2 tables. All fields except Tenant
Name come from the Tenant Transaction table. Tenant Name
comes from the Tenant Header table. The two tables are
linked by Tenant #.

I want to enter 1 row into the datasheet and then
duplicate that row 10 times, changing only Tenant Name.

So, changing Tenant Name would mean the transaction should
have a different Tenant #. It does NOT mean Tenant Name
should be changed in the Tenant Header table.

For that reason, I don't want Tenant Name to be bound to
the Tenant Header table. I thought I would use SQL
statements in my VB code to pull in the Tenant Name on
unchanged records and adjust the Tenant Transaction table
for changed records.

However, I'm having a problem. I'm using a SQL statement
in the Form Current event to grab the Tenant Name. But
when I open this datasheet and show all its rows, the same
Tenant Name appears in all the rows. How can I get the
different Tenant Names to appear in the different rows?

Is there a better way to do this? I'll very much
appreciate any help anyone can give me on this.

 

[Mary Fetsch]

Hi again Graham,

I have another issue with this form. Sometimes there are duplicate names
with different addresses and Tenant #s. If a Tenant Name with duplicates is
entered, I want the user to choose the correct address from another combo
box. I don't think the address combo box can be bound to the form because I
want addresses with different Tenant #s. I need something that will allow
me to do the following:

Select Tenant Address from Tenant Header
where Tenant Name =
(Select Tenant Name from Tenant Header where TenantHeader.Tenant# = current
TenantTransaction.Tenant#)

I don't know how to set this up and would really appreciate any help you can
give me.

Mary

Hi Mary

The field you want to change is [Tenant #] not [Tenant Name]. The easiest
way to do this is to create a continuous form based on the [Tenant
Transaction] table. Add all the fields you require, but instead of a
textbox for [Tenant Name], use a combo box bound to [Tenant #]. Set the
combo box's properties as follows:
RowSource: Select [Tenant #], [Tenant Name] from [Tenant Header] order
by [Tenant Name]
Columns: 2
BoundColumn: 1
ColumnWidths: 0

You can then duplicate a record and select the new correct tenant name from
the drop down list.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

In my Access 2000 project, I want to show a datasheet with
these fields:

Trans # Trans Date Tenant Name Brochure # Fee

The fields come from 2 tables. All fields except Tenant
Name come from the Tenant Transaction table. Tenant Name
comes from the Tenant Header table. The two tables are
linked by Tenant #.

I want to enter 1 row into the datasheet and then
duplicate that row 10 times, changing only Tenant Name.

So, changing Tenant Name would mean the transaction should
have a different Tenant #. It does NOT mean Tenant Name
should be changed in the Tenant Header table.

For that reason, I don't want Tenant Name to be bound to
the Tenant Header table. I thought I would use SQL
statements in my VB code to pull in the Tenant Name on
unchanged records and adjust the Tenant Transaction table
for changed records.

However, I'm having a problem. I'm using a SQL statement
in the Form Current event to grab the Tenant Name. But
when I open this datasheet and show all its rows, the same
Tenant Name appears in all the rows. How can I get the
different Tenant Names to appear in the different rows?

Is there a better way to do this? I'll very much
appreciate any help anyone can give me on this.

 

[Graham Mandeno]

Hi Mary

Thanks a lot for that suggestion. It works well except for two things.
First, there are several transactions for each Tenant #. So my combo box
repeats each Tenant's Name several times. I'd rather see each Tenant's Name
only once.

The rowsource I gave you was based on the [Tenant Header] table, not the
[Tenant Transaction] table, so you should not get tenants repeated because
of multiple transactions. You do have only one [Tenant Header] per tenant,
don't you??

Second, I'd like to sort the transactions by Tenant Name. Is
there any way to accomplish all that?

Yes. Base your form's recordsource on a query which includes both tables,
and include [Tenant Name] for sorting purposes only:

Select [Tenant Transactions].* from [Tenant Transactions] inner join [Tenant
Header] on [Tenant Transactions].[Tenant #] = [Tenant Header].[Tenant #]
order by [Tenant Header].[Tenant Name], [Tenant Transactions].[Trans Date]

If you have some temporarily "unassigned" transaction records (newly
batch-added records which so far have no [Tenant #]), then you can use a
"left join" (instead of "inner" join") between the tables, to include
transaction records with no tenant.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

 

[Mary Fetsch]

Hi Graham,



In my quest to simplify things in the newsgroup, I'm afraid I haven't
explained my problem correctly. So I'll start over. I'm dealing with land
records. I have a [Header] table and a [Transaction] table. [Header]
contains data about a particular piece of land (where it's located, how big
it is, etc). For each [Header] row, there are several [Transaction] rows,
which show what happened to the land over a period of time (who bought and
sold it, etc).



Each [Header] row has a [Hdr Id] that uniquely identifies it. Each
[Transaction] row has a [Trx Id] that uniquely identifies it. Each
[Transaction] row also has a [Hdr Id] that links it with a [Header] row.



[Township] is one of the fields in [Header]. My form is based on
[Transaction], but for each [Transaction] row, I want to see its [Township]
from the [Header] table. I want to be able to filter my form, and when I
do, I want to see [Township] in all the rows. I want to be able to change
[Township] in a row, and thus change the [Transaction].[Hdr Id] for that row
in the [Transaction] table.



I don't want the [Township] combo box in a subform based on the [Header]
table because when I change [Township], I don't want the [Header] table to
change. I don't want the form based on a query because I can't update it.



So how do I get [Township] to appear in each row and work the way I want?



Thanks for your help thus far. I'm sorry if I've confused things for you.

 

[Graham Mandeno]

Hi Mary

In my quest to simplify things in the newsgroup, I'm afraid I haven't
explained my problem correctly. So I'll start over.

I've just been typing a rather long answer to your question about duplicate
tenant names with different addresses! You mean that was a waste of time?
Oh well...

I'll deal with your issues, not necessarily in the order you stated them:

I don't want the form based on a query because I can't update it.

That's not a problem. Most queries are updatable, unless they include a
DISTINCT predicate or a GROUP BY clause.

I don't want the [Township] combo box in a subform based on the [Header]
table because when I change [Township], I don't want the [Header] table to
change.

You need to understand the distinction between binding the combo box to a
field (the ControlSource), and the source of the combo list (the RowSource).
These can be (and usually are) in different tables. Usually the
ControlSource is a foreign key field (FK) on the many side of a relationship
(in your case, [Hdr Id] in [Transaction]), and the RowSource is based on the
table on the one-side of the relationship (your [Header] table) with the
BoundColumn holding that table's primary key (PK) value.

I'm dealing with land
records. I have a [Header] table and a [Transaction] table. [Header]
contains data about a particular piece of land (where it's located, how big
it is, etc). For each [Header] row, there are several [Transaction] rows,
which show what happened to the land over a period of time (who bought and
sold it, etc).

There is presumably some unique descriptive field in the [Header] table (for
example, [LotNumber] or [Legal Description]). Does it not make sense to
display that field text in the combo?

Each [Header] row has a [Hdr Id] that uniquely identifies it. Each
[Transaction] row has a [Trx Id] that uniquely identifies it. Each
[Transaction] row also has a [Hdr Id] that links it with a [Header] row.

OK - this is standard one-to-many relational design - you get a bunny stamp

[Township] is one of the fields in [Header]. My form is based on
[Transaction], but for each [Transaction] row, I want to see its [Township]
from the [Header] table. I want to be able to filter my form, and when I
do, I want to see [Township] in all the rows. I want to be able to change
[Township] in a row, and thus change the [Transaction].[Hdr Id] for that row
in the [Transaction] table.

So, you really have a one-to-many relationship between Townships and blocks
of land (Headers) don't you? Why not add a Townships table with TownshipID
(PK), TownshipName (and maybe other regional/state information) and have a
TownshipID FK in your Header table.

BTW, I recommend avoiding non-alphanumeric characters (including spaces) in
table and field names. Among other reasons, it means you then don't need to
wear out your [] keys

So how do I get [Township] to appear in each row and work the way I want?

I see now that it was duplicate Townships (not duplicate names) that was
causing your other concern. Does the display of the descriptive field from
the Header table solve this?

Here is my suggestion:

First create a Townships table as described above, and link it with a
one-to-many relationship to your Header table. (You must first add the
TownshipID field to the Header table).

Create a query (qryHeaderInfo) along these lines:
Select H.*, Ts.TownshipName from Header as H inner join
Townships as Ts on H.TownshipID=Ts.TownshipID

This will display all the Header information including the name of the
township.

Use the following query for your continuous form's RecordSource:
Select T.*, H.HdrDescr, H.TownshipName from Transaction as T
left join qryheaderInfo as H on T.HdrId=H.HdrId

This will display all transactions, along with the HdrDescr and TownshipName
for those records that have been given a link to a Header.

Along with the other Transaction fields on your form, add a combo box bound
to [HdrId] and a textbox bound to TownshipName. Make the textbox locked and
disabled (so the name of the township can't be changed). For the combo, set
the following properties:
ControlSource: HdrId
RowSource: Select HdrId, HdrDescr from Header order by HdrDescr
Columns: 2
BoundColumn: 1
ColumnWidths: 0

Now, you should be able to add new Transaction records with all the fields
filled in except HdrId, then for each record select a block of land
description to link it to its header.

Thanks for your help thus far. I'm sorry if I've confused things for you.

Not a problem. I hope this works for you

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

 

[Mary Fetsch]

Hi Graham,

I've just been typing a rather long answer to your question about duplicate
tenant names with different addresses! You mean that was a waste of time?
Oh well...

Sorry about that. (It really wasn't a waste - I still needed to know that.)

So, you really have a one-to-many relationship between Townships and blocks
of land (Headers) don't you? Why not add a Townships table with TownshipID
(PK), TownshipName (and maybe other regional/state information) and have a
TownshipID FK in your Header table.

I've some more information for you. In addition to [Township, I also want
[Range] and [Sect] from the [Header] table (they will work the same as
[Township], so I omitted them from my previous post). I also have my
Description [Descr] field in the [Header] table, so I don't need a Townships
table. Each Tw-Rg-Sc location can have several land records associated with
it - [Descr] is different for each one.



My data will look something like this:



Hdr Rec 1 -- Tw 1 Rg 1 Sc 1 Desc A

Trans Rec 1

Trans Rec 2

Hdr Rec 2 -- Tw 1 Rg 1 Sc 1 Desc B

Trans Rec 4

Trans Rec 5

Trans Rec 6

Hdr Rec 3 -- Tw 1 Rg 1 Sc 2 Desc A

Hdr Rec 4 -- Tw 1 Rg 1 Sc 2 Desc C

Trans Rec 7

Trans Rec 8



I want each row in my form to have this data:



TransFile# Township Range Sect Desc Rest of Trans fields.



I created the OGMHdrDataView as



Select TOWNSHIP, RANGE, SECT, DESCR, OGM_HDR_ID FROM OGM_HDR



I set my form's Row Source as



Select T.*,H.TOWNSHIP,H.RANGE,H.SECT,H.DESCR,H.OGM_HDR_ID as HdrOGMHdrId
from OGM_TRX as T left join OGMHdrDataView as H on T.OGM_HDR_ID =
H.OGM_HDR_ID



Since [Range] and [Sect] will work the same as [Township], I'll only refer
to [Township]. Control Source = Township

Record Source = Select distinct TOWNSHIP from OGM_HDR order by TOWNSHIP



When I open the form, the data comes in nicely, but I cannot update
[Township]. (First, I couldn't update anything because I hadn't specified a
Unique Table. I could choose either the [Trans] table (OGM_TRX) or the
[Header] table (OGM_HDR) as unique. When I specified the [Trans] table, I
could only update [Trans] fields, and I couldn't update [Township].)



I mentioned that in my first post that I'm working on a project? Could this
be the reason for my problems?

Along with the other Transaction fields on your form, add a combo box bound
to [HdrId] and a textbox bound to TownshipName. Make the textbox locked and
disabled (so the name of the township can't be changed). For the combo, set
the following properties:
ControlSource: HdrId
RowSource: Select HdrId, HdrDescr from Header order by HdrDescr
Columns: 2
BoundColumn: 1
ColumnWidths: 0

Now, you should be able to add new Transaction records with all the fields
filled in except HdrId, then for each record select a block of land
description to link it to its header.

This confuses me. It seems I actually have two Desc fields. For an
existing record, the text box and combo box will show Desc for existing
records? For a new record, the text box will be blank and the combo box
provides a choice of Desc, which will appear in the text box after the
record's saved?



I've been told I could also make Township an unbound control and use a SQL
statement in the Form Current event. This works, too, except only the first
record is affected when I open my form. Is there a way on a datasheet or
continuous form to affect all the records shown when the form opens?



I really appreciate your help, explanations, and advice. I've learned a lot
from you. Thanks!



Mary