Blank form on null query results

[JackieEVSC]

I have a form based on a query. When the results are null, I get a blank screen. I need to display a message box that says ""There are no records that match this query.", and then return the user back to that form. I have searched several sites and googled for a solution, to no avail. I know this is a simple thing, but I can't figure it out.

The query this is based on has involves two tables ... the tblRepairInfo and tblMySchools. Info from tblRepairInfo is pulled only for the schools a user has selected (their selection is stored in tblMySchools). Users can search on one of several different fields (depending on which button they select). I can tweak the code for the other search criteria's if I can get one working ... right now, I'm searching on the AssetNumber field in tblRepairInfo.



Can anyone help me with this, please? Thanks.

[Toyman]

I have a form based on a query. When the results are null, I get a blank screen. I need to display a message box that says ""There are no records that match this query.", and then return the user back to that form. I have searched several sites and googled for a solution, to no avail. I know this is a simple thing, but I can't figure it out.

The query this is based on has involves two tables ... the tblRepairInfo and tblMySchools. Info from tblRepairInfo is pulled only for the schools a user has selected (their selection is stored in tblMySchools). Users can search on one of several different fields (depending on which button they select). I can tweak the code for the other search criteria's if I can get one working ... right now, I'm searching on the AssetNumber field in tblRepairInfo.

Can anyone help me with this, please? Thanks.

You can use ADO to check if data exist. If not, show a message, otherwise, open the form

Dim rs As New ADODB.Recordset
Dim conn As New ADODB.Connection
Dim sSql As String
sSql = "Select * From Your table" 'This should be the sql statement from the query of the form
Set conn = CurrentProject.Connection
rs.Open sSql, conn, adOpenKeyset, adLockOptimistic
If rs.EOF Then
MsgBox "There was no records found", vbInformation, "No Records Found"
Else
DoCmd.OpenForm "YourFormName", acNormal
End If

[dblife]

In the Footer of the results form, place a text box with a Count() of the records shown.
Create a line of VBa in the AfterUpdate event of this textbox so that when it equals zero, you generate a message box, MsgBox() warning there are no records.
Do you need further help to use this approach?

[JackieEVSC]

I might ... let me play with it a few minutes. Thank you!

[JackieEVSC]

When I paste my SQL code from the query into the code, it shows red for part of it ... how can I crrect this?

sSql = "Select * TblRepairInfo.SubmittedBy, TblRepairInfo.DateCreated, TblRepairInfo.RepairID, TblRepairInfo.LastName, TblRepairInfo.FirstName, TblRepairInfo.StudentID, TblRepairInfo.StudentSite, TblRepairInfo.AssetNumber, TblRepairInfo.TicketNum, TblRepairInfo.SvcTag, TblRepairInfo.EquipDesc, TblRepairInfo.Drop, TblRepairInfo.Spill, TblRepairInfo.OtherType, TblRepairInfo.LCD, TblRepairInfo.Palmrest, TblRepairInfo.Bezel, TblRepairInfo.Keyboard, TblRepairInfo.BottomPlas, TblRepairInfo.TopPlas, TblRepairInfo.Harddrive, TblRepairInfo.Webcam, TblRepairInfo.Battery, TblRepairInfo.Hinges, TblRepairInfo.Screws, TblRepairInfo.OtherDamage, TblRepairInfo.DamageDesc, TblRepairInfo.DateFromSchool, TblRepairInfo.DateToDD, TblRepairInfo.DateFromDD, TblRepairInfo.DateToSchool, TblRepairInfo.NotesDowntown, TblRepairInfo.NotesDesktopDr, TblRepairInfo.Billable, TblRepairInfo.Scrapped, TblRepairInfo.DD, TblRepairInfo.DDWO, TblRepairInfo.DTTech, TblRepairInfo.Void, TblRepairInfo.VoidInfo, TblRepairInfo.RepairBy, TblRepairInfo.DateRecdSchool"
FROM tbl_MySchools LEFT JOIN TblRepairInfo ON tbl_MySchools.SchoolName = TblRepairInfo.StudentSite
WHERE (((TblRepairInfo.AssetNumber) Like "*" & [Enter Asset Tag Number] & "*"))
ORDER BY TblRepairInfo.DateCreated DESC , TblRepairInfo.LastName, TblRepairInfo.FirstName;"

[Toyman]

When I paste my SQL code from the query into the code, it shows red for part of it ... how can I crrect this?

