Results 1 to 13 of 13
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Entry Validation (2000 - can be higher)

    I am trying to simplify data entry for a fairly small DB. At the moment part of the data entry for the Main Records is becoming very unwieldy. I am looking to restructure it as follows:

    Each Main Record has up to 25 Attributes - with a default value of "-". The Attribute Values are drawn from a Table which (presently) has about 300 records. Each of the Attribute Values (other than "-") should appear only once per Main Record. By the time data entry is brought up to date, many of the Attribute Values will have ended populating - at one time or another - most of the Main Record Attributes. Is there a relatively simple "no duplicates" validation rule that can be applied to the entry of Attribute Values in each Main Record?
    Gre

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

    Re: Data Entry Validation (2000 - can be higher)

    Are the attributes fields in the main record? If so, you might consider creating a separate table linked to the main table, with each record containing the ID from the main table, plus one attribute value. Put a unique index on the attribute field (the "-" value will have to be replaced by a null).

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Entry Validation (2000 - can be higher)

    I believe the Separate Table you are describing is the one I have in mind - albeit poorly described. The idea was to have frmSeparateTable with 25 ComboBoxes. (The ComboBoxes would be populated by the ~300 values in tblAttributeValues.) Is there a workable validation syntax to ensure that each ComboBox records a different value (other than Null)?
    Gre

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

    Re: Data Entry Validation (2000 - can be higher)

    Putting a unique index on the combination of the ID from the main table and the attribute value would ensure that the user cannot enter the same attribute value twice for the same ID.

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Entry Validation (2000 - can be higher)

    Thanks. In practice, the range of non-null attributes per record in tblSeparateTable will vary between 5 and ~60; although usually in the 20-25 range. From a performance point of view, presumably, once has to be in the 100s (or more) before having a large number of unique indexes becomes an issue? IF it is a potential issue, does the Access version matter?
    Gre

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

    Re: Data Entry Validation (2000 - can be higher)

    I think we're talking about different setups. What I had in mind is illustrated in the screenshot below. There is only one (composite) index on tblSeparateTable. For one record in tblMain, there can be a variable number of records in tblSeparateTable, from none up to hundreds, as long as their AttributeID's are unique.
    Attached Images Attached Images
    • File Type: png x.png (4.3 KB, 0 views)

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Entry Validation (2000 - can be higher)

    You are right. We are talking about different setups. Perhaps it would help if I added a bit of background.

    At the moment I have tblSeparateTable1, tblSeparateTable2 & tblSeparateTable3 - as shown in the attachment. Data in each AttributeValue is populated by a Table/Query from tblAttributes. As indicated, this layout has ended up making data entry unwieldy. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    The idea was to reverse the layout of the Table/Queries, but - with >300 possible AttributeValues - some kind of "no duplicates" validation is pretty essential. Is it doable?
    Attached Images Attached Images
    Gre

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

    Re: Data Entry Validation (2000 - can be higher)

    How about this then - only one intermediate table, but an extra field (could be two extra fields)? Otherwise, I fear that I don't understand what you want to accomplish.
    Attached Images Attached Images
    • File Type: png x.png (4.3 KB, 0 views)

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Entry Validation (2000 - can be higher)

    I think I've stumbled across code that does what I am looking for. It comes from WAW 3.23. Perhaps I described my problem badly. The WAW code is:<pre>Private Sub cboColor_AfterUpdate()

    On Error GoTo ErrorHandler

    Dim strColor As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSearch As String
    Dim strTable As String
    Dim intReturn As Integer

    strColor = Nz(Me![cboColor].Value)
    If strColor = "" Then
    MsgBox "Please select a color"
    Me![cboColor].SetFocus
    GoTo ErrorHandlerExit
    Else
    'Delete selected color from lookup table
    strSearch = "[ColorName] = " & Chr$(39) & strColor & Chr$(39)
    strTable = "tblColors"
    Debug.Print strSearch
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strTable, dbOpenDynaset)
    rst.FindFirst strSearch
    If rst.NoMatch = True Then
    MsgBox "Could not find " & strColor & " in lookup table"
    ElseIf rst.NoMatch = False Then
    intReturn = MsgBox("Delete " & strColor & "?", vbYesNo)
    If intReturn = vbNo Then
    GoTo ErrorHandlerExit
    ElseIf intReturn = vbYes Then
    rst.Delete
    Me![cboColor].Requery
    End If
    End If
    rst.Close
    End If

    ErrorHandlerExit:
    Exit Sub

    ErrorHandler:
    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
    Resume ErrorHandlerExit

    End Sub</pre>

    If this is indeed correct, the issue then becomes how to have the series of Combo Boxes recursively refer to the same Recordset.

    Do you have any caveats?
    Gre

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

    Re: Data Entry Validation (2000 - can be higher)

    I doubt very much that this is what you want. The code deletes the selected item from the lookup table forever. You will never be able to select that item again, even for a different main record, because it doesn't exist any more.

  11. #11
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Entry Validation (2000 - can be higher)

    A (messy) workaround would be to have an Event Procedure that fired on the creation of a new main record. For each new main record, a duplicate of the Lookup Table would be created. (The Event Procedure would first look for any existing "Input Table" (from a previous record input cycle), delete it and create a new duplicate of the Lookup table for that particular Input.

    Among other things, that would make putting new entries into the Lookup Table during the processing of a new main record rather tricky. Hasn't there also been some kind of limit on the number of new objects that can be created in a DB?
    Gre

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

    Re: Data Entry Validation (2000 - can be higher)

    It would be better to set the row source of the combo box to a query that excludes the items already selected for that main record. You would have to requery the combo box each time an item is selected or deselected, and also when moving to another main record. That way, you wouldn't have to create temporary tables.

    (By the way, I don't think there is a limit to the number of database objects that can be created in a database. There is a maximum number of 255 fields in a table, and a maximum of 754 controls on a form or report during its lifetime)

  13. #13
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Data Entry Validation (2000 - can be higher)

    On a trivial note, according to Access "Help" (in A2K), the maximum number of objects in a database is 32,768, which would be the maximum positive value of a signed 16-bit number, if you started counting at 1 instead of 0 (ie, 2^15). Hopefully you would not require that number of temporary tables in the database.

Posting Permissions

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