Hello,
I used Allen Browne's method of assigning default values from the last record which is especially helpful when adding a new record to filtered records. Using his method I had to create a module: - 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
and then assigned this code within beforeinsert() in the form's properties: - Private Sub Form_BeforeInsert(Cancel As Integer)
-
Dim strMsg As String
-
Call CarryOver(Me, strMsg)
-
If strMsg <> vbNullString Then
-
MsgBox strMsg, vbInformation
-
End If
-
End Sub
As I didn't want to assign default values to all fields I changed the line: - Call CarryOver(Me, strMsg, "Evidence", "EviDescrip")
This worked well in my form before.
I want to use the same code in a new form which is linked to three tables (many-to-many relationship) via a query. I've set it up in exactly the same way but when I put the code into beforeinsert() on the form all records are carrying over even though the line: - Call CarryOver(Me, strMsg, "Evidence", "EviDescrip")
is the same (the field names are from the same table on the previous form so it shouldn't have to be changed?).
Any help would be much obliged.
Leigh
1 2550 zmbd 5,501
Expert Mod 4TB
Oh, My!
Usually the stuff from Mr. Brown is fairly good and usable...
This is the method I use: http://support.microsoft.com/kb/210236 I have used it many times to copy the values from the prior record into the new record.
Give it a whirl
Sorry you got lost in the shuffle... busy days tend to role the posts off the list so taking a cue from one of the other mods/admins just going back thru the posts :)
-z
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Sophie Bradshaw |
last post by:
i was wondering if anyone could possibly help me!
i have a ms access database, and in one of the tables, one of the
fields is a lookup column, with several possible values. what i want
to do is...
|
by: Todd D. Levy |
last post by:
I have a table of Country names & Country codes in alphabetical order.
This is a lookup table that a number of other tables use to populate the
Country field via a drop down Combo Box on the...
|
by: jerome g |
last post by:
Hi,
I'm trying to use a (simple) form to create a new record, with all
values being default values coming from a previous form. My problem is
that I can save that new record only if I change one...
|
by: Jaap |
last post by:
Hi,
Situation is as follows:
- A report has a subreport
- The subreport has records of which two will fit on a page
- When the complete report is printed, the last record in the subreport...
|
by: marko |
last post by:
I don't know how to form my question but this is my problem:
I would like to fill out a form containing let's say 3 fields and then
when i go to the next record. Now the next record would be blank...
|
by: Anand Ganesh |
last post by:
Hi All,
How to Implement Move Next, Move Previous, Last Record and First Record in a DataGrid Control ?
Is there any standard tool available in the tool box button?
Thanks for your time.
...
|
by: Aaron Smith |
last post by:
How do I set field default values at runtime? Say I want a check box
that is bound to a boolean field to default to false for a certain set
of criteria, and then true for others? I also need to do...
|
by: Manny |
last post by:
Hi All
I need to default to the Last Record when a form opens.
Can someone please help me on how to do this?
Much appreciatiated
|
by: helenwheelss |
last post by:
Access 2003, using a bound form.
I'm seeing rather annoying behaviour when editing data in a control
with a default value. It only happens when the form is on a new
record.
A specific...
|
by: robtyketto |
last post by:
Greetings,
I have a main form. Upon filling in a combo box it then displays a subform (based on a query that uses values in the combo box on where clause)
THe subform has the paramater Data...
|
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...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
| |