Results 1 to 8 of 8

Q

  1. #1
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065

    Q

    I have a SQL Server Backend One of the tables tracks Appointments Fields include



    ApptID Identity Field
    PersonID Foreign Key to the person table
    ApptDate Date of the appointment
    ApptStatus 3 choices (Appoinment Scheduled, Appointment Kept, Appointment Missed.

    The table is populated from An Access 2010 Database Front end with a subform on the Person page Linking the person page to the appointment subform with the PersonID Field. The ApptID field is on the form but Visible is set to no so the user doesn't see it. The issue is everytime I try to insert a new appointment I get the error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert explicit value for identity column in table 'tbl_Appointments' when IDENTITY_INSERT is set to OFF.(#544)

    I have other subforms with similar data structure that work fine. It's just this one that doesn't. The user doesn't have the ability to change it I don't insert any value for the identity column or the Person ID Column. the only 2 fields that are changed are the 2 displayed Date of the Appointment and ApptStatus. If I open the table in Access and enter the information it works as it should. It's only in the form that I'm having an issue. Anybody have a thought.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It sounds like the error you'd get if you tried to specify the ID value. This is a bound subform? What are the master/child links? Perhaps the ID field is specified there.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Quote Originally Posted by pbaldy View Post
    It sounds like the error you'd get if you tried to specify the ID value. This is a bound subform? What are the master/child links? Perhaps the ID field is specified there.
    Yes it is a bound subform. The Master/child link is on PersonID which is an Identity in the person table (main form based on this table) and numeric in the appointment table. There is nothing that should specify the ApptID value which is the identity column in the appointment table. the user can't see it and unless I'm missing something I don't set it anywhere.

  4. #4
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    update: I tried something. I unhid the fields personid and apptid. Opened the subform without the main form. Entered the personid, the apptdate and the apptstatus the apptid automatically populated when I entered the last field apptstatus however when I tried to go to the next record I got the same error message. I didn't enter anything in the apptid field and that is the only identity field in this table and on this form. Also tried just entering the apptdate and apptstatus to see if the personid populates automatically it did not but still got the same error. I thought it would be a different error as the personid field is set to require data and I didn't enter any. but it was the same error.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The apptid populated after you entered the status but before trying to leave the record? Is there any code running behind that status textbox? With a SQL Server backend, the identity field doesn't populate an Access form until the record is saved, so if you're seeing it populated before that something is putting it there.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Nope there is no code on the form but the status is not a textbox it's a combo box that get's the choices from another table apptstatus with 2 fields and 3 records
    apptstatusid identity field
    apptstatusdesc varchar(50) with the following choices (Appointment Scheduled, Appointment Kept, Appointment Missed)

    The apptstatusid in the appointment table is numeric and not an identity field. and is a foreign key to the apptstatus table.

  7. #7
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Ok found the problem. The apptstatusid combo box had the ApptID field in the control source instead of the apptstatusid. Can't believe I overlooked that. All works well know

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you found the problem.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums