I created a form and set the default view as a continuous form. Basically the form is displaying records in which the user can add or edit new ones. The record source for this form is a query that I built that is based on a table.
I have been working on this for several weeks and now I have been told that many times when a user wants to create a new record, much of the information that is displayed in a preexisting record is the same. Therefore, instead of rekeying in the same information (only making changes on a couple of the fields) they would like to copy the preexisting row and paste it into the “new” record row that is being displayed (the last row that is displayed) much like one would do in Excel.
Currently I have a before update event for writing new records:
Expand|Select|Wrap|Line Numbers
- Private Sub Form_BeforeUpdate(Cancel As Integer)
- 'When a user is creating a new record the following code inserts the MonthID, YearID and
- 'The LocationsID. It does a Dlookup for the Locations ID when the control cboLocation is
- 'blank.
- Dim frm As Form
- Set frm = Forms!Forecast
- If IsNull(frm![Binding_Percentage]) Then
- MsgBox ("You need to select a Binding Percentage before you create a new record")
- Cancel = True
- Else: Cancel = False
- End If
- If Me.NewRecord Then
- 'If cboLocation is Not Null, grab the value from there
- If Not IsNull(frm![cboLocation]) Then
- JtnLocationsID = frm!cboLocation
- YearID = frm!CboYear
- MonthID = frm!CboMonth
- Else 'Forms!Forecast![cboLocation] is Null
- 'Check and see if all 3 Controls have values in them
- If Not IsNull(frm![cboDivision]) And Not IsNull(frm![cboWrkReg]) And _
- Not IsNull(frm![cboCreditReg]) Then 'values in all 3 Controls
- JtnLocationsID = DLookup("[JtnLocationsID]", "tblLocationsMM", "[DivisionIDFK] =" & frm![cboDivision] & _
- " And [WrkRegIDFK] =" & frm![cboWrkReg] & " And [CreditRegIDFK] =" & _
- frm![cboCreditReg])
- YearID = frm!CboYear
- MonthID = frm!CboMonth
- Else
- 'no value in [cboLocation], and 1 or more values are missing in [cboDivision],
- '[cboWrkReg], or [cboCreditReg]
- MsgBox "1 or more values are missing in"
- End If
- End If
- End If
- End Sub
Given the copy and paste mindset of my end user what would be the best approach to easily satisfy their desire to not have to rekey much of the information that is already in previously displayed rows? I am relatively new at this so don’t know what the best solution. One thing to mentioned is that this particular table does not have any fields where duplicates are not allowed. I guess that is one less issue to deal with.
Does anybody have ideas on how they would approach this?
Thanks,
Keith :-)