Auto Update Fields

[Nic]

I am a novice and need some help!

I have created a database that stores all our companies
proposals, purchase orders in/out, invoices, delivery
dockets etc.

I have a yes/no field in the proposals table for if the
proposal has been won. I would like this field
automatically to be selected ("yes") when the proposal
number is selected in the purchase order in table. Also I
would like the invoice number entered into the purchase
orders in table when the purchase order number is selected
in the invoices table.

Any help on this would be greatly appreciated.

 

[Scott McDaniel]

You could do this when these items are selected via a FORM, but not when an
item is changed in a TABLE. Tables have no event procedures, and Access
doesn't support triggers, so there is no way for a table to react when it
has changed. Instead, write logic on your Purchase orders form to set the
value of your field when a particular event has occurred. For example, if
you want to set the value of your yes/no field, you may do so with a
checkbox on your Purchase Order form ... you would often build a recordset
which would include the proposals table, and would expose your yes/no field
for use, or run an UPDATE query in the AfterUpdate event of your checkbox:

Currentproject.Connection.Execute "UPDATE tblProposals SET blnWon=True WHERE
lngProposalID=" & Forms!frmPurchaseOrders!ValueOfProposalID

You would of course have to change the table/field names to match your own,
and change the value of Forms! statement to reflect the actual ProposalID.
Of course, this doesn't include logic that would determine the actual state
of the checkbox; you'd have to surround this with an If - Then structure to
determine what to do when the users checks/unchecks this box ...

How does your proposals table relate to your purchase orders table? How does
your invoice table relate to your purchase orders table? I'm not saying your
design is incorrect, but often questions like yours are indications of
design issues ...

 

[Tim Ferguson]

I have a yes/no field in the proposals table for if the
proposal has been won. I would like this field
automatically to be selected ("yes") when the proposal
number is selected in the purchase order in table.

Don't use a field in the proposals table: just note the presence of the
number in the purchase order. Have a check box on the form, with its
controlsource set to something like

= (0 < DCOUNT("*","Proposals", _
"ProposalNumber=" & me!txtProposalNumber.Value))

which will return true if there is a record in the Proposals table, and
false if there are none.

Also I
would like the invoice number entered into the purchase
orders in table when the purchase order number is selected
in the invoices table.

Not sure what you mean by "selected in the invoices table"... If you have a
form called AcceptProposal then it is easy to use a command button to make
the Proposals record, with a simple INSERT command. You might want to use
the same event to display the new Proposals record in its own form for the
user to carry on with.

Hope that helps


Tim F