sSql = "Select * TblRepairInfo.SubmittedBy, TblRepairInfo.DateCreated, TblRepairInfo.RepairID, TblRepairInfo.LastName, TblRepairInfo.FirstName, TblRepairInfo.StudentID, TblRepairInfo.StudentSite, TblRepairInfo.AssetNumber, TblRepairInfo.TicketNum, TblRepairInfo.SvcTag, TblRepairInfo.EquipDesc, TblRepairInfo.Drop, TblRepairInfo.Spill, TblRepairInfo.OtherType, TblRepairInfo.LCD, TblRepairInfo.Palmrest, TblRepairInfo.Bezel, TblRepairInfo.Keyboard, TblRepairInfo.BottomPlas, TblRepairInfo.TopPlas, TblRepairInfo.Harddrive, TblRepairInfo.Webcam, TblRepairInfo.Battery, TblRepairInfo.Hinges, TblRepairInfo.Screws, TblRepairInfo.OtherDamage, TblRepairInfo.DamageDesc, TblRepairInfo.DateFromSchool, TblRepairInfo.DateToDD, TblRepairInfo.DateFromDD, TblRepairInfo.DateToSchool, TblRepairInfo.NotesDowntown, TblRepairInfo.NotesDesktopDr, TblRepairInfo.Billable, TblRepairInfo.Scrapped, TblRepairInfo.DD, TblRepairInfo.DDWO, TblRepairInfo.DTTech, TblRepairInfo.Void, TblRepairInfo.VoidInfo, TblRepairInfo.RepairBy, TblRepairInfo.DateRecdSchool"
FROM tbl_MySchools LEFT JOIN TblRepairInfo ON tbl_MySchools.SchoolName = TblRepairInfo.StudentSite
WHERE (((TblRepairInfo.AssetNumber) Like "*" & [Enter Asset Tag Number] & "*"))
ORDER BY TblRepairInfo.DateCreated DESC , TblRepairInfo.LastName, TblRepairInfo.FirstName;"

You have to combind it this way:

Dim sSql as String
sSql = "Select * TblRepairInfo.SubmittedBy, TblRepairInfo.DateCreated, "
sSql = sSql & "TblRepairInfo.RepairID, TblRepairInfo.LastName, "
sSql = sSql & "TblRepairInfo.FirstName, TblRepairInfo.StudentID, "

[JackieEVSC]

I have to bring the fields in two at a time? Okay ... but how do I handle the from where I have the table join and the order by?

I need the results to filter by only the school the tech has selected (stored in the tblMySchools), and I need the results to display by the newest date created, then alphabetically (by that date).

[Toyman]

I have to bring the fields in two at a time? Okay ... but how do I handle the from where I have the table join and the order by?

I need the results to filter by only the school the tech has selected (stored in the tblMySchools), and I need the results to display by the newest date created, then alphabetically (by that date).

No, you just break your sql statement apart in any order you want. The sSql = sSql & part of the statement marely assigns the SQL part to the veriable "sSql". It then combinds it in "sSql = sSql & "...." The result is something like this:

sSql = "Select * "
sSql = sSql & "From YourTable"

The result of the above combinds to this:

"Select * From YourTable"

[JackieEVSC]

I must not be understanding what your trying to tell me. The problem, as I see it, is because the only records I want to pull from tblRepairInfo are the ones whose school name is equal to the school name in tblMySchools ... and then I want them ordered by the students alphabetically by date created (newest first). It doesn't like it when I insert the "FROM" and the "ORDERBY" into the vba statement.

Am I missing something?

[Toyman]

I must not be understanding what your trying to tell me. The problem, as I see it, is because the only records I want to pull from tblRepairInfo are the ones whose school name is equal to the school name in tblMySchools ... and then I want them ordered by the students alphabetically by date created (newest first). It doesn't like it when I insert the "FROM" and the "ORDERBY" into the vba statement.

Am I missing something?

I have a form based on a query. When the results are null, I get a blank screen. I need to display a message box that says ""There are no records that match this query.", and then return the user back to that form. I have searched several sites and googled for a solution, to no avail. I know this is a simple thing, but I can't figure it out.

The code checks to see if the bound query in your form returns any value. If no value, opens up a message to alert the user and to prevent the user from getting a blank form. Isn't this what you were asking? It has nothing to do with which record it returns. It simply allows the code to go forward and open the form if the query returns value.

[JackieEVSC]

That is what I wanted, but the code didn't work for me ... I still got a blank screen. I don't know vba very well, so I'm certain it was something I was doing wrong, and I very much appreciate your help. My main concern was that I didn't want the users to have to close and reopen the program every time they searched for something that yielded no results. Even though it's not exactly what I wanted, the way it's working now is acceptable.

[Toyman]

That is what I wanted, but the code didn't work for me ... I still got a blank screen. I don't know vba very well, so I'm certain it was something I was doing wrong, and I very much appreciate your help. My main concern was that I didn't want the users to have to close and reopen the program every time they searched for something that yielded no results. Even though it's not exactly what I wanted, the way it's working now is acceptable.

If you want, post your db here and I will add the code for you