Hi,
While I have developed many databases using other RDBMSs I'm afraid I'm something of an Access 2007 newbie so the vast bulk of my experience counts for nought given Access' many "unique features".
What I have is a form with about 3 text fields, 2 date fields and a number of comboboxes on it. Although, for now I'm not worrying about the combo boxes until I get the other fields working.
I am trying to develop an update screen where the user types the primary key value for a record into the first field and using DLookup retrieve the other values from the table and insert them into the form so the user can adjust them if they wish and click the Save button to write the changes back to the table. That's the theory.
Unfortunately the reality is, although the values added to the other fields using DLookup are "there" they don't appear on the screen until the controls lose focus. My best guess is because they are being added programatically and no Return key is being pressed, focus will not change and they will never appear - more's the pitty. So to get around this I put a line of code in the GotFocus event of each control to move focus on to the next control, effectively "looping" the focus once around the controls. This worked well until the focus was being moved from the second last control to the last control when I got, Runtime error 2110: Can't move focus to this control. The control was visible and enabled, as was a dummy control I tacked on the end to see what happened.
Main code block in Exit event of first field:
Private Sub Text6_Exit(Cancel As Integer)
Dim varItemType, varItemTypeCode, varItemClass, varItemClassCode, varDescription As Variant
Dim varPurchaseDate, varWarrantyEnd As Variant
Dim varItemTypeTxt As AcTextFormat
Dim PurchaseDateSQL As String
varItemTypeCode = DLookup("ItemType", "ICTAssets", "[EdQuipNo] = '" & Forms![F_ICTAssets1a_Use_this]![EdQuipNo] & "'")
If (Not IsNull(varItemTypeCode)) Then
varItemType = DLookup("ItemType", "ItemTypes", "[ID] = " & varItemTypeCode)
End If
varDescription = DLookup("Description", "ICTAssets", "[EdQuipNo] = '" & Forms![F_ICTAssets1a_Use_this]![EdQuipNo] & "'")
If (Not (IsNull(varDescription) Or varDescription = "")) Then
Forms![F_ICTAssets1a_Use_this]![Description] = varDescription
End If
varPurchaseDate = DLookup("PurchaseDate", "ICTAssets", "[EdQuipNo] = '" & Nz(Forms![F_ICTAssets1a_Use_this]![EdQuipNo]) & "'")
If (Len(CStr(Nz(varPurchaseDate))) > 0) Then
Forms![F_ICTAssets1a_Use_this]![PurchaseDate] = varPurchaseDate
End If
varWarrantyEnd = DLookup("WarrantyEnd", "ICTAssets", "[EdQuipNo] = '" & Nz(Forms![F_ICTAssets1a_Use_this]![EdQuipNo]) & "'")
If (Len(CStr(Nz(varWarrantyEnd))) > 0) Then
Forms![F_ICTAssets1a_Use_this]![WarrantyEnd] = varWarrantyEnd
End If
End Sub
Line of code in the GotFocus event of other fields:
Text35 is actually the last (dummy) control and passes focus back to the first control.
Private Sub Text35_GotFocus()
Me!Text6.SetFocus
End Sub
If anyone has a solution to this problem I would greatly appreciate you passing it on while I still have some hair left!
Thank you
While I have developed many databases using other RDBMSs I'm afraid I'm something of an Access 2007 newbie so the vast bulk of my experience counts for nought given Access' many "unique features".
What I have is a form with about 3 text fields, 2 date fields and a number of comboboxes on it. Although, for now I'm not worrying about the combo boxes until I get the other fields working.
I am trying to develop an update screen where the user types the primary key value for a record into the first field and using DLookup retrieve the other values from the table and insert them into the form so the user can adjust them if they wish and click the Save button to write the changes back to the table. That's the theory.
Unfortunately the reality is, although the values added to the other fields using DLookup are "there" they don't appear on the screen until the controls lose focus. My best guess is because they are being added programatically and no Return key is being pressed, focus will not change and they will never appear - more's the pitty. So to get around this I put a line of code in the GotFocus event of each control to move focus on to the next control, effectively "looping" the focus once around the controls. This worked well until the focus was being moved from the second last control to the last control when I got, Runtime error 2110: Can't move focus to this control. The control was visible and enabled, as was a dummy control I tacked on the end to see what happened.
Main code block in Exit event of first field:
Private Sub Text6_Exit(Cancel As Integer)
Dim varItemType, varItemTypeCode, varItemClass, varItemClassCode, varDescription As Variant
Dim varPurchaseDate, varWarrantyEnd As Variant
Dim varItemTypeTxt As AcTextFormat
Dim PurchaseDateSQL As String
varItemTypeCode = DLookup("ItemType", "ICTAssets", "[EdQuipNo] = '" & Forms![F_ICTAssets1a_Use_this]![EdQuipNo] & "'")
If (Not IsNull(varItemTypeCode)) Then
varItemType = DLookup("ItemType", "ItemTypes", "[ID] = " & varItemTypeCode)
End If
varDescription = DLookup("Description", "ICTAssets", "[EdQuipNo] = '" & Forms![F_ICTAssets1a_Use_this]![EdQuipNo] & "'")
If (Not (IsNull(varDescription) Or varDescription = "")) Then
Forms![F_ICTAssets1a_Use_this]![Description] = varDescription
End If
varPurchaseDate = DLookup("PurchaseDate", "ICTAssets", "[EdQuipNo] = '" & Nz(Forms![F_ICTAssets1a_Use_this]![EdQuipNo]) & "'")
If (Len(CStr(Nz(varPurchaseDate))) > 0) Then
Forms![F_ICTAssets1a_Use_this]![PurchaseDate] = varPurchaseDate
End If
varWarrantyEnd = DLookup("WarrantyEnd", "ICTAssets", "[EdQuipNo] = '" & Nz(Forms![F_ICTAssets1a_Use_this]![EdQuipNo]) & "'")
If (Len(CStr(Nz(varWarrantyEnd))) > 0) Then
Forms![F_ICTAssets1a_Use_this]![WarrantyEnd] = varWarrantyEnd
End If
End Sub
Line of code in the GotFocus event of other fields:
Text35 is actually the last (dummy) control and passes focus back to the first control.
Private Sub Text35_GotFocus()
Me!Text6.SetFocus
End Sub
If anyone has a solution to this problem I would greatly appreciate you passing it on while I still have some hair left!
Thank you
Runtime error 2110 but only on last control
