Update Table Ms Access Vba Dlookup

admin

Hi Ritesh,

It is possible to call the DLookup function in a query to return a matching value from a referenced table, but it would be a very inefficient way of doing it. The correct way, and a far more efficient one, is to join the tables on the relevant primary and foreign key columns. Using Dlookup in Update Query criteria In Access 97, I have a source table that list 10 different factors for each month of the year. After the application's user identifies the month they want to process, I want to populate another working table's fields with just that month's factors.

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!

Update

'[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

Access Vba Dlookup With String

'[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.

Code

' 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

Access

' Nulls, delete nothing and replace the line below as you wish]

Update Table Ms Access Vba Dlookup Value

100 BooReturnValue = True

110 End If

Ms Access Vba Code Sample

'[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)

Hi,
This morning I have been looking all around for an answer and I feel like I am almost there, but I am getting a Syntax error. My table named 'MonthlySalesTax' is setup as below.
IDShip_To_CityShip_To_State
1SeattleWA
2
3
4San FranciscoCA
5
6

Ms Access Vba Tutorial


I want the query to auto fill the Ship_To_Ctiy and Ship_To_State if null to the above row, so Seattle and WA would fill down until it his San Francisco and CA. then San Francisco and CA would auto fill down until the next one. After researching it seemed like an update query with dlookup would work best. Here is what I have, but it comes back with and error. Any idea where the error lies or if I am approaching this incorrectly.
UPDATE MonthlySalesTax SET MonthlySalesTax.[Ship_To_State] =
DLookUp('[Ship_To_State]','MonthlySalesTax','[ID] = [ID]-1'),

Access Vba Dlookup With Variable

MonthlySalesTax.[Ship_To_City] =
DLookUp('[Ship_To_City]','MonthlySalesTax','[ID] = [ID]-1'),
WHERE (((MonthlySalesTax.[Ship_To_State]) Is Null)) OR

Dlookup In Ms Access

(((MonthlySalesTax.[Ship_To_City]) Is Null));
Thanks,

Ms Access Vba Code

Noel