Need Help on Fileds on a Form

[Guest]

I have a table "customers" with a field "Terms". We enter the terms like
COD,Net 30,Prepay.etc. I have another table "Orders" with a field "POTerms".
Which is almost always the same as the"terms" in the customer table. There
are cases wherein
a customer is net 30 but for a particular order it might be cod.So How can i
make a form to actually have POTerms shows the value of what is in the
customers terms
but can change it to a diffrent terms without changing the terms in the
customer
table. TIA. Will appreciate your help
Albert

 

[Dirk Goldgar]

I have a table "customers" with a field "Terms". We enter the terms
like COD,Net 30,Prepay.etc. I have another table "Orders" with a
field "POTerms". Which is almost always the same as the"terms" in the
customer table. There are cases wherein
a customer is net 30 but for a particular order it might be cod.So
How can i make a form to actually have POTerms shows the value of
what is in the customers terms
but can change it to a diffrent terms without changing the terms in
the customer
table. TIA. Will appreciate your help
Albert

There could be several ways to do this, depending on exactly how you've
set up your form, but in any case you're going to have to use the event
of creating a new order for to initially set the value of POTerms to the
customer's terms. Then the person filling out the form can change that
or not. Since you need to know the customer in order to look up the
terms, and you'll probably want to change the terms if change the
customer (due to an incorrect entry), I'd suggest something like the
following event procedure for the AfterUpdate event of the control
(combo box or text box, most likely) that is bound to the CustomerID
field on the Order form. Assuming that the controls are actually
*named* "CustomerID" and "POTerms", the code might look like this:

'----- start of example code -----
Private Sub CustomerID_AfterUpdate()

If Not IsNull(Me.CustomerID) Then
Me.POTerms = _
DLookup("Terms", "Customers", _
"CustomerID = " & Me.CustomerID)
End If

End Sub
'----- end of example code -----

That assumes that "CustomerID" is the name of the primary key of the
Customers table, and that it is a numeric field. If it's a text field,
you'll need to modify the code to surround the criterion value with
quotes, like this:

DLookup("Terms", "Customers", "CustomerID = " & _
Chr(34) & Me.CustomerID & Chr(34))

 

[Guest]

Thank you so much

Albert

There could be several ways to do this, depending on exactly how you've
set up your form, but in any case you're going to have to use the event
of creating a new order for to initially set the value of POTerms to the
customer's terms. Then the person filling out the form can change that
or not. Since you need to know the customer in order to look up the
terms, and you'll probably want to change the terms if change the
customer (due to an incorrect entry), I'd suggest something like the
following event procedure for the AfterUpdate event of the control
(combo box or text box, most likely) that is bound to the CustomerID
field on the Order form. Assuming that the controls are actually
*named* "CustomerID" and "POTerms", the code might look like this:

'----- start of example code -----
Private Sub CustomerID_AfterUpdate()

If Not IsNull(Me.CustomerID) Then
Me.POTerms = _
DLookup("Terms", "Customers", _
"CustomerID = " & Me.CustomerID)
End If

End Sub
'----- end of example code -----

That assumes that "CustomerID" is the name of the primary key of the
Customers table, and that it is a numeric field. If it's a text field,
you'll need to modify the code to surround the criterion value with
quotes, like this:

DLookup("Terms", "Customers", "CustomerID = " & _
Chr(34) & Me.CustomerID & Chr(34))

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)