Setting default value for all records in form

[robsworld78]

I have a form that has 3 subforms, in one subform I have a textbox called "BarsGiven" in another subform I have a textbox "BarsReturned".

When you open the form there are 10-20 records that show up and each record has a number in the "BarsGiven" textbox. In the "BarsReturned" textbox it has a 0 in all the records but I want that textbox to default to the same numbers showing up in "BarsGiven"

If I use the following code on the subform onload event it will only affect the first record in the subforms, all the other records still show up as 0.

Me!BarsReturned = Forms!NightCount!NightCountInventorySubform.Form.B arsLeft



If I use the same code on the textbox onfocus event it will do what I want but I need to click in every record. It will copy the correct record but I want it to do it when the form opens to all records without having to focus.

Is there something I can add to the code above so it affects all records in the onload event of the subform?

Thanks

[June7]

How are those 10-20 records originally created? That is when the values need to be saved.

[robsworld78]

Hi, the records are order details and the order is being passed around from form to form via a "status" field in the orders table.

I actually need to do something different then described above.

I have a form that has a subform and when it opens the textbox "BarsReturned" is blank, nothing in it. What I want to happen is when that form opens it populates the "BarsReturned" textbox in all the records with 0.

If I have a zero set as a default value for that field in the table when I open the form it has a 0 there however I have a field on the form doing a calculation and that field won't update unless a user enters 0 in the textbox again. The field doing the calculation is stored in a table, I know what your thinking but its all good in this senerio, no duplicate data being stored, its normalized and all that good stuff.

So when the form opens I need it to re-populate the "BarsReturned" textbox with 0 for all the records in the subform. The subform is named "NightCountEnterReturnsSubform", that's where the field "BarsReturned" sits.

Main form is linked to a subform via VendorID. (Main form holds the Vendor info, The subform holds the order information coming from the orders table)

That subform is linked to 2 other subforms via OrderID. (Data comes from orders detail table) (one subform has the textbox "BarsGiven" the other subform has the textbox "BarsReturned". I had to make 2 subforms because the query for "BarsGiven" isn't updateable so "BarsReturned" couldn't be on there.) Those 2 subfroms are linked together via InventoryID using a recordset. On the subform that is updateable it also has a textbox called "BarsSold" this is the one that does the calculation and stores the data. Again all is done proper, no need to raise an eyebrow.

So that's the structure of the form, this is what's happening.

When the form opens with the default value of 0 set in the table for "BarsReturned" the math in BarsSold didn't happen yet and I'm happy about that. I don't want that math taking place until this form is open. This form can only be opened once on an order, there is no close button, just a post button.

So "BarsSold" at this point is saying 0, but once a user enters 0 over the default 0 in "BarsReturned" if 32 was "BarsGiven" it will say 32 sold, great.

Here's the idea, once that form is opened it re-populated the "BarsReturned" textbox with 0 so it automatically assumes that "BarsSold" is 32 not 0.

When a vendor opens this form and does the night count we want to first assume that all is sold so if the vendor forgets to enter a number somewhere it will tell us all was sold and when time comes for him/her to pay we will be asking for to much instead of to little. If we ask for to little people don't mention the error because its in their favor but if we ask for to much the error is definitely brought up.

So in summary, I need the textbox "BarsReturned" in the subform "NightCountEnterReturnsSubform" to re-populate to 0 when the form opens for all the records in the subform.

Thanks

[robsworld78]

I managed to get the form populating with the 0, this is the code I'm using on the main form in the "onload" event.

Private Sub Form_Load()
Dim strBarsReturned As String
Dim OrderID As Long
Dim strSQL As String

strBarsReturned = 0
OrderID = Me!NightCountOrdersSubform.Form!OrderID

strSQL = "UPDATE Inventory set Inventory.[BarsReturned] = ('" & strBarsReturned & "') where Inventory.[OrderID] = OrderID;"

CurrentDb.Execute strSQL
Me.NightCountEnterReturnsSubform.Form.Requery
End Sub

I have my table open in the background before I open this form, once I open then form I can visually see that the field "BarsReturned" does get populated with 0 for all the records which also happens on the form so at first I was happy and thought all was good but here's my problem now.

