Link to home
Start Free TrialLog in
Avatar of mcwllc
mcwllcFlag for United States of America

asked on

Need to update records in table from form

Hi i have a table called InvoicedCommDue and also have a form called InvoicedCommDue that displays certain fields from the table.  Most of the table is populated from an append query but there are 3 fields...
CommissionableAmount, MarkPaid, CommRate that are input from the form.  What i need to happen is when they fill this information in...it populates InvoicedCommDue.  In some cases multiple records will exists for the same invoice. I want to create a button that will update the other rows with the same invoice number . Here is what i tried so far and it didn't work...calling on this query.
UPDATE InvoicedCommDue SET InvoicedCommDue.MarkPaid = 'Yes'
WHERE InvoicedCommDue.invno='Forms![InvoicedCommDue]![invno]' And MarkPaid='Null';

Please help!!
Avatar of dqmq
dqmq
Flag of United States of America image

Give this a try:

UPDATE InvoicedCommDue SET InvoicedCommDue.MarkPaid = 'Yes'
WHERE InvoicedCommDue.invno= Forms![InvoicedCommDue]![invno] And MarkPaid is null;
Avatar of mcwllc

ASKER

It doesn't want to update the additonal rows.  I also want it to when i update the main record on the form...to update the following fields: CommissionAmount, CommRate, and MarkPay.  I would like it to happen behind the scenes so that when the button is clicked...all of those fields with matching sono (Sales Order Numbers) is also updated. I know i put invoice number but looked @ it more this morning and needs to work off of the Sales Order Number.  the sono is in the same table as invno.  Hope this helps...and thanks for the response..i'm not a programmer and am already frustrated because i'm having to create a commission piece when our ERP already has one and just needs to be modified (I don't know FoxPro)!
You don't indicate what you want to update the columns with, but perhaps something like this:

UPDATE InvoicedCommDue
SET InvoicedCommDue.MarkPaid = 'Yes'
,CommissionAmount=SalesAmount*CommRate
,CommRate= ?
WHERE InvoicedCommDue.sono= Forms![InvoicedCommDue]![invno];
Avatar of mcwllc

ASKER

Sorry...here is an example
Form
Commission Rate: 5%
CommissionAmount : 85000
MarkPay: Yes

I want to update any additional rows that have the same sales order # with the info that i put on form for the first record. Hope this helps.
ASKER CERTIFIED SOLUTION
Avatar of dqmq
dqmq
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mcwllc

ASKER

thanks...dqmq i will give it a try just as soon as i can.
Avatar of mcwllc

ASKER

ok..What happens is created this query: (Copied is perhaps a better word!)
UPDATE InvoicedCommDue SET InvoicedCommDue.MarkPaid = '1', CommAmount = Forms![InvoicedCommDue].CommAmount, CommRate = Forms![InvoicedCommDue].CommRate
WHERE InvoicedCommDue.sono=Forms!InvoicedCommDue!sono;

I then created a button on the form that opens the query:
I then fill in the values for the amounts on the form. I click run and it opens a datasheet view of the query with only the top row filled out...the rest are blank.  What do i do now?  Thanks for the help!
Avatar of mcwllc

ASKER

I need it to fill on all records in the table where the sono=sono on the form.
Avatar of mcwllc

ASKER

Actually it did work...i guess i didn't do something write @ first! Thanks for the help!
Avatar of mcwllc

ASKER

Great Job