moving info in a form to the table

[Randy]

I have a table
Cat (defines the data, A=actuals, O=budget, C=current view
etc.
Year (self expanitory)
LOB (line of business)
acct
period
value

I have a form, linked to this table. However my users
want to enter data as follows:
Cat
Year
LOB
acct
jan
feb
mar
etc through Dec

So I would need to covert the one line they enter into 12
lines into my table, with cat, year, LOB, acct all being
the same, and the period being unique, with the respective
value being assigned

I looked at the Northwide application and could not see
anything that helps me.

Anyone with any suggestions.My VBA is just starting and
right now is limited to just creating VBA code associated
with buttons On Click Command. i.e open form in data
entry mode, open a report in preview mode etc.

 

[Steve Schapel]

Randy,

I would recommend that you split the table into two. It's difficult
to be specific, without knowing more about your data. But you will
need a primary key field, let's call it AcctID, which can be used to
link the amounts data in the other table to the correct Acct. So
something like...

Table: Accounts
AcctID
Cat (defines the data, A=actuals, O=budget, C=current view etc.
LOB (line of business)
Acct

Table: Amounts
AmountID
AcctID
PeriodYear
PeriodMonth
Value

You would then make a form based on the Accounts table, and another
form, in continuous view, based on the Amounts table, and the Amounts
form then gets put onto the Accounts form as a subform.

An incomplete answer, I know, but hopefully help to point you in a
useful direction.

By the way, as a side note, it is not a good idea to use the words
Year or Value as the name of a field or control, because these words
are "reserved words" (i.e. have a special meaning) in Access.

- Steve Schapel, Microsoft Access MVP