Page 2 of 6 FirstFirst 123456 LastLast
Results 16 to 30 of 87
  1. #16
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    OK I was under the impression that I could do all the validation and default values through vb coding with statements like if question id is this and answer id is this then enforce this validation. Maybe it is better just to have all the check-lists as separate tables and do the validation at the table level. I will have to re-think my structure



    Thanks for all your help I was worried about misunderstandings of requirements throughout our discussions

    The most important thing is that the data entry from a user perspective is rapid with default values and values from last inspection passed to forms and that users cannot enter any wrong information so validation needs to be implemented

    It is also very important that forms and queries can get pretty much any information from the tables as well

    thanks again

  2. #17
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    Design your form and post what you end up with. We can have a look at it and give further help.

    Suggest that you don't put too much emphasis on Validation and Input Masks at this stage. Just do what you can.

    Looking forward to what you come up with.

  3. #18
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    I am starting to think that I can't do what I need to do using this method. Can I still create a different default value for every question?

    For example can I make every question/ answer default as the same value that was input for the piece of equipment in the last inspection?
    and/or
    Can I default every answer to a unique value not -related to anything else except that number question in a certain check-list?

    You say don't put too much emphasis on validation and input masks however they seem to be the most important element of an idiot-proof system that solely consists of text boxes

    Thanks

  4. #19
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    Please do not misquote me.

    I said "Suggest that you don't put too much emphasis on Validation and Input Masks at this stage."

    We can look at these when you have designed your form.

    You can default to the value of the last inspection using the Function DLookUp in SQL or TOP in VBA.

    I also asked you to design a form as best you can and post it. This will give me and others a pictoral view of where you are going. Just get the things working that you can. We can then work on what won't work.

    Back to you.

  5. #20
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    OK here goes nothing. I have got heaps of things working now and heaps more things that are still not working (this is taking me so much longer than I thought it would)

    Here is my initial design of the form based on our design and with a bit of help from a few others as well (frmEquipPressureVessel)

    At this stage I still cannot see how to enforce validation and default values, enter different elements (images) and generally see how this is best for users entering and and extracting data (hopefully I start to see a bit more clearly soon).

    At least I can now enter all values into table checklistAnswer

    Took me so long as just little things like not being able to copy and paste text boxes/ combos from one tab to another (couldn't stop it appearing in all tabs no matter how i pasted it), amongst other steep learning curves

    Also it would be heaps better if I could establish a For loop for the questions as well, like I have done the answers

    Notably other problems still exist like I still can't enter data in the inspection, equipment and inspectionequipmentjunction tables all at once

    Oh well enough of what I have not done yet, here is what I have for opinions/ discussions/ conversations etc

    Thanks

  6. #21
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    You have done good. (Bad English I know)

    You have embarked upon a rather ambitious project for a beginner but I feel that with time and patience you will get there.

    I have had a quick look and will get back later.

    In the intrim search for some code to do with "Not on List". It will help you a lot.

    Tidy up your code with white space an indentatioin. Also add Heaps of notes. It will help you in the future.

    PS You missed Option Explicit in one of your Modules.

    More later.

  7. #22
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Thanks Rain. Yeah I have included Not In List code in frmCities only so far

    keep the tips coming they are much appreciated

    Cheers

  8. #23
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    Using SQL Statements in VBA is correct but a little tricky. Why not use Queries to start with and if you want you can convert them to SQL.

    Going Forward.

    Create a Form that has at the top the Inspection Number Date etc.

    Then create a SUB form that lists all the pieces of equipment to be inspected on that Inspection Number. Suggest using either Continious Forms or Data Sheet view.

    As you select each piece of equipment you need an APPEND Query that will write to your Table tblResults all the applicable Questions from tblChklist. You will only need the Primary Key from that table and the EquipmentID.

    Then you should be able to create a Form and Sub Form that lists all the questions for each Piece of Equipment plus Empty Text Boxes for the Answers. A Combo Box would be best.

    Because you are selecting the answers from a Combo Box you don't need to worry about Validation. As far as Default Values are concerned I am not sure how to easilly do this. I will give it further thought. However in my opinion a user would be more inclined to make a mistake by doing nothing rather than actually making a selection. I will let you think about this.

    I get the feeling that not only do you want to be able to choose from a list you also want room for comments and Dates etc. If this is the case then adjust tblResults to include additional Fields.

    Hope this helps.

  9. #24
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    I just noticed this in your Code.

    "DoCmd.SetWarnings False" and "DoCmd.SetWarnings True"

    Get rid of them. How will you know if you have an error when one occurs.

    I know a lot of people use this but I Totally disagree with the usage.

  10. #25
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    A user should never see the Primary or Foreign Key.

    However, if you are currently displaying them for your own use then do not forget to delete them when finished.

    These Keys are part of your Record Source so I am not suggesting you delete them there. Just on Forms and Reports.

  11. #26
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    A user should never see the Primary or Foreign Key.

    This is for me to see what is getting passed from form to form

  12. #27
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    DoCmd.SetWarnings False" and "DoCmd.SetWarnings True"

    Get rid of them.


    Thanks, noted just picked this up in my travels

  13. #28
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Because you are selecting the answers from a Combo Box you don't need to worry about Validation.

    This line worries me as it still appears we may have a major misunderstanding, there is NO selection of answers from a combo box, I have no idea where this comes from.

    Every answer can be and is a unique comment, the only things that can be selected from a combo box is the yes, no and N/A answers

  14. #29
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    As far as Default Values are concerned I am not sure how to easilly do this. I will give it further thought. However in my opinion a user would be more inclined to make a mistake by doing nothing rather than actually making a selection

    Again this is non-negotiable... there must be a default value present for all answers, this will be a certain response originally however it will also (always) be the exact same response as it was in the last inspection for that piece of equipment as default

    Thanks

  15. #30
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Because you are selecting the answers from a Combo Box you don't need to worry about Validation.

    This line worries me as it still appears we may have a major misunderstanding, there is NO selection of answers from a combo box, I have no idea where this comes from.

    Every answer can be and is a unique comment, the only things that can be selected from a combo box is the yes, no and N/A answers

    In retrospect I can see how this could possibly work, I may be thinking about this in the wrong way

Page 2 of 6 FirstFirst 123456 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Excess Resource (MSDB)
    By mei909 in forum Programming
    Replies: 1
    Last Post: 09-04-2011, 12:15 AM
  2. Need Direction
    By sabrish72 in forum Programming
    Replies: 5
    Last Post: 06-08-2011, 09:25 PM
  3. Some general direction
    By Darkglasses in forum Database Design
    Replies: 4
    Last Post: 02-20-2011, 02:38 PM
  4. Need Direction Parts Form
    By Deano in forum Forms
    Replies: 2
    Last Post: 01-22-2011, 06:01 AM
  5. Direction needed.....
    By EVS Director in forum Database Design
    Replies: 7
    Last Post: 06-22-2010, 05:10 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