473,322 Members | 1,421 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Can't get Allen Browne's Carry Over records code to work in a form and subform

I have a form that also contains a subform. I have tried to input Allen Browne's code to have the last records entered on the form to carry over when a new record is added. However, nothing is carrying over. Here is my code.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Public Function CarryOver(frm As Form, strErrMsg As String, ParamArray avarExceptionList()) As Long
  4. On Error GoTo Err_Handler
  5.     'Purpose: Carry over the same fields to a new record, based on the last record in the form.
  6.     'Arguments: frm               = the form to copy the values on.
  7.     '           strErrMsg         = string to append error messages to.
  8.     '           avarExceptionList = list of control names NOT to copy values over to.
  9.     'Return:    Count of controls that had a value assigned.
  10.     'Usage:     In a form's BeforeInsert event, excluding Surname and City controls:
  11.     '               Call CarryOver(Me, strMsg, "Surname", City")
  12.     Dim rs As DAO.Recordset         'Clone of form.
  13.     Dim ctl As Control              'Each control on form.
  14.     Dim strForm As String           'Name of form (for error handler.)
  15.     Dim strControl As String        'Each control in the loop
  16.     Dim strActiveControl As String  'Name of the active control. Don't assign this as user is typing in it.
  17.     Dim strControlSource As String  'ControlSource property.
  18.     Dim lngI As Long                'Loop counter.
  19.     Dim lngLBound As Long           'Lower bound of exception list array.
  20.     Dim lngUBound As Long           'Upper bound of exception list array.
  21.     Dim bCancel As Boolean          'Flag to cancel this operation.
  22.     Dim bSkip As Boolean            'Flag to skip one control.
  23.     Dim lngKt As Long               'Count of controls assigned.
  24.  
  25.     'Initialize.
  26.     strForm = frm.Name
  27.     strActiveControl = frm.ActiveControl.Name
  28.     lngLBound = LBound(avarExceptionList)
  29.     lngUBound = UBound(avarExceptionList)
  30.  
  31.     'Must not assign values to the form's controls if it is not at a new record.
  32.     If Not frm.NewRecord Then
  33.         bCancel = True
  34.         strErrMsg = strErrMsg & "Cannot carry values over. Form '" & strForm & "' is not at a new record." & vbCrLf
  35.     End If
  36.     'Find the record to copy, checking there is one.
  37.     If Not bCancel Then
  38.         Set rs = frm.RecordsetClone
  39.         If rs.RecordCount <= 0& Then
  40.             bCancel = True
  41.             strErrMsg = strErrMsg & "Cannot carry values over. Form '" & strForm & "' has no recrods." & vbCrLf
  42.         End If
  43.     End If
  44.  
  45.     If Not bCancel Then
  46.         'The last record in the form is the one to copy.
  47.         rs.MoveLast
  48.         'Loop the controls.
  49.         For Each ctl In frm.Controls
  50.             bSkip = False
  51.             strControl = ctl.Name
  52.             'Ignore the active control, those without a ControlSource, and those in the exception list.
  53.             If (strControl <> strActiveControl) And HasProperty(ctl, "ControlSource") Then
  54.                 For lngI = lngLBound To lngUBound
  55.                     If avarExceptionList(lngI) = strControl Then
  56.                         bSkip = True
  57.                         Exit For
  58.                     End If
  59.                 Next
  60.                 If Not bSkip Then
  61.                     'Examine what this control is bound to. Ignore unbound, or bound to an expression.
  62.                     strControlSource = ctl.ControlSource
  63.                     If (strControlSource <> vbNullString) And Not (strControlSource Like "=*") Then
  64.                         'Ignore calculated fields (no SourceTable), autonumber fields, and null values.
  65.                         With rs(strControlSource)
  66.                             If (.SourceTable <> vbNullString) And ((.Attributes And dbAutoIncrField) = 0&) _
  67.                                 And Not (IsCalcTableField(rs(strControlSource)) Or IsNull(.Value)) Then
  68.                                 If ctl.Value = .Value Then
  69.                                     'do nothing. (Skipping this can cause Error 3331.)
  70.                                 Else
  71.                                     ctl.Value = .Value
  72.                                     lngKt = lngKt + 1&
  73.                                 End If
  74.                             End If
  75.                         End With
  76.                     End If
  77.                 End If
  78.             End If
  79.         Next
  80.     End If
  81.  
  82.     CarryOver = lngKt
  83.  
  84. Exit_Handler:
  85.     Set rs = Nothing
  86.     Exit Function
  87.  
  88. Err_Handler:
  89.     strErrMsg = strErrMsg & Err.Description & vbCrLf
  90.     Resume Exit_Handler
  91. End Function
  92.  
  93. Private Function IsCalcTableField(fld As DAO.Field) As Boolean
  94.     'Purpose: Returns True if fld is a calculated field (Access 2010 and later only.)
  95. On Error GoTo ExitHandler
  96.     Dim strExpr As String
  97.  
  98.     strExpr = fld.Properties("Expression")
  99.     If strExpr <> vbNullString Then
  100.         IsCalcTableField = True
  101.     End If
  102.  
  103. ExitHandler:
  104. End Function
  105.  
  106. Public Function HasProperty(obj As Object, strPropName As String) As Boolean
  107.     'Purpose: Return true if the object has the property.
  108.     Dim varDummy As Variant
  109.  
  110.     On Error Resume Next
  111.     varDummy = obj.Properties(strPropName)
  112.     HasProperty = (Err.Number = 0)
  113. End Function
Aug 3 '10 #1
0 1116

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Jason | last post by:
I am trying to filter records in a primary form based on records in related tables. The data in the related tables is being displayed in the primary form through subforms. To be more specific, I...
1
by: Mary Ann | last post by:
I have a main form with 4 subforms. One of the subforms reflects other costs/miscellaneous charges that an account might be assessed. The main form includes a field with a total pulled from that...
1
by: Steven Britton via AccessMonster.com | last post by:
Follow Up question to the below posted by Allen Browne on 02/06/2005 - Could something like this work for a form that has a subform and the subform is a datasheet? ...
15
by: Jerry Alexander | last post by:
The Northwind Order Entry Application database is great! ----------------------------------------- But one thing is lacking: Real-time Stock Qty calculation!...
3
by: dhowell | last post by:
In reading some of the posts on this group, it appears as though it is not strait forward at all to filter a form, which has subforms, by criteria which are either on subforms or span more than one...
10
by: Thelma Lubkin | last post by:
My form/subform combination that allows user to display 'ColorSet' records, move to other records via a selector, to add,delete, and edit them, and to manage the related 'Color' records from the...
7
by: Swinky | last post by:
Mr. Browne's copy code on his web site has saved me. I have been struggling to copy a record with several related sub-form tables. I found code on his web site that copies a sub-form table,...
2
by: sara | last post by:
I use Allen Browne's Audit Trail code in everything I do and I love it. Recently, I've run into a problem I can't figure out. I have a database with about 35 lookup tables. I am creating an...
6
by: Dave | last post by:
On my form I have combo boxes. These combo boxes, after updating them, populate respective listboxes that are located below the combo boxes on the same form. I am trying to use a "generate...
6
by: babamc4 | last post by:
I have a main form (mainformlung) with 5 subforms (followupacute, followuplate, biochemresults, haemresults and pftresults). I have copied Allen Browne's Audit Trail code (thanks to Allen Browne)...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.