A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Need guidance for Inventory dbase, want to avoid calculated field.



 
 
Thread Tools Display Modes
  #21  
Old November 5th, 2009, 01:42 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Need guidance for Inventory dbase, want to avoid calculated field.

On Thu, 05 Nov 2009 00:25:03 GMT, "plisvb via AccessMonster.com" u55912@uwe
wrote:

Ok so,

First of all thanks for your help. I'm learning a lot even in my failures.

I believe that I've duplicated what you said to do but it still doesn't work.

I already have a transactions subform which is linked to an overall Orders
(Invoices) form. So I'm trying to implement the solution with what I've
already done.


Forms aren't "linked". Is this in a Subform control? or are you opening the
transactions form using an OpenForm macro or VBA call?

In the Subform I have a dropdown field called "Transaction Item" here's the
code for that field:


A "dropdown field" is properly called a "Combo box". It helps if you use the
Access jargon...

SELECT Inventory.[Item Number], Inventory.Item, Inventory.ID, Inventory.
Discontinued, Inventory.Cost FROM Inventory WHERE (((Inventory.Discontinued)
=False)) ORDER BY Inventory.[Item Number], Inventory.Item;


What's the distinction between the Item Number and the ID? Is each "Item" in
the Inventory table unique? Does it correspond with what I've been calling a
"product"?

I've also created a field called Current Price in my Inventory Transactions
Table and added that field to the subform.

My code for the event update added to the Transaction Item field is the
following:

Private Sub Transaction_Item_AfterUpdate()
Me.Current_Price = Me.Transaction_Item.Column(4)
End Sub


That should work, if there's a textbox (not a field) on the form named
Current_Price.

I'm not exactly sure if I've done something inherently wrong here. If you
could advise, that would be great.



Is it working as you expect, or not? The VBA code looks ok, assuming the
control and fieldnames are correct.
--

John W. Vinson [MVP]
  #22  
Old November 5th, 2009, 02:54 AM posted to microsoft.public.access.tablesdbdesign
plisvb via AccessMonster.com
external usenet poster
 
Posts: 13
Default Need guidance for Inventory dbase, want to avoid calculated field.

Hi again John,

1) "Forms aren't "linked". Is this in a Subform control? or are you opening
the
transactions form using an OpenForm macro or VBA call?"


I'm using a subform.

2) "A "dropdown field" is properly called a "Combo box". It helps if you use
the
Access jargon..."


my apologies

3) "What's the distinction between the Item Number and the ID? Is each "Item"
in
the Inventory table unique? Does it correspond with what I've been calling a
"product"?"


ID is the auto number field (key which nobody sees) Item Number is a number
created by my client although it is usually the UPC of the product preceded
by the supplier unique code (i.e. PA-00001)

Yes, each item is a product.

4) That should work, if there's a textbox (not a field) on the form named
Current_Price.


This was a problem, I was using a field, I'm now using a textbox but whenever
I try to bind the textbox to my inventory transactions Current Price field I
get a #NAME? error.

this is my code to bind it: [Inventory Transactions]![Current Price]

and this where I'm now stuck.

Thank you soooo much

John W. Vinson wrote:
Ok so,

[quoted text clipped - 5 lines]
(Invoices) form. So I'm trying to implement the solution with what I've
already done.


Forms aren't "linked". Is this in a Subform control? or are you opening the
transactions form using an OpenForm macro or VBA call?

In the Subform I have a dropdown field called "Transaction Item" here's the
code for that field:


A "dropdown field" is properly called a "Combo box". It helps if you use the
Access jargon...

SELECT Inventory.[Item Number], Inventory.Item, Inventory.ID, Inventory.
Discontinued, Inventory.Cost FROM Inventory WHERE (((Inventory.Discontinued)
=False)) ORDER BY Inventory.[Item Number], Inventory.Item;


What's the distinction between the Item Number and the ID? Is each "Item" in
the Inventory table unique? Does it correspond with what I've been calling a
"product"?

I've also created a field called Current Price in my Inventory Transactions
Table and added that field to the subform.

[quoted text clipped - 5 lines]
Me.Current_Price = Me.Transaction_Item.Column(4)
End Sub


That should work, if there's a textbox (not a field) on the form named
Current_Price.

I'm not exactly sure if I've done something inherently wrong here. If you
could advise, that would be great.


Is it working as you expect, or not? The VBA code looks ok, assuming the
control and fieldnames are correct.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200911/1

  #23  
Old November 5th, 2009, 05:04 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Need guidance for Inventory dbase, want to avoid calculated field.

On Thu, 05 Nov 2009 01:54:35 GMT, "plisvb via AccessMonster.com" u55912@uwe
wrote:

4) That should work, if there's a textbox (not a field) on the form named
Current_Price.


This was a problem, I was using a field, I'm now using a textbox but whenever
I try to bind the textbox to my inventory transactions Current Price field I
get a #NAME? error.


If you added the currentprice field to the table, it is probably not yet in
the Form's Recordsource. Open the Form in design view. View its Properties;
the first property on the Data tab will be the Recordsource. Click the ...
icon by it, and open it in query design view (if Access asks if you want to
create a query, accept the offer). Add the new field to the query and close
the query design window; accept Access' offer to save the SQL.

You should now be able to use currentprice as the control source of the
textbox.
--

John W. Vinson [MVP]
  #24  
Old November 5th, 2009, 02:11 PM posted to microsoft.public.access.tablesdbdesign
plisvb via AccessMonster.com
external usenet poster
 
Posts: 13
Default Need guidance for Inventory dbase, want to avoid calculated field.

Did that, I've added the field to the table and form's record source and
still a no go.

I no longer have the #NAME? error but the field still doesn't seem to update
at all.

John W. Vinson wrote:
4) That should work, if there's a textbox (not a field) on the form named
Current_Price.


This was a problem, I was using a field, I'm now using a textbox but whenever
I try to bind the textbox to my inventory transactions Current Price field I
get a #NAME? error.


If you added the currentprice field to the table, it is probably not yet in
the Form's Recordsource. Open the Form in design view. View its Properties;
the first property on the Data tab will be the Recordsource. Click the ...
icon by it, and open it in query design view (if Access asks if you want to
create a query, accept the offer). Add the new field to the query and close
the query design window; accept Access' offer to save the SQL.

You should now be able to use currentprice as the control source of the
textbox.


--
Message posted via http://www.accessmonster.com

  #25  
Old November 5th, 2009, 02:44 PM posted to microsoft.public.access.tablesdbdesign
plisvb via AccessMonster.com
external usenet poster
 
Posts: 13
Default Need guidance for Inventory dbase, want to avoid calculated field.

Ahhhh,

Figured it out, I knew if was a bonehead mistake. I didn't have macros
enabled by default since upgrading my operating system and reinstalling
office.

Thank you so much for all of you help John, huge help.

plisvb wrote:
Did that, I've added the field to the table and form's record source and
still a no go.

I no longer have the #NAME? error but the field still doesn't seem to update
at all.

4) That should work, if there's a textbox (not a field) on the form named
Current_Price.

[quoted text clipped - 12 lines]
You should now be able to use currentprice as the control source of the
textbox.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200911/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:30 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.