auto fill records in a form

[Pat]

I am trying to design a form/subform where the user
enters energy sales with various companies throughout the
day, similar to a sales and purchase form. The main form
has the company info and date and MasterID, and the
subform has the associated megawatts purchased/sold per
hour and price, along with the SalesID(PK) and linked to
the main form with MasterID(FK). It seems to work fine,
but I would like the subform to show all 24 hours in a
day in the HourEnding field, so the user doesn't always
have to enter the hours 0100 - 2400 every time. Another
words, 24 records would auto fill for every transaction
so the user could just enter quantities in the megawatt
and price fields.

Also, I'm not sure if this is the best way to handle the
data entry since not all hours will necessarily have data
associated with it so there will be several records with
zeros in the megawatts and price fields. I tried
creating just one table with company name, date, and each
hour as a field, which also worked, but it ends up being
a lot of fields in the table (i.e.: 24 fields for each
megawatt hour: MW1, MW2.MW24, along with 24 additional
fields for the associated prices: Price1, Price2.
Price24) besides the calculations to sum the total days
megawatts in the reports (MW1 + MW2.MW24). This also did
not seem like proper relational database structure ?

Any suggestions?

Tia,
Pat

 

[Michel Walsh]

Hi,


Nz(MW1, 0) + Nz(MW2, 0) + ... + Nz(MW24, 0)


Hoping it may help,
Vanderghast, Access MVP

 

[Pat]

Hi Mike,
Thanks for taking the time to look at my question. I'm
not sure I understand what the string is trying to do or
where I attach it. I'm assuming it is looking at the
amount of MW in each hour and if there is no data, put a
zero in the field?? If so, that is not necessarily what
I was looking for. I'll try to explain my problem a
different way.

My subform has the following fields:

SalesID (PK autonumber)
HourEnding
MW
Price

When I create a new transaction, the subform shows 1 line
item ready to enter data. What I would like to see when
I enter the new form, is the subform with 24 new line
items already filled in with the SalesID showing the next
24 autonumbers, and the HourEnding fields filled in with
0100, 0200...2400. The MW and Price fields will be empty
and ready for data entry. Therefore, every transaction
will have 24 associated line items. Is that possible?

I hope I explained it a little better this time. Thanks!

Pat

 

[Michel Walsh]

Hi,

ah. I would use a temporary table with 24 records, 3 fields, the HourEnding
pre-filled, the other two fields NULL. Use that temp table as subform (if
the records do not exist in the permanent table), and once done, on leaving
the main record, append the temp table to the permanent table, and update
the temp table to nulls (to make it ready for a new use)... or something
along the same line.



Sure, that implied a different procedure when you append a new record than
when it is existing, that can be detected by Me.NewRecord, in the onCurrent
procedure event handler.



Hoping it may help,
Vanderghast, Access MVP

 

[Pat]

That worked great until I tried to link the Tempsubform
to the main form. Once I added in the MasterID(FK) link
on the subform, the subform goes back to showing one line
item at a time. Maybe I don't have my tables set up
properly. Here's what I have:

tblCompany
CompanyID
CompanyName

tblMaster
MasterID
CompanyID (FK)
MasterDate

tblSales
SalesID
HourEnding
MW
Price
MasterID (FK)

tblTemp
HourEnding
MW
Price
MasterID(FK)

The main form consists of a query based off of the
tblMaster info, and the subform has the tblTemp info, and
then I'm appending that info into tblSales. It worked
great without the MasterID (FK) but then there is no way
to associate the sales with a particular company???

Pat

 

[Michel Walsh]

Hi,

"on insert" (that is not a real event, but I imagine you follow), append
the records directly to Sale, supplying the fourth field, MasterID ... as a
constant :


DoCmd.RunSQL "APPEND tblSales(HourEnding, MW, Price, MasterID)
SELECT HourEnding, MW, Price,
FORMS!FormNameHere!MasterIDControlNameHere
FORM tblTemp "



if your code is too fast, the update may not have finish its job... mainly
if the table is remote, and the network is busy.***IF*** that arrives, use
something like:

Do Until 24 = DCount("*", "tblSales",
"MasterID=FORMS!FormNameHere!MasterIDFieldHere")
DoEvents
Loop

but that should not be required, in general.

Once you are have appended the 24 records from the temp table to the
Sales, you can process as usual ... and use tblSales filtered as required
(well, there are still the problem of UNDO-ing the insertion, in case of
error, etc...)






Hoping it may help,
Vanderghast, Access MVP

 

[Pat]

Thanks, I'll give that a try. I'm not sure if I totally
understand the code, but I'll strudy it a bit more and
let you know.

Another question...

Instead of using the table structure that I showed you in
my last post, would it be possible to have all the data
in tblMaster with the tblCompany as a lookup table. A co-
worker tried it this way. Instead of having a tblSales
table with an HourEnding field, each hour was a field and
entered the MW qty in each field (MW1, MW2 ... MW24) and
then also having 24 price fields associated with each of
the MW (Price1, Price2, ... Price24). This also seemed
to work, eliminating a lot of code, but also seemed like
an awful lot of fields in one table. But maybe thats not
a real concern since ACCESS allows 255 fields in a
table??? Any comments??

 

[Michel Walsh]

Hi,


If you have to find data or work with data across the MW fields, that
won't be the way to go, such as finding the max price, or the minimum price
(on what would be the record) for all the MW fields. If you have the
vertical disposition, it is just a matter of using MAX, if you use a
vertical disposition, you have to write VBA code. Basically, it is either
some code now, and have a future easy work, or have no code now, but almost
always code as you progress in your application.


Hoping it may help,
Vanderghast, Access MVP

 

[Pat]

Hi Michel,
OK, I tried the DoCmd.RunSQL and I keep getting errors.
Here is my code:

DoCmd.RunSQL "INSERT INTO tblSales(HourEndingS, MWS,
PriceS, MasterID)"
SELECT HourEnding, MW, Price,
Forms!frmMaster!MasterID
FROM tblTemp, Forms!frmMaster!MasterID

Don't know if I needed the quotations on the first line,
but without it I got the error: "Expected: end of
statement" and it highlighted the INTO.

Also, it doesn't seem to like the word "SELECT". I get
the error: "Expected: CASE" as if I'm trying to do a
select case. I know SELECT is used in APPEND queries...

Thanks in advance!
Pat

 

[Michel Walsh]

Hi,


The whole SQL statement should be in a string, you stopped it after
MasterID).... and in just one line.

Hoping it may help,
Vanderghast, Access MVP

 

[Pat]

Thanks, Michel. That worked perfectly! I attached the
code to a "Save" command button. Once that has been
selected and data transferred from tblTemp to tblSales, I
designed an identical-looking form except the tblSales
will now be the subform instead of tblTemp. When the
screen refreshes after clicking the "Save" button, the
new form will be showing. That way all edits will be
done directly in the Sales table instead of the temp
table. That should hopefully solve the problem of UNDO-
ing the insertion, etc that you mentioned previously.
A "New Entry" button will take them back to the temp
form, etc. Don't know if there's a better way to handle
that, but it seems to work!

Thanks again for all your help.... and patience!!

Pat

 

[Michel Walsh]

Hi,


You are somehow gifted to make "palpable" your interest in your problem,
and in those circumstances, patience is very unlikely to ever run thin ;-)


Vanderghast, Access MVP

 

[Pat]

Thank you... you made my day!!!
I also have to compliment all of you for your help. This
newsgroup has been so helpful to me over the past couple
years... whether its help on my own problems or reading
postings from others. I have learned so much... thanks!