Subform RecordSource Error 3008

[thelonelyghost]

Just searched google and this website but I couldn't come up with an answer to this.

Basic Information
Software: Microsoft Access 2000
OS: Windows XP Professional SP3
Problem: Error 3008 (locked table, [tblReturnLog]) when trying to modify recordsource of subform.

Private Sub btnSearch_Click()
 
'Changes recordsource of subform. subform name: frmSubform
' subform source form: [frmTEMP2]
Me.frmSubForm.Form.RecordSource = BuildSQLStr
 
End Sub

BuildSQLStr is a function that I've debugged and it's working correctly. It outputs a string containing a SELECT statement with WHERE conditions. Before anyone objects, I need it to be formatted this way (as opposed to using filters) to address potential bandwidth issues.

What am I doing wrong? The parent form, a filter form, is bound to a different table and only a combo box reads the field names of the locked table, thereby enabling an ORDER BY option for the results of the filter. Let me know if there's any more info that you need.

[thelonelyghost]

@NeoPa:

That depends. Controls are not actually dates as such, even though they can contain data that represents dates. If you absolutely know that it will always be formatted that way, then you needn't format it again. The formatting is important for date/time data which, although it may display in various ways, is actually stored as a real number. The important thing at the end of it is just that the string of characters in your SQL string is formatted correctly. Does that make sense?

I see what you mean, and I'll have to figure out a way to ensure it stays in the MM/DD/YYYY format on the form. I'm familiar with the date being stored as a real number on if the datatype is date/time, but as you said, it only matters if the SQL string is formatted correctly.

I had planned to post this (Debugging in VBA) earlier but I'm not sure I did now.

You did post that earlier. If not on this thread, then on one of the other ones I started. I read through that before and concluded (after little more than a glance) that it was a description of the debugging tool, not instructions on how to use it or the frame of mind needed to do the necessary debugging. I'm looking through it again now because that judgement seems a bit harsh when compared to the quality of your other guides. It's probably just that I missed something.

EDIT: I found an example. In the section about the Watch Pane it talks about the functionality existing, not about how to use it. This isn't the only example available either.

[NeoPa]

You're quite right. I'm afraid that the full scope of debugging seemed a little much. Covering all the bases could have taken just too long.

What I did do, or more precisely my intention was to, outline the framework available there so that anyone with any previous debugging experience could find themselves at home quite quickly. There are probably as many debugging techniques as there are developers, so explaining How to Debug was not something I felt was too appropriate. I have my particular way of doing things, but that may not suit all.

If you follow the Watch Pane link, then open this up in your own project, you will be able to use the Right-Click menu to see some of the items introduced. I would hope that this would give you a starting point from where you could be aware of, and explore, the facilities available to you.

If the whole concept is foreign to you though, I'm happy to answer specific questions related to the article. Feel free to create a related thread and direct me to it. I will help where I can and will probably add bits into the article as and when it seems appropriate.

[thelonelyghost]

Project update: After much debugging and more help from the community, I've come to realize that my use of filters would not affect the network traffic (bandwidth) the same as if I used recordsource only. After successfully debugging the SQL, I injected it into the WHERE section of a SQL SELECT statement and set that as the recordsource. On startup, the form loads 5-10 seconds faster when referencing a table on the local machine, leading me to believe it'll be that much faster in a client/server setting.

I will post my sourcecode w/ documentation here if I get a chance. Doing this should ensure others won't have to stumble nearly as much as I did. Thanks everybody!

[patjones]

@thelonelyghost
I'm really glad to hear it. You'll find that it cuts down on the amount of coding that you have to do as well. Definitely post your final results when you have a chance!

Pat

[thelonelyghost]

I finally have a compilation of the source code for the application I've been using. You do have to edit some of the code to fit to your situation, but that info lies between the labels "Begin User Settings" and "End User Settings", located at the beginning of SQLFilter() and BuildSQLStr(). Let me know if anyone has any questions, comments, or suggestions for the code. I'm more than willing to help!

Note: The Access 2000 Replace() workaround is purely optional. If Replace() works on your version of Access, fix its application on line 15 of SQLFilter().

Disclaimer: This disclaimer is not to be modified or removed except when the source appears in any Visual Basic console. The following code is not to be sold at any price and will be distributed freely and without discrimination. Please refer to the latest copy of the General Public License (currently version 3.0) at http://www.gnu.org/copyleft/gpl.html for further disclaimer.

"lib" Module:

Transformation function in module "lib" for inputs on form:

Public Function FormToSQL(strField As Variant, strValue As Variant, strDelim As Variant, strOperator As Variant) As String 
 
  If strValue = "''" Or strValue = "" Or IsNull(strValue) Then 
  Exit Function 
  Else 
    strValue = Trim(strValue) 
    If strValue Like "##/##/####" Then 'Normally determines if value is a date, but LIKE shouldn't be used with dates anyway. 
      strValue = "#" & strValue & "#" 
    Else: If Not IsNumeric(strValue) Then strValue = "'" & strValue & "'" 
    End If
 
'Combines inputs for 
      Select Case strOperator
    Case "EQ"
      strOperator = " = "
    Case "GT"
      strOperator = " >= "
    Case "LT"
      strOperator = " <= "
    Case "LK"
      strOperator = " LIKE "
    Case Else
      Exit Function
      End Select
 
    FormToSQL = strDelim & "[" & strField & "]" & strOperator & strValue 
 
  End If 
 
End Function

.
Only needed if using Access 2000 (see above note):

'*********Access 2000 Replace() Workaround*********
Public Function StrReplace(strHaystack As String, strNeedle As String, strReplaceWith As String, intRepeat As Integer) As String
 
Dim init As Long, i As Integer
 
