Access application gives 'Not responding' at this code line. Having difficulty in resolving

[chazrab]

The code below in the Form Load Event of form SEARCHF if, works correctly, supposed to display records in two textboxes on the Form. . The first on the left, named txtMatchedVerses displays records from a table named tblSearchResults as shown in the top code block. That code block works great every time and the records are
displayed in txtMatchedVerses as shown in the image below.

The problem is with the second code block. This line is preventing further execution and I have no idea why:

Me.Controls("Textbox2").Value = IIf(Len(Me.Controls("Textbox2").Value) = 0, rs.Fields(1).Value, Me.Controls("Textbox2").Value & vbNewLine & rs.Fields(1).Value)

This is the entire code block in the Form Load Event:

Private Sub Form_Load()
Me.txtSearchCriteria = gSavedValue
Dim cnt As Long
 If DCount("*", "tblSearchResults") > 0 Then
        strSQL = "SELECT * FROM tblSearchResults;"
        Set db = CurrentDb: Set rs = db.OpenRecordset(strSQL)
        rs.MoveFirst
        Do Until rs.EOF
            Me.Controls("txtMatchedVerses").Value = IIf(Len(Me.Controls("txtMatchedVerses").Value) = 0, rs.Fields(0).Value, Me.Controls("txtMatchedVerses").Value & vbNewLine & vbNewLine & rs.Fields(0).Value)
            rs.MoveNext
        Loop
        Set rs = Nothing: Set db = Nothing
    Else
    End If
cnt = DCount("*", "tblSearchResults")
Me.Totrows.Value = cnt

Dim cnt2 As Long
 If DCount("*", "maktxtbx2tbl") > 0 Then
       strSQL = "SELECT * FROM maktxtbx2tbl;"
        Set db = CurrentDb: Set rs = db.OpenRecordset(strSQL)
       rs.MoveFirst
      Do Until rs.EOF
         Me.Controls("Textbox2").Value = IIf(Len(Me.Controls("Textbox2").Value) = 0, rs.Fields(1).Value, Me.Controls("Textbox2").Value & vbNewLine & rs.Fields(1).Value) --->this is the line that gives 'Not responding' errror.
         rs.MoveNext
      Loop
        Set rs = Nothing: Set db = Nothing
    Else
  End If
cnt = DCount("*", "maktxtbx2tbl")
Me.totrows2.Value = cnt2
End Sub

I don't know why the top code block runs and populates the txtMatchedVerses textbox like a charm, but has a problem with this line on the second code block:

Me.Controls("Textbox2").Value = IIf(Len(Me.Controls("Textbox2").Value) = 0, rs.Fields(1).Value, Me.Controls("Textbox2").Value & vbNewLine & rs.Fields(1).Value)

The image below shows how both textboxes should look when the Form Load code for both textboxes works correctly.

My apologies for a lengthy post. Could not describe problem completely with less text and images.

I would greatly appreciate anyone's help. Been at trying to solve this mystery for the past 24 hours.

cr

 

[chazrab]

