Access - Forms - SQL Server - Search Results not displaying

[Predro]

Like most “problems” I have inherited a MS Access DB which contains forms and is linked to a SQL server backend and there is no MS access specialist here, so I am trying to help the business. I hope I have posted this in the correct area of the forum. (Administrators, please feel free to move to the correct area if required)

The Main Menu (F000_Main_Menu) has a search functionality where search criteria can be entered by the user.

[IMG]file:///C:/Users/pnorman1/AppData/Local/Temp/msohtmlclip1/01/clip_image002.jpg[/IMG]

After selecting the search button, a new form (F005_List_Work_Orders_Selected) is displayed and should show the search results in the sub form at the bottom of the form. Unfortunately, the results are not showing.

I rate my Access skills as intermediate, but my Form and VBA skills as poor (never really had to do this). I know how to use the debugger in VBA so I am able to follow the code and see the values

Below is the VBA code after clicking Search. It builds up varWhere with each search criteria


Private Sub B_Search_Click()
Dim varQuery As String
Dim varWhere As String

' On Error GoTo Error_routine

'Reset the existing search criteria ready for the new search criteria
If [Forms]![F000_Main_Menu]![txtSearchQuery] <> "" Then
DoCmd.Close acForm, "F005_List_Work_Orders_Selected", acSaveNo
[Forms]![F000_Main_Menu]![txtSearchQuery] = ""
[Forms]![F000_Main_Menu].Refresh
varQuery = ""
varWhere = ""
End If

' Start concatenating all the different search criteria into one statement

' Work Order ID
If [Forms]![F000_Main_Menu]![txtWorkOrderID] <> "" Then
varWhere = varWhere & "([Work Order ID] = " & [Forms]![F000_Main_Menu]![txtWorkOrderID] & ")"
End If

' Region
If [Forms]![F000_Main_Menu]![txtRegion] <> "" Then
If varWhere <> "" Then
varWhere = varWhere & " AND "
End If
varWhere = varWhere & "([Region] = '" & [Forms]![F000_Main_Menu]![txtRegion] & "')"
End If

' Area
If [Forms]![F000_Main_Menu]![txtArea] <> "" Then
If varWhere <> "" Then
varWhere = varWhere & " AND "
End If
varWhere = varWhere & "([Area] = '" & [Forms]![F000_Main_Menu]![txtArea] & "')"
End If


(I have removed the rest of the code from here for ease of reading in the forum, it just follows the same format until all available search criteria have been reviewed and added if required)


' Prepare SQL query
If varWhere = "" Then
varWhere = "[Work Order ID] > 0"
End If

'To see the sql on the screen you need to make the txtSearchQuery box Visible = Yes and then rerun.
[Forms]![F000_Main_Menu]![txtSearchQuery] = "SELECT * FROM T010_Work_Order WHERE (" & varWhere & ") ORDER BY [Proposed Date];"
[Forms]![F000_Main_Menu].Refresh


At this point, the values can been seen below and all is in order
[IMG]file:///C:/Users/pnorman1/AppData/Local/Temp/msohtmlclip1/01/clip_image004.jpg[/IMG]

The code then goes into ResetWorkOrderId and OpenForm, which calls Form_Open and Form_Activate (in that order)

Call ResetWorkOrderID

Public Function ResetWorkOrderID()

globCurrentWorkOrderID = 0

End Function



DoCmd.OpenForm "F005_List_Work_Orders_Selected"
Private Sub Form_Open(Cancel As Integer)

End Sub

Private Sub Form_Activate()
' Display records or redisplay the list of records in case a new record was created since the last time the list was displayed
[Forms]![F005_List_Work_Orders_Selected].RecordSource = [Forms]![F000_Main_Menu]![txtSearchQuery]
[Forms]![F005_List_Work_Orders_Selected].Refresh


At this point the values are all correct including RecordSource
[IMG]file:///C:/Users/pnorman1/AppData/Local/Temp/msohtmlclip1/01/clip_image006.jpg[/IMG]

' navigate back to the selected/newly created work order in the list
If globCurrentWorkOrderID > 0 Then
[Forms]![F005_List_Work_Orders_Selected].Recordset.FindFirst "[Work Order Id] = " & globCurrentWorkOrderID
End If

End Sub

Exit Sub

Error_routine:
MsgBox "F000_Main_Menu - Search Button - Error: " & Err.Number & " - " & Err.Description
Exit Sub

End Sub





The form F005_List_Work_Orders_Selected is now displayed on a new tab, with F000_Main_Menu also still displaying on it own tab. F005_List_Work_Orders does not show any results even though there are records meeting the search criteria


[IMG]file:///C:/Users/pnorman1/AppData/Local/Temp/msohtmlclip1/01/clip_image008.jpg[/IMG]


