Minggu, 03 Juli 2016

Re: [MS_AccessPros] Re: Help with subform

 

Thanks John, this has been a real puzzle but you have help.  That is exactly what is happening.  I went back to earlier and found that the individual that was adding the answers input the information from EXCEL and he had added a field in the import file that include ID_ANSWER.  Apparently the import does not set the AUTONUMBER to new numbers so it still thought is was at the beginning.  Things appear to be working great now.

Thanks for all the help.
Rod


---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Rod-

I forgot that the link is on ID_Question.  As long as ID_Question is being set properly, that's all that matters.  ID_Answer will probably never match ID_Question - and it will increment every time you "dirty" a record.  If you don't save, for example, record 11, then the next time you do it, you'll see the value 12.  Can you see ID_Question on the subform?  That should be set automatically by the Link Master / Link Child properties as soon as you start to create a new record.

John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)




On Jul 3, 2016, at 2:44 PM, desertscroller@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



John,
I thought that the primary key should be an autonumber so it would be unique and defined by the system. When the form displays that a new record is being input (prior to entering data) the field shows 'NEW' then when the first data is entered in a field other that the primary key (which is locked for edit), the system displays a number that is sequential as if it is being autonumbered.  The ID_Answer field is not really used for anything but a record id, ID_Question field links the question to answer between the two tables.  

Do I have a miss understanding of the use of a autonumbered primary key?

Rod 


---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Rod-

Ah, you said: ".. but the autonumber 'ID_Answer' (primary key) does not appear with the expected value."

The ID_Answer field in the table being edited by the subform must be Number, Long Integer, NOT AutoNumber.

John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)




On Jul 3, 2016, at 2:39 AM, desertscroller@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



Hi John, I have reworked the forms and still get the same result.  From what I can tell the record source of the subform does not appear to be opening properly.   The record source is a table with 411 records. However, when I try to verify the fact it says on 1 record.  Also tried a query of all records.  The linked fields between the main form and subform  "ID_Question" works create but the autonumber "ID_Answer" (primary key) does not appear with the expected value.  If the main form is filtered to edit 7 records which have no corresponding record in the subform then the "ID_Answer" comes back with a number (8).  If a new record is being added to the main form and subform together the "ID_Answer" is also incorrect.

I have tried a different layout with the same results.  I even tried creating a record into the subform table from the parent and if does not appear to work.
Rod 


---In MS_Access_Professionals@yahoogroups.com, <desertscroller@...> wrote :

HI John,
The code in AuditChanges takes the old value and the new value and creates a string to indicate the change.  The other fields in the AuditChanges table indicate the Date, Record number that changed, and the user making the change.   I use AuditChanges to have traceability of any changes to data.

I am in the process of looking at code and will use the Debug.Print to help with the debugging the code.
Rod


---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Rod-

Is there any code in AuditChanges that changes the value of ID_Answer?  You could also try doing a Debug.Print Me.ID_Answer and Debug.Print Me.Parent!ID_Answer in the Before Update code to see if the values are the same on entry to that code.

John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)




On Jul 2, 2016, at 8:12 PM, desertscroller@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



There is code in the subform.  The subform is made up of 10 textboxes and 10 checkboxes.  The controls (expect the first of each) is locked.  Once data is entered into the 1st textbox and losses focus the next textbox and checkbox are unlocked:

Private Sub Option1_LostFocus()
    If Len(Me.Option1.Value) Then
      Option2.Locked = False
      Ck_Opt2.Locked = False
   End If
 End Sub

The 1st 9 textbox have the same type of routine.

The checkboxes are used to set which textbox represents the correct solution.  Only one checkbox can be selected so there is code to ensure the last checkbox selected is the only one check.

Private Sub Ck_Opt1_Click()
   If Ck_Opt1 Then
     Clear_Ck_Opt ("Ck_Opt1")
   Else
   End If
End Sub

Each checkbox have the same type of routine.
The Clear_Ck_Opt  function is used to ensure all other checkboxes are set to false.

Private Sub Clear_Ck_Opt(NotCtrl As String)
Dim ctl As Control
Dim tmpStr As String

On Error GoTo ErrorHandler

   For Each ctl In Me.Controls
     If ctl.ControlType = acCheckBox Then
        If ctl.Name <> NotCtrl Then
           tmpStr = ctl.Name
           Me.Controls(tmpStr).Value = False
        End If
     End If
   Next ctl
   
   ID_Answer = CLng(Mid(NotCtrl, 7))  ' Answer is checkbox not cleared

