Force Continuous Subform to Display all 10 Rows?

[clearwave]

I have a Continuous Subform on my Main Tabbed form.(See image below)
[image

http://cerberus.clearwave.com/jerry/frmRoc.gif

]
[image

http://cerberus.clearwave.com/jerry/frmRoc2.gif

]

When the user clicks this Tab, I need the subform to open and automatically display a row for each of the values in dropdown box Combo12. (Combo12 is based on a Table that curently has 10 items in it but will grow in the future)
I do not care if this is a drop-down or text box field as the user will not be allowed to change.

To make it easier for an end user to input the Totals, how do I have this subform open, with a row for every item in my table that isn't discontinued? (This table has the following fields, ID, Desc, & Discontinued checkbox)
[image

http://cerberus.clearwave.com/jerry/frmRoc3.gif

]

Thanks for your help, Hope this is enough information.

 

[Pampers]

Dont really understand the problem... You don't want to show the discontinued in your combox (=dropdown)?

Pampers
There is only one way to change a diaper - fast

 

[TheAceMan1]

How are ya clearwave . . .

Your appending as many new records as you have values in the combo. It appears your intent is generate the records for editing ahead of time (user fills in the empty fields of those records).

Not the best interface I've come across . . . but if you must, use an [blue]Append Query[/blue] to add the records along with a final [blue]requery[/blue] of the subform . . .

See Ya! . . . . . .

 

[clearwave]

Thanks for the input guys,

While I'm not fond of this layout either, this is what the client wants. They use to have an old DOS application that has all of the Receipt categories displayed down the right column, & the user inputs the Total Dollars & hits Enter or Tab untill all have been entered.

I thought that by adding the tblReceiptType table & adding a Discontinued checkbox, if checked in the table then it will not be displayed & not need a Total Dollar entered?

I was wanting to make this form open in Data Entry mode so it always has 0.00 for the totals. My problem is getting the full list to display?

Aceman, How do I list the items each time the form opens, with 0.00 amounts? I have not used Append queries in my subforms before.
Thanks again,
cw

 

[Pampers]

Hi clearwave,
On open, I put this code. It created 5 records with the different charges. PaymentAmount is set to 0.

Private Sub Form_Open(Cancel As Integer)
Dim sql As String
sql = "INSERT INTO tblPayment (PaymentTypeID, PaymentAmount) " & _
      "VALUES (1,0);"
CurrentDb.Execute (sql)
sql = "INSERT INTO tblPayment (PaymentTypeID, PaymentAmount) " & _
      "VALUES (2,0);"
CurrentDb.Execute (sql)
sql = "INSERT INTO tblPayment (PaymentTypeID, PaymentAmount) " & _
      "VALUES (3,0);"
CurrentDb.Execute (sql)
sql = "INSERT INTO tblPayment (PaymentTypeID, PaymentAmount) " & _
      "VALUES (4,0);"
CurrentDb.Execute (sql)
sql = "INSERT INTO tblPayment (PaymentTypeID, PaymentAmount) " & _
      "VALUES (5,0);"
CurrentDb.Execute (sql)
Me.Requery
End Sub

Pampers
There is only one way to change a diaper - fast

 

[clearwave]

Pampers, Thanks for the response.
& disregard my "programmer" status. How do I change that thing to "Technical User"?

anyway...

I have never used the Insert command to auto-populate my subforms. I guess it's about time to learn a new technique.
I appreciate your patience as I would really like to learn..

Question #1) If I put code like this into my subform's OnOpen event, will it correctly display each item from the Receipt Category on my subform & wait for Dollar values to be entered?

Thnks again,
cw

 

[Pampers]

yes it will,
but you may run into problems if more records in the subform are entered...

I'm not sure, but what you could do is make a table Payment with all the different type sif payments as a variable (although that is not very 'normalized'. Then for every customer/sale you have a subform ( on a single not continuous form) with automatically all the entries. I think it will make live a lot easier in this case. Then you dont have to work with the append query on open()