If Len(strHaystack) < 1 Or Len(strNeedle) < 1 Or intRepeat > 0 = False Then 'Failsafe for illegal inputs
    Exit Function
Else 'General declarations of variables
  init = InStr(strHaystack, strNeedle)
  i = intRepeat
End If
 
Do While init > 0 And i > 0
  strHaystack = Left(strHaystack, init - 1) & strReplaceWith & Right(strHaystack, Len(strHaystack) - Len(strNeedle) - init + 1)
 
  'Maintenance for While-loop
  i = i - 1
  init = InStr(strHaystack, strNeedle)
Loop
 
StrReplace = strHaystack
 
End Function

.
Form's Module:

Header:

Option Compare Database
 
'**********Begin User Settings**********
'total number of control boxes that are related to the fields being queried
Private Const numberOfInputs = 8
'**********End User Settings**********
 
Private aFld(1 To 2, 1 To numberOfInputs) As String, aVal(1 To numberOfInputs) As Variant, aOp(1 To numberOfInputs) As String
Private strTableName As String

Transforming a form's inputs into one string of SQL for filter (SQLFilter) or SELECT application (BuildSQLStr):

'************Creates Filtering criteria in SQL syntax************
Private Function SQLFilter() As String
 
Dim strSQLContent As String, strDelim As String
 
'Dim aFld(1 To numberOfInputs) As String, aVal(1 To numberOfInputs) As Variant, aOp(1 To numberOfInputs) As String
 
'******************Field Names, Control Sources, and Operators******************
'****************Begin User Settings****************
'Sets the associated table of all field names to strTableName by default
For i = 1 To numberOfInputs
  aFld(1, i) = strTableName
Next
 
'********Notes********
'aFld : name of the field in [tblReturnLog]
'aVal : value of the field to look for in [tblReturnLog]
' aOp : abbreviation of operator in SQL, i.e. "LK" = LIKE
'*********************
aFld(2, 1) = "OldLogNumber"
aVal(1) = Me.txtOldLogNumber
 aOp(1) = "LK"
aFld(2, 2) = "CTSLogNumber"
aVal(2) = Me.intCTSLogNumber
 aOp(2) = "LK"
aFld(2, 3) = "ReportSentTo"
aVal(3) = Me.txtReportSentTo
 aOp(3) = "LK"
aFld(2, 4) = "CustomerPartNumber"
aVal(4) = Me.txtCustomerPartNumber
 aOp(4) = "LK"
aFld(2, 5) = "DateReceived"
aVal(5) = Format(Me.dtMinDateR, "MM/DD/YYYY")
 aOp(5) = "GT"
aFld(2, 6) = "DateReceived"
aVal(6) = Format(Me.dtMaxDateR, "MM/DD/YYYY")
 aOp(6) = "LT"
aFld(2, 7) = "CompletionDate"
aVal(7) = Format(Me.dtMinDateClosed, "MM/DD/YYYY")
 aOp(7) = "GT"
aFld(2, 8) = "CompletionDate"
aVal(8) = Format(Me.dtMinDateClosed, "MM/DD/YYYY")
 aOp(8) = "LT"
 
'detecting whether to use AND or OR
'based on button 'ANY' or 'ALL'
  strDelim = IIf(Me.btn2SearchSettingsAnyAll = 1, " OR ", " AND ")
'****************End User Settings****************
 
'transforming form inputs into SQL code with subfunction
For i = 1 To numberOfInputs
  strSQLContent = strSQLContent & lib.FormToSQL(aFld(2, i), aVal(i), strDelim, aOp(i))
Next
 
'Clean up the concatenation of all of the fields into a SQL statement fragment
SQLFilter = lib.StrReplace(strSQLContent, strDelim, "", 1)
 
End Function

'*********Builds SELECT statement in SQL*********
Private Function BuildSQLStr() As String
 
Dim strSQLFilter As Variant, strFieldNames As String
 
'*********Begin User Settings*********
strTableName = "tblReturnLog" 'Sets the name of the table to a global variable
'*********End User Settings*********
 
 
strSQLFilter = SQLFilter 'Optimization: calculates value of SQLFilter once
 
'Lists all field names and associated tables in SQL format.  Useful if JOIN is used.
For i = 1 To numberOfInputs
  strFieldNames = strFieldNames & "[" & aFld(1, i) & "].[" & aFld(2, i) & "], "
Next
strFieldNames = Left(strFieldNames, Len(strFieldNames) - 2)
 
'Overall: builds SELECT statement from User Settings
'Line 1: lists the specific name of each column/field to be referenced, opening up ability to allow JOIN
'Line 2: lists criteria to filter by, if there are any
BuildSQLStr = "SELECT " & strFieldNames & " FROM [" & strTableName & "]" & _
               IIf(Len(strSQLFilter) > 0, " WHERE " & strSQLFilter & ";", ";")
 
End Function

[patjones]

I am happy to see that you got it all sorted out; and thanks for posting your solution. Too often, people just never respond as to what worked or what didn't work, diminishing the thread's value.

One small suggestion I have is that you can easily combine GreaterThanAttachedAnd, LessThanAttachedAnd, LikeAttachedAnd into one function. They are all the same except for the comparison operator, so I would simply make one function, put an argument in that allows the user to indicate what comparison they want to perform, and then pick the respective operator using a Case statement or whatever logic structure you prefer.

Pat

[thelonelyghost]

Successful testing resulted in me posting the revised code above. I also modified all dependencies on the old code to fit with the revision.

[thelonelyghost]

This leads into an idea for further revision. I'm looking at using a For-each loop in SQLFilter() for FormToSQL() and setting up three arrays (arrFieldName, arrValue, arrOperator). Does anyone know if this would actually optimize the code, or would it just look prettier?

EDIT: Just did some major code revision to carry out this idea and make it easier to implement JOINs in SELECT statement.