Problem solved(at least I think it is. Loads both texboxes and runs in 4-5 tries. . It did not like putting these two statements on one line

Set db = CurrentDb : Set rs = db.OpenRecordset(strSQL2)

I changed it like this:

Set db = CurrentDb
Set db = CurrentDb: 
Set rs = db.OpenRecordset(strSQL2)

and removed the colon
The whole code that "seems" to work now is:

Private Sub Form_Load()
Me.txtSearchCriteria = gSavedValue
Dim cnt As Long
 If DCount("*", "tblSearchResults") > 0 Then
        strSQL = "SELECT * FROM tblSearchResults;"
        Set db = CurrentDb: Set rs = db.OpenRecordset(strSQL)
        rs.MoveFirst
        Do Until rs.EOF
            Me.Controls("txtMatchedVerses").Value = IIf(Len(Me.Controls("txtMatchedVerses").Value) = 0, rs.Fields(0).Value, Me.Controls("txtMatchedVerses").Value & vbNewLine & vbNewLine & rs.Fields(0).Value)
            rs.MoveNext
        Loop
        Set rs = Nothing: Set db = Nothing
    Else
    End If
cnt = DCount("*", "tblSearchResults")
Me.Totrows.Value = cnt

Dim cnt2 As Long
Dim strSQL2 As Variant
Dim db2 As Recordset2
Dim rs2 As Variant
Dim totrows2 As Long
 If DCount("*", "maktxtbx2tbl") > 0 Then
       strSQL2 = "SELECT * FROM maktxtbx2tbl;"
        Set db = CurrentDb
        Set rs = db.OpenRecordset(strSQL2)
       rs.MoveFirst
       Do Until rs.EOF
        Me.Controls("Textbox2").Value = IIf(Len(Me.Controls("Textbox2").Value) = 0, rs.Fields(1).Value, Me.Controls("Textbox2").Value & vbNewLine & vbNewLine & rs.Fields(1).Value)
        rs.MoveNext
       Loop
        Set rs2 = Nothing: Set db = Nothing
  Else
  End If
cnt2 = DCount("*", "maktxtbx2tbl")
Me.totrows2.Value = cnt2
End Sub

cr

 

[chazrab]

Premature reply. Still giving me fits with 'Not responding' error. Please help
cr

 

[welshgasman]

Have you even walked your code with F8 and breakpoints?

 

[Micron]

The message is not about crashing, it is about Access being busy processing something and interaction with the application is not possible at the time.

Not sure why in your case except to suggest that you might speed things up by not using so many domain aggregate and vba functions (DCount is a domain aggregate function). F'rinstance, run your sql and then check the rs.count - much simpler and no point in testing for records then loading them. Same with your IIF on a form control. If Textbox2 = "" (in some cases you need to test for Null as well) then there is nothing in it, so why run a function to test the length of contents if you don't care about the length, just whether or not anything is there. Also, assign control values to variables rather than look them up in expressions, which need to evaluate every time they're used. A variable that holds a control value is in memory and is much faster to evaluate as opposed to fetching the values.

BTW, a MoveFirst with no records will generate an error, which won't happen the way you have it but can if you do what I'm suggesting but my way is more efficient for sure. So all you need to do is wrap things in an If block. IMO the best test is #3 here

Microsoft Access tips: VBA Traps: Working with Recordsets

Common mistakes developers make when working with Recordsets in a Microsoft Access database

allenbrowne.com

 

[chazrab]

Have you even walked your code with F8 and breakpoints?

No - I not - and that is what I should have done. I changed the following from

Set db = CurrentDb: Set rs = db.OpenRecordset(strSQL)

to two lines and it runs fine now. Apparently it didn't lie having two statements on 1 line separated by a :

Set db = CurrentDb
 Set rs = db.OpenRecordset(strSQL)

 

[chazrab]

The message is not about crashing, it is about Access being busy processing something and interaction with the application is not possible at the time.

Not sure why in your case except to suggest that you might speed things up by not using so many domain aggregate and vba functions (DCount is a domain aggregate function). F'rinstance, run your sql and then check the rs.count - much simpler and no point in testing for records then loading them. Same with your IIF on a form control. If Textbox2 = "" (in some cases you need to test for Null as well) then there is nothing in it, so why run a function to test the length of contents if you don't care about the length, just whether or not anything is there. Also, assign control values to variables rather than look them up in expressions, which need to evaluate every time they're used. A variable that holds a control value is in memory and is much faster to evaluate as opposed to fetching the values.

BTW, a MoveFirst with no records will generate an error, which won't happen the way you have it but can if you do what I'm suggesting but my way is more efficient for sure. So all you need to do is wrap things in an If block. IMO the best test is #3 here

Microsoft Access tips: VBA Traps: Working with Recordsets

Common mistakes developers make when working with Recordsets in a Microsoft Access database

allenbrowne.com

Micron - I'm sure your offer to help is well meant. Your reply indicates your advanced level of experience with Access.
I am not at your level of experience, but would greatly benefit by it.
I try to explain issues with coding the in best and most complete way that I know.
Am very new to Access VBA - which is NOT the same as Excel VBA, which I know from past posts you know well also. .

I'm working on trying to develop the Next Record button now.
The record results from source table of this app did not have an ID originally - my inexperience, so I went back and added it.

I just need to use this ID to develop Next button code - which tells me that Access needs to know what record(verse) in the large
textbox the blinking cursor is on, identify it back to the source table record, move to the next record in the source table and transfer, for
lack of a better term, that move back to the location of the blinking cursor in the textbox - and move it down.
That does not seem that complicated or difficult to achieve - if you know what code to write to tell Access what to do.

As always, thanks for all your valuable help.
cr