How to edit an existing record instead of adding a new one using VBA?

[beacon]

Hi everybody,

[Access 2003]

On the surface, I thought my question would be an easy one to resolve, but it's turned out to be more challenging than I expected...or, I'm sleep deprived and just not thinking clearly.

Anyway, I've got a subform [sfrmDataCollection] in a main form [frmStandards] that are linked using [ID] as the master and [StandardsIDFK] as the child. The primary key for the query that [sfrmDataCollection] is based on is [DataCollectionID].

The subform is set to datasheet view and I've hidden the [DataCollectionID] and [StandardsIDFK] fields. The user has to enter values in the [CollectionYear], [Score], [CollectedBy], and [DataSource] fields.

I'm trying to add logic that will only allow the user to enter one record for a given year. So, if the user enters a collection year of 2011 in the first record, they can't enter 2011 again for that subset of the master [ID] field.

However, I want the user to be able to edit the record for 2011 and update the data. I've been able to successfully allow the user to edit a record, but it's not preventing the user from enter another record with the same year.

So, if the subform has collection year 2011 for the first row in the datasheet, the user can edit this information successfully and have it applied to the same row in the underlying table/query. However, if the user adds a new record at the bottom of the datasheet, they can enter 2011...the form will display a prompt asking the user if they want to edit the found year, but if the user says yes and adds info in the other fields, it creates a new record in the underlying table/query instead of updating.

Here's the code I've been working with so far:

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
    Dim db As DAO.Database
    Dim qdfDataCollection As DAO.QueryDef
    Dim rsCurrentYear As DAO.Recordset
    Dim strSQL As String
    Dim dataExists As String
    Dim userselection As Integer
    Dim varStandard, varCollectionYear, varWhere, varStandardOld, varCollectionYearOld, varScoreOld, varCollectedByOld, varDataSourceOld, varCommentsOld
 
    varStandard = Null
    varCollectionYear = Null
 
    'Strings to notify the user about
    'the status of the collection year
    '------------------------------------
    dataExists = "Data for " & Me.CollectionYear & " has already been collected for the standard you selected." + vbCrLf + vbCrLf + "Do you want to edit the data for " & Me.CollectionYear & "?"
 
    varStandard = Me.StandardsIDFK
    varCollectionYear = Me.CollectionYear
 
    'Set the parameters string
    '---------------------------
    strSQL = "SELECT * FROM qryDataCollection WHERE " & _
             "[StandardsIDFK] = " & varStandard & " AND [CollectionYear] = " & varCollectionYear
 
    'Open the recordset and move to the last
    'record to determine the number of records
    '-------------------------------------------
    Set db = Access.Application.CurrentDb
 
    Set rsCurrentYear = db.OpenRecordset(strSQL)
 
    'Check that there are results and notify the user
    '--------------------------------------------------
    If Not rsCurrentYear.BOF And Not rsCurrentYear.EOF Then
        rsCurrentYear.MoveLast
        If rsCurrentYear.RecordCount = 1 Then
            If rsCurrentYear!StandardsIDFK = varStandard And rsCurrentYear!CollectionYear = varCollectionYear Then
                varStandardOld = rsCurrentYear!StandardsIDFK
                varCollectionYearOld = rsCurrentYear!CollectionYear
                varScoreOld = rsCurrentYear!Score
                varCollectedByOld = rsCurrentYear!CollectedBy
                varDataSourceOld = rsCurrentYear!DataSource
                varCommentsOld = rsCurrentYear!Comments
                Beep
                userselection = MsgBox(dataExists, vbQuestion + vbYesNo, "Data Exists: Update/Cancel?")
                If userselection = vbNo Then
                    Cancel = True
                    Me.Undo
                End If
            End If
        ElseIf rsCurrentYear.RecordCount > 1 Then
            MsgBox "An error occurred. There are multiple records in the database for " & Me.CollectionYear, vbExclamation + vbOKOnly, "Error: Multiple Collection Years"
            Cancel = True
            Me.Undo
        End If
    End If
 
    Set rsCurrentYear = Nothing
    Set db = Nothing
 
End Sub
 

Me and my tired brain appreciate the help! =)

Thanks,
beacon

[NeoPa]

You need to get your principle logic working better Beacon. Consider the following and see if it doesn't make the whole thing fall into place more naturally, and make designing the code far easier (BTW nice job on explaining the problem. I wish all problems were explained as clearly and succinctly.) :

Start with a form/subform setup very similar to what you currently have but with the following difference - There is now an unbound control (You can make it a ComboBox if you like but a simple TextBox would do fine.) on your subform (in the header or footer so that it appears only once for the form) for selecting a year.

Whenever the value of this control is updated some code runs to :

1. Determine if the matching year record already exists.
2. Select the correct record for that year (The New record if the record doesn't already exist).

The Form_Current() procedure would need to ensure no other method was used to navigate through the records.

The DefaultValue property of the [Year] control would be set when a new record was selected and either of its Enabled or Locked properties should be set to disallow the operator from ever changing it.

[beacon]

Hi NeoPa,

I left out one important detail, which may change things slightly, although I think a minor tweak to your suggestion would still work...please interject if I'm off base.

The main form actually has a tab control and the subform is on one of the tabs (there's actually a subform on three out of the four tabs, but the one I'm working with is on the last tab, the other two are good). So, when the form opens, the record is associated with a standard number and the first tab displays general info about the standard, while the next three tabs show different pieces of information.

The info on the last tab, where the sfrmDataCollection resides, contains info that may be added or edited, depending on the needs of the user. The requestor had asked that they be able to update the subform datasheet and have it determine if the record needs to be edited or if it needs to be added.

I think I could probably get away with your suggestion, but I'm wondering, since I'm using the tab control, if I really need to put an unbound combo/text box in the header/footer of the subform. It sounds like your suggestion will display nothing until the user selects a year and the requestor wants all records to remain visible in the datasheet for the sake of making quick comparisons for a given standard.

I was thinking that I should be able to add unbound controls immediately above the subform to allow the user to input info. Once the user has input his/her info, they could hit a command button that would use DAO to add/edit the underlying table/query, then requery the now locked subform to display the addition/change.

Then, I could use the Form_Current() event to clear the data in the input fields in case the user tried to navigate to a different standard before submitting the info for another standard.

Like I said above, main thing that the requestor is looking for on this tab is for the user to be able to see multiple years in the datasheet for a given standard for quick comparison. I don't think the user really cares if the info is updated directly in the subform, although it would be nice if it could without having to add any additional fields.

Does this make sense? Is what I've outlined above basically what you were suggesting and will the placement of unbound fields in the detail section, as opposed to the header/footer, cause problems?

Thanks again for the help,
beacon

[NeoPa]

It doesn't sound like you've quite grasped this Beacon. At this point I'd suggest reading the post (#2) again carefully.

Now I'll just point out some things which hopefully, by now, won't come as a surprise :

1. The suggestion doesn't stop any of the records of the subform from being displayed. It merely stops any being navigated to without using the new, unbound control in your header / footer section (or if on a Tab (or Page) then as a separate control on the tab, but not in the Detail section).
2. The code in the Form_Current() procedure can actually be omitted if you prefer. If you want to allow the operator to navigate normally between existing data records on that subform. The [Year] control should still be locked / disabled if you want to ensure the operator never changes the year for an existing record, but they would need to use the unbound control to enter the year for a new one.

Obviously, you can deviate from the suggestion wherever you feel it suits your purposes, but I wouldn't add the unbound control to the Detail Section unless you want a copy of it for every record (which wouldn't fit the plan, as I've outlined it at least).

It does help to mention that you're working on a tab, but it shouldn't effect the concept too drastically. Let me know whether this new understanding makes more sense. remember though, most of what you want to do can be done easily enough. It's the proper logical determination of exactly what's required that's the hard bit (and in the case of working on a forum, writing that down clearly enough so it makes sense).

[beacon]

Just to clarify one part, which may be partly what was confusing me in your original reply...I wouldn't be able to put the unbound control in the header/footer of the subform since the subform is set to datasheet view, right? Or am I being completely dense and forgetting how to display the header/footer in datasheet view (I didn't think it was possible, but just wanted to check that there isn't some workaround)?

I've used continuous forms to mimic datasheet view before, but the subforms on the other tabs are set to datasheet view and I was really hoping a) that the subforms would use the same default view and b) I wouldn't have to re-do the default view on any of the subforms (this is my OCD/perfectionist side rearing it's ugly head)

[NeoPa]

Datasheet view does indeed preclude the use of Headers/Footers. I was confusing that with Continuous Forms. My apologies. Nevertheless, the unbound control can still be maintained on the Page (Tab) instead. It just means references to it are a little more complicated.

Never apologise to me for being OCD/Perfectionist. Database developers particularly should see these attributes as a definite bonus.

Personally I never use Datasheet View, as I don't think it looks as good as you can get with form design, but consistency is definitely worth appreciating regardless. If Datasheet View works for you then I'm sure we can manage the concept around that (but as you say it certainly precludes seeing any Headers/Footers).