• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • Last Modified:

Special Feature in MS Access Database?

I have very little expiernce in MS Access.  I currently use Access to keep up with my inventory.  I have managed to edit the program slightly to fit my needs, but i am unable to do one thing that seems so simple.

I need an indicator to flash up when a part number has the same amount on hand as the minimum on hand level.  I have managed to create a query that will give the information.  I also can get the program to send up a message box, but it does this on every part number.  I need to limit it to the part numbers where the two specific fields match.  I am doing this with macros since I have no idea how to use Visual Basic.

Any help is greatly appreciated.

Supermom
0
Supermom
Asked:
Supermom
  • 7
  • 5
  • 4
  • +2
1 Solution
 
funkeCommented:
Seems like a pretty easy thing but i would probably need to see the db.  if it's not to confidential can you send a copy of the db and where (what form etc) you want this to happen?  I can add the code and explain what I did if that helps. funke@ravenswoodsoftware.com
0
 
bcadCommented:
If you are using a form for inputting this information (are you?), then you can just put a macro in the AfterUpdate event of your PartsOnHand field that has a condition that checks to see if it equals your PartMinimum.

When creating the macro, turn on the Condition column (11th button in on the toolbar), and put in a condition of (for example) [PartsOnHand] = [PartMinimum].  Then put an Action of MsgBox, and fill in whatever message you want displayed in the Action Properties.
0
 
SupermomAuthor Commented:
Funke,

I will send you a version of the Database and see what you can do.  Please send me your e-mail and i will attach the specs.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
nexusnationCommented:
try a loop.

Do Until Me.PartsOnHand = Me.PartMinimum
MsgBox ("Your parts on hand equals minimum parts.")
Loop

Do Until Me.PartsOnHand < Me.PartMinimum
MsgBox ("Your parts on hand are less than minimum parts.")
Loop


need help applying the code?

nex
0
 
CRagsdellCommented:
Supermom,

Time for you to learn just a LITTLE about VB... and it is easy... and is a lot easier than trying to build a macro to do what you want.

The following code assumes you have a form with the current inventory [CurrentInventory], the level you want to compare against [CompareAmt], and a text box with your warning [Warning]. If your names are diofferent, just change the corresponding text in the sample below to match what you are using.

First, copy the form so you have a backup if something goes wrong...

Next, open the form and select [Warning] and set the Visible property of [Warning] to "No".

Next, select the form and click the expression builder next to "On Current". Slect Code Builder from the dialog box. When it opens, copy and paste this code between the Sub / End Sub statements shown. Be sure to leave the Sub and End Sub as they were when the Code Builder opens.

    If Me![CurrentInventory] = [CompareAmt] Then
        Me![Warning].Visible = True
    Else
        Me![Warning].Visible = False
    End If


Save the code, save the form and test.

CR
0
 
SupermomAuthor Commented:
BCAD,

I am using a form, but the info in the form is only updated after an item is allocated or picked from inventory.  Where else can i place the macro?
0
 
bcadCommented:
When you say "allocated or picked from inventory" what exactly do you mean (because any change to the field will trigger the AfterUpdate event).  Is this form being used for data entry purposes, or for viewing only??
0
 
SupermomAuthor Commented:
BACD,

Viewing only.
0
 
CRagsdellCommented:
Use On Current- if you go from one record to the next, you get the desired results.
0
 
bcadCommented:
When you say "allocated or picked from inventory" what exactly do you mean (because any change to the field will trigger the AfterUpdate event).  Is this form being used for data entry purposes, or for viewing only??
0
 
bcadCommented:
Sorry about the repeated post...I mistakenly refreshed and it reposted!!

As CRaqsdell said, OnCurrent would work as well if no actual changes are being made to the info.
0
 
nexusnationCommented:
supermom,

you must change a field or click a button when you drop the inventory, right?

p.s. did you eval my code, or at least look at it?

nex
0
 
SupermomAuthor Commented:
CRaqsdell

That works perfectly and it was not hard!! Can I make it flash so my boss does not miss it????
0
 
SupermomAuthor Commented:
CRaqsdell

My text box wants to be bound to other info so it comes up #name.  Am I using the right type of text box?
0
 
SupermomAuthor Commented:
nex,

I do not know how to eval the code?  I do not know what a loop is and do not know where to apply it.  I thank you very much for trying to help me.  
0
 
nexusnationCommented:
make the warning bigger and a bright red. unfortunately, no flash. sorry (at least without a lot of code)
0
 
CRagsdellCommented:
Not sure, but you could try an animated GIF instead of the text box...
0
 
CRagsdellCommented:
No, you need a Label, not a text box. Sorry... my bad...
0
 
SupermomAuthor Commented:
THANK YOU SO MUCH!!!
This has made my day!!
0
 
CRagsdellCommented:
I tried an animated GIF, and it just sits there in Access... no animation...
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now