Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Forms and #deleted (2000)

    Hey all,

    I have a form used to insert contact information into a single table on my
    SQL Database. I
    wanted to use an identity column for the RecordID field. When I click
    submit on my form the data gets inserted but #deleted shows up in all my
    fields in the form.

    It seems to be unable to find the identity value to reselect the record
    which I assume it does. I am new to VBA and
    forms. How can I tell the form to retrieve the new record by @@Identity so
    that it can display the record.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Forms and #deleted (2000)

    Take a look at this thread on a similar problem with SQL Server identify values.
    Charlotte

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

    Re: Forms and #deleted (2000)

    Charlotte has you on the right track - but in looking at the thread she pointed you to I realized that I never responded to the final question from Santosm. First of all, is there a reason why you are using VBA to do things from a form, rather than binding the recordset to the form? It may be that a simple bound form will do what you are after. Presuming that's not the case, the trick is to use some other unique value in the record you just added to find the record and lookup the value of the identity field in SQL Server. We do this kind of thing where we use Automation to create a link between an event in an Access database and a task reminder in Outlook. Hope this gives you some ideas to work with.
    Wendell

  4. #4
    New Lounger
    Join Date
    Oct 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms and #deleted (2000)

    Wendell,

    You mentioned in the other post about creating a unique id - to repost. I am interested in that. One thing that I am struggling with is that there seems to be no way to grap and minipulate the SQL string that Access sends to SQL. I am relatively new to this, but couldn't I - after the save button is clicked - just send this query to sql

    select * from table where recordid = @@Identity

    Thanks for your help

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

    Re: Forms and #deleted (2000)

    Actually you can manipulate the SQL string that gets sent to SQL Server, but that's another story. First we need to establish a couple of things:
    <UL><LI>Are you using a SQL backend that uses linked tables, or are we dealing with an ADP here?
    <LI>Why do need to have the SAVE button - Access saves data automatically as long as you use a bound form?
    <LI>What does <font color=blue>@@identity</font color=blue> represent?[/list]The bottom line is that if you can avoid having to extract the value of the autonumber field, you are better off. And if you are using the autonumber field for identity purposes that go beyond simply creating a unique primary key, you should probably consider your own routine to generate the AutoNumber field. There are several posts on how to do that - the best approach is to simply store the next value in a table and get it from there. On the other hand there are times when you don't have any option, and then you have to involve either a DAO or an ADO recordset to find the record you just added and get the value of the autonumber field for that record.

    As to manipulating the SQL string, you can use Pass Through queries or ODBC Direct queries and in that manner specify things that SQL Server can deal with better, but it generally involves using a Stored Procedure in SQL Server. Hope this sheds further light on the situation.
    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
  •