none
Update Table on close of Form RRS feed

  • Question

  • When I close my form I am wanting to update my Table [tblInvoice] The field I want to update is [OwnerPercentage] which corresponds with  tblInvoice.InvoiceID which has a text box tbInvoiceID

    Thanks for any help...............Bob

    SELECT tblInvoice.InvoiceID, tblHorseDetails.OwnerID, tblHorseDetails.HorseID, tblHorseDetails.OwnerPercent
    FROM tblHorseDetails RIGHT JOIN tblInvoice ON (tblHorseDetails.OwnerID = tblInvoice.OwnerID) AND (tblHorseDetails.HorseID = tblInvoice.HorseID);

    Saturday, July 11, 2015 1:35 AM

Answers

  • Thanks DB got it done :) regards Bob

    UPDATE tblHorseDetails INNER JOIN tblInvoice ON (tblHorseDetails.HorseID = tblInvoice.HorseID) AND (tblHorseDetails.OwnerID = tblInvoice.OwnerID) SET tblInvoice.OwnerPercent = [tblHorseDetails].[OwnerPercent]
    WHERE (((tblInvoice.InvoiceID)=[Forms]![frmInvoice]![tbInvoiceID].[value]));

    Sunday, July 12, 2015 2:19 AM

All replies

  • Hi Bob,

    Not sure what you're trying to do. Is the form not bound to tblInvoice? Also, are you trying to store duplicate values in multiple tables? If so, that's usually not recommended. Otherwise, to store a value into a field in a table, you can use an UPDATE or APPEND query.

    Just my 2 cents...

    Saturday, July 11, 2015 3:02 AM
  • Thanks DB the form is not bound, Only One value into one table/field. Thanks Bob

    Saturday, July 11, 2015 8:18 AM
  • Hi,

    Storing duplicate values in multiple tables is not considered best practice, but if you wanted to "add" the value as a new record into another table, the SQL might look something like this:

    CurrentDB.Execute "INSERT INTO TableName (FieldName) VALUES ('" _
       & Me.ControlName & "')", dbFailOnError

    That assumes the field and value are text data types.

    Saturday, July 11, 2015 4:13 PM
  • Thanks DB got it done :) regards Bob

    UPDATE tblHorseDetails INNER JOIN tblInvoice ON (tblHorseDetails.HorseID = tblInvoice.HorseID) AND (tblHorseDetails.OwnerID = tblInvoice.OwnerID) SET tblInvoice.OwnerPercent = [tblHorseDetails].[OwnerPercent]
    WHERE (((tblInvoice.InvoiceID)=[Forms]![frmInvoice]![tbInvoiceID].[value]));

    Sunday, July 12, 2015 2:19 AM
  • Congratulations! Glad to hear you got it sorted out. Good luck with your project.
    Sunday, July 12, 2015 2:30 AM