?
Solved

Special Feature in MS Access Database?

Posted on 2003-03-06
20
Medium Priority
?
184 Views
Last Modified: 2006-11-17
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
Comment
Question by:Supermom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 4
  • +2
20 Comments
 
LVL 5

Expert Comment

by:funke
ID: 8082651
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
 
LVL 1

Expert Comment

by:bcad
ID: 8082690
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
 

Author Comment

by:Supermom
ID: 8082765
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 12

Expert Comment

by:nexusnation
ID: 8082772
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
 
LVL 4

Accepted Solution

by:
CRagsdell earned 120 total points
ID: 8082776
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
 

Author Comment

by:Supermom
ID: 8082806
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
 
LVL 1

Expert Comment

by:bcad
ID: 8082857
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
 

Author Comment

by:Supermom
ID: 8082883
BACD,

Viewing only.
0
 
LVL 4

Expert Comment

by:CRagsdell
ID: 8082909
Use On Current- if you go from one record to the next, you get the desired results.
0
 
LVL 1

Expert Comment

by:bcad
ID: 8082911
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
 
LVL 1

Expert Comment

by:bcad
ID: 8082918
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
 
LVL 12

Expert Comment

by:nexusnation
ID: 8082947
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
 

Author Comment

by:Supermom
ID: 8082968
CRaqsdell

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

Author Comment

by:Supermom
ID: 8083001
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
 

Author Comment

by:Supermom
ID: 8083025
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
 
LVL 12

Expert Comment

by:nexusnation
ID: 8083038
make the warning bigger and a bright red. unfortunately, no flash. sorry (at least without a lot of code)
0
 
LVL 4

Expert Comment

by:CRagsdell
ID: 8083043
Not sure, but you could try an animated GIF instead of the text box...
0
 
LVL 4

Expert Comment

by:CRagsdell
ID: 8083049
No, you need a Label, not a text box. Sorry... my bad...
0
 

Author Comment

by:Supermom
ID: 8083055
THANK YOU SO MUCH!!!
This has made my day!!
0
 
LVL 4

Expert Comment

by:CRagsdell
ID: 8083064
I tried an animated GIF, and it just sits there in Access... no animation...
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question