[IMG]file:///C:/Users/pnorman1/AppData/Local/Temp/msohtmlclip1/01/clip_image010.jpg[/IMG]


Is it me, the code or is there a problem with the query when it goes to SQL server?


Any help is greatly appreciated

If the screenshots do not come up I have attached a zip file of the same storyline so you can view them

Thanks

[ssanfu]

Welcome to the forum.

First, when you post code, please use code tags. (Click the # in the Quick Reply Menu)

Next, does the code compile and execute? After looking at the code, as shown in Post #1, should not compile.

Are the first two lines at the top of the module

Option Compare Database
Option Explicit

???

I can't see where it would compile because you have FUNCTIONS inside a SUB.

Would you post the FE so we can see the actual code??
(Do a "Compact and Repair" then compress the dB before attaching the file.)

[Predro]

Sorry about the # I wrote it all up in word first
The code compiles and execute
Yes Compare and Explicit are there
This is a work DB, so please be carefulTrafficControl_v4_5c_Frontend_TMSDEV - orig - PN.zip

[Minty]

There is little to no point in creating Functions in separate modules that are completely form specific.
Simply make them Subs in the forms module, as that's where they will be called from and it makes debugging easier.

You have a lot of extraneous code / typing in your VBA.
This for instance

 [Forms]![F000_Main_Menu]![txtDepot] = ""

Could simply be

Me.[txtDepot] = ""

When you are referring to the current form object then simply use Me.
It will also allow intellisense to kick in and highlight any errors.

Because of the linked tables I can't easily make your form work.
The easiest route to filter your secondary forms record set, would be to open the form with the where clause set on the form open command from the first form.

Add a Debug.Print varWhere before you open the form.

All of these

Private Sub Additional_Fees_AfterUpdate()


'   Update the "Last updated by" field
    [Forms]![F010_Create_Delete_Work_Order]![Last updated by] = GetLogonName()
    [Forms]![F010_Create_Delete_Work_Order]![Last updated date] = Date


End Sub

Could be replaced by a single form before update event, and allow you to validate.

[Predro]

Ta for that. Unfortunately I inherited the DB. I have debugged the varWhere and all is in order. See original attachment

[ssanfu]

OK, I want to say that I understand that this is a dB that you inherited.... but there are a lot of things that need/should be fixed (IMHO). So this is in no way directed at you.


I converted the linked tables to local tables. That took care of the start up errors.
There is a control on the main form for "Project Num", but there is not a field in any table for "Project Num"!

I don't like using compound primary keys - every table has an autonumber field that I use as the PK field. (See Microsoft Access Tables: Primary Key Tips and Techniques)
As minty said, there are lots of things in the code that could be cleaned up. I would also convert the macros to VBA code - I never use macros.


The first thing I would do is remove ALL SPACES in all object names.


In the module "Rate Functions", in the Function AddRates, you have this snippet of code:

<snip>
        If (
           (rT120![Supplier Num] = Null) _ 
        Or (rT120![ContractArea] = Null) _ 
          Or (rT120![Start Date] = Null) _
            Or (rT120![End Date] = Null) _
             Or (rT120![Rate No] = Null) _
                Or (rT120![Rate] = Null) _
                 Or (rT120![UOM] = Null) _
            ) 
        Then
            iCntNull = iCntNull + 1
            GoTo LabelNextRecord
        End If
<snip>

You CANNOT test if a control/field = NULL! The If() test will ALWAYS be FALSE because nothing is equal to null, not even null.

If A = NULL --> the result is False
If Null = Null --> the result is False

If you want to test if a control/field is NULL, you should use the IsNull function

  If IsNull(rT120![Supplier Num]) Then
     Bla-bla
Else
Yada-yada
End If

Or check the length of the value

 If Len(Trim(rT120![Supplier Num] & "")) Then
     Bla-bla
Else
Yada-yada
End If

I changed the record source for the form "F005_List_Work_Orders_Selected" and modified the code to open the form

    ' Prepare SQL query
    If varWhere = "" Then
        varWhere = "[Work Order ID] > 0"
    End If

    '    Debug.Print varWhere

    'To see the sql on the screen you need to make the txtSearchQuery box Visible = Yes and then rerun.
'    Me.[txtSearchQuery] = "SELECT * FROM T010_Work_Order WHERE (" & varWhere & ") ORDER BY [Proposed Date];"
'    Me.Refresh

    Call ResetWorkOrderID

    DoCmd.OpenForm "F005_List_Work_Orders_Selected", , , varWhere

EDIT: Tables T100_Regions, T105_Areas and T110_Depots have a field named "Current". "Current" is a reserved word in Access.
(JET reserved (kb248738);SQL Server reserved;ODBC (kb125948);ANSI-92 Reserved (kb287417))


I'm attaching the dB I modified. Would you add data to the tables and put in 5 - 10 work order records for testing?