Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2013

    Unanswered: MS Access 03 : Should I use Forms or Queries?

    Hi All

    As mentioned in my previous post I'm designing a database for use with our weights and measures equipment. We have multiple types of equipment (e.g. weights, balances, volumetric equipment) all of which have different pieces of information we need to record against them. At the moment I am working with weights only.

    I have designed 5 tables, 2 of these tables I hope to use with all equipment types, 3 of these will be used with weights only. These tables are:
    • tblOfficers (this contains records of all the officers who are permitted to carry out work on the equipment)

    • tblSetLog (our equipment is grouped into sets this table will hold all the generic information)

    • tblEquipmentLogWeights (this contains records of all weights)

    • tblPLEsWeights (this contains the prescribed limits of error for the weights)

    • tblTestResultsWeights (this contains the results of any tests carried out on the weights)

    I would like to set up a form of some sort which will show each individual set, a list of the weights within the set and information about each weight (individually) - I've drafted out how I would like this to look (pending minor changes to the arrangement of the fields) in a word document which I've attached. I am hoping that I can build a form which allows you to view the equipment information and make alterations or add equipment all in one place (if possible).

    My question is, whats the best way of going about this. Should I set up individual forms and linke 2 into 1 as subforms. Or should I be using queries as the basis of my forms?

    I realise there will be some duplication between the set log and the equipment log, but this is necessary (individual items get lost, replaced or quarantined and so their generic details (such as date of aquisition) will be different from the rest of the set).

    I would be grateful for any suggestions or advice you can give me.

    Many thanks

    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    if you use forms, then defacto you are using queries, but you may not be aware that you are using queries. the native way Access talks to databases is SQL.

    how you design the user interface is upto you
    the form/subform motif works well on parent/child realtionships
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2013
    Oh right I see, thanks for that. Guess I'm more clueless about forms and queries than I thought. Better hit the books again see if I can learn a bit more. Thanks for your comment.

  4. #4
    Join Date
    Feb 2004
    New Zealand
    Provided Answers: 11

    Think of forms as the skin (where user inport the data or u want to show user something but not print that what reports are for

    Tables are the bones of your system.

    Think of querys as differance views to join the bones in a differance way
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008

  5. #5
    Join Date
    Jun 2013
    Thanks myle I think that helps. Must admit I'm struggling to get my head round this at the moment, particularly in relation to what I'm attempting to do.

    So far I've created a form for my Set Log (the idea being it will display all information for each set - one set at a time) based on the table tblSetLog. The form works really well apart from one thing. I want to set it up so the system calculates when the next calibration is due. Ideally I want the system to update iteself with the last calibration date once new test data is entered and then calculate the next calibration date, based on the previous date plus the calibration period (given in months). I will then build a report which will identify all equipment due for calibration in a set time frame (preferably picking up anything overdue as well).

    I've worked out how to do a calculation in the form (for the next calibration date) which works brilliantly (its a DataSerial calculation) but it doesn't update the underlying table. This isn't a problem if I can build a report based on the form but from what I've read so far it doesn't look like it will work. So instead I created a query to do the calculation, but when I've altered my form to include the query, nothing is updated on the underlying table. Do I need to create an update query as the basis of my form, or can I alter the bones of the form so its based on both the query and the table? Or something else alltogether?

    I seem to have thoroughly confused myself here. Any assitance is greatly appreciated.

    I've attempted to create a basic query which incorporates the date calculation. I've then built a form which uses the date calculation from the query and all other data from the table. Not sure why at this point but its resulted in a read only form. I can't seem to make any updates to the data.

    I forgot to mention earlier I would also like to incorporate an If statement into the next calibration date field. I have a checkbox field called "out of service". If the field is checked I don't want a new calibration date to be entered as its no longer needed. It may seem more sensible to delete old equipment that is no longer in service, but we need to keep the old data in the system for audit purposes. I've been working on an If statement and thus far got pretty much nowhere. Am I better off building it into a query or the form or via VBA?
    Last edited by SamJP; 07-05-13 at 07:51. Reason: Update

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts