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. - Option Compare Database
-
-
Public Function CarryOver(frm As Form, strErrMsg As String, ParamArray avarExceptionList()) As Long
-
On Error GoTo Err_Handler
-
'Purpose: Carry over the same fields to a new record, based on the last record in the form.
-
'Arguments: frm = the form to copy the values on.
-
' strErrMsg = string to append error messages to.
-
' avarExceptionList = list of control names NOT to copy values over to.
-
'Return: Count of controls that had a value assigned.
-
'Usage: In a form's BeforeInsert event, excluding Surname and City controls:
-
' Call CarryOver(Me, strMsg, "Surname", City")
-
Dim rs As DAO.Recordset 'Clone of form.
-
Dim ctl As Control 'Each control on form.
-
Dim strForm As String 'Name of form (for error handler.)
-
Dim strControl As String 'Each control in the loop
-
Dim strActiveControl As String 'Name of the active control. Don't assign this as user is typing in it.
-
Dim strControlSource As String 'ControlSource property.
-
Dim lngI As Long 'Loop counter.
-
Dim lngLBound As Long 'Lower bound of exception list array.
-
Dim lngUBound As Long 'Upper bound of exception list array.
-
Dim bCancel As Boolean 'Flag to cancel this operation.
-
Dim bSkip As Boolean 'Flag to skip one control.
-
Dim lngKt As Long 'Count of controls assigned.
-
-
'Initialize.
-
strForm = frm.Name
-
strActiveControl = frm.ActiveControl.Name
-
lngLBound = LBound(avarExceptionList)
-
lngUBound = UBound(avarExceptionList)
-
-
'Must not assign values to the form's controls if it is not at a new record.
-
If Not frm.NewRecord Then
-
bCancel = True
-
strErrMsg = strErrMsg & "Cannot carry values over. Form '" & strForm & "' is not at a new record." & vbCrLf
-
End If
-
'Find the record to copy, checking there is one.
-
If Not bCancel Then
-
Set rs = frm.RecordsetClone
-
If rs.RecordCount <= 0& Then
-
bCancel = True
-
strErrMsg = strErrMsg & "Cannot carry values over. Form '" & strForm & "' has no recrods." & vbCrLf
-
End If
-
End If
-
-
If Not bCancel Then
-
'The last record in the form is the one to copy.
-
rs.MoveLast
-
'Loop the controls.
-
For Each ctl In frm.Controls
-
bSkip = False
-
strControl = ctl.Name
-
'Ignore the active control, those without a ControlSource, and those in the exception list.
-
If (strControl <> strActiveControl) And HasProperty(ctl, "ControlSource") Then
-
For lngI = lngLBound To lngUBound
-
If avarExceptionList(lngI) = strControl Then
-
bSkip = True
-
Exit For
-
End If
-
Next
-
If Not bSkip Then
-
'Examine what this control is bound to. Ignore unbound, or bound to an expression.
-
strControlSource = ctl.ControlSource
-
If (strControlSource <> vbNullString) And Not (strControlSource Like "=*") Then
-
'Ignore calculated fields (no SourceTable), autonumber fields, and null values.
-
With rs(strControlSource)
-
If (.SourceTable <> vbNullString) And ((.Attributes And dbAutoIncrField) = 0&) _
-
And Not (IsCalcTableField(rs(strControlSource)) Or IsNull(.Value)) Then
-
If ctl.Value = .Value Then
-
'do nothing. (Skipping this can cause Error 3331.)
-
Else
-
ctl.Value = .Value
-
lngKt = lngKt + 1&
-
End If
-
End If
-
End With
-
End If
-
End If
-
End If
-
Next
-
End If
-
-
CarryOver = lngKt
-
-
Exit_Handler:
-
Set rs = Nothing
-
Exit Function
-
-
Err_Handler:
-
strErrMsg = strErrMsg & Err.Description & vbCrLf
-
Resume Exit_Handler
-
End Function
-
-
Private Function IsCalcTableField(fld As DAO.Field) As Boolean
-
'Purpose: Returns True if fld is a calculated field (Access 2010 and later only.)
-
On Error GoTo ExitHandler
-
Dim strExpr As String
-
-
strExpr = fld.Properties("Expression")
-
If strExpr <> vbNullString Then
-
IsCalcTableField = True
-
End If
-
-
ExitHandler:
-
End Function
-
-
Public Function HasProperty(obj As Object, strPropName As String) As Boolean
-
'Purpose: Return true if the object has the property.
-
Dim varDummy As Variant
-
-
On Error Resume Next
-
varDummy = obj.Properties(strPropName)
-
HasProperty = (Err.Number = 0)
-
End Function
0 1135 Sign in to post your reply or Sign up for a free account.
Similar topics |
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 have a primary form named
TestResults, which is connected to data in a table named TestResults.
There are basically two other tables that are related to the
TestResults table (and the primary form) named Names-Normalized and
SiteAddresses. The...
|
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 other
cost subform. The total field reflects the correct amount as long as
there is at least one record for the account in the other cost
subform. However, many accounts won't have other costs assessed so
those accounts are not in the query for...
|
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?
_______________________________________________________________________
Re: Multiple Sort in a Continuous Form - Similiar to AutoFilter
06 Feb 2005 06:21
Allen Browne
|
by: Jerry Alexander |
last post by:
The Northwind Order Entry Application database is great!
-----------------------------------------
But one thing is lacking: Real-time Stock Qty calculation!
-----------------------------------------
I know that this topic has been discussed over & over in forums, but I
have yet to find anyone that has shown the following:
-----------------
1) Linking Allen Browne's code to Northwind? then an
2) Inventory report to show Products &...
|
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
subform.....
This MSDN article however makes it sound as simple as clicking "Filter
by Form", entering the search criterial in the form and/or it's
subforms, then just clicking "Apply Filter"......
...
| |
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 subform
is close to completion, but I still need help...
The parent form's recordset has fields
string integer
COLORNAME CLASSSIZE and a few more that aren't relevant here.
|
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, implemented it
and was successful to make it work. Thank you Mr. Browne!
However, the copy code on his web site only updates one sub-form table.
My database has three sub-forms that need to be updated. Although, I
am not a programmer (I am...
|
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
"Admin" screen to allow the head social worker to maintain items in
the list. (This is a volunteer project for a non-profit that is trying
to help elders "age successfully" in their own homes, and I'm trying
to help them do some tracking for...
|
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 report" button
located on my form to print all of the list box values (that have been
updated via selection from combo boxes) from the form to the report.
I've tried using a macro with the code:
Macro Name: cmdGenerateReport : On Click
Action:...
|
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) and this is working great, edit, insert etc is working bar when I try to delete a record in one of my subforms (I'm in test stage at the mo) I get a run time error 3022 'The changes you requested to the table where not successful because they would...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |