Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2009
    Posts
    4

    Unanswered: MS Access Forms - pull up complete record with id

    I have a form based on table with ID (primary key), name, address. I want to be able to pull up the complete record in my form by entering the ID.

    LK

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14

  3. #3
    Join Date
    Apr 2009
    Posts
    4
    The combo box does allow me to enter the id and pull up the record. However it does not allow me to edit any information. I do have the Allow Edits to yes. I want to be able to edit fields in the records by having the user pull up the record by id.

    Thanks, LK

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Then replace the combo and use a textbox but you'll have to manage the case when what is typed in the text box does not match any record into the table.

    Have a nice day!

  5. #5
    Join Date
    Apr 2009
    Posts
    4
    Thanks. I had a text box originally and it did not let me enter a number in the id field. That's all I thought I had to do but it's not working. I can scroll to a record and change it but not enter the number.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    AllowEdits is True... is there much code behind the form? You might like to temporarily change the code to be applying a filter and see if the result is the same. Struggling to think what else it could be that might "lock" the form....
    Last edited by StarTrekker; 04-23-09 at 22:23.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    There is something I do not understand. Is it the whole form that is read-only or is it the text box or combo box you use to seek a record?

    If it's the text/combo I cannot understand why you would like to edit it's contents as it relates to a finite and determined set of data that consists in the IDs of the recordset. In such a case whether you enter a valid number (ie. the ID of an existing record) and everything is fine : the record is found and the form displays it, or you enter a number that does not match any existing ID and you have the choice between displaying a message (eg. "Not found") or ask if the user wants to create a new record.

    Moreover if this ID is the primary key of a table it's evident that you cannot change it. Would the system allow you to do so you'll end up with a big mess in your data. As a reminder:
    The primary key must uniquely identify each record.
    A record’s primary-key value can’t be null.
    The primary key-value must exist when the record is created.
    The primary key must remain stable—you can’t change the primary-key field(s).
    The primary-key value can’t be changed.
    Note that the word must in the above list doesn’t mean perhaps or most of the time—must is absolute.
    see: The great primary-key debate
    and: The Relational Data Model, Normalisation and effective Database Design

    If it's the entire form that is in read-only mode it should have nothing to do with the way you seek the records, be it with a combo box a text box or any other mean. There must be something else in the way you built your form that prevents from changing the data it displays and makes it read-only.

    Can you please supply the code you use in your form as well as information pertaining to it's data source (schema of the sub-laying tables, SQL of it's query etc...).

    Have a nice day!

  8. #8
    Join Date
    Apr 2009
    Posts
    4
    Thanks for the answer.

    I have a table with ID, Name, Attendance, Points fields. In form I want to enter the ID number so the name, attendance, points field are displayed. I want user to be UNable to change name but to say yes to attendance and enter points earned. No new entries are made and no one can mess with the Primary Key (ID).

    I decided to start over and it seems to work with either text box or combo box. I'm not sure what I changed to make it not work the first go around but won't worry about that.

    Thanks again for everyone's help. LK

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    It should work with either text or combo box. There is no difference between them; only that a combo box has a drop down list associated with it's text box element.

    Glad you got it sorted
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Aug 2009
    Posts
    12
    Hi
    Having the same problem:

    I'm trying to create a form that will pull up a record based on the primary key.

    I have a bunch of fields on my form which are bound to the related columns on my table, including a text field for the primary key

    In this text box where I enter the primary key, I want the form to pull up the correct record based on the primary key I typed.

    I tried to use the code through the link posted by Sinndho, but i'm having problems - I can't type in the primary key text box, it says that this autonumber field cannot be edited. I'm not trying to edit it, I'm just using it to pull up the other pieces of information on that record.

    Is it the way I am setting up the text box that makes it think that I might be editing it? I just need a field that will allow me to enter the primary key number so that the form can pull up the related record. Should it not be bound to the primary key column? How will this field work?

    Help!

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Unbind the object from the primary key.

    Then at least you will be able to type into the control. Then use the control's AfterUpdate code to "go fetch".
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Aug 2009
    Posts
    12
    yes!!!

    a thousand thanks

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    a thousand "you're welcome"'s!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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