Runtime error 2110 but only on last control (1 Viewer)

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

I would use an unbound combobox for choosing the "primary key value" and set the form filter on the OnUpdate event for the combobox.
And ofcause the form is bound to the table or query.

JHB,
Thanks for your reply, that has given me a new direction to head and I will give it a try but I'm afraid that Access is so different to all the other RDBMSs I have used that I'm not 100% sure of the concepts behind what you're telling me. A wee bit more stumbling in the dark it looks like but you have given me food for thought. Thank you again

mfish123

dlookup is a very inefficient way of populating fields and not editable.

You should set your form recordsource to the name of your table and each control controlsource to the name of the relevant field.

Hi again,
So far I have implemented both suggestions that I have received and learnt a lot about Access. However, I am still getting Runtime-error 2110 when I try to move the focus from the second last field on the form to the last. I also get it when I edit the "SetFocus" code in the second last field to move focus back to the third last field! This suggests to me that the problem lies not with the last field but rather the second last field. Has ANYONE seen ANYTHING like this??? Or any suggestions as to how the code "knows" it is the second last field?
Thanks again,
mfish123

Has ANYONE seen ANYTHING like this???

Click to expand...

No - but then you haven't shown us anything relevant. You say the problem is with the second to last control and you have shown code for the first control and the last control

Or any suggestions as to how the code "knows" it is the second last field?

Click to expand...

there is no such thing as first/last in access - how are YOU defining it is the second to last? using the tab order? it's position on the form?