As I mentioned I have a textbox on that form, in the same subform as "BarsReturned" which is called "BarsSold". There's another textbox on another subform called "BarsGiven". Now when someone enters a number in "BarsReturned" it subtracts it from "BarsGiven" and puts the answer in "BarsSold" which is bound to table "Inventory" (the order details)

So when the form is open and a user enters 0 in the "BarsReturned" textbox it says all was sold. This is what I wanted to happen using the code above.

It is putting the 0 in the textbox for all the records however its doing it differently then if a user was to do it. Its not triggering the math for "BarsSold".

The math for "BarsSold" is in the "AfterUpdate" event on the "BarsReturned" textbox.

So when this code runs the form isn't assuming an update for that textbox and triggering the code even though that textbox is being updated with the 0 it never had.

The idea is once this form opens the system automatically assumes everything given was sold and its up to the user to enter the correct returns.

Hope this makes sense and if you have any ideas that would be great.

Thanks

[June7]

I haven't used the new Calculated field type in Access 2010.

This description makes my head spin. I think I know what you are trying to do but would help if I could see the form in action. Instead of me trying to replicate your structure, would you care to make project available for analysis? Attach to post, zip if large, run Compact & Repair first.

[robsworld78]

I attached the db. The form you want is "Vendor Night Count" (2nd row, 4th button on the dashboard) don't use the post button, just close and open it. If you use post it will change the status of the order and can't be viewed again. The order info is stored in the table "Orders" and the order details are stored in the the table "Inventory"

The db is split so I've linked them to be in c:\Inventory Control you can change it if you want obviously.

Let me know if you need info on how something works.

Thanks

[robsworld78]

I haven't used the new Calculated field type in Access 2010.

The field is a standard number field, not a calculated field, the calculation happens on the "Night Count" form.

[June7]

Oh, I misread what you said about where the calc is taking place.

You are doing something in this project I am not familiar with. Why can't I see all the objects in the Access Navigation pane? The tables group doesn't show at all. I open the backend and none of the tables show. I changed Navigation pane to show hidden files and then the tables show but are faded, however, I can open them. Even holding the shift key as file opens has odd results to me. The tables and other objects now show but are faded.

I am trying to reconcile your description of the Night Count form with what I see. I see textboxes for Bars Given and Bars Returned but not Bars Sold.

Setting objects to open maximized, popup, modal can interfere with debugging. My practice, as I was taught, is to not set those properties until ready to implement the app.

[robsworld78]

I have most of the forms set to hidden thats why there faded out. A few shouldn't be faded, those were recently created. I do that so I can see what I'm currently working on better.

BarsSold isn't on the form but its in the record source so its there but not shown. There's no reason to see it that's why its not there. If you open the table "Inventory" go to the bottom records, OrderID 10, there's 4 records. The last field is BarsReturned, blank them all out, then open night count without using the dashboard, when it asks for the vendorID enter "11" this is Cindy? Keep your eyes on the "BarsReturned" field in the table in the background and when the form opens you'll see what happens.

[June7]

Am confused by seemingly contradictory statements.

As I mentioned I have a textbox [you meant field?] on that form, in the same subform as "BarsReturned" which is called "BarsSold".

BarsSold isn't on the form but its in the record source so its there but not shown. There's no reason to see it that's why its not there.

When I first opened the Night Count form, Given textboxes had values other than 0. I did some entries in BarsReturned and watch values in Inventory table change. Now when the form opens, Given textboxes all say 0. I discovered that these textboxes are not bound to the field BarsGiven in the Inventory table but to a calculated field in a query. The BarsReturned textboxes always have 0 when form opens.

I still don't understand the data flow here. You seem to be trying to modify values in Inventory table to account for cumulative transactions. I don't think I would do this. But I have never designed an inventory db. Would have to know more about your business operations which impacts how data is organized and managed. You have some very complex queries and relationships in attempting to manage this update behavior. The Relationships editor is about the most spider-webby I have ever seen and includes queries, never seen that.

Don't think I have the time to invest in trying to understand your setup and having you try to explain through postings has its frustrations. Maybe someone else will have more insight than I.

[robsworld78]

