problem with subform

[Ankit.Patel.UK]

hey every1,

I have a form which is used for a receipt. The receipt contains the
following fields :

item id(drop down menu comes up with product Names)
quantity(amount of items wanted)
unit price (PROBLEM AREA, i have a field in my receipt which says 'unit
price', the problem i have is, i want the 'unit price' from the Items
Table, to appear and be stored in the 'unit price' field of the receipt
table depending on which item is chosen from the drop down menu in
'item id'. How would i go about doing this?

I also wanted to find out how would i make the quantity field change to
'1' when the user chooses an item from the drop down box because this
would help me entering the quantity value myself,everytime the
customer only buys 1 of any item.

This problem has been casuing me endless problems, your help would be
very generous.

 

[strive4peace]

Hi Ankit,

first, the Qty field...

for the control, make the DefaultValue (Data tab on Forms) --> 1

now for the price...

make the ItemID dropdown get the price for one of its columns

ie:

Name --> ItemID
ControlSource --> ItemID
ColumnCount --> 3
Listwidths --> 0;2;1 (or 0;2;0 if you don't want price to show)
Width --> 3 (0r 2) -- add the Listwidths
RowSource -->
SELECT ItemID, ItemName, Price
FROM Tablename
ORDER BY ItemName

on the AfterUpdate event of ItemID -->

'~~~~~~~~~~~~~~~
if isnull(me.activecontrol) then exit sub

me.price_controlname = me.ItemID.column(2)
'~~~~~~~~~~~~~~~

since column indexing starts with 0, column 2 is really column 3...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[Ankit.Patel.UK]

Hi Crystal

i have changed my drop down column to contain the price field now, but
i was wondering the code you gave me for the 'Item ID' after update:

if isnull(me.activecontrol) then exit sub
me.RetailPrice = me.ItemID.column(2)

Am i supposed to just copy and paste it into the field? or am i
supposed to copy the code into:
1.Expression Builder
2.Macro Builder
3. Code Builder

Because I pasted it straight into the field and its not working. I can
provide you a screenshot of the ItemID properties if it makes it easier
for you to help me?

BTW I had to change the sql code into the following:

SELECT [ItemID], [ItemName], [RetailPrice] FROM Stock_Items_Tbl ORDER
BY [ItemName]; (somehow without the square brackets it didn't work)

Thanks for your reply tho! If you could help me even more to solve the
problem I would be delighted!!!!Hope your day went well

Kit

 

[Ankit.Patel.UK]

Hi Crystal

Don't worry i played around with it and got it working!!!!!!!!!!!
Thanks a bunch!!!!!!!!!! Your info was great and made my day!!!!!!!!!!
Now i can move on to something else of my database.

Have a great week! your help was great and appreciated



Kind Regards KIT

 

[strive4peace]

you're welcome, Kit happy to help

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*