February 17, 2010 at 9:30 am
Hi,
I'm trying to enter a numeric value in a text box. eg. client Id,
I have a search button that will then search on the ID entered. eg Client ID =1 so pull data for this client on the form. This works.
What I want to do is : if an ID such as 412 is entered but doesnt exist in the database then to bring up a message box else carry on as normal.
Below is the code I have used:
If Forms![frmMainPart1]![txtClientID].Value <> _
IIf(IsNull(DLookup("ClientID", "tblClient", "ClientID = " & Me!txtClientID)), 0, DLookup("ClientID", "tblClient", "ClientID = " & Me!txtClientID)) Then
MsgBox "Client ID does not exist"
Else
If the correct ID is entered it keeps bringing up the msgbox.
Any HELP please- thanks
February 17, 2010 at 10:05 am
Are you sure you are at the correct site?
IIF and DLOOKUP are ACCESS Visual Basic For Applications (VBA) functions ....
February 18, 2010 at 4:41 pm
Hi Ritesh,
This really isn't a forum for MS Access VBA questions, and there's lots of more specialised forums that you should use that will be able to help you. That being said, this is my first post on this marvellous SQL Server forum, and I'd really like my first post to help someone so here's a quick solution...
If you can, you should always avoid using IIf (Integrated If) in VBA. It's OK with MS Access SQL, but in VBA always use "If... Then... Else..." instead.
You've got quite a long line of code that can (and should) be made more maintainable by using a couple of variables. I've over-commented the following code to help you understand the various steps and what they're actually doing. You should be able to copy and paste it, and once you understand what it's doing, delete my comments - but please promise me you'll add meaningful comments of your own so that other developers in your company can understand what you're doing!
'[Create a variable that stores what has been entered in the txtClientID TextBox that
' you can refer to - we'll use a variant data type so it can handle Null values]
Dim varClientID As Variant
'[Create a second variable that will hold the return value of the DLookUp function.
' The datatype returned by DLookUp is a variant, so as with the first variable we'll
' use a variant to handle the possibility of a Null value being returned]
Dim varDLookUp As Variant
'[Store the value of the client ID textbox in the variable you've created - the
' default property of a textbox is 'Value' so you don't need to explicity specify it]
varClientID = Forms!frmMainPart1!txtClientID
'[First you should make sure that the user has actually entered a value in the Client
' ID textbox!!!]
If Not IsNull(varClientID) Then
'[OK, we know we've got a valid value in the txtClientID textbox that we can use
' with the DLookUp function, so we'll store that in our second variable]
varDLookUp = DLookup("ClientID", "tblClient", "ClientID = " & varClientID)
'[Check if the Dlookup has not been able to return a value in the database, i.e.
' you've got a null value]
If IsNull(varDLookUp) Then
'[Tell the user that the client ID doesn't exist - use the exclamation icon
' (vbExclamation) to make it clear that something is wrong but the system is
' not about to fall over! Also use the title argument of the MsgBox function
' to make the message nice and clear to them]
MsgBox "The Client ID " & varClientID & " does not exist.", vbExclamation, _
"CLIENT ID DOES NOT EXIST"
End If
End If
As I don't have tables or forms that match your objects, I haven't been able to test the above code, but "instinctively" :w00t: I believe it should work.
As far as programming is concerned, you should always look at your code to see if it can be made reusable - and this may be a prime candidate, i.e. you may need to do the same check on IDs elsewhere in your application - or applications. So that being the case, we'll make it into a separate procedure; if you want it to be used throught the application create a module (if you're in the code window, choose 'New Module' from the 'Insert' menu). Copy and paste the following procedure into the new module:
'====================================================================================
' NAME : CheckIDExists
' PURPOSE : Checks if an ID in a table exists.
' RETURNS : Boolean - True if the ID exists in the passed table, otherwise
' : return False.
' ARGUMENTS : astrField - The name of the table we're looking in.
' : atrField - The name of the field we're checking for matches.
' : avarID - The ID that we're looking for.
' CREATED BY : Ritesh (with grateful thanks to RainbowFfolly and SQL Server
' : Central)
' DATE CREATED : 18/02/2010
'====================================================================================
Public Function CheckIDExists(astrTable As String, _
astrField As String, _
avarID As Variant) As Boolean
'[The function's return value - if it finds a match it will return True,
' otherwise it will return False]
Dim BooReturnValue As Boolean
'[Store the value of the DLookup function that returns the ID from the
' passed table]
Dim varDLookUp As Variant
'[Explicitly initialise the function's return value to False. This is the
' default for a Boolean datatype but beginner programmers are often unaware
' of this, and although this line is redundant, it makes it clear to them]
10 BooReturnValue = False
'[Ensure that a valid ID value has been passed to look for]
20 If Not IsNull(avarID) Then
'[Find the matching value - if it exists - based on the passed table
' and field]
30 varDLookUp = DLookup("[" & astrField & "]", _
"[" & astrTable & "]", _
"[" & astrField & "]=" & avarID)
'[Check if the Dlookup function has been able to find a matching value
' in the table]
40 If Not IsNull(varDLookUp) Then
'[We've found a matching value so return True]
50 BooReturnValue = True
60 Else
'[we've not found a matching value so return False - this line is
' also redundant but it does make it easier to understand for
' beginning programmers]
70 BooReturnValue = False
80 End If
90 Else
'[If we want to ignore passed IDs that are Null we should have the
' function return True. If we don't, then the the following line (and
' the 'Else' above it) should be deleted. If you want to deal with
' Nulls, delete nothing and replace the line below as you wish]
100 BooReturnValue = True
110 End If
'[Have the function return if we've found a matching value]
120 CheckIDExists = BooReturnValue
End Function
If you use the above procedure, you'll then be able to replace your original quoted code with the following:
'[Check if the value entered in the Client ID textbox doesn't exist]
If CheckIDExists("tblClient", "ClientID", Forms!frmMainPart1!txtClientID) = False Then
'[Inform the user that the Client ID does not exist]
MsgBox "The Client ID " & Forms!frmMainPart1!txtClientID & " does not exist.", _
vbExclamation, "CLIENT ID DOES NOT EXIST"
End If
You should take extra-special notice that in line 30 of the function I've used the "[" and "]" characters to enclose the field and table names. This is extremely important! You should NEVER EVER use spaces or non-alphanumeric characters in field or table names, and nor should you start them with a number (the same goes for controls, variables etc.). By enclosing field and table names in these square brackets you can be reasonably sure that MS Access's SQL will be able to handle them.
There are far more elegant and correct solutions to your problem, but I think this answers it based on your original code. If you feel it solves your problem, then (as this isn't an MS Access forum), drop a dime, nickel, quarter, shekel, rupee or pound in the collection box of any charity that you feel deserves it. I'd be especially chuffed it if went to DEC / Medicin Sans Frontiers with the current Haiti situation. There's absolutely no obligation, but remember that in life you give and you take, but when you take sometimes it's nice to give... 🙂
Cheers,
RF
_____________________________________________________________
MAXIM 106:
"To know things well, we must know the details; and as they are almost infinite, our knowledge is always superficial and imperfect."
Francois De La Rochefoucauld (1613-1680)
February 18, 2010 at 4:48 pm
Hi All,
Before replying to my above post please read this apology for answering a non-SQL Server related question!
These are my first two posts, and I couldn't find a specific board or post on which to introduce myself. As a lurker I've taken so much from these forums, that I really wanted my first post to help someone; OK, I know it wasn't SQL Sever based but I hope it will have helped someone nevertheless! I come from an MS Access background (15 years of VBA and old school VB) and although I've dabbled in SQL Server and written backends in it, it's only in the last few months that I've had the opportunity to take it seriously. Yesterday I had an ridiculously unbelievable epiphany about joins, and I know they sound easy to well-seasoned practitioners like yourselves, but for me, who's always used a GUI to create views and queries and then tried to translate the SQL it outputs, I've always found myself in a sea of brackets and it's felt like I've been drowning in molasses. I really can't believe that once it clicks how easy it is to to type an SQL statement over two or three (or four or more!) tables and for it to actually be readable and understandable.
Today - thanks to this board and the odd book here and there - I quickly and confidently wrote an update query with a couple of linked subqueries directly into the query pane in SQL Server as SQL. It executed in less than 15 seconds. The same process I'd previously written in VBA looping through an ADO recordset took the best part of 20 minutes. You can imagine that in a small IT department how impressive this looked, but Brownie points aside, most importantly I'm now fully converted and motivated to learn!
I can't begin to match the skills and knowledge of the regular posters, but I'd like to think that I can still contribute to the board; especially as my experiences are the same that a number of people will go through, upsizing their skillset from MS Access to SQL Server.
If anyone can point me in the right direction to introduce myself, or where with my limited knowledge I should post, I'd be extremely grateful. Let's face it, you really don't want people like me answering non-SQL Server VBA questions over here - before you know it you'll be swamped with my kin asking all sorts of crap like why is the sky blue, where do flies go in the winter, and why don't Australians fall off the bottom of the world... ;-):-D
Cheers,
RF
p.s. Although the code in the previous post is VBA, I'd hope that it shows how strongly I feel about maintainability, readibilty, and that when working in a team you have to cater for the lowest common denominator. 🙂
_____________________________________________________________
MAXIM 106:
"To know things well, we must know the details; and as they are almost infinite, our knowledge is always superficial and imperfect."
Francois De La Rochefoucauld (1613-1680)
February 18, 2010 at 7:13 pm
I've only just noticed you originally asked:
I'm trying to enter a numeric value in a text box. eg. client Id,
There's a really important function in VBA called "IsNumeric"; you pass it a value and if it's a number it will return True, otherwise it will return False. It's a great way of checking that a user has input a valid value. It can handle Nulls as well (they return False), so throw it in as a check before you do anything else to make sure the user has entered a number and not a letter.
As a developer, I like to think I'm smarter than the average bear/user, but missing this kind of thing out makes me realise that on plenty of occasions I can be even more dumb (maybe more dumberer?) than I ever thought I could be...
_____________________________________________________________
MAXIM 106:
"To know things well, we must know the details; and as they are almost infinite, our knowledge is always superficial and imperfect."
Francois De La Rochefoucauld (1613-1680)
February 19, 2010 at 8:36 am
Considering that this is the MS Access forum for the site, I think it's perfectly acceptable to post questions and answers about Access here. 🙂
On the original question, I'd set up the control to have two steps. The first would query to see if there are any results with that value. I'm not sure how to do that in Access, but in T-SQL it would be a simple "select case when exists ..." query. Access probably can do the same thing. If that finds that there are results, then run the main query and handle that as usual. If it returns a negative (no results exist), then branch to the message box and all that.
It's been 3 years since I touched MS Access, and my VBA is definitely rusty, so the answer Rainbow gave is probably better.
To Rainbow: Welcome to posting on the forums here. No need to appologize for answering a question. You should see some of the questions in the Nothing to Do with SQL forum. WAY more distant from SQL than Access and VBA! And thanks for introducing yourself. If you want to, you could post that in the Nothing SQL forum. Might start a thread there for that kind of thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 19, 2010 at 7:17 pm
Hi GSquared,
The "Select Case When Exists..." query in T-SQL is lovely and it holds a special place in my heart. It's absent from MS Access's SQL, but I discovered it just a few days ago thanks to Itzik Ben-Gan's "Microsoft SQL Server 2008 T-SQL Fundamentals", and the knowledge of it - and its subsequent application - was what enabled me to write the update query I mentioned in a previous post. A single SQL statement that takes 15 seconds compared to looping through each row in a recordset which takes 20 minutes? I'll never use VB/VBA or ADO as my first choice again when I have to deal with data - it's now SQL every time, and if that doesn't do the job I'll grudgingly go back to my comfort zone. To any developer trying to upsize their skills from MS Access to SQL Server, I can't begin to say how much you really need to understand T-SQL's "Select Case When Exists..." - it replaces the IIf (integrated If) you've used so often in MS Access queries and have grown to love and loathe.
I like your suggestion about the two step approach, and the more I think about it, the more I'm convinced I'd apply it using SQL. Unfortunately, VB/VBA can be quite a beast, in that you always have to look at the datatype that the function you're calling expects; even then you still need to consider how it may react to the value you're passing it. If the value can't be handled then you could just let it trigger an error and deal with in an error handler, but I really hate having to scroll down to the error-handler and then back up to the code. I'm a big fan of having my logic flow and not jump about, so I like to handle as many eventualities as I can before they happen, hence my liberal use of "IsNull"s and "IsNumeric"s, and the assortment of various "Is" function's I've created in my code-library to help me cope with my ever-increasing paranoia about user input.
There is an inherent problem with using DLookUp and the other domain aggreagate functions in VBA - it's quite well documented that they're not very efficient functions and that you should really avoid them in VBA. After saying that, if you're just calling them the once (and not looping through a table or recordset) then the slight trade-off in performance compared to being able to use a single quick 'n' dirty line of maintainable code makes them worthwhile on more than the odd occasion. To improve perfomance of these functions, you should always ensure that you've indexed the field(s) that your criteria is being applied to, but I guess that when I say that, on this board i'm preaching to the converted!
Oh, and thanks a lot for the welcome - as a new poster it genuinely means a lot to me to feel comfortable here and your reply has helped me feel that way. I'll take a look at the "Nothing To Do With SQL" forum, even if I still don't believe they'll be able to tell me why Australians don't fall off the bottom of the world. Keep it quiet, but just between the two of us, I have a feeling it involves all Antipodeans being supplied government-issued magnetic boots, the Earth's iron core, and most chillingly of all... that ex-Australian Rupert Murdoch. :w00t:
Cheers,
RF
_____________________________________________________________
MAXIM 106:
"To know things well, we must know the details; and as they are almost infinite, our knowledge is always superficial and imperfect."
Francois De La Rochefoucauld (1613-1680)
February 21, 2010 at 12:23 pm
If the possible values are limited it may be better to use a ComboBox instead of a TextBox, then you can use a SQL query to populate the ComboBox and use the Not-In-List function to trap and report invalid values - you can then avoid the DLookup altogether.
If not, then DLookup should work fine, however there are syntax errors in your code ...
If Forms![frmMainPart1]![txtClientID].Value <> _
IIf(IsNull(DLookup("ClientID", "tblClient", "ClientID = " & Me!txtClientID)), 0, DLookup("ClientID", "tblClient", "ClientID = " & Me!txtClientID)) Then
MsgBox "Client ID does not exist"
Else
The IsNULL function in VBA returns a true/false condition (unlike in SQL where it replaces NULL values with a 'known' value) - in VBA it only takes a single parameter (the value to be compared). Try something like ...
If txtClientID <> NZ(Dlookup("ClientID", "tblClient", "ClientID=" & txtClientID), 0) then
MsgBox "Client ID does not exist"
Else
The NZ function is the VBA equivalent of the SQL IsNULL function. I typically use it with a DLookup to convert NULL/non-existent values to a known value for comparison (and thereby avoid Null error exceptions)
HTH
🙂
Chris
February 22, 2010 at 5:13 am
Hi RainbowFfolly,
I think your contribution is valuable and outstanding! No reason for any appologie!
Cheers and keep up this attitude
René
🙂
February 22, 2010 at 6:24 am
The reason Australians, et al, don't fall off is the same as for those of us in the northern hemisphere. Earth sucks!
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 22, 2010 at 9:07 am
Since this forum is for Access and all... Here is an alternate approach that might work. I'm not sure about later versions of Access, but it works for 2003.
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQLIDCheck As String
Dim strSQLInsert As String
Dim booldupid As Boolean
strSQLIDCheck = "Select YourValue FROM YourTable where YourValue = '" & Me.FormField.Value & "'"
strSQLInsert = "INSERT INTO YourTable (YourField) " & _
"VALUES ('" & YourField.Value & "') "
booldupid = False
Set rs = New ADODB.Recordset
Set cn = Application.CurrentProject.Connection
rs.Open strSQLIDCheck, cn, adOpenForwardOnly, adLockOptimistic
If rs.EOF Then
booldupid = False
If MsgBox("YourField not in table,do you want to add it now?", vbYesNo, "Text Box") = vbYes Then
DoCmd.RunSQL strSQLInsert
Else
If Not rs.EOF Then
MsgBox ("YourField already exists in table")
End If
End If
rs.Close
Cancel = booldupid
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
February 22, 2010 at 10:07 am
Greg - just be careful - that has SQL injection written all over it.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 22, 2010 at 11:26 am
Matt, thanks for the reminder. I work in a fairly safe environment, so I don't always consider that as a threat. However, I don't want to be complacent. Can you give me an example of something that a user could enter in a text box that would cause a disaster?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
February 22, 2010 at 11:45 am
Greg Snidow (2/22/2010)
Matt, thanks for the reminder. I work in a fairly safe environment, so I don't always consider that as a threat. However, I don't want to be complacent. Can you give me an example of something that a user could enter in a text box that would cause a disaster?
The RunSQL command will have the most potential for damage.
For example if the "YourField" column had the following (everything in bold between the dashes)
-- ');Truncate table YOURTABLE;update audittable set changes=null,userid=null;drop table CLIENTS;Select ('I just deleted your data, and wiped out your audit table --
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 22, 2010 at 12:30 pm
That's pretty scary. I'm thinking an IF statement looking for key words like 'SET','UPDATE','DROP','ALTER', etc might work to thwart that kind of attemp. But if the user does not have permissions to drop or update a table, he or she would get an error anyway. Right?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply