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,
enter:
' Call CarryOver(Me)
' Notes: This example limited to text boxes and combo
boxes.
' 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
rst.MoveLast
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
Next
End If
I'm not so smart
2 posts
• Page 1 of 1
- 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
Next
I'm not so smart
by Humble David » Thu, 13 May 2004 12:11:08 GMT
Re: I'm not so smart
by 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
control.
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
http://go.compuserve.com/msdevapps?loc=us&access=public
< 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
control.
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
http://go.compuserve.com/msdevapps?loc=us&access=public
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! Jennifer
2 posts
• Page 1 of 1
Who is online
Users browsing this forum: No registered users and 33 guest