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-
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
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-
---In MS_Access_Professionals@yahoogroups.com, <desertscroller@...> wrote :
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
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-
End Sub
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
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
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
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
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
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
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
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
'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
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
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
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
' 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:
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:
Posting Komentar