Check for Existence of Record THEN Open Form

[bluray]

Hello guys, thanks again for taking the time to help me out with my problems!

This problem seems super simple in my head, however getting the coding to make it work is turing out to be not so simple...

Basically what I am trying to do is have a Command Button check to see whether the current "Record ID" exists in the sister table before opening the sister table's bound form for editing when the user clicks on it.

I figured that I would be able to do this with a SELECT query that does a search for the current record's "Record ID" in the sister table, and couple it with an "If Then" statement that will either a) allow access to the form or b) pop up a MsgBox if the results of the SELECT query are Null or Empty and disallow access.

From my research the code below should work, but Access keeps getting hung up on the "Dim db As Database" line, and I cannot for the life of me figure it out!

Private Sub GoToCertifiedBUTTON_Click()
On Error GoTo Err_GoToCertifiedBUTTON_Click
 
 
 
Dim VerifyExists As String
    VerifyExists = "SELECT tbl_VEH4b.AppID " & _
                          "FROM tbl_VEH4a LEFT JOIN tbl_VEH4b ON tbl_VEH4a.ID = tbl_VEH4b.AppID " & _
                    "WHERE (([Forms]![frm_VEH4a].[ID]=[AppID]));"
 
Dim db As Database
Dim rs As Recordset
Dim qdf As QueryDef
 
Set db = CurrentDb
Set qdf = db.QueryDefs("VerifyExists")
Set rs = qdf.openrecordset()
 
If rs.EOF Then
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "frm_VEH4b"
 
    stLinkCriteria = "[AppID]=" & Me![ID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Else
    'Open MsgBox with "Cannot Allow" Message
End If
 
 
 
Exit_GoToCertifiedBUTTON_Click:
Exit Sub
 
Err_GoToCertifiedBUTTON_Click:
    MsgBox Err.Description
    Resume Exit_GoToCertifiedBUTTON_Click
 
End Sub

[JKing]

Check that you have a reference to Microsoft DAO Object Library. Also when declaring data objects you should use the full reference to a DAO or ADO object library.

Dim db as DAO.Database
DIm rs as DAO.Recordset
 

[bluray]

Check that you have a reference to Microsoft DAO Object Library. Also when declaring data objects you should use the full reference to a DAO or ADO object library.

Dim db as DAO.Database
DIm rs as DAO.Recordset
 

tried

Dim db as DAO.Database
DIm rs as DAO.Recordset

and

Dim db as ADO.Database
DIm rs as ADO.Recordset

and it is still having issues.

are "Database" and "Recordset" supposed to be default objects in these libraries? Or do I have to define them somewhere?

[JKing]

Sorry I should have been more clear in my previous post about checking your reference.

You need to go to Tools > References from the VBA editor window and ensure that you have included the Microsoft DAO Object Library.

[bluray]

Sorry I should have been more clear in my previous post about checking your reference.

You need to go to Tools > References from the VBA editor window and ensure that you have included the Microsoft DAO Object Library.

Thanks JKing, it looks like were on to something here!

I no longer am being sent back to the editor now, but Access is not recognizing the the query when I reference in in the

Set qdf = db.QueryDefs("VerifyExists")

line. It is giving me an "Item not found in this collection" error. Would it work if I just planted the SQL query directly into the brackets?

For example:

Set qdf = db.QueryDefs("SELECT tbl_VEH4b.AppID " & _
                    "FROM tbl_VEH4a LEFT JOIN tbl_VEH4b ON tbl_VEH4a.ID = tbl_VEH4b.AppID " & _
                    "WHERE (([Forms]![frm_VEH4a].[ID]=[AppID]));")
 

Or maybe I am way out to lunch on that...?

[missinglinq]

What not just use the DCount() function against the sister table to see if the RecordID exists there? If RecordID is a text field, something like:

If DCount("[RecordID]", "SisterTable", "[RecordID]='" & Me![RecordID] & "'") > 0 Then
  'Open SisterTable here  to the desired record 
Else
 Msgbox "This Record ID does not exist in SisterTable!"
End If
End Sub

Linq ;0)>

