Display Msg Box Based Upon Query

dzirkelb · Jul 28, 2006

I wish to display a message box, only if a query returns results. I would have it on the OlLoad portion of my form. The problem I am having is doing the sql in the vba portion of the code...it would look something like this:

sql = "select * from table"
if results of sql are not blank, then
msgbox("hello")
end if

Can someone help me please?

elbweb · Jul 28, 2006

Code:

    sTable = "table" 'table name
    sAlert = "Warning, no records in table" 'alert to return when no records found
    
    Dim rsTemp As ADODB.Recordset
    
    Set myConn = CurrentProject.Connection
    Set rsTemp = New ADODB.Recordset
    
    rsTemp.CursorLocation = adUseClient
    rsTemp.CursorType = adOpenStatic
    rsTemp.LockType = adLockOptimistic
    
    rsTemp.Open "SELECT * FROM " & sTable & ";", myConn

    If rsTemp.EOF = True then
        msgbox sAlert
    End If

    rsTemp.Close
    
    Set rsTemp = Nothing
    myConn.Close
    Set myConn = Nothing

the two variables at the top are what are used, i did this so that you can look and try to understand the code.

EOF stands for End Of File, it returs true either when, you cycled through all the record in a recordset, or if the requested SQL query returned no records to being with.

dzirkelb · Jul 31, 2006

On this line of code:

Dim rsTemp As ADODB.Recordset

I receive the following error:

User-defined type not defined

How do I go about fixing it?

dzirkelb · Jul 31, 2006

Using access 97 btw

Banana · Jul 31, 2006

First, I should make some comments about ADO/DAO.

If you are using Access only (no SQL back end or whatever), you may want to use DAO as it is much better and optimized for Jet (Access's engine). However, if you use a different backend, or expect to move to a SQL to keep your database scalable, then you want to use ADO as it is future-proof.

In your case, you want to check your library's references. In VBA editor, Tools -> Reference and ensure you have ADO 2.x library checked.

Alternatively, you can use DAO instead.

Code:

 sTable = "table" 'table name
    sAlert = "Warning, no records in table" 'alert to return when no records found
    
    Dim dbTemp As DAO.Database
    Dim rsTemp As DAO.Recordset
    
    Set dbTemp = Currentdb
    Set rsTemp = "SELECT * FROM " & sTable & ";"

    If rsTemp.EOF = True then
        msgbox sAlert
    End If

    rsTemp.Close
    
    Set rsTemp = Nothing
    Set dbTemp = Nothing

dzirkelb · Jul 31, 2006

Here is my current code

Code:

Dim PurchSpecialHandling As String

    Dim dbTemp As DAO.Database
    Dim rsTemp As DAO.Recordset
    Dim sTable As String
    Dim CurrentProject
    Dim myConn
    sTable = "InventoryMaster1" 'table name
    Set myConn = CurrentProject.Connection
    Dim Part As String
    
    Part = Forms!PreRequisitionBuysMasterForm1!Part
    rsTemp.Open "SELECT * FROM InventoryMaster1 WHERE [Part #]='" & Part & "';", myConn

If Not rsTemp.EOF = True Then
    PurchSpecialHandling = DLookup("PurchSpecialHandling", "InventoryMaster1", "[Part #]='" & (Forms!PreRequisitionBuysMasterForm1!Part) & "'")
    MsgBox (PurchSpecialHandling)
End If

I receive an error on rsTemp.open stating "Method or data member not found"

Any ideas?

dzirkelb · Jul 31, 2006

I have the following references checked in this order:'

Visual Basic for Applicatinos
Microsoft Access 8.0 Object Library
Microsoft DAO 3.51 Object Library

Banana · Jul 31, 2006

This code:

Code:

Set myConn = CurrentProject.Connection

is ADO specific.

You only need:

Code:

Set dbTemp = CurrentDb

prior to setting your recordset.

Edit= Saw your new post about references: That is why ADO didn't work because it wasn't checked. I hope you have clear information about whether to use DAO or ADO. HTH.

dzirkelb · Jul 31, 2006

I instaled dao 2.5 compatablility and I get a different error now:

Variable Not Defined

On Set myConn = CurrentProject.Connection with CurrentProject being highlighted.

Banana · Jul 31, 2006

That's because you're trying to use DAO and ADO. You have to choose one or other. They are entirely different model of accessing data. Read up on that if you are not sure what they are all about.

If you compare elobweb's code to my code, you would notice that there are different syntaxes being used to do same things. You have to select one.

dzirkelb · Jul 31, 2006

I saw the changed I didn't make, and I corrected them. Here is my updated code:

Code:

    Dim PurchSpecialHandling As String
    Dim dbTemp As DAO.Database
    Dim rsTemp As DAO.Recordset
    Dim Part As String
    
    Set dbTemp = CurrentDb
    Part = Forms!PreRequisitionBuysMasterForm1!Part

    Set rsTemp = "SELECT * FROM InventoryMaster1 WHERE [Part #]='" & Part & "';"

If Not rsTemp.EOF = True Then
    PurchSpecialHandling = DLookup("PurchSpecialHandling", "InventoryMaster1", "[Part #]='" & (Forms!PreRequisitionBuysMasterForm1!Part) & "'")
    MsgBox (PurchSpecialHandling)
End If

    rsTemp.Close
    
    Set rsTemp = Nothing
    Set dbTemp = Nothing

I now receive the following error:

Type Mismatch

on

Code:

Set rsTemp = "SELECT * FROM InventoryMaster1 WHERE [Part #]='" & Part & "';"

with the second & highlighted after Part. Any ideas?

Banana · Jul 31, 2006

1) # is a reserved word- Access may trip over that. I'd suggest that you fix it and take a look at this list of reserved words you want to avoid.

