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
10 5568 JKing 1,206
Recognized Expert Top Contributor
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
-
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 1,206
Recognized Expert Top Contributor
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.
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...?
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 1,206
Recognized Expert Top Contributor
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.
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 1,206
Recognized Expert Top Contributor
Any queries you create and save within the database are part of this collection. You can programmaticall y create saved queries using the createquerydef method. Open the helpfile on querydef and read through it. It explains it better than I can.
Any queries you create and save within the database are part of this collection. You can programmaticall y 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!
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Steve |
last post by:
I have a form with about 25 fields. In the BeforeUpdate event of the form, I
have code that sets the default value of each field to its current value. For a
new record, I can put the focus in any field to start. If I edit that field and
then click on the new record button in the navigation buttons, the form goes to
a new record and each field has the default value of the previous record. If I
put the focus in any field to start, edit that...
|
by: aaj |
last post by:
Hi all
I have a continuous bound form and on each record is a tick box.
The user ticks the boxes and these boxes define the batch. for future
operations
before they leave the page I count the number of ticks using (ADO)
rst.Open "SELECT COUNT(update_po_ref) AS update_qty FROM dbo.tbl_forecasted
|
by: Shaldaman |
last post by:
Hi
Is there a property in MS Access for the following:
1) For a Command Button on a form, is there a property that can be used
to determine if it has been clicked?
eg: Me!button7.Clicked - I don't know if there is even a property like
that(Clicked)
2) For a Text Field on a form, is there a property that can be used to
find out if data has been entered in it?
I've tried Me!Textfield1.Value = Null but I'm not sure if that's the
|
by: S. van Beek |
last post by:
Dear reader
A record set can be empty because the condition in the query delivers no
records.
Is there a VBA code to check the status of a record set, record set empty
|
by: www.MessageMazes.com |
last post by:
Greetings,
I'm experimenting with an ASP page that reads data from a file name
that is passed to it as a parameter, as in this page, which works,
because the "good" file exists.
http://mazes.com/asp-maze/customized.asp?file=good&firstname=jwk
(make "your name is an amazing asp.general programmer" mazes.
But when I try this page, with the "bad" file which does not exist, I'm
| |
by: scprosportsman |
last post by:
Please help guys, i am trying to set up a database here at work and im
fairly new to access in terms of writing functions and queries and
stuff. I have 2 different places on my design that will require
checking a check box to tell which category something will pertain to.
Well after each record is entered i want the check to remain with that
record and auto clear when going to the next record so i can input
something else if i have a...
|
by: pamelafluente |
last post by:
Hi Guys,
I have the following HTML code which is doing a GET to a page, say
MyUrl.aspx :
<body>
<form name="form1" method="get" action="MyUrl.aspx" id="form1">
<input type="hidden" name="ClickedElement" id="Messenger" />
</form>
</body>
|
by: amuth25 |
last post by:
I have a form which displays records based on filter. I need the user to select a record or multiple records by clicking a check box and the selected record details should be displayed in a new form.
I want to use a check box, select and unselect the values for each record in the form.
The form is a continous Form.
How do I use the record check box value to open a new form with the relevant details.
|
by: rdsandy |
last post by:
Hi,
Im using Access 2003. I have a form, with two buttons and a list box. When a user selects an item in the list box, the user clicks on one of the buttons which opens up the selected record. The other button is for adding a new record. The form that opens up has a subform within a subform on the form, and at present it doesn't open up on a new record (the sub subforms). In vba code, how would you bring up a message box on form open if...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |