Find a record, and display current record number. 2

[rlatham]

Hello,

I have looked at many other forums and threads and have not found anything that does exactly what I need. But, I also know very little VB.

Item 1 is done. I can do items 3 and 4.

I need help with Item 2.

1) I want to have a button that when pressed opens an Input box and asks for a number. (done)

2) After the user enters the number, and clicks OK,
I want "some code" to look for a record in a query that matches the number entered in a particular column.
AND, obtain the current record number for the record found.

3) If a record is found, I want a message box that displays the current record number for that record.

4) If no record is found, then a message box should display "No record found".

Thanks in advance.

 

[Remou]

Hi
As in:
[tt]strSQL="Select ID From tblTable Where SomeField = " & intNumber[/tt]
Or as in the number is entered in a form and a query is run when the button is clicked:
[tt]SELECT tblTable.ID, tblTable.SomeField
FROM tblTable
WHERE tblTable.SomeField=[forms]![frmForm]![txtNumber];[/tt]

 

[rlatham]

As in:
strSQL="Select ID From tblTable Where SomeField = " & intNumber

tblTable is a static Query

 

[Remou]

Well, that's it really.

Dim rs As DAO.Recordset
Dim strSQL As String
Dim lngNumber As Long

lngNumber=InputBox("Number?")
strSQL="Select ID From tblTable Where SomeField = " & lngNumber
Set rs = CurrentDB.OpenRecordset(strSQL)

If rs.EOF Then
   'What to do when not found
Else
   'What to do when found
End If
rs.Close
Set rs = Nothing

I hope I got the syntax right, I haven't tested.

 

[rlatham]

Thanks Remou...
I modified a few variables to fit my column names.

One more question:
How do I obtain the current record number for the record if found ?

 

[Remou]

Record Number? As in an ID? rs!ID.

 

[PHV]

rlatham, in RDBMS no record numbers but Primary Keys ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rlatham]

By CurrentRecord number I mean the number displayed in the Navigation bar if you had a form or report open. The relative number of the record in the table.

 

[PHV]

If the form based on the query is already open then have a look at the RecordsetClone, FindFirst, NoMatch and Bookmark methods/properties of the Form/DAO.Recordset objects.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rubbernilly]

You are returning a limited recordset based on your WHERE statement, so you "record number" in that recordset will be different than that record in the sea of all of your data. Anything meaningful will have to be a field in your record itself so that it is constant no matter the number of other records or its position in that recordset.

To reference a field, then, use Remou's suggestion, where "ID" is your field name.

HTH

 

[Remou]

As PHV said, there isn't one. If you opened a form or report you would get a different number each time, depending on such things as sort order. The recordset above could return any number of records from the query. The only way you can match the information back to the query is with an ID of some sort.

 

[rlatham]

Thanks, I understand the concept of Primary Key, but the table I am using does not have one. ( I didn't create it)
=(

However, the query that would feed a form if used for the FIND purpose, is static. I will always sort the same way.
I understand that each time the form is opened it will give a different CurrentNumber, that is fine.

Basically, what I need is a limited version of the FIND (binoculars) tool, where I only allow the user to enter the keyword, and not choose the field, etc.

 

[rubbernilly]

You can look at the AbsolutePosition property of the recordset, but it is really a meaningless value once the recordset closes.

 

[Remou]

Hm, just a thought, have you looked at DoCmd.FindRecord?

 

[rlatham]

I have tried DoCmd.FindRecord, but since I don't know a lot of VB I mostly do not know what I'm doing.

I also tried AbsolutePosition as rs.AbsolutePosition and got a 0.

Thank you.

 

[Remou]

Ok. You have a form that shows the results of a query. Add a command button called, let's say, cmdFind. Add this to the click event of that button:

Dim rs As DAO.Recordset
Dim lngNumber As Long

lngNumber=InputBox("Number?")
Set rs = Me.RecordsetClone

'SomeField is the name of the field in the table
rs.FindFirst "SomeField =" & lngNumber

If rs.NoMatch Then
   MsgBox "Could not find " & lngNumber & "."
Else
   MsgBox "Found it. Click OK to go there."
   Me.Bookmark = rs.Bookmark
End If
rs.Close
Set rs = Nothing

See if this does what you want, if not, get back to me / here.

 

[rubbernilly]

rlatham,

realize that Remou's suggestion has you searching in a particular field for a particular value. That's the preferred method (if you can identify a unique value, otherwise you will return the first).

If you just want to go to the third record, I believe that you can say:

Me.Form.Recordset.AbsolutePosition = 2

or, for your case:

me.form.recordset.AbsolutePosition = InputBox("Number?")

But I'd have to pull out a database to test that for sure.

 

[rlatham]

Remou, your last suggestion was the perfect fit!
That is exactly what I needed.

I only had to change the variables and DAO for ADOBC, tested and it worked.

I have learned something new today! :

Thanks a bunch to you and everyone else who helped to make my day!! :-D