2) What data type is Part? Is it a string or numeric? If it's numeric, then you don't need single quotes.

dzirkelb · Jul 31, 2006

Since # is a reserved word, that is why I have placed [] around it...I am unable to change the name of the field as there are hundreds of applications already using that field with part # as being the field.

It is a string, so the ' ' are necessary.

Banana · Jul 31, 2006

Doh!

Forgot one more syntax.

Code:

Set rsTemp = dbTemp.OpenRecordset("Your SQL Here")

dzirkelb · Jul 31, 2006

When I try putting in a specific part which should bring up results, I get a type mismatch error referring to the entire sql statement...here is the statement:

Set rsTemp = "SELECT * FROM InventoryMaster1 WHERE ([Part #]='0-7260-010-BL');"

dzirkelb · Jul 31, 2006

Set rsTemp = dbTemp.OpenRecordset = ("SELECT * FROM InventoryMaster1 WHERE ([Part #]='0-7260-010-BL');")

That brings up the following error:

Argument not optional

with .Openrecordset being highlited.

Banana · Jul 31, 2006

You don't need two equals. Look at my code again.

dzirkelb · Jul 31, 2006

It appears I got everything to work so far; however, I now get an error:

You entered an expression that has no value.

This is on the line of code:

Part = Forms!PreRequisitionBuysMasterForm1!Part

There is definatly not a value on the form and this is sort of the problem I was trying to avoid at the beginning by re-writing my old code to its current state. When there is no value on the form, then I do not wish to do anything...how should I go about doing so?

dzirkelb · Jul 31, 2006

I have an idea...i'll do the rs.eof on the query that the form is drawn from. I'll get back to you in a bit to see if that works.

Banana · Jul 31, 2006

If there is a case where it will have no value, then you need to encase your whole code with this:

Code:

If Not Isnull(YourTextbox) Then 
    'Execute your code here
End If

Display Msg Box Based Upon Query

Registered User.

Self Taught Hero

Registered User.

Registered User.

split with a cherry atop.

Registered User.

Registered User.

split with a cherry atop.

Registered User.

split with a cherry atop.

Registered User.

split with a cherry atop.

Registered User.

split with a cherry atop.

Registered User.

Registered User.

split with a cherry atop.

Registered User.

Registered User.

split with a cherry atop.

Similar threads

Users who are viewing this thread