Dlookup and simple calculation

[ryguy7272]

This is a 2-part question

First, I have several TextBoxes on my Form. Of those, one is named
‘Quantity’ and one is named ‘Rate’. In my commission TextBox, I am using
this as my Control Source:
=IIF([QUANTITY]<>"",[QUANTITY]*[RATE],"")

That’s giving me an error. It was working before; not sure what changed.


Also, I am trying to pull a rate from a table using the Dlookup method, but
not having much success with this. Basically, when I enter data in a TextBox
named ‘Cust’ (for customer), I want to see the rate that a specific customer
pays. Here’s my code, which I found from an online video of how to do this.

Private Sub CUST_AfterUpdate(Cancel As Integer)
RATE = DLookup("Cust", "Customers", "Rate=" & RATE)
End Sub

‘Cust’ is the Field on my Form, Customers is the Table, and in theta Table I
have Customer and rate and a few other things.

The Error message is ‘The expression After Update you entered as the event
property setting produced the following error: Procedure declaration does not
match description of event or procedure having the same name.’

I must be missing something pretty simple. Can someone please tell me what
it is.

Thanks!
Ryan--

 

[John W. Vinson]

This is a 2-part question

First, I have several TextBoxes on my Form. Of those, one is named
‘Quantity’ and one is named ‘Rate’. In my commission TextBox, I am using
this as my Control Source:
=IIF([QUANTITY]<>"",[QUANTITY]*[RATE],"")

If Quantity is a Number datatype field (which it should be, if you're doing
calculations) it will never be eaual to the text string "". Given that a NULL
value multiplied by a number will give NULL, it sounds to me like you don't
need to do anything at all other than

=[QUANTITY] * [RATE]

in the second textbox's control source.

That’s giving me an error. It was working before; not sure what changed.

I'm not sure either, since it never should have worked.

Also, I am trying to pull a rate from a table using the Dlookup method, but
not having much success with this. Basically, when I enter data in a TextBox
named ‘Cust’ (for customer), I want to see the rate that a specific customer
pays. Here’s my code, which I found from an online video of how to do this.

What data are you entering? The customer's ID, the customer's name, part of
the customer's name...???

Private Sub CUST_AfterUpdate(Cancel As Integer)
RATE = DLookup("Cust", "Customers", "Rate=" & RATE)
End Sub

This is making no sense. Your DLookUp isn't using the CUST information at all;
it's looking up the value of the Cust field in the Customers table using the
RATE control on the form, and assiging that Cust value to the RATE control on
the form.

‘Cust’ is the Field on my Form, Customers is the Table, and in theta Table I
have Customer and rate and a few other things.

The Error message is ‘The expression After Update you entered as the event
property setting produced the following error: Procedure declaration does not
match description of event or procedure having the same name.’

I must be missing something pretty simple. Can someone please tell me what
it is.

I *THINK* what you want to do is to set the Rate to the value of Rate looked
up from the Cust table:

Private Sub Cust_AfterUpdate() ' AfterUpdate does not have a Cancel
Me!Rate = DLookUp("Rate", "Customers", "Customer = """ & Me!Cust & """")
End Sub

This assumes that you're entering a text customer name, and trusting your user
to never, ever under any circumstances make a typo, and that you will never
have two customers who happen to both be named Jones (bad assumptions all);
you'll do a lot better to use a Combo Box to select a numeric CustomerID.

 

[ryguy7272]

Thanks for all the help with this stuff, John!! I've read many Access books,
and I have to say, the books are great for getting started, but there's only
so much you're gonna learn from a book. I've learned much, much, much ore
from this discussion forum, and the other Access-Groups, than I learned from
any book.

I followed your advice, and changed that 'Cust' TextBox to a ComboBox.

Thanks for everything!!

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

This is a 2-part question

First, I have several TextBoxes on my Form. Of those, one is named
‘Quantity’ and one is named ‘Rate’. In my commission TextBox, I am using
this as my Control Source:
=IIF([QUANTITY]<>"",[QUANTITY]*[RATE],"")

If Quantity is a Number datatype field (which it should be, if you're doing
calculations) it will never be eaual to the text string "". Given that a NULL
value multiplied by a number will give NULL, it sounds to me like you don't
need to do anything at all other than

=[QUANTITY] * [RATE]

in the second textbox's control source.

That’s giving me an error. It was working before; not sure what changed.

I'm not sure either, since it never should have worked.

Also, I am trying to pull a rate from a table using the Dlookup method, but
not having much success with this. Basically, when I enter data in a TextBox
named ‘Cust’ (for customer), I want to see the rate that a specific customer
pays. Here’s my code, which I found from an online video of how to do this.

What data are you entering? The customer's ID, the customer's name, part of
the customer's name...???

Private Sub CUST_AfterUpdate(Cancel As Integer)
RATE = DLookup("Cust", "Customers", "Rate=" & RATE)
End Sub

This is making no sense. Your DLookUp isn't using the CUST information at all;
it's looking up the value of the Cust field in the Customers table using the
RATE control on the form, and assiging that Cust value to the RATE control on
the form.

‘Cust’ is the Field on my Form, Customers is the Table, and in theta Table I
have Customer and rate and a few other things.

The Error message is ‘The expression After Update you entered as the event
property setting produced the following error: Procedure declaration does not
match description of event or procedure having the same name.’

I must be missing something pretty simple. Can someone please tell me what
it is.

I *THINK* what you want to do is to set the Rate to the value of Rate looked
up from the Cust table:

Private Sub Cust_AfterUpdate() ' AfterUpdate does not have a Cancel
Me!Rate = DLookUp("Rate", "Customers", "Customer = """ & Me!Cust & """")
End Sub

This assumes that you're entering a text customer name, and trusting your user
to never, ever under any circumstances make a typo, and that you will never
have two customers who happen to both be named Jones (bad assumptions all);
you'll do a lot better to use a Combo Box to select a numeric CustomerID.