Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    7

    Unanswered: Cannot update table from query

    I have two tables, tEmployee which is a DSN-Less linked SQL table and tTransactions which is a linked back-end Access table. tEmployee holds employee information and tTransactions holds credit card transactions.

    I made the below query (qTransactions) to feed a subform (fTransactions) that will list the employees card transactions for the month. The tEmployee.LogonName=fOSUserNmae() is a filter on the query to only pull those transactions from the tTransactions table where there is a match for their domain logon name. The user should then go through the subform (fTransactions) and fill in the field “Description” from a drop-down list to update each transaction with a General Ledger account number. Problem is, whenever I add the tEmployee table to the query, whether I add any filters or not, I cannot update tTransactions via the query.

    SELECT tTransactions.ID, tTransactions.Name, tTransactions.Number, tTransactions.Status, tTransactions.Date, tTransactions.Charge, tTransactions.Payment, tTransactions.Merchant, tTransactions.Category, tTransactions.StatementDate, tTransactions.Description, tEmployee.LogonName
    FROM tTransactions INNER JOIN tEmployee ON tTransactions.Name = tEmployee.CardName
    WHERE (((tTransactions.Payment)<=0) AND ((tTransactions.Description) Is Null) AND ((tEmployee.LogonName)=fOSUserName()));

    I am at a loss as to why this is happening or what to do, any thoughts?

  2. #2
    Join Date
    Dec 2003
    Posts
    7
    Did I post in the wrong place?

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    in order for a query to be updateable it needs to contain the primary key(s) for the table(s). it also may depend ont he type of query being used, and or the properties of the query if used in a recordset object.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Dec 2003
    Posts
    7
    I made sure to add all primary and foreign keys to the query and I still cannot update any fields. As far as depending on the type of query being used, it is a Select query. I'm not knowledgeable enough to understand the meaning of, or comment on your last statement.

  5. #5
    Join Date
    Dec 2003
    Posts
    7
    The problem was that neither of the related fields tTransactions.Name or tEmployee.CardName were Unique-Indexed fields. Once I make tEmployee.CardName a Unique-Indexed field the query worked properly. Thanks for all your help.

Tags for this Thread

Posting Permissions

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