Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2007

    Question Unanswered: Data Validation Question

    I have a form named "Claims Tracking System" and another named "Client Database". On the Claims form I have three fields "Account Number", "Client Name" and "State". In the Client Database, I have fields "Client Name" and I have fields for all 50 states and in the text boxes that correspond with each state there is an account number.

    When a user enters an account number in the claims form, I want the employer and state to be filled in automatically based on the data in the Client Database. If the user enters a wrong account number, I need an error message to appear.

    What is the best way to go about this?

    Thanks all!

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    Either I don't understand or there is something wrong in what you describe. You do not store data into a form, data reside in tables, which forms can display.

    As for retrieving data from a table and provided that you have a unique identifier that works for both the current data in the form and the data in a related tables, there are several techniques:

    - Use a Combo or a Listbox whith several columns. The common value is selected from the visible column of the combo or list and the other values are extracted from the other (possibly hidden) columns of the object using:
    Where x represents the position of the column with x=0 for the first (leftmost) column.

    - Use a set of DLookUp() functions, one for each data to be retrieved.

    - Open a Recordset on the data provider table. This is often more efficient than the use of DLookUp() when there are many pieces of data to retrieve.

    - If the form is bound, it's also possible (sometimes) to write a query that will include the desired information and use it as the RecordSource of the form.
    Have a nice day!

  3. #3
    Join Date
    Oct 2007
    Well, I should have been more specific. The data is stored in a table which is displayed on the form in which the user enters more data. Tables have the same names as my forms. Maybe this helps clear it up. Thanks!

  4. #4
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    so effectively you want to lookup the data fromt he other tabels

    the lazy way is to use two DLOOKUP's
    the better way is to open a recordset and retrieve the data

    if you use a dlookup then if the return value was null then the details weren't found
    if you use a recordset then if the recordsets .eof & .bof properties are both true then nothing was found
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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