Query ignoring new records 1

[amberdextrous]

Hello. I'm new to Access and VBA. I'm developing a database for my company and I've basically been teaching myself as I go along, and I've been using A LOT of code help from forums. We have a cabinet full of CDs with archived project files on them and if you need to look at an old project for whatever reason, there's no way to see what's on each disc, which needless to say causes a lot of headaches. So I'm making a database that stores project data and disc data, so that you can do a search by project #, client #, etc. and the database will pull up the names of the disc(s) the data is stored on.

That being said, I have a form that has 2 parts: the top part displays the records and the bottom part has a Search feature that once executed, filters the records showing at the top so that only records matching the search criteria are shown. Here are some screenshots, the first one is after I hit "Show all records", the second is after executing a Search.


My problem is that while the Search function works for the records I already had stored when I started designing the GUI interface for this database, when I added a new record and tried to search for it as a test, the new record did not appear in the results.

This is the SQL code I'm using for the query, SearchQuery2:

SELECT Client.[Client ID], Project.[Project ID], Disc.[Disc ID], Client.[Client Description], Project.[Project Description], Project.[Project Manager]
FROM Client, Project, Disc, [Project On Disc]
WHERE Client.[Client ID]=Project.[Client ID] And Project.[Project ID]=[Project On Disc].[Project ID] And [Project On Disc].[Disc ID]=Disc.[Disc ID] And Client.[Client ID] Like "*" & Forms!SearchForm!EnterClientNumber & "*" And Client.[Client Description] Like "*" & Forms!SearchForm!EnterClientName & "*" And Project.[Project ID] Like "*" & Forms!SearchForm!EnterProjectNumber & "*" And Project.[Project Description] Like "*" & Forms!SearchForm!EnterProjectDescrip & "*" And Project.[Project Manager] Like "*" & Forms!SearchForm!EnterProjectManager & "*" And Disc.[Disc ID] Like "*" & Forms!SearchForm!EnterDiscID & "*"
ORDER BY Client.[Client ID], Project.[Project ID], Disc.[Disc ID];

And this is the code for my form, ViewDataForm:

Option Compare Database

Private Sub CloseButton_Click()
On Error GoTo Err_CloseButton_Click


    DoCmd.Close

Exit_CloseButton_Click:
    Exit Sub

Err_CloseButton_Click:
    MsgBox Err.Description
    Resume Exit_CloseButton_Click
    
End Sub

Private Sub SearchButton_Click()

DoCmd.OpenForm "SearchForm", , , , , acDialog

End Sub

Private Sub SearchButton2_Click()

        'Filter data based on search criteria
        Form_ViewDataForm.RecordSource = "SearchQuery2"
        Form_ViewDataForm.Caption = "Search Results"
        
        MsgBox "Results have been filtered."
End Sub

Private Sub ShowAllButton_Click()

Dim LSQL  As String
    
    'Display all customers
    LSQL = "SELECT Client.[Client ID], Project.[Project ID], Disc.[Disc ID], Client.[Client Description], Project.[Project Description], Project.[Project Manager] FROM (Client INNER JOIN Project ON Client.[Client ID]=Project.[Client ID]) INNER JOIN (Disc INNER JOIN [Project On Disc] ON Disc.[Disc ID]=[Project On Disc].[Disc ID]) ON Project.[Project ID]=[Project On Disc].[Project ID] ORDER BY Client.[Client ID], Project.[Project ID], Disc.[Disc ID]"
    
    Form_ViewDataForm.RecordSource = LSQL
    Form_ViewDataForm.Caption = "View all records"
    
    MsgBox ("All records are now displayed.")
    
End Sub
Private Sub GenerateReportButton_Click()
On Error GoTo Err_GenerateReportButton_Click

    Dim stDocName As String

    stDocName = "rptSearchResults"
    DoCmd.OpenReport stDocName, acPreview

Exit_GenerateReportButton_Click:
    Exit Sub

Err_GenerateReportButton_Click:
    MsgBox Err.Description
    Resume Exit_GenerateReportButton_Click
    
End Sub

Any ideas of why this is happening? I do have forms for adding records, so at first I thought the problem was there, but even when I tried adding data to the standard Access datasheet tables (the "old school" way, haha), the query would not pull up any of my new test records.

 

[amberdextrous]

Nevermind, once again I figured it out myself. I guess the weekend refreshed my brain, because I saw the problem right away when I started working on it today. My SQL code wasn't set to return entries with nulls. I just never realized it because all my entries up to this point have all fields filled in, even though some of the fields are optional. I left those fields blank in all the test entries I did. When I did a test entry today that had all fields filled in, the query worked. So I went back and edited my code:

SELECT Client.[Client ID], Project.[Project ID], Disc.[Disc ID], Client.[Client Description], Project.[Project Description], Project.[Project Manager]

FROM Client, Project, Disc, [Project On Disc]

WHERE Client.[Client ID]=Project.[Client ID]
And Project.[Project ID]=[Project On Disc].[Project ID]
And [Project On Disc].[Disc ID]=Disc.[Disc ID]

And (((Client.[Client ID]) Like "*" & Forms!ViewDataForm!EnterClientNumber & "*")
And ((Project.[Project ID]) Like "*" & Forms!ViewDataForm!EnterProjectNumber & "*")
And ((Disc.[Disc ID]) Like "*" & Forms!ViewDataForm!EnterDiscID & "*")
And (((Client.[Client Description]) Like "*" & Forms!ViewDataForm!EnterClientName & "*") OR ((Client.[Client Description]) Is Null))
And (((Project.[Project Description]) Like "*" & Forms!ViewDataForm!EnterProjectDescrip & "*") OR ((Project.[Project Description]) Is Null))
And (((Project.[Project Manager]) Like "*" & Forms!ViewDataForm!EnterProjectManager & "*") OR ((Project.[Project Manager]) Is Null)))

ORDER BY Client.[Client ID], Project.[Project ID];

 

[Remou]

Have a star for not only figuring out the problem but taking the time to come back and tell us about it.