[JKing]

Thanks JKing, it looks like were on to something here!

I no longer am being sent back to the editor now, but Access is not recognizing the the query when I reference in in the

Set qdf = db.QueryDefs("VerifyExists")

line. It is giving me an "Item not found in this collection" error. Would it work if I just planted the SQL query directly into the brackets?

For example:

Set qdf = db.QueryDefs("SELECT tbl_VEH4b.AppID " & _
                    "FROM tbl_VEH4a LEFT JOIN tbl_VEH4b ON tbl_VEH4a.ID = tbl_VEH4b.AppID " & _
                    "WHERE (([Forms]![frm_VEH4a].[ID]=[AppID]));")
 

Or maybe I am way out to lunch on that...?

QueryDefs is a collection of saved queries in the database. So you can only reference saved queries and not SQL strings. However you can alter a querydef's SQL as well as create new querydefs using an SQL string.

Be sure to have a look at Linq's posted above as he's provided a simple solution.

[bluray]

What not just use the DCount() function against the sister table to see if the RecordID exists there? If RecordID is a text field, something like:

If DCount("[RecordID]", "SisterTable", "[RecordID]='" & Me![RecordID] & "'") > 0 Then
  'Open SisterTable here  to the desired record 
Else
 Msgbox "This Record ID does not exist in SisterTable!"
End If
End Sub

Linq ;0)>

After hours of finicking I finally got it to work :D Thanks missinglinq and JKing! Here is the finished code:

        If DCount("[AppID]", "tbl_VEH4b", "Forms![frm_VEH4a]![AppID]") > 0 Then
 
            Dim stDocName As String
            Dim stLinkCriteria As String
 
            stDocName = "frm_VEH4b"
 
            stLinkCriteria = "[AppID]=" & Me![AppID]
            DoCmd.OpenForm stDocName, , , stLinkCriteria
        Else
 
         MsgBox "This applicant has not yet been certified!", vbOKOnly + vbExclamation, "Record Not Found"
 
        End If

Turns out missinglinq that Access didnt like the

"[RecordID]='" & Me![RecordID] & "'"

part of the DCount. I kept getting a Data Type Mismatch error, so I swapped it for

"Forms![frm_VEH4a]![AppID]"

and I dont seem to be having any problems thus far. Switches to the sister form without a hitch. Thanks again missinglinq!

QueryDefs is a collection of saved queries in the database. So you can only reference saved queries and not SQL strings. However you can alter a querydef's SQL as well as create new querydefs using an SQL string.

On another note (for educational purposesmore or less) JKing, when you say QueryDefs is a collection of saved queries, where exactly are they saved? In the standard access query area? Or can these queries be coded in SQL within the form and referenced in QueryDes like I was trying to do before (as seen in the code below)?

Dim VerifyExists As String
    VerifyExists = "SELECT tbl_VEH4b.AppID " & _
                          "FROM tbl_VEH4a LEFT JOIN tbl_VEH4b ON tbl_VEH4a.ID = tbl_VEH4b.AppID " & _
                    "WHERE (([Forms]![frm_VEH4a].[ID]=[AppID]));"
 
Dim db As Database
Dim rs As Recordset
Dim qdf As QueryDef
 
Set db = CurrentDb
Set qdf = db.QueryDefs("VerifyExists")
Set rs = qdf.openrecordset()

[JKing]

Any queries you create and save within the database are part of this collection. You can programmatically create saved queries using the createquerydef method. Open the helpfile on querydef and read through it. It explains it better than I can.

[bluray]

Any queries you create and save within the database are part of this collection. You can programmatically create saved queries using the createquerydef method. Open the helpfile on querydef and read through it. It explains it better than I can.

great, thanks JKing, thats all I need!

Have a good day!

[missinglinq]

Sorry, your ID "number" is actually a number! I only use numeric datatypes for fields that are actually going to be used as numbers, i.e. for mathematical calculations!

Glad you got it working!

Linq ;0)>