Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form/Subform check (Access 2003)

    I have code which opens a form which contains a subform. When the form opens, I need to check that a field on the subform (Keycode) is unique, and does not already exist in the table 'Items'. This is because the items in the subform have been imported from another database. To achieve what I want I use the following code:

    Dim dbs As Database
    Dim rst As Recordset
    Dim strsql, ikey
    Set dbs = CurrentDb
    ikey = Me.Keycode
    strsql = "SELECT * FROM items WHERE keycode like '" & ikey & "'"
    Set rst = dbs.OpenRecordset(strsql)
    If rst.RecordCount = 0 Then
    Exit Sub
    End If
    MsgBox "This record contains a duplicate Keycode. Please enter a unique one.", vbOKOnly, "Duplicate Keycode"

    Items is the table containing all the products. Keycode is a field in that table which must be unique.
    The above code works OK, but where should I insert it? I have inserted it on the 'on current' event of the subform, but the message displays before the form & subform are displayed. I have tried most of the event triggers for the subform, but either the message displays before the screen is displayed (on load, on current) or it doesn’t display at all (on query, after render).

    How can I get the message to display *after* the form loads (assuming the first record triggers the message) and on each screen as required?

    I just want the message to be displayed every time a record is displayed where the keycode field in the main form matches the keycode field in the subform.

    Many thanks.

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

    Re: Form/Subform check (Access 2003)

    I wouldn't do it that way. I'd create a query that returns the duplicate values and create a form based on that query.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form/Subform check (Access 2003)

    I've done that and based the form/subform on it. The query checks another field, productCode and displays duplicates based on that field. Then I wanted to check to ensure that no one entered a duplicate keycode when they were attending to the productCode. Is this possible?
    Thanks again.

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

    Re: Form/Subform check (Access 2003)

    You've lost me. First you wanted to check for duplicate keycodes on opening a form or going to a record, now you want to prevent users from entering a duplicate keycode.

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form/Subform check (Access 2003)

    Hi Hans,
    Sorry for not being clear.
    The form/subform I have created is based on a query that checks for duplicates in the ProdCode field. As users correct this, I realised that there was nothing to alert them to the fact that they could also have duplicate keycodes, so I attempted to create another query ran as each record was being displayed, that grabbed the keycode field from the subform and tested it to see if it already exists. If it did I wanted to alert the user to ensure they entered a unique one.
    Have I bitten off more than I can chew here?
    Thanks and regards!

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

    Re: Form/Subform check (Access 2003)

    I'm sorry, I still don't understand. If you want to ensure that the user doesn't enter a duplicate keycode, you don't want to check this when a record is being displayed, but after the user enters the keycode (i.e. in the Before Update event of the keycode text box) or before the record is saved (in the Before Update event of the form).

  7. #7
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form/Subform check (Access 2003)

    Hi Hans, ordinarily your solution would work fine, however there is one occasion when it wont catch duplicate keycodes and that is when the imported file already has a duplicate keycode.

    As a result I've decided to do this another way - after checking for duplicate SuppProd fields, it will then check for duplicate keycodes via another screen based on another query. Probably not as smooth or as pretty, but at least it works!

    Thanks for all your help, much appreciated as always.

Posting Permissions

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