Getting a parameter query to prompt "No Records Found" 1

[Kayo]

I have the following parameter query.
SELECT ENLSSN.EP_SSN, ENLSSN.COMPONENT_CD, ENLSSN.PAY_GRADE_ID, ENLSSN.RECSTA_CD, ENLSSN.SEX_CATEGORY_CD, ENLSSN.STRENGTH_CAT_CD, ENLSSN.UIC, ENLSSN.PMOS_CD
FROM ENLSSN
WHERE (((ENLSSN.EP_SSN)=[ENTER ENLISTED SSN]));

Problem is, I need the query to prompt the user "No Records Found" if no match is found against the input data:

Any help will be greatly appreciated.

V/r,

 

[Tranman]

One solution that comes to mind is to change your where clause to call a function that runs a query in the background and responds with a message box if no records are found. Like so:

WHERE (((ENLSSN.EP_SSN)= enlFound([ENTER ENLISTED SSN])));

The function would look like this:

Public Function enlFound(strSSN As String) As String
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Select EP_SSN from ENLSSN where EP_SSN = '" & strSSN & "';&quot
If rs.EOF Then MsgBox strSSN & " not found", vbOKOnly, "NOT FOUND"
enlFound = strSSN
End Function

The only problem I see with this is that it would still return a grid with no rows, which might be confusing to the user. This could probably be resolved by running the whole thing in a macro, which only ran the actual query if it found the entered ssn. Sounds like a fun problem...

Tranman

 

[Kayo]

Tranman, thanks for the input. Looks like I'm biting off more than I can chew. I'm not that good of a programmer to pull off what you just advised. Wouldn't know where to start. However, I really appreciate your time w/this thing. I'll take your code and play w/it, may just get lucky. Again thanks Tranman for you time and effort.

V/r,

 

[Tranman]

V/r,
Don't be intimidated by this. Just go to your Modules tab, click "New", and paste in the code above. Then close the module. It will ask you if you want to save, just say yes, and give the module a name. It does not matter what you call it.

Then change the criteria box under the enlSSN column to:
enlFound([ENTER ENLISTED SSN]).

It works like this: When you run the query, it will ask you for the SSN (like it did before), then instead of using the entered ssn for the criteria of the query, it passes it as an argument to the enlFound function.

When enlFound runs, it uses the field you entered as criteria for a separate query that just looks for the SSN in the table. If EOF (end of file) is true for the recordset object (no rows found) then the messagebox is displayed. No matter if rows are found or not, the function returns the SSN you entered as the value of the function (enlFound = strSSN) and that value is used as criteria in the query.

BTW, just before the end of the function, it should say:
rs.Close
Set rs = Nothing
End Function

Like I said, don't be intimidated...write back if you have problems, and I will lead you down the path of understanding.

Tranman

 

[Kayo]

Tranman, your post was reassuring. I cut and pasted as you advised; changed the criteria as instructed. Got the following error msg when I ran the program:

Run Time Error '13':
Type Mismatch

When I ran the debugger the following code was highlighted:

Set rs = Currentdb.OpenRecordset("Select EP_SSN from ENLSSN where EP_SSN = ' " & strSSN & " ';&quot

Tranman, the scary thing is that I think I understand what the code saying. Pls tell me more but beware Tranman you may be creating a monster. Again, thanks for everything.

V/r,

 

[Tranman]

V/r,
I'm sorry about leading you astray. I assumed you were using the DAO (Data Access Objects--Access '97) but you are obviously using the ADO (Active-X Data Objects--Access 2K or XP). All that means is that you need to declare and use the ADO-type recordset object instead of the DAO type.

I think the following code should work OK: (it's just a different way of doing the same thing)

Public Function enlFound(strSSN As String) As String
Dim rs As New ADODB.Recordset
rs.Open "Select EP_SSN from ENLSSN where EP_SSN = '" & strSSN & "';", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

If rs.EOF Then MsgBox strSSN & " not found", vbOKOnly, "NOT
FOUND"

enlFound = strSSN
rs.Close
Set rs = Nothing
End Function

Just paste this code in place of the old function.

Don't let this scare you. There are lots of examples in the help files, and the ability to write and use your own functions will enable you to do things that you couldn't imagine previously.

Don't hesitate to write back if you have more questions.

Tranman

 

[Kayo]

Tranman, did what you advised and was prompted the following error msg after I ran the program:

Compile error:
Sub or Function not defined

The following code was highlighted in the debugger:
Public Function enlFound(strSSN As String) As String

Tranman, is there anything else that I could advise you that could help figure this thing out e.g., I'm using Access 2000, the EP_SSN field is a "Text" field. Trying to be helpful. This program will be great when working. Thanks for everything.

V/r,

Kayo

 

[Tranman]

V/r,
Go ahead and run the query again, and when it hangs and you open the debugger, look to see if the line "Public Function enlFound... is highlighted in yellow and something else is highlighted in blue.

What it sounds like to me is that when you cut and pasted, something that was supposed to be on a line with some other code ended up on a line by itself. For example, the phrases: adOpenDynamic, adLockOptimistic are supposed to be at the end of the previous line instead of on a line by themselves.

If they are on a line by themselves, the system thinks you are trying to call a subroutine called adOpenDynamic, and when it can't find it, it gives the message you are seeing.

These are not major problems...just petty annoyances.

Call me Paul

 

[Kayo]

Paul, yes, the 1st line of code is highlighted in yellow:
Public Function enlfound(strSSN As String) As String

And the 3rd line of code is highlighted in blue:
rs.Open "Select EP_SSN from ENLSSN where EP_SSN = '" & strSSN & '";",

Pls call me Gregg

 

[Tranman]

Gregg,
The following information needs to be on the same line as the info highlighted in blue:

CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Let me know if that fixes the problem.

Paul

 

[Kayo]

Paul, It all got to me and I took some time off. Below is the program. I have tried all the fixes you've advise me. Don't know where to go from here. Don't want to sound like a damp cloth, but is this thing I'm trying to do with Access 2000 doable?

Public Function enlFound(strSSN As String) As String
Dimm rs As New ADODB.Recordset
rs.Open "Select EP_SSN from ENLSSN where EP_SSN = '" & strSSN
& "';", CurrentProject.Connection , adOpenDynamic, adLockOptimistic

If rs.EOF Then MsgBox strSSN & " not found", vbOKOnly, "NOT FOUND"

enlFound = strSSN
rs.Close
Set rs = Nothing
End Function


Gregg

 

[Tranman]

Gregg,
It is a little bit hard to tell what your code really looks like because you have to type it in this little window, but you need to be sure that the line that starts with & "';... is on the same text line as the info before it (rs.Open.....). Also, your Dim statement has too many m's.

Don't worry about this not working. It works on my computer, so it will work on yours when we get the wrinkles out of it.

Let me know if you get more errors.

Paul

 

[Kayo]

Paul, it worked. However, I still have a problem. After the msg box displays "no record found" and I click on the "ok" button, a blank form still appears. This is the same blank form that I wanted the msg box to get rid of. Any suggestions.


Gregg

 

[Kayo]

Paul, I forgot. your last post was a two star. We here
at the pentagon (Army) really appreciate your help. As you know our computer help resources are extremely limited during these times and when we get into a programming fix, it's good to know you good folks are out there willing and able to help. So thanks from the folks here at Army, G1.

Gregg

 

[Tranman]

Gregg,
I'm having a bit of trouble following what you're doing.

Is this a form we're dealing with, or is the blank form that pops up just a data grid?

In my first post, I said, "The only problem I see with this is that it would still return a grid with no rows; blah, blah, blah...". Apparently, we need to split your process up so that we first find out if there are any rows to return, and then only run the query if there are rows.

Tell me a little more about what you're doing operationally (is it a form?/are you clicking a command button to run the query?/running a macro?; etc.) and we'll come up with a solution.

Army, huh? I assumed the "enl" stuff was enlisted. I'm a Gold Star brother--my brother was KIA in Vietnam (LRRP 173rd Airborne), and a Navy (also Vietnam) vet myself. We are behind you guys 100%. I'm glad to be able to help you out in any way I can.

Paul