VBA search code

[Duncan]

Having a little trouble with some VBA code designed to grab a number from a form (MRN) then use this data to find the corresponding data in a table making a small change to that record (changing a checkbox to true). The table opens but only the first record is updated. The code works well as a standard VBA search code on another form. Any help great full.

Dim SearchMRNList As Variant

SearchMRNList = Me!MRN [name of textbox on form holding search data]

myRSInfectionDC.Open "tblClientAssessmentCDInfection", , adOpenDynamic, adLockOptimistic

DoCmd.GoToControl ("txtMRN") [name of column to be searched]
DoCmd.FindRecord SearchMRNList

With myRSInfectionDC
!DC = True
.Update
End With

[Robeen]

Your code is only designed to update the first record it finds.
You might consider including a recordset object and looping through the records in your table to update the rows which have matching data.

[Duncan]

Thanks for the reply. You are correct so I have spent the last couple of days searching the web and can’t find a simple example to try. Most relate to linking to excel or performing various tasks that I do not require. I’m using ADO recordsets. Any leads to a website for some code would be great.

[Robeen]

Hi Duncan,

Here's a VBA function that I created to use as an example of looping through rows of data in a Table and then doing something with particular values from each row.
Let me know if you have any questions.

'Get values from a table using a query in VBA.
Function Get_DB_Values()
'Assumes that you have a Table1 and that Field1 is Text and Field2 is a Number.
' . . . just for testing purposes.
' . . . You should use your actual Table & field names & set your variables to match the data types of your table fields.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strField1 As String
Dim intField2 as Integer
On Error GoTo Error_Handle
   
Set db = CurrentDb
strSQL = "Select * From [Table1]"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
With rs
'This Do While loop goes through all the records in strSQL.
Do While Not rs.EOF
strField1 = rs![Field1]
intField2 = rs![Field2]
'I put this little If Then here with a MsgBox . . . to look at the values 
'In your case - you will need to put in logic here to update the current record.
If intField2 = 5 Then
    'MsgBox strField1 & ", " & intField2
End If
.MoveNext                               'Move to next record in recordset.
Loop                                    'Back to 'Do While' to check if we are at the end of the file.
        
Exit_Get_DB_Values:
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    Set db = Nothing
    Exit Function
    
Error_Handle:
    Resume Exit_Get_DB_Values
End With
End Function

Hope this helps!
All the best.

[pbaldy]

Given what you're doing, the most efficient method would be an update query:

CurrentDb.Execute "UPDATE tblClientAssessmentCDInfection SET DC = True WHERE MRN = " & Me.MRN, dbFailOnError

which assumes the MRN field is numeric. If you needed a loop, you'd want to loop just on the desired records by opening the recordset on an SQL statement that restricted the records returned to those desired. Tweaking Robeen's code:


strSQL = "Select * From [Table1] WHERE MRN = " & Me.MRN

You don't want to loop a table of a million records to update 5.

[Duncan]

Thank you for your response. The update query works just fine in updating multiple records. Before I mark this thread as solved is there any advantage in looping though the record sets using the VBA code opposed to the simple update query.

[pbaldy]

No the query is most efficient, as I said. I'd only use the loop if a query couldn't do the job.