Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    check for dupes - unbound subform (A2K, Win2K Pro)

    I have a project with a main screen that uses a drop-down to open various forms. one of the forms has several unbound 'subforms' embedded. I want to implement error-checking to make sure a newly entered record is not already in the system. one form has a field called 'Filing Number' that is expected to be unique, so this is what I want to use. I have VBA module attached to the Before_Update on this control and the VBA does a Select Count(*) on a query with just the Filing_Number column selected. In order make the VBA fire when desired, I need to first refresh the subform and then run the dupe-checking code. On other forms in the project, this is accomplished with Me.Refresh. If a record is flagged as a dupe, additional coding will delete the newly created record using DoCmd.Menu Items. The problem I am having is referencing the unbound sub-form. Me.Refresh doesn't work; the DoCmd functions for deleting the record won't work either.

    My suspicion (since this strategy works on other forms that don't have unbound subforms) is that there is an issue with trying to reference a subform in code - even if the code is attached to that form itself. I haven't had luck using Forms![FormName].Refresh either...

    Any ideas??

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: check for dupes - unbound subform (A2K, Win2K Pro)

    Sorry, I'm confused. An unbound subform doesn't have a record source, so there is nothing to refresh or requery. Why do you think you need to refresh an unbound subform?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: check for dupes - unbound subform (A2K, Win2K Pro)

    the "subforms" do have record sources. the design is a little unconventional as I was asked to group several different forms in one category. In effect, for UI purposes only, there is a main form that displays other "subforms" based on selections in a drop-down. These "subforms" are built on separate tables at this time.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: check for dupes - unbound subform (A2K, Win2K Pro)

    So you actually meant that the subforms are not linked to the main form (the Link Master Fields and Link Child Fields properties of the subforms are blank) instead of that they are unbound. Is that correct?

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: check for dupes - unbound subform (A2K, Win2K Pro)

    yes - sorry I thought that was implied in the "unbound".

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: check for dupes - unbound subform (A2K, Win2K Pro)

    Try code like this:

    Me.SubFormName.Form.Refresh

    SubFormName must be the name of the subform as a control on the main form; this is not necessarily the same as the name of the subform in the database window, To see the control name, open the main form in design view and click once on the subform to select it. The control name will be displayed in the Object box on the Formatting toolbar, and in the title bar of the Properties window.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: check for dupes - unbound subform (A2K, Win2K Pro)

    no luck - and now I'm getting all sorts of strange errors - KeyUp as event property not found... when clicking into and out of the control MouseMove as event property not found...on opening the main form/subform

    ?????

    I stripped the event handling code completely. this db project has become possessed... Never even looked at KeyUp or MouseMove...

    Compact and Repair seem to have fixed the ghosts...

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: check for dupes - unbound subform (A2K, Win2K Pro)

    Why don't you simply set a unique index on the field that shouldn't have duplicates? If you do that, Access will prevent the user from saving a record if the value already occurs. If you don't like the error message Access displays, you can intercept it and replace it with one of your own using the On Error event of the form.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: check for dupes - unbound subform (A2K, Win2K Pro)

    in this particular case that might work as the field in question is definately supposed to be unique. I would set that in the Table defs for the form, I suppose... for some other forms in this project, I need to set 2 or more fields as unique. I don't like bunching unrelated forms into a form/subform design, but they asked to have a set of forms bundled into one selection choice on the main drop-down...

    I am a bit frustrated by the fact that, in the form VBA code, I have a 'Refresh' button that works fine on the target form, but if I move the code for that button into the VBA for the text box it also fails to find the referenced controls. I thought perhaps to assign a hot-key to the button and use SendKeys to 'press' it but I can't find a way to even assign a hot key to the form button. of course, the user can always do a find before entering new data, which is what i ended up doing just to get the info in.

    so basically I need to understand how to, in code, reference the controls in the currently active form (be it a subform or subform or a subform...).

    I've tried me.form.refresh, no go. using the ! syntax doesn't work either. I got freaked out about the db just starting to throw errors on events I never even tried to capture (such as KeyUp and MoveMouse) which indicates to me that this unrelated forms/subforms design is causing Access some amount of brain-damage...

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: check for dupes - unbound subform (A2K, Win2K Pro)

    1) If you have two fields in a table, each of which needs to be unique, you can create two unique indexes. (A table can have only one primary key, but you can create multiple indexes, of which one or more can be unique). If you want the combination of two fields to be unique, you can create a unique index on the combination of those fields. This is all done in the Indexes window of the table.

    2) See Forms: Refer to Form and Subform properties and controls on the Access Web for a comprehensive overview of how to refer to controls and properties of forms, subforms and subforms on subforms.

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: check for dupes - unbound subform (A2K, Win2K Pro)

    basically, I want to have the form check as soon as possible for a dupe (whether it be from one unique value or a concatenated set) rather than blocking the user from saving the form 'at the end', so setting the indexing to a form field isn't ideal. also, it may indeed be the case that a document comes in that is, indeed unique or needs to be logged even if the it fails the definition for 'unique'. I am trying to be flexible in terms of what's allowed *and* user-friendly. I've managed to get everything I want when i code on a simple form; it's these unbound subforms that are causing problems. I don't know if ultimately it will make much difference as this project is 'on-going' and I may just lobby to break the subforms out into a normal form screen as it seems that doing it the way i am trying to is a bit difficult for Access to deal with.

    the code for checking on a dupe fires on Before_Update *should* look like this:

    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Me.Refresh

    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset

    Set cnn = CurrentProject.Connection

    CheckName = Me.Date & " " & Me.To_Name & " " & Me.From_Name

    quote = Chr(34)

    strSQL = "SELECT Count(*) FROM CheckDupes_Letters WHERE CheckDups LIKE " & quote & CheckName & quote & ";"
    rst.Open strSQL, cnn, adOpenDynamic, adLockOptimistic, adCmdText

    If rst.Fields(0) > 1 Then
    Response = MsgBox("There appears to be another entry in the source catalog for the same letter." & vbCrLf & "Click 'Yes' to delete this entry or 'No' to keep this entry.", vbYesNo)
    End If

    If Response = vbYes Then
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
    Else
    Exit Sub
    End If

    on the form Letters this works perfectly. Me.Refresh is there to make sure the entered data in the form is rolled into the count SQL; the DoCmd block deletes the just-entered record if desired; the user has the option to accept the apparent dupe and go on...I'd like to add some addtional functionality to show details on the duplicate(s) of course. Also the count is run off a query that concatenates the fields as desired. For technical reasons I want to maintain a good list of unique fields for various documents being entered.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: check for dupes - unbound subform (A2K, Win2K Pro)

    Deleting a record is *much* less ideal than preventing the user from saving "at the end".

    I wouldn't refresh or save the record, and I wouldn't use a recordset for this, and I wouldn't delete the record - I'd just cancel the update. I'd use DCount more or less like this:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strWhere As String
    Dim lngCount As Long
    strWhere = "[Date]=# & Me.Date & "# AND [To_Name]=" & quote & Me.To_Name & quote & _
    " AND [From_Name]=" & quote & Me.From_Name & quote & " AND Not [ID]=" & Me.ID)
    lngCount = DCount("*", "NameOfTable", strWhere)
    If lngCount > 0 Then
    Cancel = (MsgBox("Blah blah. Continue anyway?", vbQuestion + vbYesNo) = vbNo)
    End If
    End Sub

    ID is an AutoNumber field - if you don't have one, add such a field, you always need one.

  13. #13
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: check for dupes - unbound subform (A2K, Win2K Pro)

    yes this looks much cleaner. i was intent on using DCount at first, especially for checking on single-field values. your example does allow concatenation, so will look at that. Let me play with it...much better to not mess with saving, polling a recordset and deleting. and I was wary of building dupe-checking queries just to support the check function. and yes there's an autonumber on all these tables...

    thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •