By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,846 Members | 1,211 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,846 IT Pros & Developers. It's quick & easy.

Check for Existence of Record THEN Open Form

P: 14
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!


Expand|Select|Wrap|Line Numbers
  1. Private Sub GoToCertifiedBUTTON_Click()
  2. On Error GoTo Err_GoToCertifiedBUTTON_Click
  3.  
  4.  
  5.  
  6. Dim VerifyExists As String
  7.     VerifyExists = "SELECT tbl_VEH4b.AppID " & _
  8.                           "FROM tbl_VEH4a LEFT JOIN tbl_VEH4b ON tbl_VEH4a.ID = tbl_VEH4b.AppID " & _
  9.                     "WHERE (([Forms]![frm_VEH4a].[ID]=[AppID]));"
  10.  
  11. Dim db As Database
  12. Dim rs As Recordset
  13. Dim qdf As QueryDef
  14.  
  15. Set db = CurrentDb
  16. Set qdf = db.QueryDefs("VerifyExists")
  17. Set rs = qdf.openrecordset()
  18.  
  19. If rs.EOF Then
  20.     Dim stDocName As String
  21.     Dim stLinkCriteria As String
  22.  
  23.     stDocName = "frm_VEH4b"
  24.  
  25.     stLinkCriteria = "[AppID]=" & Me![ID]
  26.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  27.  
  28. Else
  29.     'Open MsgBox with "Cannot Allow" Message
  30. End If
  31.  
  32.  
  33.  
  34. Exit_GoToCertifiedBUTTON_Click:
  35. Exit Sub
  36.  
  37. Err_GoToCertifiedBUTTON_Click:
  38.     MsgBox Err.Description
  39.     Resume Exit_GoToCertifiedBUTTON_Click
  40.  
  41. End Sub
Aug 23 '07 #1
Share this Question
Share on Google+
10 Replies


JKing
Expert 100+
P: 1,206
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.

Expand|Select|Wrap|Line Numbers
  1. Dim db as DAO.Database
  2. DIm rs as DAO.Recordset
  3.  
Aug 23 '07 #2

P: 14
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.

Expand|Select|Wrap|Line Numbers
  1. Dim db as DAO.Database
  2. DIm rs as DAO.Recordset
  3.  
tried

Expand|Select|Wrap|Line Numbers
  1. Dim db as DAO.Database
  2. DIm rs as DAO.Recordset
and

Expand|Select|Wrap|Line Numbers
  1. Dim db as ADO.Database
  2. 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?
Aug 23 '07 #3

JKing
Expert 100+
P: 1,206
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.
Aug 23 '07 #4

P: 14
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

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. Set qdf = db.QueryDefs("SELECT tbl_VEH4b.AppID " & _
  2.                     "FROM tbl_VEH4a LEFT JOIN tbl_VEH4b ON tbl_VEH4a.ID = tbl_VEH4b.AppID " & _
  3.                     "WHERE (([Forms]![frm_VEH4a].[ID]=[AppID]));")
  4.  
Or maybe I am way out to lunch on that...?
Aug 23 '07 #5

missinglinq
Expert 2.5K+
P: 3,532
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:

Expand|Select|Wrap|Line Numbers
  1. If DCount("[RecordID]", "SisterTable", "[RecordID]='" & Me![RecordID] & "'") > 0 Then
  2.   'Open SisterTable here  to the desired record 
  3. Else
  4.  Msgbox "This Record ID does not exist in SisterTable!"
  5. End If
  6. End Sub
Linq ;0)>
Aug 24 '07 #6

JKing
Expert 100+
P: 1,206
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

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. Set qdf = db.QueryDefs("SELECT tbl_VEH4b.AppID " & _
  2.                     "FROM tbl_VEH4a LEFT JOIN tbl_VEH4b ON tbl_VEH4a.ID = tbl_VEH4b.AppID " & _
  3.                     "WHERE (([Forms]![frm_VEH4a].[ID]=[AppID]));")
  4.  
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.
Aug 24 '07 #7

P: 14
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:

Expand|Select|Wrap|Line Numbers
  1. If DCount("[RecordID]", "SisterTable", "[RecordID]='" & Me![RecordID] & "'") > 0 Then
  2.   'Open SisterTable here  to the desired record 
  3. Else
  4.  Msgbox "This Record ID does not exist in SisterTable!"
  5. End If
  6. End Sub
Linq ;0)>
After hours of finicking I finally got it to work :D Thanks missinglinq and JKing! Here is the finished code:

Expand|Select|Wrap|Line Numbers
  1.         If DCount("[AppID]", "tbl_VEH4b", "Forms![frm_VEH4a]![AppID]") > 0 Then
  2.  
  3.             Dim stDocName As String
  4.             Dim stLinkCriteria As String
  5.  
  6.             stDocName = "frm_VEH4b"
  7.  
  8.             stLinkCriteria = "[AppID]=" & Me![AppID]
  9.             DoCmd.OpenForm stDocName, , , stLinkCriteria
  10.         Else
  11.  
  12.          MsgBox "This applicant has not yet been certified!", vbOKOnly + vbExclamation, "Record Not Found"
  13.  
  14.         End If
Turns out missinglinq that Access didnt like the
Expand|Select|Wrap|Line Numbers
  1. "[RecordID]='" & Me![RecordID] & "'"
part of the DCount. I kept getting a Data Type Mismatch error, so I swapped it for
Expand|Select|Wrap|Line Numbers
  1. "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)?

Expand|Select|Wrap|Line Numbers
  1. Dim VerifyExists As String
  2.     VerifyExists = "SELECT tbl_VEH4b.AppID " & _
  3.                           "FROM tbl_VEH4a LEFT JOIN tbl_VEH4b ON tbl_VEH4a.ID = tbl_VEH4b.AppID " & _
  4.                     "WHERE (([Forms]![frm_VEH4a].[ID]=[AppID]));"
  5.  
  6. Dim db As Database
  7. Dim rs As Recordset
  8. Dim qdf As QueryDef
  9.  
  10. Set db = CurrentDb
  11. Set qdf = db.QueryDefs("VerifyExists")
  12. Set rs = qdf.openrecordset()
Aug 24 '07 #8

JKing
Expert 100+
P: 1,206
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.
Aug 24 '07 #9

P: 14
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!
Aug 24 '07 #10

missinglinq
Expert 2.5K+
P: 3,532
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)>
Aug 24 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.