setting default value for all records in textbox

[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.BarsLeft

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

 

[MarkK]

You want to check out the Current event of a form, which is raised whenever a new record is loaded into the form. Handle that event to display custom info or calculations in respect to the current record.
Mark

 

[robsworld78]

I just tried that but it has the same effect as the onfocus event. You need to select the record before it changes the number, I need it to change onload.

 

[MarkK]

Sorry, I misuderstood. Forms aren't that sharp a tool for this job. What I would do in the current event of the parent form is unload the subform edit all the child records, and reload the subform. Code might look like ...

private sub form_current
  if not me.newrecord then 
[COLOR="Green"]    'unload the subform[/COLOR]
    me.subform.sourceobject = ""
[COLOR="Green"]    'edit child records [/COLOR]
    currentdb.execute _
      "UPDATE tTable " & _
      "SET BarsGiven = " & Me.BarsReturned & " " & _
      "WHERE ParentID = " & me.ID
[COLOR="Green"]    'reload subform[/COLOR]
    me.subform.sourceobject = "SomeSuformName"
  end if
end sub

See what's going on there? Does that make sense?
Mark

 

[robsworld78]

thanks for that I maybe able to use it later. I went with a different approach on this one. I have it now so when the form opens BarsReturned is empty, no number at all, and a person can't post the count until all fields are filled. This works better in the big picture.

Thanks for the help

 

[robsworld78]

I've been playing with your code to try and make something else work on that form but can't get it going, maybe because I'm trying something slightly different.

As I mentioned I now have it so when the form 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

 

[MarkK]

So is it correct to say that you need to set a field to zero in all the child records of a particular parent?
And if so, what have you tried? What fails about the code I posted? You need to change the names of stuff right, to your own system.
You can also requery the subform, rather than unload it...

private sub form_current
  if not me.newrecord then 
[COLOR="Green"]    'edit child records [/COLOR]
    currentdb.execute _
      "UPDATE tTable " & _
      "SET BarsGiven = 0 " & _
      "WHERE ParentID = " & me.ID
[COLOR="Green"]    'requery subform[/COLOR]
    me.subform.form.requery
  end if
end sub

Don't know what else to tell you. If I understand correctly what you are doing this will solve it.
Mark

 

[robsworld78]

On this one I get error 3061, too few parameters, expected 2. I looked up the error and it sounds like its not finding the field BarsReturned. I've tried a few strings in that line but same error pops up. This code was put in the "On Current" Event in the main form.

Private Sub form_current()
  If Not Me.NewRecord Then
    'edit child records
    CurrentDb.Execute _
      "UPDATE Inventory " & _
      "SET Forms!NightCount!NightCountEnterReturnsSubform.Form!BarsReturned = 0 " & _
      "WHERE ParentID = " & Me.VendorID
    'requery subform
    Me.NightCountEnterReturnsSubform.Form.Requery
  End If
End Sub

Then I tried the same thing in the "On Current" Event of the subform that has the textbox "BarsReturned" but get the same error.

Private Sub form_current()
  If Not Me.NewRecord Then
    'edit child records
    CurrentDb.Execute _
      "UPDATE Inventory " & _
      "SET BarsReturned = 0 " & _
      "WHERE ParentID = " & Me.OrderID
    'requery subform
    Me.Form.Requery
  End If
End Sub

I like this code, it sounds like it will do the job if I can get the references correct. The error comes when you go to open the form, when I hit debug it will highlight in yellow the following part of the code.

CurrentDb.Execute _
      "UPDATE Inventory " & _
      "SET BarsReturned = 0 " & _
      "WHERE ParentID = " & Me.OrderID

When you hover over that code it will say the correct OrderID.

Here's what I've found and think, correct me if I'm wrong.

You said to put that code in the main form but I think thats wrong in my case. My main form is linked to another subform which doesn't have the numbers I want to play with. So my thinking is if I put that code in the main forms "On Current" event it is trying to play with the records in the subform that has the order info because their the child records.

When I put that code on the main form instead of giving me OrderID when I hover the yellow highlighted error in the debugger it shows me the VendorID even though I'm not asking for that ID, but because thats the ParentID thats what its looking for.

Now when I throw that same code in the subform that holds the order info which is tied to the subform that holds the order details it shows the correct OrderID because now that is the ParentID.

Now it has the correct ID number and when looking for the child records to do the edit that same ID is on all the records that need changing and no more.

I can see exactly what it wants to do but its not doing it.

I changed the "tTable" to "Inventory" because thats the table for the order details. I know not the best choice for the name of the table.

"BarsGiven" is actually suppose to be "BarsReturned" thats what I need to change to 0. I've added me. infront of it and some other strings but nothing.

Thanks

 

[robsworld78]

So is it correct to say that you need to set a field to zero in all the child records of a particular parent?

Exactly, the parent, which is the order, and the children are the order details (inventory, how I got the bad name. )

All the children need to change, at any given time when this form is opened there will only be one parent record in the subform. There will always be many children for that order though.

 

[robsworld78]

I was reading on the CurrentDb.Execute function and found some code I changed for my form and it kinda works. This is the code, its on the main form "onload" event. I tried other events but all the same results.

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

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

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

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

Before I open the form I have my table in the background and I can see that the field "BarsReturned" is blank for the records as soon as this form opens all the records fill with the 0 and in the table in the background I can see it also put the 0 there as well so I was super excited at first but, of course, I noticed that the math for "BarsSold" still didn't take place. Once I override the 0 that was loaded when the form opened with another 0 it will then do the math and the table will change to reflect it.

So why isn't that math happening when that 0 is loaded on the form?

One other thing I should mention, the "BarsSold" field is populated from a calculation but not directly on the table, its a standard number field, not a calculated field. The calculation happens on the form when someone enters their returns.

 

[robsworld78]

I should have mentioned the math for "BarsSold" is located in the "AfterUpdate" event on the "BarsReturned" textbox. When "BarsReturned" gets changed the math gets triggered. Obviously when the code is run and 0 gets inserted in "BarsReturned" access isn't seeing this as an update even though techinally it is because that textbox had nothing and was updated with 0 when it was opened.

 

[boblarson]

Once you run the update query you would need to requery the form for the changes to be reflected.

 

[robsworld78]

I added the requery function but it doesn't do anything. This is what I have.

I have a requery in the code but it doesn't appear to help any. I tried requery the main form and the subform the numbers are on. The requery is right after the execute.

When the form opens the table does update with the 0 and my form also shows the 0 but the "AfterUpdate" event on the "BarsReturned" isn't running so no math is being done on that form. I put the math I have in "AfterUpdate" with the code above after the table gets updated, the code ran with no errors when I changed the locations but no math was performed.

 

[robsworld78]

Good news is the 0's that are coming up in the form are good enough for the math if I can get it to run at the right time for all the child records.

If I put the math code in the "OnClick" event of the "BarsRemaining" it will do the math once I click on the textbox for the record and click off it. So if I click each record inside the textbox it will do the math without re-entering that 0.

I've tried putting the code in all the different events available for the field but it won't do anything better than this. I've tried most events for the subform that holds this textbox but no different.

Any ideas where I can put this code so it updates all the child records when the form opens?