ExitPoint:
    On Error GoTo 0
    Exit Sub
    
ErrorHandler:
    Call LogError(Err.Number, Err.Description, "frmEditQ_Sub-Clear_Ck_Opt()")
    Resume ExitPoint
   
End Sub

There are two other pieces of code; Form_BeforeUpdate and Check_Answers.

Private Sub Form_BeforeUpdate(Cancel As Integer)
   Dim varTPTID As Variant
   Dim strTPTNo As String

   'If the form data has changed a message is shown asking if
   'the changes should be saved. If the answer is no then
   'the changes are undone
 
   On Error GoTo BeforeUpdate_Error
   If (Check_Answers) Then
      If MsgBox("Do you want to save this record? ", _
                 vbYesNo + vbQuestion, "Save Record") = vbNo Then
         Me.Undo
      Else
         If Me.NewRecord Then
            Me.ID_Question = GetQuestionID
            Call AuditChanges(Me, "ID_Answer", "NEW")
         Else
            Call AuditChanges(Me, "ID_Answer", "EDIT")
         End If
      End If
   Else
     MsgBox "Not valid data. Verify the following." & vbCrLf _
           & " 1. At least two (2) answers have been entered." & vbCrLf _
           & " 2. Answer checkbox has been selected to a valid answer."
           Me.Undo
           Exit Sub
   End If

BeforeUpdate_Exit:
   Exit Sub

BeforeUpdate_Error:
   MsgBox Err.Description
   If Not LogError(Err.Number, Err.Description, "Form_BeforeUpdate()", "Form_frmEditQ_Sub") Then
      Me.Undo
   End If
   Resume BeforeUpdate_Exit

End Sub

Private Function Check_Answers() As Boolean
Dim ctl As Control
Dim tmpName As String
Dim tmpAnsCnt As Long
Dim boolAns As Boolean
Dim tmpAns As Long

On Error GoTo ErrorHandler
   
   ' Determine if more than 1 answer is available
   tmpAnsCnt = 0
   For Each ctl In Me.Controls
     If ctl.ControlType = acTextBox And InStr(1, ctl.Name, "Option", 1) Then
       tmpName = ctl.Name
       If Not (ctl.Locked) And Len(Me.Controls(tmpName).Value > 0) Then
          tmpAnsCnt = tmpAnsCnt + 1
       End If
     End If
   Next ctl
   
   ' Determine which is answer is selected
   boolAns = False
   For Each ctl In Me.Controls
     If ctl.ControlType = acCheckBox And InStr(1, ctl.Name, "Ck_Opt", 1) Then
       tmpName = ctl.Name
       If (Not (ctl.Locked)) And (Me.Controls(tmpName)) Then
          boolAns = True
          tmpAns = CLng(Mid(tmpName, 7))
          tmpName = "Option" & tmpAns
          If IsNull(Me.Controls(tmpName).Value) Or Me.Controls(tmpName).Value = "" Then
             boolAns = False
          Else
             Exit For
          End If
          
       End If
     End If
   Next ctl
   
   If tmpAnsCnt > 1 And boolAns Then
      Check_Answers = True
   Else
      Check_Answers = False
   End If
   
ExitPoint:
    On Error GoTo 0
    Exit Function
    
ErrorHandler:
    Call LogError(Err.Number, Err.Description, "frmEditQ_Sub-Function Check_Answers")
    Resume ExitPoint

End Function

That is the extent of the code.  The LogError routine has been in all my apps and work good in logging errors.
And AuditChanges is used to create a log of when a record is created or edited; again is used in all my and works fine with no issues.

I am in the process of re-constructing the subform and main form to see if I have done something in error.
Rod


---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Rod-

Either you have a corrupted form, or you have some code that is interfering with the setting of the key on the subform.  Do you have any code in the subform?

John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)




On Jul 2, 2016, at 3:53 AM, desertscroller@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



I have a form and subform combination.  The main form has a table as source and the subform with a separate table.  The two forms use a master and child fields "ID_Question".  ID_Question is the primary key on the main form and a normal field on the subform.


When I open the main form with a filter, its filtered records appear.  If the subform has no data everything appears good.  When new data is added to the subform the associated ID_Question appears correctly but the primary key of the subform data is the count of records + 1 of the filtered main form.  Example:  when the filter return 7 records and new data is added to the subform the primary is set to 8 instead of the next record in the table thereby causing an error of duplicate primary key values.

Rod















__._,_.___

Posted by: desertscroller@cox.net
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (10)

Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.


.

__,_._,___

Tidak ada komentar:

Poskan Komentar