Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    vCallNSPF is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Dec 2009
    Posts
    49

    Battery Test results + replacing over time periods

    The business that im creating a database for needs a automated way to control battery testing for its equipment.



    basically there needs to be a query displaying the last 10 years of test results.

    the test results arent an issue, its timing the replacement of batteries.

    my client wants an alert to appear in the database when a battery is due for replacement, some batteries have 3 year lives, some have 5 year lives.

    another predicament is, say if theres a storm, some of the batteries tend to die. if a battery that has a 3 year life span has been replaced, but gets fried by a storm 1 year later need the timer to reset from when the latest battery went in.

    thanks for your help

  2. #2
    MAF4Fam6's Avatar
    MAF4Fam6 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Location
    Fruit Heights, Utah USA
    Posts
    140

    Post Customized Pop-up Alert Messages

    Microsoft Access has the ability to throw customized pop-up alert messages via VBA or a Macro. You could create a simple select query containing all records with expired dtmLifeSpanEnd (example field name) dates. You can then use a DCount function to throw the pop-up alert message. Click Ok on the alert message to open a form in datasheet view that lists all battery replacement requirements, or Cancel to view later.
    Hope this helps.

    -RC
    Last edited by MAF4Fam6; 01-06-2010 at 11:13 AM.

  3. #3
    vCallNSPF is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Dec 2009
    Posts
    49
    Quote Originally Posted by MAF4Fam6 View Post
    Microsoft Access has the ability to throw customized pop-up alert messages via VBA or a Macro. You could create a simple select query containing all records with expired dtmLifeSpanEnd (example field name) dates. You can then use a DCount function to throw the pop-up alert message. Click Ok on the alert message to open a form in datasheet view that lists all battery replacement requirements, or Cancel to view later.
    Hope this helps.

    -RC
    thanks for the help, before you posted here i had absolutely no starting point.

    im unfamiliar with the DCount function,

    say for a battery that has a 3 month life span, how would i use the DCount function to see if it has expired?

  4. #4
    MAF4Fam6's Avatar
    MAF4Fam6 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Location
    Fruit Heights, Utah USA
    Posts
    140

    Post Using DCount Function to Throw Alert Message(s)

    The DCount function is not necessarily used to determine when a record meets a certain criteria(s). This task is given to queries.
    Instead, the DCount function is used to count the number of records, in a specified table or query after execution.
    Therefore, you would create a query to select all records containing dtmLifeSpanEnd (example field name) dates, which are less than today's date (meaning they would have expired life-span dates). The DCount function would then be used to count the number of records selected after running the query.
    If the DCount function counts 1 or more records, then throw the customized pop-up alert message. If the DCount function counts 0 records, then no action is necessary.
    It will be totally up to you when you want to perform this task in your database (i.e. opening the database, before/after a form opens, etc.).

    -RC
    Last edited by MAF4Fam6; 01-07-2010 at 09:39 AM.

  5. #5
    vCallNSPF is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Dec 2009
    Posts
    49
    That makes sense to me now.

    How would I set up the table and query to get the query to recognise an expired date though? Would you be able to explain the process?

    I have the table with four fields,
    EquipmentName
    BatteryType
    BatteryLife
    DateLastReplaced

    I know the query would be based on:
    IF BatteryLife + DateLastReplaced < todays date THEN
    display in query
    ELSE
    dont display in query
    END IF

    I just dont know how to get it to do this. Like what field properties do I need in the table and how to get the query to act in the correct fashion.

    Thanks for your help

  6. #6
    MAF4Fam6's Avatar
    MAF4Fam6 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Location
    Fruit Heights, Utah USA
    Posts
    140

    Post

    There are many possible ways that you can construct your database. And the only right way, is of course...your way.
    Therefore, purchasing a Microsoft Access book in the version (2007) you are currently using, is highly recommend if you have not already done so.
    With that said...have you read up on Access database normalization yet? You can find this in an Access book or on the web through a Google search.
    To get started (as a recommendation only), you will need to first begin building your tables/structure and ensure true database normalization.
    Two tables would more than likely be warranted in this case:
    Table 1: BatteryTypes
    This table should store ONLY the types of batteries you are currently testing in the field.
    One of the field names in this table should be something like BatteryLifeYrs (Number type field) in order to calculate the life-span of a battery that is currently being tested.
    Table 2: BatteriesBeingTested
    This table should store all the batteries that are currently being tested in the field.
    Two of the field names in this table should be something like BatteryIn (date type field) and BatteryExpiration (date type field).
    The BatteryIn field is obviously the date the battery is installed in a piece of equipment.
    Once your tables/structure are built, you can then create a form in order to start adding records to the BatteriesBeingTested table. During data entry into your form, you can use a combo box to select the type of battery being tested from the BatteryTypes table and dynamically update the BatteryExpiration date based on the number of years in the BatteryLifeYrs field from your first table.
    Once you have your form working and have multiple records in your BatteriesBeingTested table, you can then create a select query that selects all records with BatteryExpiration dates being less than today's date. In your query's design view, locate the BatteryExpiration field and type the following in the criteria section: <Date()
    Save and run query.
    Once you get to this point, post back for assistance, if needed, on how to throw a customized pop-up alert message.
    Thanks.

    -RC

  7. #7
    vCallNSPF is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Dec 2009
    Posts
    49
    Thanks heaps for all of that information,

    The only part I am still unsure of is how to dynamically update the 'date expired' field.

    where do i actually put the code and what is the syntax?

    coding becomes confusing for me when the value added is coming from another table.

    if you could give me a complete code with your example field names that would be MUCH appreciated

    thankyou in advance

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    It looks like RC is off line. The DateExpired is really a calculated value and belongs in a query rather than a field in a table.

  9. #9
    MAF4Fam6's Avatar
    MAF4Fam6 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Location
    Fruit Heights, Utah USA
    Posts
    140

    Post

    Thanks RG for bringing up that point on using a calculated field. Just wasn't sure how the form/records source structure on the user end was looking on this particular case.

    vCallNSPF,
    Can you provide some additional info?
    Will you be entering the BatteryType on your form via a Combo Box?
    If you will be using a Combo Box, what value will you be storing? The BatteryType or BatteryLifeYrs
    If you will be storing the BatteryLifeYrs instead of the BatteryType, then RuralGuy is correct: the BatteryExpiration date would then be a calculated value in a query rather than a field in a table.
    However, if you will be storing the BatteryType instead of the BatteryTypeYrs via a Combo Box, then a calculated value in a query may not work since the query would require the BatteryTypeYrs to calculate the BatteryExpiration date.
    The choice if pretty much up to you.
    I'm attaching a very generic example in Access 2007 if you choose to store the BatteryType instead of the BatteryTypeYrs on the form.

    Attachment 515

    The database uses a Macro since I am not the expert in VBA.
    The tblBatteriesBeingTested table contains no records. It is ready for data entry via the form.
    The other table, tblBatteryTypes contains 4 records with BatteryTypes and BatteryLifeYrs
    Try opening the form and add a few records by selecting a BatteryType and then entering a BatteryIn date.
    Not sure if this is what you are looking for.
    Please let us know if you want to store the BatteryLifeYrs in the form instead so we can assist you in using a calculated value in a query. That is normally the way to go...and, plus...it would not require a Macro.
    Thanks.

    -RC

  10. #10
    vCallNSPF is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Dec 2009
    Posts
    49
    the way youve done it is basically perfect for calculating the expiry date. but ive got the smallest idea on how you did it. So i cant change the names to my own convention in fear of stuffing up the code, im trying to change it to months instead of years and the client has specific naming conventions that i am required to use.

    after speaking to my client they would prefer not to have an alert appear for expired batteries, but instead a command button from the menu form to open the query, i can do the command button opening the query bit i just need the query itself.

    or perhaps try it with Batterylifeyears (months preferably) stored instead of Batterylifetype

    if you could write out an instructions list, that would be much appreciated

    sorry this has dragged on but im kind of stuck here

    thanks
    Last edited by vCallNSPF; 01-11-2010 at 06:38 PM.

  11. #11
    MAF4Fam6's Avatar
    MAF4Fam6 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Location
    Fruit Heights, Utah USA
    Posts
    140

    Post

    It's alright...it's no problem at all.
    Sounds like you might need to use a calculated field RuralGuy and I spoke about in the earlier posts then.
    We will need to see how your tables and field names are structured first in order for us to see how to go about building the query. Is there any way that you can post a stripped down version of your database?
    Thanks.

    -RC

  12. #12
    vCallNSPF is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Dec 2009
    Posts
    49
    ive only have the tables that I originally had before I posted here, but it shows the field names im after I guess.

    if you havnt already downloaded it, the client wants it changed back to years, sorry for the inconvenience,

    the database has been changed and reuploaded to include years instead of months

    also, instead of a specific date, just the year of replacement needs to be there. so that when it is that year, it will need to be replaced

  13. #13
    MAF4Fam6's Avatar
    MAF4Fam6 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Location
    Fruit Heights, Utah USA
    Posts
    140

    Post

    I had to add a JobID field name in the BatteryTestedReplace table as a Primary Key.
    Thanks.

    -RC
    Last edited by MAF4Fam6; 01-12-2010 at 08:04 PM. Reason: The BatteryExpire field name will be staying after all. Thanks. -RC

  14. #14
    MAF4Fam6's Avatar
    MAF4Fam6 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Location
    Fruit Heights, Utah USA
    Posts
    140

    Post

    New version of your database is attached.
    The query you were looking for is named: qryExpiredBatteries
    Start by modifying your records in BatteryTypes...then try pluggin in some records in the form. Use some dummy BatteryIn dates to make some dates equal the current year or earlier (expire) so you can run the command button (Run Expired Batteries Query) and see your expired batteries display in the query.
    The mcrUpdateBatteryExpire macro runs during the AfterUpdate event in the BatteryIn control on form.
    The mcrShowExpiredBatteries macro runs during the OnClick event in the command button on form.
    Thanks.

    -RC

  15. #15
    MAF4Fam6's Avatar
    MAF4Fam6 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Location
    Fruit Heights, Utah USA
    Posts
    140

    Post

    Attaching Access database file.

    -RC

    Attachment 522

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Keying in Test Answers from Hard Copy
    By CityOfKalamazoo in forum Forms
    Replies: 4
    Last Post: 12-07-2009, 12:44 PM
  2. download test file from WS_FTPin Access VBA
    By dollygg in forum Access
    Replies: 0
    Last Post: 07-28-2009, 08:08 PM
  3. Replacing Null with 0
    By gilagain1 in forum Queries
    Replies: 5
    Last Post: 04-23-2009, 01:47 PM
  4. Replacing text in my database
    By sbrobin in forum Access
    Replies: 0
    Last Post: 02-08-2009, 02:17 PM
  5. Replies: 0
    Last Post: 12-01-2008, 03:01 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums