problem with a subform

[Gwendolyn]

Hi, I had created a Form to enter all the income/expense for our projects.
It was a singular form. TransactionID was the Primary Key and autonumbered.
I have used this form for the last six months. Ready to explore more options
in my database creation, I realized I could create a subform to eliminate
some duplication of the data, ie date, general journal number, etc. I
created a subform using the TransactionID as the link between the subform and
the main form and also used the Project number, Income source, Income,
Expense type, expense. These were previously on the main form and I moved
them to the new subform. Now when I try to use the subform, it only allows
one line of information and the reports will only show one line per
transaction. What have I done wrong? I hope I have explained this well~?

 

[Mark A. Sam]

Gwendolyn,

The TransactionID field on the subform should be a long integer. Also it
seemed like you were also linking the other fields mentions. Only link the
TransactionID field.

God Bless,

Mark A. Sam

 

[Gwendolyn]

Hi Mark,

Thanks for the reply. I created the subform using the form wizard, and as a
result there is no table for it. The transactionID in the transactions main
form is set for long integer, but I am unsure where to find the this
information in the form properties. Sorry for my basic understanding. I
appreciate the help very much.

 

[Mark A. Sam]

I'm not understanding this. If you used the form wizard, I believe you
needed to specify a table or query. But in any case, you need a table for
the subform. If the TransactionID for the main form's table is an
autonumber then you don't need to concern yourself with it.

It it is not then that could be a problem, becuase it needs a number. You
can assign it manually or programmatically, but I thought stated it is
autonumbered. In you reply you said it is a long integer. An autonumber is
a long integer, but it isn't identified that way.

The subform table should have TransactionID which is a long integer but not
an autonumber.

Also make sure you don't have a relationship established between the tables
linking TransactionID in a one to one relationship. If you have a
relationship established, it should be one to many.

I hope I didn't confuse you.

God Bless,

Mark

 

[Gwendolyn]

Hi Mark,

Would it help if I explained it further?

Original form (used for past 6 months) called Transactions

TransactionID
TransactionDate
TransactionDescription
ReferenceType
Number
AccountID
ExpenseType
WithdrawlAmount
DepositAmount
DonationSource
MissionsDepartment

Then I created a subform using the AccountID - MissionsDepartment and
inserted it into the old Transaction form - deleting the text boxes in the
old form for those accounts.

The subform is in table format and when I do more than one transaction in
that table, it won't allow it...I was wondering if the transactionID (from
previous used form) only had one transaction per record, (no duplicates-
primarykey and all) if that was wrecking things....but I'm so lost. I had
compared it to some examples in the templates at microsoft, but somehow I'm
missing some property value....?

I don't know if this is helping or making it more confusing.

thanks again Mark!




Thank you for your patience with me.

 

[Mark A. Sam]

It would help if you talked about tables instead of forms. I think you
created the subform using the original table. If you have a form and
subform, you need a table for each. From your description, the main table
should have these fields:
TransactionID - This is an autonumber field - Field Size property is Long
Integer
TransactionDate
TransactionDescription
ReferenceType
Number
ExpenseType
WithdrawlAmount
DepositAmount
DonationSource

You need table for the subform with these fields

TransactionID - This is a Number field - Field Size property is Long
Integer
AccountID
MissionsDepartment

Open the subform as in design view and open the form property sheet. For
the RecordSource property select the new table from the list.

Open the main form and select the original table for the Recordsource.

If the subform is still on the main form, delete it and add it again. This
should solve the problem.

God Bless,

Mark

 

[Gwendolyn]

I will definitely try this Mark!

I'll let you know how it goes.

God bless you richly!

 

[Gwendolyn]

Hi Mark,

I created the 2 tables as you suggested - well with Expensetype,
withdrawlamount, depositamount, and Donation source in the subform table (I
explained that poorly earlier). I changed Record source properties as you
suggested. Unfortunately I get alot of blanks or error messages. And I
don't see any of the data that I have put into the orginal form displayed at
all.

Good news...my hair is still on my head...I have managed to keep from
pulling it out.

 

[Mark A. Sam]

If you want to email me the Database, I can take a look at it. My address
is

Mark A Sam Att M S N Dott Comm

Remove the spaces, change Att to @ Dott to . and Comm to com. I think you
understand why.

Also change the database extension from .mdb to .mdx. I'll rename it when I
get it. Otherwise I can't save it to my drive.

God Bless,

Mark

 

[Gwendolyn]

Thanks Mark...it does already contain information and I don't think I could
send it...might get in trouble...confidenciality and all.

But I fiddled and managed to create it without the old information. Can I
import the old information into this new form? It will mean creating new
transaction queries and reports ...alot of work. Any short cuts? I'm
willing to do the work if there isn't. But I do have to get some reports to
our missionaries.

Thanks again Mark. Gwen

 

[Mark A. Sam]

Thanks Mark...it does already contain information and I don't think I
could
send it...might get in trouble...confidenciality and all.

But I fiddled and managed to create it without the old information. Can I
import the old information into this new form? It will mean creating new
transaction queries and reports ...alot of work. Any short cuts?

It can be done an not likely a lot of work, but I don't know enough about
your tables or the data to instruct you. It is something that is easier to
do.

I'm

willing to do the work if there isn't. But I do have to get some reports
to
our missionaries.

Christian missionaries?

 

[Gwendolyn]

Yes Christian Missionaries...I am the bookkeeper for all their accounts
related to our charity.

I have the old data (transaction1) exported onto an excel spreadsheet. I
have the MS Access 2003 Step by Step book. I will try importing that.
Hopefully that will work. My Querys are based on the first tables, as are my
reports. Is there an easy way to change the linkage from Transaction1
information to Transaction2 information? Otherwise...I will keep plugging
away.

Thanks for your patience with me. I was pretty lost and frustrated before
your help...so much appreciated! I am feeling much more optimistic.
thanks again...Gwen

 

[Mark A. Sam]

I'd like to discuss this with you off of the newsgroup. If you are
interested, go to www.Promote.Org and email me from the website. I
understand the fact that the data is confidential, but maybe that issue can
be worked out.

God Bless,

Mark