I'm not so smart

MS Office Access


  • 1. Updating calculated field in table without tabbing out of record.
    Hello, I have a Purchase Order form which contains a subform of line items. The main form is based on tblPurchaseOrders, the subform is based on tblItems. The subform has a CostTotal field, as well as a text box CostOfAllItems totaling all items =Sum([CostTotal]). tblPurchaseOrders has a field POItemsTotal. In the subform, in the AfterUpdate event of both Cost and Quantity is the following code: CostTotal = Quantity * Cost Forms![frmPurchaseOrder]![POItemsTotal] = DSum("[CostTotal]", "PO_Items", "[PurchaseOrderID]=[PO_ID]") Yet I have the following problem: In an existing purchase order, when I modify the quantity or the cost of a line item, if I do not tab out of that record, neither the subform's CostOfAllItems nor tblPurchaseOrders field POItemsTotal get updated. I modify the qty or cost, close the form, check tblPurchaseOrders (it's not updated), reopen the form, then check tblPurchaseOrders and at that point it is updated. If I tab to the next record, then both the subform's CostOfAllItems and tblPurchaseOrders.POItemsTotal do get updated. How can I make sure that the table gets updated when the form is closed, regardless of whether the user tabs through to the end of the record or not? Many thanks in advance! Matthew
  • 2. How to get Position of a Form (Access 97)
    Hi forum, I like to restore the screen if the user restart the application. To save the position of a form I am looking for a way to get is't position (Top ?, Left ?). I found code for Access 2000, using the variable .top ... but how can i get this Information with *ACCESS 97* Thanks for your help. Niels
  • 3. Binding Label controls to data
    I am trying to use label ctrls to represent data in a table. Each ctrl would represent one record. Here is a Table layout: lblName Test XPos Single YPos Single I will have a label ctrl for each Record, and I want to loop through the record set and set each lbl ctrl to the X&Y position in the table. My Problem is that the only way I can make it work is by looping through all of the controls until the ctrl.name =RS!lblname for each record in the table. I would like set up a control varible and somehow set it to the current record in the record set. BTW: Creating new controls with Createcontrol() won't work because I need code for an on click event. Unless someone knows how to create code on the fly. Any help would be greatly appreciated. fryere
  • 4. Email Address
    When my Users enter their information into my trouble call database, is there a way to get their email address automatically rather than them filling it in a field I have provided? I want a be able to go into their Outlook profile and retrieve this email address. Thanks, Sailor

I'm not so smart

Postby Humble David » Thu, 13 May 2004 12:11:08 GMT

I pulled this code from Allen Browne's web site.  It 
copies the values of text boxes and combo boxes in a 
form.  I am in "data entry" mode where users will enter 
multiple records at one time (from a form).  I want to 
copy the value of user_id  from one record to another as 
they enter.
Here is the code...  Can someone PLEASE explain this to 
me in ENGLISH?  

Do I just change the i to user_id?

 http://www.**--****.com/ ~allenbrowne/ser-24.html

----- partial copy of code written by MVP Allen Browne
Sub CarryOver(frm As Form)
On Error GoTo Err_CarryOver
' Purpose:  Carry the values over from the last record to 
a new one.
' Usage:   In a form's BeforeInsert event procedure, 
'                   Call CarryOver(Me)
' Notes:    This example limited to text boxes and combo 
'           Text/combo boxes must have same Name as the 
fields they represent.
    Dim rst As DAO.Recordset
    Dim ctl As Control
    Dim i As Integer

    Set rst = frm.RecordsetClone
    If rst.RecordCount > 0 Then
        For i = 0 To frm.count - 1
            Set ctl = frm(i)
            If TypeOf ctl Is TextBox Then
                If Not IsNull(rst(ctl.Name)) Then
                    ctl = rst(ctl.Name)
                End If
            ElseIf TypeOf ctl Is ComboBox Then
                If Not IsNull(rst(ctl.Name)) Then
                    ctl = rst(ctl.Name)
                End If
            End If
    End If

Re: I'm not so smart

Postby John Vinson » Fri, 14 May 2004 10:40:43 GMT

n Tue, 11 May 2004 20:11:08 -0700, "Humble David"
< XXXX@XXXXX.COM > wrote:

No. i is just a loop variable.

The code as written goes through ALL of the textboxes and combo boxes
in the entire Form (not just one of them). I'll comment the code on
the following line...

If the code encounters an error, execution will jump to the line
labeled Err_CarryOver.
Reserve a variable named rst for a Recordset object
reserve a variable named ctl for a Control object (e.g. a textbox)
reserve a variable named i as an Integer
Set the variable rst to the current form's RecordsetClone property,
i.e. the set of records available for display on the Form
Check to see if the form actually HAS at least one record
If so, move to the last (most recently entered) record
Loop through all the Controls on the form, starting from the last and
looping backwards. Me.Controls(i) will be a pointer to the i'th
Set the variable ctl to the Control object in the i'th position
Is this control a Textbox?
And does it have a Name property? (a bit odd, all controls do)
Assuming (as in the comment above) that the name of the control is
identical to the name of the field displayed in that control, set the
default Value property of the control to the value of that field in
the Form's RecordsetClone
Similar logic for Combo Box controls
If the control is neither a textbox nor a combo box, don't do anything
- just loop to the next control

Hope this helps!

It may be simpler to JUST carry over the User_ID: you could use the
following code in the User_ID control's AfterUpdate event:

Private Sub User_ID_AfterUpdate()
' this code will run when the user enters a value into the UserID
' control. If that control is named, say, txtUser_ID, change the
' name as appropriate.
Me!User_ID.DefaultValue = Chr(34) & Me!User_ID & Chr(34)
' Set the DefaultValue property of the control to a text string
' delimited by " - Chr(34) - containing whatever the user entered.
' When the user moves to the new record, it will now default to
' whatever they last typed.
End Sub

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday

Similar Threads:

1.Help, I'm new at this, and I'm sure it's not difficult

Hi, I'm trying to create a personalized label for a memo 
field in my report. It should look like this:

David's Personal Story:
His story goes here from the memo field.

I want the field label to work something like this,
Trim[First Name]& "'s Personal Story:"

But I don't know the syntax for this. Can anyone help me?

Thank You!

2.work smarter not harder

Return to MS Office Access


Who is online

Users browsing this forum: No registered users and 33 guest