tblPayementsNew
PaymentID
Cash
Cheque
TeleCheque
PaperCheque
Etc...






Pampers
There is only one way to change a diaper - fast

 

[clearwave]

Thanks again, I'll start looking into this later this evening. I have attached a copy of my database thus far for reference.
[download

http://cerberus.clearwave.com/jerry/ROC_beta.zip

]

Thanks,
cw

 

[clearwave]

Pampers, In regards to "you may run into problems if more records in the subform are entered..."

I plan to give them a button on the Form that says "Add New Category?" which when clicked will open up the frmCategories, allow them to Add or Check one as discontinued & then close the frmCategories.

Currently I can turn on Data Entry mode & when the form opens it displays no records at all. The user can go down the list & choose the correct item from my Combo12 box & enter in the Dollars, then continue to the next line. (The date is auto-filled) But they would get tired of having to manually select the Category every time.

What I have done in the database so far is to turn off Data Entry mode, so that when the form opens it displays all the lines. But obviously this displays the lines for every record listed in the database. Not good.

Your idea seems good to me in that it populates the form based on the Combo12 box Record ID? As I look it over I wonder how to exclude the Categories marked as Disc?

Thanks again,

 

[clearwave]

Pampers, I finally got back to this & changed the code to this:
---------------------------------------
Private Sub Form_Open(Cancel As Integer)
Dim sql As String
sql = "INSERT INTO tblReceiptDetails (rec_catg_id, rec_total) " & _
"VALUES (1,0);"
CurrentDb.Execute (sql)
sql = "INSERT INTO tblReceiptDetails (rec_catg_id, rec_total) " & _
"VALUES (2,0);"
CurrentDb.Execute (sql)
sql = "INSERT INTO tblReceiptDetails (rec_catg_id, rec_total) " & _
"VALUES (3,0);"
CurrentDb.Execute (sql)
sql = "INSERT INTO tblReceiptDetails (rec_catg_id, rec_total) " & _
"VALUES (4,0);"
CurrentDb.Execute (sql)
sql = "INSERT INTO tblReceiptDetails (rec_catg_id, rec_total) " & _
"VALUES (5,0);"
CurrentDb.Execute (sql)
Me.Requery
End Sub
------------------------------------------

I'm not doing it right because my form just displays nothing. Also I need to add the field rec_station_id to this code which is my subform linked-field to my Main forms station_id field.


Can I add a variable to this code that pulls the Main form ID field & adds it to the sql update?

Thanks again,
cw

 

[Pampers]

Hi Clearwave,
I downloaded your app and I think what you are trying to construct is a sort of accounting system, which gives you an overview of expenses and revenues for the different stations. You created two special tabs (and tables) for the main products, fuel and cigs. Just for my understanding, how is the data entered. Is the info from the different stations coming from point of sales-machines, on paper, as excel-sheets? Does the overview has to give totals, or totals by month, by week...?

Pampers
Just let it go...

 

[clearwave]

They are a chain of gas stations & retail stores & the owners have no plans to computerize the sites. Each day the manager prints off a cash register tape, transfers the values to a closing paper sheet & sends this End-of-Day sheet to the main office.

The secretary inputs the data into this database at the main office & then prints off reports for management. (I have attached an updated version of this database at the same link as before. This one includes the reports via Stephen Lebans pdf. You must copy the 2 files DynaPdf.dll & StrStorage.dll from

http://www.lebans.com/reporttopdf.htm

to your /windows/system32 folder) Anyway, everything is finished except the Ztape tab.

The secretary wants to be able to take her paper sheet from each station for each day, and input a list of DOLLARS for each of the RECEIPTS.

Then she does the same thing for a list of DOLLARS for each of the SALES.

As you can see, She wants the Z-Tape tab to open & have all of the Receipt & Sales Descriptions to display down the form but have $0.00 for their values. (The date is auto-filled by the Key Date on first tab) I need the form to list the descriptions in the exact order as the paper sheet to make her data entry easier.

