Page 3 of 7 FirstFirst 1234567 LastLast
Results 31 to 45 of 101
  1. #31
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You would need to alter the data in the table CategoryQuestionsTbl. You can do this via a form (that would need to be created) or run a series of delete queries to get rid of the questions 1311-1411 for each category other than commercial.

    Delete pkCatQuestionsID from CategoryQuestionsTbl WHERE fkquestionID between 1311 and 1411 and fkCat_ID in (2,3,4,5,6,7,8,9)

    You will need a separate delete query for the commercial category since the question numbers involved are a little different

    Make sure you have a backup of the database before running the delete query.

  2. #32
    cdell7up is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    74
    So if I wanted to start from scratch, where I don't have any records saved, How would I make it so the right questions go with the right categories? I am planning on creating a new database and importing just the structure of this checklistdbv5. Where is it that I tell the categories to look for the right questions?

  3. #33
    cdell7up is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    74
    Ok, I ran the delete queries. When I create a new record and press the "add questions to this reviewer button" something funny happens. It does populate with the correct questions but the it is sorted funny. I mean I choose the A&E category and the first 5 questions are QuesNum 1810-1814, then it starts with 1001- ect.. What happened? All the right questions are there but the first 5 are out of order.

  4. #34
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I did not order the records when they were being appended. I have adjusted that in the attached database.

    Alternatively, you can order the records in the subform using the order by property.
    Attached Files Attached Files

  5. #35
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    So if I wanted to start from scratch, where I don't have any records saved, How would I make it so the right questions go with the right categories?
    You would basically set up and run a series of append queries that pull the appropriate question number range (like we did before) and include the category key value as a constant. The records would be appended into to the CategoryQuestionsTbl table.

    As an example, for the commercial category whose cat_ID=1 and using this info you provided earlier:

    Commercial= 1001-1310 and 1401-1411


    The first append query would look like this:

    INSERT INTO CategoryQuestionsTbl ( fkQuestionNumber, fkCat_ID )
    SELECT QuesTbl.QuestionNumber, 1 AS Expr1
    FROM QuesTbl
    WHERE (((QuesTbl.QuestionNumber) Between 1001 And 1310));

    You would then need a second append query for the 1401-1411


    INSERT INTO CategoryQuestionsTbl ( fkQuestionNumber, fkCat_ID )
    SELECT QuesTbl.QuestionNumber, 1 AS Expr1
    FROM QuesTbl
    WHERE (((QuesTbl.QuestionNumber) Between 1401 And 1411));


    If you were doing the Major Systems category (catID=2), you could take the first query shown above and modify it slightly by changing the 1 to a 2 (Shown in red below)

    Major Systems=1001-1310 and 1501-1507


    INSERT INTO CategoryQuestionsTbl ( fkQuestionNumber, fkCat_ID )
    SELECT QuesTbl.QuestionNumber, 2 AS Expr1
    FROM QuesTbl
    WHERE (((QuesTbl.QuestionNumber) Between 1001 And 1310));

  6. #36
    cdell7up is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    74
    How would I go about make the category dropdown list ( on frmRev) default to a blank. Right now it is defaulting to whatever was previously entered. I can see this being a problem.

    Also how can I get the default for the Response field on frmRevAnswers to be N/A. I think would help because each question needs to be answered and if the reviewer is not sure it should be an N?A.

  7. #37
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    How would I go about make the category dropdown list ( on frmRev) default to a blank. Right now it is defaulting to whatever was previously entered. I can see this being a problem.
    You can add some code in the on current event of the Rev form as follows:

    Me.cboCategory = Null


    Also how can I get the default for the Response field on frmRevAnswers to be N/A.
    In the on current event of the subform, you can set the response combo box to N/A (key value =3)

    Me.cboResponse = 3

    When the user clicks on the question in the subform the response will set to N/A using the above code. And of course, they still have the option of changing it.

  8. #38
    cdell7up is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    74
    do I go to the VBA interface to enter this code? When I do, i see the Private sub cboCategory_BeforeUndate(Cancel As Interger). What is this doing? and do I add the code immediately following that, or do I enter it in the Private Sub cmdAddQues_Click()? or do I make a complete new private sub?

    I would like to know more about what i am seeing in the VBA window, and the proper syntax and so on.

  9. #39
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Private sub cboCategory_BeforeUpdate(Cancel As Interger)
    This is the Before Update event of the category combo box on the form.

    You want the on current event of the form (frmRev). Open frmRev in design view (in the upper left corner of the form should be a black square; this indicates that the form is the current selection. Then go to the property sheet and then the Events tab. Look for the event named On Current. Click in the blank space next to the text and a small button with three dots will appear at the right end of the line. Click that button and then select Code Builder. Access/VBA will fill in the first and last line as follows:

    Private Sub Form_Current()


    End Sub

    You would insert the code I provided between the first and last lines.

    You will have to repeat the same process with the frmRevAnswers but of course, substitute the other line of code I showed earlier.

  10. #40
    cdell7up is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    74
    Thanks for the help I got it working good now.

    There are a couple of error dialog boxes that I need to address before I go any further. 1st I get a message saying "The Microsoft Access Database Engine cannot find a record in the table "QuesTbl" with the key matching field(s) 'QuesNum'. I get this when I go past the last record ( a blank field after the last question). I can get past this message if I press delete and then escape, otherwise it keeps showing the error message. Is there something can do to avoid this or possible create another dialog box saying something like (press delete, and then esc key)?

    The other error happens on a new record of the FrmRev. If there is no auto incremented number in the Record_ID field, I get the following message Microsoft VB, Runtime Error '3075' Syntax(missing operation) in query expression 'Reviewer'='.. I also get it when the cursor is not in the QueNum field of the frmRevAnswers. Maybe there is someway of defaulting the cursor to the Record_ID. Then after the appendquery happens the focus is on the QuesNum field on the frmRevAnswers?

  11. #41
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    "The Microsoft Access Database Engine cannot find a record in the table "QuesTbl" with the key matching field(s) 'QuesNum'. I get this when I go past the last record
    You are getting this error because the query behind the subform needs the question number (it cannot be left blank). The on current event in the subform we added is also contributing, so you will have to remove that code. You will need to go back to the subform in design view, go the the VBA window and clear out the entire event (the 3 lines).

    If your intent is to add questions in addition to the ones added by the append query then you will need to redesign the subform to replace the question number text box with a combo box.

    Now I'm not sure on what event to set the default property of the response combo box since the questions are already populated.

    If there is no auto incremented number in the Record_ID field...
    You have to type something into one of the controls in the main form for the record ID to populate. You should not be going directly to the subform, however that code in the on current event in the subform might have been impacting this as well. You will have to test to see if you still have the problem after you removed the code as described above.

  12. #42
    cdell7up is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    74
    Ok I removed the code from the subform. I can live with it like this.

    One more thing I want to do before we beta test this database is, have a way to view the entire Question field on the frmRevAnswers subform. So of the questions are too long to be viewed, and the reviewer will need to be able to see all the text of the question. Any thoughts on what I can do for this?

  13. #43
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Open the main form in form view, go to the subform. Click to highlight any row and then right click and choose the row height option and adjust as necessary (save the form). You might find this site useful as well.

  14. #44
    cdell7up is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    74
    That worked great. Thanks

    Now 2 more things.

    1st how can I force all caps?

    2nd how can I force the reviewer to fill all fields on the frmRev?

  15. #45
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    1st how can I force all caps?
    Put a greater than sign (>) in the format property of each control on the form. If you want it in all caps at the table level as well, do the same for each field in the table.

    2nd how can I force the reviewer to fill all fields on the frmRev?
    Set the required property to yes for each field in the table that you want to be required.

Page 3 of 7 FirstFirst 1234567 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Append Query?
    By kwooten in forum Queries
    Replies: 27
    Last Post: 10-19-2011, 10:06 AM
  2. Replies: 1
    Last Post: 10-06-2011, 08:37 AM
  3. append query
    By gimmy in forum Queries
    Replies: 1
    Last Post: 09-09-2011, 10:41 AM
  4. Replies: 7
    Last Post: 07-21-2011, 01:01 PM
  5. Append query won't append
    By yelkenli in forum Queries
    Replies: 5
    Last Post: 02-12-2010, 11:19 AM

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