Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lookup Table (A2000)

    In our database we use a Lookup table to estimate certain parts.

    I'll try to explain briefly to set the scene.
    The lookup hold pre-defined part names along with an abbreviated code.ie.

    FB = Front Bumper
    RB = Rear Bumper
    OFW = Off Side Front Wing
    NFW = Near Side Front Wing
    FBTAC = Front Bumper Trim & Clips
    etc etc etc

    These items are pre-defined because they are mostly common, popular items.
    The Estimators and receptionists are familiar to these codes FB RB etc and i'm keeping to this format.
    But as with everything in this world, there has to be a twist !!
    Here's the twist <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    If we allocate a part which is spurious or uncommon, we use the code "UN" meaning unKnown.
    This code can be used for anything e.g

    UN = Bumper Fixing Kit
    UN = Sunroof Locator
    UN = Interior Window Winder etc etc etc.

    Now, I "Could" create a new code for each additional item added by a common dialog and inserting into the lookup table so updating it all the time. This would be great for reporting at a later date, but.
    The amount of codes it would generate would be surely over powering.
    It would create confusion for the user and they would never be able to remember all the new codes.
    Besides, users never use the same description creating even more confusion.ie:

    Interior Window Handle
    Inner Window Handle
    Interior Window Winder !!!!! (They're all the same part )

    So "UN" has to be free to the user to add any combination of text they feel appropriate.

    Is this do-able or not do-able.

    See attached
    Attached Files Attached Files

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

    Re: Lookup Table (A2000)

    Dave,

    I haven't been able to look at your attachment here, so this is off the top of my head.

    I wouldn't store all the extra text in the lookup table, but in the data table. Add a text field named Other (or something like that). In the data entry form, enable this field if the part code is "UN", disable it otherwise. You have to do this in the AfterUpdate event of the combo box for the part code, and in the OnCurrent event of the form (so that the enabled status is kept up to date as the user navigates from record to record).

    This way, the lookup table stays small, but the user can enter arbitrary text if the part code is "UN".

    I have attached a very simple Access 97 database to illustrate this. It contains 2 tables, a form and a report. Look at the code behind the form to see how the txtOther text box is enabled/disabled.
    Attached Files Attached Files

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Table (A2000)

    Hans
    Your demo is very inspiring but not quite what I was hoping for.
    I've converted my demo as attached to possibly give a clearer view on what I was trying to do.
    I was looking at the post from last week regarding the PLTRN.txt and this has given me an idea.
    The input for the user ideally would be from the main form, obviously not knowing whats going on in the background.

    I thought maybe some manipulation of the not in list event would be the answer to my project.

    If the code is not in the lookup table then:
    Determine what code the user has enterred:
    If code entered is "UN" then proceed and let the user carry on with spurious text:
    If the code is " Not UN " then force the user to select from the lookup table.

    As you can see from the demo, all input is in the subform.

    See what you think.

    I was using the following code in the Not In List event which forced an input box, but didn't want to have the input popping up, instead writng direct to the data table, not the lookup table.

    Function Append2Table(cbo As ComboBox, NewData As Variant) As Integer
    'On Error GoTo Err_Append2Table
    ' Purpose: Append NotInList value to combo's recordset.
    ' Return: acDataErrAdded if added, else acDataErrContinue
    ' Usage: Add this line to the combo's NotInList event procedure:
    ' Response = Append2Table(Me.MyCombo, NewData)
    Dim rst As DAO.Recordset
    Dim sMsg As String
    Dim vField As Variant ' Name of the field to append to.
    Dim vPrompt As Variant
    Append2Table = acDataErrContinue
    vField = cbo.ControlSource
    If Not (IsNull(vField) Or IsNull(NewData)) Then
    sMsg = "UnRecognised Entry, Do you wish to add " & NewData & " As A New " & cbo.Name & "?"
    If MsgBox(sMsg, vbOKCancel + vbQuestion, "Add new value?") = vbOK Then
    Set rst = CurrentDb.OpenRecordset(cbo.RowSource)
    vPrompt = InputBox("Please enter description")
    rst.AddNew
    rst.Fields(0) = NewData
    rst.Fields(1) = vPrompt
    rst.Update
    rst.Close
    Append2Table = acDataErrAdded
    End If
    End If
    Exit_Append2Table:
    Set rst = Nothing
    Exit Function
    Err_Append2Table:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbInformation, "Append2Table()"
    Resume Exit_Append2Table
    End Function
    Attached Files Attached Files

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

    Re: Lookup Table (A2000)

    Dave,

    I think I understand what you were trying to do, but I don't see the use of storing all those extra items in a separate table, especially since there is no check for duplicate items. I would still advise to put them directly in the tblEstimateDetails table.

    I have attached a modified version in Access 97 format that incorporatesideas from my previous reply. The user can only edit the Item if the Code is "UN", otherwise it's locked.
    Attached Files Attached Files

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Table (A2000)

    Hans
    You've got it exactly.
    Storing all the "UN's" in the lookup is what I didn't want as you've noted.
    The "UN's" only need to go in the main entry table.
    The demo works fine but still accepts spuriouse codes such as ZZZ, OR QQQ Which are not in the lookup table, can we do something so the db won't accept them.
    ie

    Only accept codes from the lookup unless they are "UN"

    So close

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

    Re: Lookup Table (A2000)

    That is easy. When I was modifying the subform, I temporarily set the LimitToList property of the Code combo box to No, and I forget to set it to Yes again. If you do that, it should work OK.
    Note: you will need to have an "UN" record in the tblEstimateItems table, as in my version. You can leave the description empty; whether you need an ItemID for this record is for you to decide.

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Table (A2000)

    Hans
    Silly me, I should have seen that.
    Perfect, Thanks

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Table (A2000)

    Further to this subject and the appropriate fix, the attached is of the same origine with the code identical, only using "OTHER" instead of "UN".

    I've copied all the code to the relevant actions but won't work. Is there something obvious I'm doing wrong, ie naming conventions or ???

    <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    Attached Files Attached Files

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

    Re: Lookup Table (A2000)

    Hello Dave,

    The Bound Column property of the combo box (CmbOtherCode) is set to 2 instead of 1. This means that the Code field is filled with the second column (the description) instead of the code itself. If you select OTHER, the Code field is set to Free Text, not to OTHER, so the Locked property doesn't get set correctly.

    Changing the Bound Column property from 2 to 1 should correct this for new records. The Code column in existing records must be corrected manually.

  10. #10
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Table (A2000)

    Hans
    Thanks again.
    I new it was something to do with the combo, just couldn't fathom it out.

Posting Permissions

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