How to repeat big chunk of code? 3

[tobymom]

I have codes looking like followings. It is IF-THEN and While Loop.
I have about 100 lines of code being repeated twice. (exactly same code) How can I make this simpler?

I can use Function or Sub and pass variables but is there any other way to do this?

Thank you so much for your time. Always appreciate experts help here..

======= code sample follows...

Dim curdb As Object
Dim rst As DAO.Recordset

Set curdb = CurrentDb
Set rst = curdb.OpenRecordset("meme")

strwhere = "[meme] = " & Me!id

rst.MoveFirst
rst.FindFirst strwhere

If Not rst.NoMatch() Then

'** about 100 lines of code goes here

rst.FindNext strwhere
While Not rst.NoMatch()

'** about 100 lines of the same code goes here

rst.FindNext strwhere
Wend

Else
MsgBox "no match"
End If
rst.Close
Set rst = Nothing

 

[Remou]

I would say get rid of the marked lines.

Dim curdb As Object
Dim rst As DAO.Recordset

Set curdb = CurrentDb
Set rst = curdb.OpenRecordset("meme")

strwhere = "[meme] = " & Me!id

rst.MoveFirst
rst.FindFirst strwhere

[s]If Not rst.NoMatch() Then

    '** about 100 lines of code goes here
    
    rst.FindNext strwhere[/s]
    While Not rst.NoMatch()

       '** about 100 lines of the same code goes here
   
     rst.FindNext strwhere
    Wend
    
Else
   MsgBox "no match"
End If
rst.Close
Set rst = Nothing

 

[KenReay]

Hi

How about

Dim curdb As Object
Dim rst As DAO.Recordset

Set curdb = CurrentDb
Set rst = curdb.OpenRecordset("SELECT * FROM meme WHERE [Meme] = " & MeIid & ";")

If rst.Recordcount > 0 Then
While Not rst.EOF

'** about 100 lines of the same code goes here

rst.Movenext
Wend

Else
MsgBox "no match"
End If
rst.Close
Set rst = Nothing


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[JoeAtWork]

I like Ken Reay's approach of opening the recordset pre-filtered (why grab all records when you don't need them?).

However, I would advise not using RecordCount, especially if you have not done a MoveLast to make sure it is populated. The no-fail way to find out if a recordset is populated is to check for (rs.BOF And rs.EOF).

Instead of

If rst.Recordcount > 0 Then

I would use

If Not(rst.BOF And rst.EOF) Then

 

[tobymom]

Remou, KenReay, JoeAtWork,

Thank you all for your help! There are still so many things to learn for me. You guys are the best!

Regards,

Tobymom