Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    65

    Unanswered: Code works in Access 97 but not in 2000

    Hi!

    I have another problem. The code below works in Access 97. But when I upgraded to Access 2000, it executes the code, tells me by means of a message box that a record was updated. But when I view the table, it does not appear to be updated. What is the problem?


    sqlStatement = "UPDATE [Update Tracker] INNER JOIN ([$10 Change Of Address] INNER JOIN " _
    & "[Main Data] ON [$10 Change Of Address].HU_FAM_ID = [Main Data].HU_FAM_ID) ON ([$10 " _
    & "Change Of Address].HU_FAM_ID = [Update Tracker].HU_FAM_ID) AND ([Update Tracker].HU_" _
    & "FAM_ID = [Main Data].HU_FAM_ID) SET [$10 Change Of Address].DATE_MOVED = #" _
    & txtDATEMOVED.Text & "#, [$10 Change Of Address].DATE_NOTIFIED = DATE(), " _
    & "[Update Tracker].DATE_CHANGE = DATE(), [Update Tracker].TIMES_UPDATED = " _
    & "[Update Tracker].TIMES_UPDATED + 1 WHERE " _
    & "[Main Data].HU_FAM_ID = '" & HUtxt & "';"

    dbsHBHCFamilies.Execute (sqlStatement)

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    What I would do is put a break point after you set up the sqlStatement variable. Then view the Immediate window and type ?sqlStatement. That will show you what is stored in the variable. Then copy what is printed and put it in a new query. You will typically get better feedback from the query design mode.

  3. #3
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    65
    Quote Originally Posted by DCKunkle
    What I would do is put a break point after you set up the sqlStatement variable. Then view the Immediate window and type ?sqlStatement. That will show you what is stored in the variable. Then copy what is printed and put it in a new query. You will typically get better feedback from the query design mode.
    I did what you said. The query extracted from sqlStatement worked and it did update the record. But when it's executed in VBA using the Execute command it does nothing.

    I'm confused. What's with Access 2000 that makes it not work?

Posting Permissions

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