Problem using recordset and Findfirst

[dkmoreland]

I am trying to use a form to find a record and open a second form to allow editing that record. But, I also want to load the other records in the table so the users can scroll through them. Here's my setup:

I have two unbound controls, Job number and NCR Number on the first form. The data source for both forms is the same - [Non Conformance].

The user should be able to enter the Job number OR the NCR number, click Continue, then the second form should open to the correct record.

This is the code I have in the On Click property for the Continue button. Right now I have it set to only look for the NCR number - I can add an if statement to look for the job number once I get the first search working:

Private Sub CmdNCRContinue_Click()

Set db = CurrentDb
Dim rs As Recordset
Set rs = dbOpenRecordSet("Non Conformance", dbOpenDynaset)

Dim strNCR As Long
lngNCR = Me.TextNCR

rs.FindFirst (lngNCR)

If rs.NoMatch Then
    MsgBox "No records found"
Else
    DoCmd.OpenForm "Resolve Non Conformance Form", , , "[NCR#] = " & lngNCR
End If

End Sub

I'm getting a "Sub or function not defined error". I suspect it is because this is the first time I have tried to used a recordset and FindFirst and I am doing it wrong. Would someone mind pointing out my error(s), please?

One more thing - I know that the field name listed above [NCR#] should not have a special character in it. I inherited this database and cannot rename it at this point. I only mention this because invariably, someone feels led to tell me this is a bad practice.

Thanks in Advance,
D

 

[arnelgp]

You need to add the field to search in findfirst:

rs.Findfirst "fieldname =" & lngNCR

 

[dkmoreland]

Ok - I fixed that part but I'm still getting "Sub or function not defined". The debugger is landing on this:

Private Sub CmdNCRContinue_Click()

here's the whole bit of code again:

Private Sub CmdNCRContinue_Click()

Set db = CurrentDb
Dim rs As Recordset
Set rs = dbOpenRecordSet("Non Conformance", dbOpenDynaset)

Dim strNCR As Long
lngNCR = Me.TextNCR

rs.FindFirst "[NCR#] =" & lngNCR

If rs.NoMatch Then
    MsgBox "No records found"
Else
    DoCmd.OpenForm "Resolve Non Conformance Form", , , "[NCR#] = " & lngNCR
End If

End Sub

 

[arnelgp]

Chk your rs... there must be dot after db.

Set rs = db.openrecordset(...

 

[dkmoreland]

Making progress - thanks for pointing that out. Now I am getting a compile error - Method or data member not found on this line:

rs.FindFirst "[NCR#] =" & lngNCR

[NCR#] is the field in the Non Conformance table that I am searching on.

 

[arnelgp]

Put bracket in your tabke since you have space in it:


Set rs = db.openrecordset("[Non Conformance]", ...

 

[dkmoreland]

Sorry - getting the same error.

 

[arnelgp]

Use DAO recordset

Dim rs As DAO.Recordset

 

[MarkK]

FWIW, you don't have to open a recordset on the whole table and search thru the whole thing to see if the record exists. You can use DCount() and considerably simply your code. Consider...

Private Sub CmdNCRContinue_Click()
    If DCount("*", "[Non Conformance]", "[NCR#] = " & Me.TextNCR) = 0 Then
        MsgBox "No records found"
    Else
        DoCmd.OpenForm "Resolve Non Conformance Form", , , "[NCR#] = " & Me.TextNCR
    End If
End Sub

hth
Mark

 

[dkmoreland]

Now I'm getting a different compile error, landing on this line

Dim rs As DAO.Recordset

The error is: User-defined type not defined.

 

[dkmoreland]

FWIW, you don't have to open a recordset on the whole table and search thru the whole thing to see if the record exists. You can use DCount() and considerably simply your code. Consider...

Private Sub CmdNCRContinue_Click()
    If DCount("*", "[Non Conformance]", "[NCR#] = " & Me.TextNCR) = 0 Then
        MsgBox "No records found"
    Else
        DoCmd.OpenForm "Resolve Non Conformance Form", , , "[NCR#] = " & Me.TextNCR
    End If
End Sub

hth
Mark

Mark,

I don't just want to know if the record exists, I want to find it and move to it when the second form opens.

 

[MarkK]

Try the code.
Cheers,

 

[dkmoreland]

MarkK - I should have never doubted you.

However, it only loads the one record in the 2nd form. I want to go to that record but allow the users to scroll through the others in the table as well.

That's why I thought I needed a recordset.

 

[MarkK]

The form opens its own recordset. To navigate in the form's recordset you can use code like this in that form's module...

Public Sub GoToID(NCR as long)
[COLOR="green"]   ' navigate in the clone, it's faster[/COLOR]
   With Me.Recordsetclone
[COLOR="Green"]      ' here is your FindFirst expression[/COLOR]
      .FindFirst "[NCR#] = " & NCR
[COLOR="green"]      ' if the record is found in the clone, got straight to it in the recordset using bookmarks[/COLOR]
      If Not .NoMatch Then Me.Bookmark = .Bookmark
   End With
End Sub

...so that is where you want to execute your findfirst.

And then you would amend this part on the calling form...

Private Sub CmdNCRContinue_Click()
    Const FN as string = "Resolve Non Conformance Form"

    If DCount("*", "[Non Conformance]", "[NCR#] = " & Me.TextNCR) = 0 Then
        MsgBox "No records found"
    Else
        DoCmd.OpenForm FN           [COLOR="Green"]' open the form, showing all the rows[/COLOR]
        Forms(FN).GoToID Me.TextNCR [COLOR="green"]' navigate to this particular row without filters [/COLOR]
    End If
End Sub

...so we still test for the existence of that record using DCount(), and we don't open the form at all if the record doesn't exist, but the cool part is after we open the form. Now it exposes the new public GoToID method, and to navigate in the form without filtering it, we call that method, passing in the ID of the record we want to make current.
Makes sense?
Mark

 

[dkmoreland]

That does make sense and it works great! I appreciate it so much!

Now, just to complicate it a little bit, I would actually like to give them the option on the first form to search by [job#] or [NCR#].

I'm sure I can add an if statement to the calling form but how would I handle it on the second form?

 

[MarkK]

Well, if I wanted to find a record in the form using different criteria, I would likely add another public method with a very specific name, maybe...

Public Sub GoToJobID(JobID as Long)

...and of course you might already have a method like...

Public Sub GoToNCR(NCR as Long)

...and you might want to name it as specifically as possible, to make sure there is no confusion between your growing collection of GoToSomething() methods.

A cool thing you can start to do with repetition like this is break up generic parts of the functionality into subroutines. Consider code like...

Public Sub GoToJobID(JobID as long)
   GoToRow "JobID = " & JobID
End Sub

Public Sub GoToNCR(NCR as Long)
   GoToRow "NCR = " & NCR
End Sub

Private Sub GoToRow(FindFirstExpression As String)
   With Me.RecordsetClone
      .FindFirst FindFirstExpression
      If Not .NoMatch Then Me.Bookmark = .Bookmark
   End With
End Sub

See how the Private GoToRow method receives any valid FindFirst expression? See how we can save a ton of code by breaking the problem down into subroutines by function, and then pass around little incrementally completed parts of the solution?
Hope this makes sense,
Mark

 

[dkmoreland]

This does make a lot of sense. I do have a couple of questions, though, to make sure I understand.

Am I correct that the public routines go in the module for the second form and the private GoToRow function goes in the On Click property for the calling form?

So, I'm thinking I need to do something in the calling form like
if txtJobID is not null, go to Job Id, else if txtNCR is not null, go to NCR.

Is that right?

 

[MarkK]

Am I correct that the public routines go in the module for the second form and the private GoToRow function goes in the On Click property for the calling form?

No. GoToRow() is navigating in the 2nd form's recordset, and so belongs on the second form. It is Private and it is being called by, say, GoToJobID(), so it must be in the same module with that method.

So, I'm thinking I need to do something in the calling form like if txtJobID is not null, go to Job Id, else if txtNCR is not null, go to NCR.

Yes, that makes sense. I would add an ElseIf block to the If...Else...End If from other posts, and do another DCount() to test for existence of that other data point.

hth
Mark

 

[dkmoreland]

Thanks so much for all of your help. I have learned a good deal from this exercise and I appreciate it.

 

[MarkK]

Yeah, you bet, all the best with your project.
Mark