Results 1 to 4 of 4
  1. #1
    MichaelC
    Guest

    Sub-Form Data Entry Lookup/Validation (Access 2k )

    I need to enter/plant data into a table (subform) from a lookup/validation table on the subform. I am able to click in datasheet view and see the fields I want to validate/plant. I am able to plant the name field (if it exists) but not the ID field. I also want to be able to pop up a lookup in the subform and either choose from an existing record and plant the "Name" and "ID" fields into the relevant fields in thesubform/dataentry table, or, if the name does not exist I would like to "Add" a new name/record into the lookup/validation table.

    If this is not the proper forum for this type of question can you point me to a better place....or book?
    Thanks

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Sub-Form Data Entry Lookup/Validation (Access 2k )

    I think you are probably referring to a drop-down combo box and its behavior. As to the ID field, if you are using an AutoNumber field type, then you can't manipulate it at all. The preferred approach for this kind of problem is to store only the ID field in the dataentry table. If you subsequently want to display the name associated with the ID, you either do a join in a query, or you use a combo box to look it up for you. The beauty of this solution is that if a Name changes, you can change it in one record, and all of the records in the dataentry table can now be made to display the new name.

    Another trick relates to the use of the combo box and setting the first column width (which is bound to the dataentry table field) to a zero width. Thus when the user picks a person, they do so by name without ever knowing that an ID field is involved. The final thing is to use the "Not In List" event to pop up a form and let a user add a new record in the Name table.

    The Help file is of some use for these things, but you would probably find more and better explanations in one of the Access books - we like the Access Developers Handbook, but it's on the advanced side. Another is the Using Access 2000. Hope this helps.
    Wendell

  3. #3
    New Lounger
    Join Date
    Jun 2001
    Location
    MO
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub-Form Data Entry Lookup/Validation (Access 2k )

    In popping up a data entry form from the Not in List event, is there a way to populate the popup form with the value type into the combo box? That way they could just click an OK command button to save and close the entry confirming it as an addition to the drop-down list.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Sub-Form Data Entry Lookup/Validation (Access 2k )

    Yes - it typically is done directly however following a prompt to the user confirming that they really want to add a new name. That of course involved either ADO or DAO depending on your specific needs. I believe you can find a sample in of the smple .mdb files that comes with Access.
    Wendell

Posting Permissions

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