Yeah sorry to confuse you, the BarsSold isn't physically on the form but this field is being filled in the table through the night count form. If you look in the AfterUpdate event on the BarsReturned you'll see it.

Everytime you play with that night count form with the order for Cindy? you need to reset the numbers in the table so it looks right when you look again. Go in the Inventory table, the last 4 records are for that order. Look at the "BarsSold" field in the table you'll see 30, 29, 24, 32 something like that, change them back to 0 whatever they are. Then in the BarsReturned field clear out the 0. Now when you open the form again the BarsGiven will have her original order amount again.

So I take it you saw the 0's being entered in the table? When you see that happen then look at the field "BarsSold" if they are all still 0 then the problem still exists, if those numbers change to the 30, 29, 24, 32 or whatever you have now then its a success and working the way I want it.

So yeah anytime you open that form night count make sure BarsSold is all 0's and BarsReturned blanked out.

Yeah the db is kinda complex and I'm actually quite proud of it because its only my 2nd db I've made, my first was very simple. I know some things should be done differently but I've managed to make everything work the way I want, well almost everything. This is my last hurdle to climb for now.

The db can track inventory for a Pup Trailer, freezers, vans, scooters, bins and vendors and then tell everything overall thats why so many relationships and funny queries. When you know how it works everything makes sense.

I understand if you don't want to play with it, I hear where you're coming from but thanks for taking a look.

For anyone who does want to take a look, which would be awesome, you only need to play with one form and one table, I know the code just needs to be on the form in the correct spot, its very possible.

[robsworld78]

Here's the story on BarsGiven. The label for the form is "BarsGiven" the source is like you said from a query and the field in the query is called "BarsLeft". "BarsLeft" is a running balance for the inventory item. I named BarsLeft on the form "BarsGiven" because thats what was given on that order.

The field "BarsGiven" in the table "Inventory" that you see is used on the "Create New Order" form, this is for new product given which is extra product over what the Vendor already has allocated to them.

Bubble Gum Swirl on the Night Count form has the value of "30" in BarsGiven but you see in the Inventory table the field "BarsGiven" only says "24" Thats because 24 new Bubble Gum Swirl's were given to Cindy who already had 6 in her bin so now she's actually leaving with 30.

Thats how that works.

[June7]

So I take it you saw the 0's being entered in the table? When you see that happen then look at the field "BarsSold" if they are all still 0 then the problem still exists, if those numbers change to the 30, 29, 24, 32 or whatever you have now then its a success and working the way I want it.

Like a bulldog, I do hate letting go. I opened the project again and the Given textboxes are back to their original values. I must have done the fix you suggested before closing. Yes, I see the BarsSold field changing in Inventory table, with exception of the last line. It is moving to next record when hit Enter that commits each value to table so from the last line don't have another record to move to. However, when that record loses focus as when tabbing or mouse click elsewhere the value commits to table. So, again, what is the issue?

[robsworld78]

lol, yeah its lots of fun. Yeah after you close the form you need to reset the numbers in the table. The "BarsSold" field has to be 0 for teh last 4 records and blank out "BarsReturned" The very last record you mentioned is for a new record, its in all the tables.

You see the values for "BarsSold" change when you enter a 0, if you put 0 in bubble gum swirl you see "BarsSold" go to 30 because Cindy started with 30 bars and returned 0.

But what I need is when that form opens and the 0 goes in all the fields it should at that point also do the math like you personally entered in that 0. But when the form loads the "BarsSold" isn't changing.

If you make it so when you open the form and see the 0 go in "BarsReturned" like it is but now also see the "BarsSold" change to 30 for bubble gum swirl, then its a success, that's of course without you entering in that 0 for bubble gum swirl. I say just the one product but it needs to happen for all records.

[June7]

Will this serve? In the Load event of NightCountInventorySubform

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
While Not rs.EOF
    'rs!BarsSold = rs!BarsLeft 'this doesn't work because the form's recordset is not editable.
    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE Inventory SET BarsSold=" & rs!BarsLeft & " WHERE InventoryID=" & rs!InventoryID
    DoCmd.SetWarnings True
    rs.MoveNext
Wend
rs.Close

Since the comboboxes are locked and not enabled I suggest changing to textboxes. That dropdown button is just distracting.