How do I setup an AfterUpdate calculation from another table?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The objective is to load the field “ExtPrice1†after Qty1 is entered (in one
table) using the UnitPrice from the Parts table. I thought a very simple
process, but...


If AfterUpdate(Qty1) is, “=[ExtPrice1]=Forms!Parts!UnitPrice*[Qty1]â€, then I
get this error: Test can't find the form "Parts" referred to in a macro
expression or Visual Basic code.

If AfterUpdate(Qty1) is “=[ExtPrice1]=Parts!UnitPrice*[Qty1]â€, then I get
this error: The object doesn't contain the Automation object "Parts".

Now, if I open the Form “Parts†manually, I do not get an error, but the
calculation (Qty1*Parts!UnitPrice) does not work.

So now I change my design and simply decide to plug this calculation into
the Control Source for ExtPrice1. I get this when I use,
“=[Qty1]*Forms!Parts!UnitPriceâ€: #Error in the ExtPrice1 field.

If I translate to the Parts table for UnitPrice
(=[Qty1]*[Parts]![UnitPrice]), I get this: #Name? in the ExtPrice1 field.

I'M GOING CRAZY. I have never had so much trouble like this before. I have
spent hours and hours trying to work my way out of this using every trick and
troubleshooting method I know. I'm not a VBA expert, so maybe this is the
problem.

Can anyone please help me out? I'm already a week late on this...MDM
 
MDM,

I'm sorry, I have read through your question several times, and I just
can't see what you are trying to do. What does AfterUpdate(Qty1) mean?
It is clear that as part of the process you were trying to refer to
Forms!Parts!UnitPrice when the Parts form was not open, so that was part
of the problem. I mean, this means "the value of the UnitPrice control
in the current record on the Parts form", so obviously there is no way
that this can be evaluated if the Parts form is not open at the time.

I also am not familiar with the syntax like
"=[ExtPrice1]=Forms!Parts!UnitPrice*[Qty1]" with two =s in it.

