error 2105 you can't go to the specified record.

[phillip9]

Hello everyone,

I have the following problem that I hope someone can help me with. I have a
data entry form that has half the fields bound to a table. The other fields
are unbound and used to capture some data that is then run through extensive
VBA code to automate some complex work-flow. then it should call
docmd.gotorecord,,acnewrec to create a new record.

I have narrowed the problem down to one combo-box. The combo box has a
small amount of VBA code and retreives its list from a query. This combo box
is not a bound field (unbound). when this field:txtID has anything entered
my VBA code will error out at the docmd.gotorecord,,acnewrec
error: run-time error '2105': You can't go to the specified record.
I have found if I clear the txtID field before making the call to docmd...
then it will work fine.

Can anyone look at some of this code and tell me what might cause this
problem?
Since I narrowed the problem down to the combo-box:txtID I will only submit
that code (the other code consists of 300 lines anyway)

Private Sub txtID_LostFocus()
Dim PID As Long

If txtID = "" Then
Exit Sub
ElseIf IsNull(txtID) Then
Exit Sub
End If

PID = PropertyID(txtID)
If PID < 0 Then
'PID was not found, display error
MsgBox "there was an error finding the ID (" & txtID & ")"
txtprojectnumber.Value = ""
txtPropertyID.Value = ""
txtContractNumber = ""
txtProjname.Value = ""
txtServicer.Value = ""
FIELDOFFICE.Value = ""
Exit Sub
Else
'PID was found, continue processing...
End If

Dim sqlString As String
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
'Set cnn = New adodb.Connection
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
sqlString = "SELECT
property_id,associated_fha_number,associated_contract_number,
servicing_site_name_text,project_manager_name_text,property_name_text FROM
active_property WHERE property_id = " & PID
rst.Open (sqlString), cnn


'MsgBox "[" & rst!property_name_text & "]"
txtprojectnumber.Value = rst!associated_fha_number
txtPropertyID.Value = rst!property_id
txtContractNumber = rst!associated_contract_number
txtProjname.Value = rst!property_name_text
txtServicer.Value = rst!project_manager_name_text
FIELDOFFICE.Value = rst!servicing_site_name_text



rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing




End Sub


qryProjectID ( query for combo-box:txtID field)
SELECT DISTINCTROW active_property.property_id,
active_property.property_name_text, active_property.associated_fha_number
FROM active_property
ORDER BY active_property.property_name_text;