It looks like I need "unbound" text-boxes for each Description & Dollar field, and then use code like you suggested to write it to the table. That part I'm very weak. Still learning.

Thanks for your time & teaching.
cw

 

[VicRauch]

I would sure think that if you have a table with all the possible categories, and a field for discontinued so that record can be ignored, then on the change event of the tab control, check if the tab you are using is selected, if so, populate the table that you are using by doing an Append query from the table with all the possible categories. Whatever fields you need populated, they are available somewhere on your form, execpt the categories, which are in the category table. The append query just puts the correct date, and the $0.00, and whatever else into the correct fields, and let the categories be populated from the category table. A much better approach, and if your categories have a sort field, then the categories will always be able to be in the right order, even if the input hard copy document is ever changed.

 

[Pampers]

Hi clearwave,
Downloaded your latest version. There a few things in the app I don't understand. Data is entered on a daily basis, but the receipts and sales are on continuous forms, and it just accumalates all the dates for a station. Then the fuel and cigaret_tabs are not linked with the Z tape, so the data has to be typed in two times. Furthermore, you want to receipt_part on the Z tape display all the 'items'. But I can not see who this can be done if the form is not made on a daily basis.

My suggestion is to base the data entering on daily basis . If you create a new day, update the tables so you can enter the required data for the various stations. Now you can aslo create the ten entries for the Z Tape.


Pampers
Just let it go...

 

[clearwave]

VicRauch & Pampers, Thanks for the response.

Disregard the way my Receipts & Sales subforms display as continous forms. (I was trying to list all items.) And you are right to say that the DATA ENTRY is always based on a DAY/DATE.

But I'm still lost on how to:
1) List all items down the subform
2) Have the secretary enter in Amounts for those items
3) Save the results to tblReceiptDetails.

(and do the same for tblSales)

But one thing for sure is that these constants apply to each day:
1) The KeyDate on Tab 1
2) The Station ID#

Also, They need the Receipts & Sales listed down the page so they can see if they are "In-Balance" for that day. That's why I have the "Running Total" Over/Short at the bottom.

As far as the double-data entry, They have 3 separate paper sheets they go by.
1) Fuel Totals
2) Cigarette Totals
3) Summary information

This they cannot change & the secretary wants the three Tabs. But I planned to forward the Fuel & Cigarette Totals to the Receipts & Sales fields if I had gotten that far.

Thanks again,
cw

 

[Pampers]

Hi Clearwave,
I tweaked your database a little bit and got rid of some of the problems... But I left it at my office (I am home now). I will upload to my website tomorrow morning so you can have a look at it.


Pampers
Just let it go...

 

[TheAceMan1]

clearwave . . .

I was waiting for this thread to calm down!

You have an [purple]operational problem[/purple] of wether to Add New or View/Edit existing . . . [purple]Add New is the problem here![/purple] . . . When the user opens the form and the intent is [blue]viewing or editing[/blue] . . . the combobox array of added records becomes a problem ([blue]gotta delete all newly added if adding was not the intent![/blue]). This is undesireable! So my suggestions is:

Make a seperate form displaying the table of combo12 (excluding discontinued) and append according to the specific records filled out. [blue]Validation will test for any required missing records![/blue] This form would of course be triggered by an [blue]Add New[/blue] button on the subform! . . .

Your thoughts?

See Ya! . . . . . .

 

[Pampers]

Hi Clearwave,
Maybe this is something you can use.

http://www.geocities.com/peteralaerds/ROC_beta_3.zip

Pampers
Just let it go...

 

[clearwave]

Thanks again guys, ...
Pampers, I don't see any changes to the file or code?
Sorry if I missed your changes.
cw

 

[Pampers]

Hi Clearwave,
Not such a clearwave at this end. Posted the wrong dbase. Sorry. Put it up tommorrow...

Pampers
Just let it go...