So am I correct in surmising that the form you are using is based on
"one table", and this table contains the Qty1 field? So you enter a
value in the Qty1 control (hint: forms don't have fields), and then...?
You want the ExtPrice1 textbox to display something, right? How do
you work that out? Maybe an example would help.
 
Steve, Thanks so much for at least looking at my problem.

I have worked with relation databases dating back over 20 years, so some of
my terminology is a bit off from today. Please bear with me.

The main issue here, is that I simply need to take Qty (in existing table)
and multiply it by a Unit Price found in another table. We used to call this
"translating" where we would send the table name, the record ID, and the
field name we want to use. In this case I need to pull out the Unit Price of
a part (from the Parts table) and simply multiply this to the Quantity field
in the existing table.

Steve Schapel said:
MDM,

I'm sorry, I have read through your question several times, and I just
can't see what you are trying to do. What does AfterUpdate(Qty1) mean?
It is clear that as part of the process you were trying to refer to
Forms!Parts!UnitPrice when the Parts form was not open, so that was part
of the problem. I mean, this means "the value of the UnitPrice control
in the current record on the Parts form", so obviously there is no way
that this can be evaluated if the Parts form is not open at the time.

I also am not familiar with the syntax like
"=[ExtPrice1]=Forms!Parts!UnitPrice*[Qty1]" with two =s in it.

So am I correct in surmising that the form you are using is based on
"one table", and this table contains the Qty1 field? So you enter a
value in the Qty1 control (hint: forms don't have fields), and then...?
You want the ExtPrice1 textbox to display something, right? How do
you work that out? Maybe an example would help.

--
Steve Schapel, Microsoft Access MVP

The objective is to load the field “ExtPrice1†after Qty1 is entered (in one
table) using the UnitPrice from the Parts table. I thought a very simple
process, but...


If AfterUpdate(Qty1) is, “=[ExtPrice1]=Forms!Parts!UnitPrice*[Qty1]â€, then I
get this error: Test can't find the form "Parts" referred to in a macro
expression or Visual Basic code.

If AfterUpdate(Qty1) is “=[ExtPrice1]=Parts!UnitPrice*[Qty1]â€, then I get
this error: The object doesn't contain the Automation object "Parts".

Now, if I open the Form “Parts†manually, I do not get an error, but the
calculation (Qty1*Parts!UnitPrice) does not work.

So now I change my design and simply decide to plug this calculation into
the Control Source for ExtPrice1. I get this when I use,
“=[Qty1]*Forms!Parts!UnitPriceâ€: #Error in the ExtPrice1 field.

If I translate to the Parts table for UnitPrice
(=[Qty1]*[Parts]![UnitPrice]), I get this: #Name? in the ExtPrice1 field.

I'M GOING CRAZY. I have never had so much trouble like this before. I have
spent hours and hours trying to work my way out of this using every trick and
troubleshooting method I know. I'm not a VBA expert, so maybe this is the
problem.

Can anyone please help me out? I'm already a week late on this...MDM
Click to expand...
Click to expand...
 
Steve,

Also, if the issue is related to openign the Parts Form or Table, then how
can I achieve this in a VBA macro?

MDM

Steve Schapel said:
MDM,

I'm sorry, I have read through your question several times, and I just
can't see what you are trying to do. What does AfterUpdate(Qty1) mean?
It is clear that as part of the process you were trying to refer to
Forms!Parts!UnitPrice when the Parts form was not open, so that was part
of the problem. I mean, this means "the value of the UnitPrice control
in the current record on the Parts form", so obviously there is no way
that this can be evaluated if the Parts form is not open at the time.

I also am not familiar with the syntax like
"=[ExtPrice1]=Forms!Parts!UnitPrice*[Qty1]" with two =s in it.

So am I correct in surmising that the form you are using is based on
"one table", and this table contains the Qty1 field? So you enter a
value in the Qty1 control (hint: forms don't have fields), and then...?
You want the ExtPrice1 textbox to display something, right? How do
you work that out? Maybe an example would help.

--
Steve Schapel, Microsoft Access MVP

The objective is to load the field “ExtPrice1†after Qty1 is entered (in one
table) using the UnitPrice from the Parts table. I thought a very simple
process, but...


If AfterUpdate(Qty1) is, “=[ExtPrice1]=Forms!Parts!UnitPrice*[Qty1]â€, then I
get this error: Test can't find the form "Parts" referred to in a macro
expression or Visual Basic code.

If AfterUpdate(Qty1) is “=[ExtPrice1]=Parts!UnitPrice*[Qty1]â€, then I get
this error: The object doesn't contain the Automation object "Parts".

Now, if I open the Form “Parts†manually, I do not get an error, but the
calculation (Qty1*Parts!UnitPrice) does not work.

So now I change my design and simply decide to plug this calculation into
the Control Source for ExtPrice1. I get this when I use,
“=[Qty1]*Forms!Parts!UnitPriceâ€: #Error in the ExtPrice1 field.

If I translate to the Parts table for UnitPrice
(=[Qty1]*[Parts]![UnitPrice]), I get this: #Name? in the ExtPrice1 field.

I'M GOING CRAZY. I have never had so much trouble like this before. I have
spent hours and hours trying to work my way out of this using every trick and
troubleshooting method I know. I'm not a VBA expert, so maybe this is the
problem.

Can anyone please help me out? I'm already a week late on this...MDM
Click to expand...
Click to expand...
 
MDM,

In Access, there are Macros, and there are VBA Procedures. They are
very different. There are no VBA Macros.

Anyway, I don't think you need to open a form, or a table. Here's my
question, then... In the "another table", is there only one record? If
there are more than one record, and you need to use the value of the
Unit Price field from that table, how do you know which record to refer
to? Maybe if you gave a specific example, and told us some more details
about your tables and your data, it would help.
 
Steve

This is EXACTLY my problem. I too cannot figure this out. The Part No. field
(a combo box) lists the available parts (from the Parts table) and bounds to
column 1 which is the PartID. Okay, this works fine. Now I want to pull the
UnitPrice from the Part No. selected, but I've not figured out a way to
submit the Part No. selected in the prior Part No. field. Clearly there must
be a way to do this, but I am so burnt out on this I can't see the forest for
the trees. I agree, I merely need to (I thought) translate from the Order
Table, to the Parts table, and lookup the Part selected in the Part No.
field, grab the UnitPrice and bring it back.

How on earth do you do this in Access?
 
MDM,

You could make the Part No combobox multicolumn, so that it contains the
PartID, maybe the PartName, and then the UnitPrice. You can use the
combobox's Properties (Column Count, Column Widths) to determine how
this works. That means the Unit Price can be retrieved via =[Part
No].[Column](2) (the column numbers are 0 based). So, in the ExtPrice1
textbox (*don't* have a [ExtPrice1] field in the Orders table please!),
you can just set the Control Source to...
=[Qty1]*[Part No].[Column](2)

There are other possible approaches to this as well. You may find this
article helpful...
http://accesstips.datamanagementsolutions.biz/lookup.htm
 
Steve

I'm not sure who you are, but I'm glad you chose to reseond.

Yes, I am bringing over the Unitprice when I do the lookup. I've been using
the Expressions feature and even tried a bit of VBA to try to get it to work.
I'll try this, it makes sense.

If I do not respond, then it worked.

Thank you very uch for being there on a Saturday, wherever you are...MDM



Steve Schapel said:
MDM,

You could make the Part No combobox multicolumn, so that it contains the
PartID, maybe the PartName, and then the UnitPrice. You can use the
combobox's Properties (Column Count, Column Widths) to determine how
this works. That means the Unit Price can be retrieved via =[Part
No].[Column](2) (the column numbers are 0 based). So, in the ExtPrice1
textbox (*don't* have a [ExtPrice1] field in the Orders table please!),
you can just set the Control Source to...
=[Qty1]*[Part No].[Column](2)

There are other possible approaches to this as well. You may find this
article helpful...
http://accesstips.datamanagementsolutions.biz/lookup.htm

--
Steve Schapel, Microsoft Access MVP
Steve

This is EXACTLY my problem. I too cannot figure this out. The Part No. field
(a combo box) lists the available parts (from the Parts table) and bounds to
column 1 which is the PartID. Okay, this works fine. Now I want to pull the
UnitPrice from the Part No. selected, but I've not figured out a way to
submit the Part No. selected in the prior Part No. field. Clearly there must
be a way to do this, but I am so burnt out on this I can't see the forest for
the trees. I agree, I merely need to (I thought) translate from the Order
Table, to the Parts table, and lookup the Part selected in the Part No.
field, grab the UnitPrice and bring it back.

How on earth do you do this in Access?
Click to expand...
Click to expand...
 
Steve

As I said earlier, I don't know who you are, but you're now my new, bestest
friend. I never thought of spinning off the combo box I used to grab the part
no. info from, but when I set it up it worked PERFECTLY!

As far as I'm concerned, you are a heroe.

Thanks many times over and I hope you're there on the next dilema I have.

MDM :)

Steve Schapel said:
MDM,

You could make the Part No combobox multicolumn, so that it contains the
PartID, maybe the PartName, and then the UnitPrice. You can use the
combobox's Properties (Column Count, Column Widths) to determine how
this works. That means the Unit Price can be retrieved via =[Part
No].[Column](2) (the column numbers are 0 based). So, in the ExtPrice1
textbox (*don't* have a [ExtPrice1] field in the Orders table please!),
you can just set the Control Source to...
=[Qty1]*[Part No].[Column](2)

There are other possible approaches to this as well. You may find this
article helpful...
http://accesstips.datamanagementsolutions.biz/lookup.htm

--
Steve Schapel, Microsoft Access MVP
Steve

This is EXACTLY my problem. I too cannot figure this out. The Part No. field
(a combo box) lists the available parts (from the Parts table) and bounds to
column 1 which is the PartID. Okay, this works fine. Now I want to pull the
UnitPrice from the Part No. selected, but I've not figured out a way to
submit the Part No. selected in the prior Part No. field. Clearly there must
be a way to do this, but I am so burnt out on this I can't see the forest for
the trees. I agree, I merely need to (I thought) translate from the Order
Table, to the Parts table, and lookup the Part selected in the Part No.
field, grab the UnitPrice and bring it back.

How on earth do you do this in Access?
Click to expand...
Click to expand...
 
Steve

How do I rate this post? I've looked over the page and cannot find how to do
this.

MDM

Steve Schapel said:
MDM,

You could make the Part No combobox multicolumn, so that it contains the
PartID, maybe the PartName, and then the UnitPrice. You can use the
combobox's Properties (Column Count, Column Widths) to determine how
this works. That means the Unit Price can be retrieved via =[Part
No].[Column](2) (the column numbers are 0 based). So, in the ExtPrice1
textbox (*don't* have a [ExtPrice1] field in the Orders table please!),
you can just set the Control Source to...
=[Qty1]*[Part No].[Column](2)

There are other possible approaches to this as well. You may find this
article helpful...
http://accesstips.datamanagementsolutions.biz/lookup.htm

--
Steve Schapel, Microsoft Access MVP
Steve

This is EXACTLY my problem. I too cannot figure this out. The Part No. field
(a combo box) lists the available parts (from the Parts table) and bounds to
column 1 which is the PartID. Okay, this works fine. Now I want to pull the
UnitPrice from the Part No. selected, but I've not figured out a way to
submit the Part No. selected in the prior Part No. field. Clearly there must
be a way to do this, but I am so burnt out on this I can't see the forest for
the trees. I agree, I merely need to (I thought) translate from the Order
Table, to the Parts table, and lookup the Part selected in the Part No.
field, grab the UnitPrice and bring it back.

How on earth do you do this in Access?
Click to expand...
Click to expand...
 
MDM,

Very happy to help, and it is great to know that you have got it working
nicely.

Yes, please post back whenever you need further help. If I don't spot
it, there are a heap of very generous and knowledgeable folks around here.

Best wishes with the rest of your project.
 
I'm not all that familiar with the web interface to these newsgroups, as
I usually use a newsreader client. I think there is a place there
somewhere where you can say that it was helpful :-).
 
Back
Top