ref columns in a macro to fill in other fields?

[Msimpson79]

Hi all...
I'm trying to set up a stock & order database. I have some work in OOBase but seen as we have Access and never use it, I thought I would give it a try.

So.... If the parts required for an order are available, the we wouldn't need to order any. If there isn't enough in stock ([Wendland Products].[Units Avalibe]) it should put the pack quantity ([Wendland Products].[Pack Qtt]) into the order field.

It would seem the value from the column isn't passed on in the macro.

I have an image of how it's set up but I cannot post it (need 10 posts)

My combo box is:

SELECT [Wendland Products].ProductID, [Wendland Products].[Product Code], [Wendland Products].[Product Description], [Wendland Products].[Units Allocated], [Wendland Products].[Pack Qtt], [Wendland Products].[Units Avalibe] FROM [Wendland Products] ORDER BY [Wendland Products].[Product Code];


in the on update event i have tried:

Private Sub Quantity_AfterUpdate()
If [ProductCode_comb].[Column](5) < Me.Quantity Then
Me.Ordered = [ProductCode_comb].[Column](4)
End If
End Sub

and

Private Sub Quantity_AfterUpdate()
Select Case [ProductCode_comb].[Column](5)
Case Is < Me.Quantity
Me.Ordered = [ProductCode_comb].[Column](4)
End Select
End Sub

Eventually, I would like it to update the units in stock, units available and units on order fields in the products table but if i can't even get this working.... :banghead:

Can this be done? If so, what's the problem?
Thanks
Mark

 

[burrina]

Hi and welcome. Are you able to get the correct stock items on your form?
Sorry, I don't use macros but usually the stock is re-queried after an item is added or subtracted. Are you using code for this?

Here is a screenshot of a qry to get InStock items.


HTH

 

[Msimpson79]

Hi,
Thanks for your reply and sorry for me being a bit slow... I was briefly put onto something else.
I haven't look into updating the tables yet as I can't seem to even be able to do a simple calculation on the columns.... I feel like a complete newbie! I've been googling and messing about etc and can't seem to find the answer.... Maybe it's too simple of a question!

If i do this:

Private Sub Quantity_AfterUpdate()
Me!Stock = Me!ProductCode_comb.Column(5)
'If Me!ProductCode_comb.Column(5) < Me!Quantity Then
Me!Ordered = Me!ProductCode_comb.Column(4)
'End If
End Sub

Me!Stock and Me!Ordered return the values but I only want it to fill out Ordered field with Column 4 (Pack Qtt) if it's less than Column 5 (stock).

If I remove the quotes to create the if statement, nothing happens in "Me!Ordered" It's like I'm using less than wrong because the result is always false.
Mark

 

[burrina]

Are you able to post a demo copy of your db for evaluation? Of course with NO confidential data.

 

[Msimpson79]

Hi again,
Thanks for your help! I have attached a zip file with the mdb inside.
I have also tried on access 2010... without success.
The 2 yellow text boxes are just for reference to see the values the columns should be returning.
Regards
Mark

 

[burrina]

I'm not sure I understand what your getting at but here is a stab at it.
I created an unbound textbox named Diff on your form.

Me.Diff = Me.ProductCode_comb.Column(3) - Me.ProductCode_comb.Column(4)
Me.Dirty = False 'Also needed on OnCurrentEvent



If (Me.Diff) < (Me.ProductCode_comb.Column(5)) Then
Me.Ordered = (Me.Diff.Value)
Else
Me.Ordered = ""
End If
Me.Dirty = False

HTH