FindRecord not available

[Joshua61679]

Im running this code. I'm not quite sure its all doing what it is supposed to yet, but I need to figure out this FindRecord is not available now error. I've double checked to make sure that both pop-up and modal are set to no. Sorry for the link, but I wanted to make sure that I wan't missing something stupid early on that was causing the problem. Thanks.

Private Sub CasedHole_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSql As String
Dim strWhere As String
Dim strWhere1 As String
Dim strCustomer As String
Dim strLease As String
Dim strCallSheet As String
Dim strMsg As String

Set db = CurrentDb
strCustomer = Me!Customer
strLease = Me!Lease
strWhere = " WHERE Customer = '" & strCustomer & "' AND " _
& " Lease = '" & strLease & "'"
strWhere1 = " Customer = '" & strCustomer & "' AND " _
& " Lease = '" & strLease & "'"
strSql = "SELECT Customer, Lease " _
& " FROM NewJob " & strWhere
strMsg = "There is already a job record for this service on this well, would you like to create a new job?"

Set rst = db.OpenRecordset(strSql, dbOpenSnapshot)
If (rst.BOF And rst.EOF) Then
Call DoCmd.OpenForm("Cased1", , , , acFormAdd)
Forms!Cased1!Customer = strCustomer
Forms!Cased1!Lease = strLease
Forms!Cased1!CallSheet = rst.RecordCount + 1
Else
Response = MsgBox(strMsg, vbYesNo)
If Response = vbYes Then
Call DoCmd.OpenForm("Cased1", , , strWhere1, acFormEdit)
DoCmd.FindRecord (strWhere)
DoCmd.FindNext

Else
Call DoCmd.OpenForm("Cased1&quot
DoCmd.Close
End If

End If

rst.Close
Set rst = Nothing
db.Close
Set db = Nothing

Exit Sub

Error:
Call MsgBox(Err.Number & ": " & Err.Description)

End Sub

 

[BillPower]

Setting the focus to the Customer control before FindRecord should work.

If (rst.BOF And rst.EOF) Then
Call DoCmd.OpenForm("Cased1", , , , acFormAdd)
Forms!Cased1!Customer = strCustomer
Forms!Cased1!Lease = strLease
Forms!Cased1!CallSheet = rst.RecordCount + 1
Else
Response = MsgBox(strMsg, vbYesNo)
If Response = vbYes Then
Call DoCmd.OpenForm("Cased1", , , strWhere1, acFormEdit)
Me!Customer.SetFocus
DoCmd.FindRecord (strWhere)
DoCmd.FindNext

Else
Call DoCmd.OpenForm("Cased1&quot
DoCmd.Close
End If
End If

 

[Joshua61679]

Well, now instead of saying that I can't use FindRecord, its say that Find or Replace.

 

[BillPower]

I'm at a bit of a loss.

To test your code here at home I:

1. Created a table NewJob, with 2 Fields - Customer and Lease, added a few dummy records.

2. Created a form called Cased1, Recordsource = NewJob, 2 Textboxes named Customer and Lease with their controlsources set to Customer and Lease.

3. Added a button named CasedHole. Pasted your code behind the on click event of the button, got the same error as you. Added Me!Customer.Setfocus before the FindRecord. Works perfectly.

Have you got your form set up differently?