How to find Records in MS access using VBA Code

[Cybermac]

Hi guys pls help, how do i find a records using recordset in microsoft access 2002, Sample pls?

 

[Zion7]

Hello Cybermac,

Using ADO...

Sub OpenADO()

Dim rec As New ADODB.Recordset
    
rec.Open "tblCountries", CurrentProject.Connection, adOpenStatic, adLockOptimistic

With rec
   While Not .EOF
      Debug.Print !txtCountry
   
    .MoveNext
    Wend
End With
rec.Close: Set rec = Nothing
End Sub

Replace "tblCountries" with the name of the table, you want to open. Replace "txtCountry", with the name of the field, you want to view.

This will open a recordset, & loop through each record & print the desired field, in the debug window.

Hope this helps, good luck!

 

[bodmin]

You could also try using the recordset function findfirst which will search through the record set to find any records where there is a specific value.

Dim rs As Recordset
Dim Find As String
Dim ID_Number As Integer

Find = "[Identification] = " & ID_Number

rs.FindFirst Find

where ID_Number is a variable used to store the value you are searching for.

Hope this helps
Andi

 

[tdrBates]

I'm trying to change the !LINEABBR field to "18", but this
is not working. Can anyone figure out what's wrong with
this function? Sample records are below:

BLOCKNAME LINEABBR NODEABBRA NODEABBRB
004-02 4 AIRP MLK
004-02 4 AIRP MLK
004-02 4 AIRP MLK
004-02 4 AIRP MLK
004-02 4 SPRI MLK
004-02 4 SPRI MLK
004-02 4 SPRI MLK
004-02 4 AIRP MLK
004-02 4 SPRI MLK
004-02 4 VARG H183
004-02 4 IH35 E7TH
004-02 4 IH35 E7TH
004-02 4 IH35 E7TH
004-02 4 IH35 E7TH
004-02 4 PVAL E7TH
004-02 4 PVAL E7TH
004-02 4 PVAL E7TH
004-02 4 PVAL E7TH
004-02 4 PVAL E7TH
004-02 4 TECH CENTR
004-02 4 TECH CENTR
004-02 4 TECH CENTR
004-02 4 TECH CENTR

Public Function basPairedRoutes()

Dim dbs As DAO.Database
Dim rstSrc As DAO.Recordset

Set dbs = CurrentDb
Set rstSrc = dbs.OpenRecordset("WithoutPairedRoutes", dbOpenDynaset)

With rstSrc
While Not .EOF
If ((!BLOCKNAME = "004-02" And _
!NODEABBRA = "SPRI" And !NODEABBRB = "MLK")) Then
'Edit Record
.Edit
!LINEABBR = "18"
.Update
End If
.MoveNext
Wend
End With
rstSrc.Close
Set rstSrc = Nothing
End Function

 

[PaulF]

Try This

Dim dbs As DAO.Database
Dim rstSrc As DAO.Recordset

Set dbs = CurrentDb
Set rstSrc = dbs.OpenRecordset("WithoutPairedRoutes", dbOpenDynaset)

With rstSrc
If .RecordCount>0 then
.MoveFirst
Do While Not .EOF
If ((!BLOCKNAME = "004-02" And _
!NODEABBRA = "SPRI" And !NODEABBRB = "MLK")) Then
'Edit Record
.Edit
!LINEABBR = "18"
.Update
End If
.MoveNext
Loop
End If
End With
rstSrc.Close
Set rstSrc = Nothing
End Function


PaulF