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

C

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
884
Office Version
  1. 365
Platform
  1. Windows
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:
Code: 
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:
Code: 
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:
Code: 
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
 

Attachments

  • WHEN FORM LOAD CODE WORKS CORRECTY FOR BOTH TEXTBOXES.png
    WHEN FORM LOAD CODE WORKS CORRECTY FOR BOTH TEXTBOXES.png
    140.1 KB · Views: 5
  • SOURCE TABLE FOR 1st TEXTBOX.png
    SOURCE TABLE FOR 1st TEXTBOX.png
    40.1 KB · Views: 6
  • SOURCE TABLE FOR 2ND TEXTBOX - Textbox2.png
    SOURCE TABLE FOR 2ND TEXTBOX - Textbox2.png
    92.4 KB · Views: 5

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
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
Code: 
Set db = CurrentDb : Set rs = db.OpenRecordset(strSQL2)
I changed it like this:
Code: 
Set db = CurrentDb
Set db = CurrentDb: 
Set rs = db.OpenRecordset(strSQL2)
and removed the colon
The whole code that "seems" to work now is:
Code: 
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
 
Upvote 0
Premature reply. Still giving me fits with 'Not responding' error. Please help
cr
 
Upvote 0
Have you even walked your code with F8 and breakpoints?
 
Upvote 0
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
 
Upvote 0
welshgasman said:
Have you even walked your code with F8 and breakpoints?
Click to expand...
No - I not - and that is what I should have done. I changed the following from
Code: 
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 :
Code: 
Set db = CurrentDb
 Set rs = db.OpenRecordset(strSQL)
 
Upvote 0
Micron said:
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
Click to expand...
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
VBA code for Next button to move to each record in Textbox1
Replies
2
Views
288
welshgasman
welshgasman
Guinaba
  • Question
Writing back from recordset to table or query
Replies
7
Views
3K
Guinaba
Guinaba
P
  • Question
Access VBA - How to split data in one field and insert into other fields in an existing table
2
Replies
10
Views
6K
Peter_W
P
S
  • Question
VB Code to Open Save and Close xls file
Replies
0
Views
641
santa12345
S
J
  • Question
Checkbox Form-Table VBA Update Code
Replies
1
Views
2K
Micron
M

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top