Quick OnUpdate Question/Flagging Question 1

[jksb]

I am trying to set up my database to save any changes *somewhere* so that I can go back and pull up the dates/changes in the future (it's for an inventory control for a warehouse...I need to know when items are received, distributed, etc.). Quick question...will the "AfterUpdate" in forms automatically store them changes somewhere, or do I have to make a file/table to send them to? Also, do I have to tag each field (I found in the archives someone saying so). Also, do I use both before/after update? I think I might want to...that way I will have records, where the table data will be changing constantly.

Also, I want to have Access flag a field when the inventory gets below a certian number. I want the field to flash (or beep..which may drive the warehouse manager nuts ; what is an easy formula (I could figure it out..after hours of thinking...or ask you all) to get it to do so. Each field will be different, so I will need to tag each record individually.

Thank you thank you !

Jackie
[sig][/sig]

 

[PaulF]

you should look at the Northwind Sample database that comes with ACCESS, it does what you want to do as far as an inventory database goes. It uses a Shipment, Orders and Products table (which has a Reorder Field).

As far as indicating the Need to Reorder... If you are using a form that is NOT a continuous form, then you can set the ForeColor for the Textbox Showing On Hand qty to be a different color, using the Form's Current event, or in conjuction with the Form's Timer, make that Field Flash.

PaulF
[sig][/sig]

 

[WP]

Jackie,

Do you really expect anyone to help you when you come out with "I could figure it out ...after hours of thinking ... or ask you all".

Try the following approach - "I have spent many hours on this and need help .... " you may have more success !

Also the more specific the question the better the answer you will get. To even attempt to answer the above would take some time.

Hence no responses.... not even from me. Well except this one of course!

WP [sig]<p>Bill Paton<br><a href=mailto:william.paton@ubsw.com>william.paton@ubsw.com</a><br><a href=

http://www.neptune400.co.uk>www.neptune400.co.uk</a><br>

Check out my website ![/sig]

 

[jksb]

Actually, Paul's response *was* helpful...since the MS Office 97 book makes no mention of a Northwind at all. Nor does Help bring up Northwind when you type in &quot;Inventory&quot;. And I have not seen it in any of the keyword searches I have done. I would never have found it. Now I have something to look up, which is a big help. If I had asked it two weeks ago when I started this project, I probably would have saved hours. I have spent hours trying to do a lot of this and found the leads I have gotten a lot of help; but I am also just starting and have found that asking this list for leads valuable. I was also under the impression it was also for newbies to Access..or am I wrong? Is it just for programmers and experienced people doing the really neat things? Is there another list where my basic questions can be asked/answered? If so, please let me know.

Thank you

Jackie [sig][/sig]

 

[WP]

Jackie,

The site is for everyone - from the beginner to the Access guru.

I'm glad Paul's advice was helpful and I'm sorry for being so hostile.

I do try to help where possible but when the question is at functional level it can be very difficult without spending a great deal of time on it.

Ask lots of questions but keep them focused and direct and you will get much more help. Even bounce ideas - &quot;I'm trying to do this ... am I on the right track ?&quot;


WP

[sig]<p>Bill Paton<br><a href=mailto:william.paton@ubsw.com>william.paton@ubsw.com</a><br><a href=

http://www.neptune400.co.uk/>http://www.neptune400.co.uk/</a><br>

Check out my website ![/sig]

 

[jendendav]

I have also set up an inventory control program with access. The changes are saved to a table I called inventory transactions. I did not have the reorder field on my form because I did not want the person entering changes to the inventory to be able to change that field. Now I wonder if I should add the field and somehow lock it from being accessed by the data entry clerk while still using the color change or flashing warning.

However, I am not a programmer and did not fully understand Paul's answer. Is the field flashing only when the required low volume is reached?

Thank you.Jen [sig][/sig]

 

[PaulF]

This code checks a textbox with the on hand qty (txtOH) and compares it to the ReOrder qty in txtReorder. If the On hand qty is less than the ReOrder qty, it sets the Form's timer to 3/4 of a second, and changes the color of the ForeColor in txtReorder to red, otherwise it ensures that the ForeColor is black. When the timer event occurs it checks to see if the ForeColor is red, if it is then it changes it to the same color as the BackColor for txtReOrder, otherwise it changes it to red. This will occur every 3/4 of a second, thereby making the label appear to flash.

Private Sub Form_Current()
If txtOH < txtReOrder Then
Me.TimerInterval = 750
txtReOrder.ForeColor = 255
Else
Me.TimerInterval = 0
txtReOrder.ForeColor = 0
End If
End Sub

Private Sub Form_Timer()
Dim lngColor As Long
lngColor = txtReOrder.BackColor
If txtReOrder.ForeColor <> 255 Then
txtReOrder.ForeColor = 255
Else
txtReOrder.ForeColor = lngColor
End If
End Sub


PaulF [sig][/sig]

 

[jendendav]

Just to get really specific here. My field names are SumOfUnits and Reorder Level. These were made before I realized that it is better to make names without spaces. My fields are numeric. I assumed I would have to put parenthesis or brackets around the field name with the space. Do I need them around the other field name? Do the brackets also enclose the txt which in my case I assume is num? I'm having a problem getting this to compile so if you can help it is appreciated. I'm assuming the modifications would run something like:

If numSumOfUnits<num(Reorder Level) Then

But the compiler doesn't like this. Putting parenthesis around SumOfUnits didn't help and neither did changing the parenthesis to brackets. So, what simple thing have I missed please.

Thanks [sig][/sig]

 

[PaulF]

If the name of the TEXTBOX that displays the field and the field are the same it can cause problems at times. To make it really easy (unless you have code already attached to the textbox on the form that these fields are displayed), just rename the TEXTBOX that displays the field SumOfUnits to txtOH, and rename the TEXTBOX that displays Reorder Level to txtReOrder, then the code should work fine. You do not need to worry about the field names, because the code is looking at the value in the textboxes, and isn't concerned what the field names. In order to prevent the user from changing the reorder number, set the Locked Property (under the Data Tab) for txtReOrder to Yes. However if you want to keep the name of the TEXTBOX the same as the Field name then

change txtOH to [SumOfUnits]
change txtReOrder to [Reorder Level]
in all of the code

PaulF [sig][/sig]

 

[jendendav]

Thanks. I renamed the textboxes as you suggested and modified the code to suit and it works! I am also working on thread 10965 and will be posting a new message to it in a couple of hours. I wonder if you have any suggestions on it.

Jen [sig][/sig]