dlookup in a form

[hardyd44]

hi,

this is driving me nuts

I have looked everywhere for a solution and it should work but dosn't !!

I have a form used to book holidays into a database,

The driveres name is supplied from a conmo box and then I want the depot filled in automatically from a lookup from a table containing all the driver details - this is important as the depot is used to drive some queries later.

I have used a dlookup but keep getting a syntax error - I know it is something stupid but i just can't see it. Here is the little snippet of code that is causing me the problems:

Private Sub txtName_AfterUpdate()

FullName = txtName.Value
Depot = DLookup("Depot", "Drivers", "FullName1=" & FullName)
txtDepot.Value = Depot



End Sub

any help will be great

 

[llkhoutx]

There's no dim statement defining Depot or FullName.

Note that in VBA a ".Value" is not required.

FullName = txtName.Value
Depot = DLookup("Depot", "Drivers", "FullName1=" & FullName)
txtDepot.Value = Depot

Try

Me!txtDepot = DLookup("Depot", "Drivers", "FullName1=" & chr(34) & me!txtname & chr(34))

A string field has to be specified (concantenated) with a quotation mark, before and aft. I use chr(34) to avoid coding ambiguities.

 

[carash77ash]

Bascially the same as above but this sets string filter

Dim strfilter As String
strfilter = "driverid = " & Me!driverid 'driverid is the primary key for your drivers table
Me!txtdepot = DLookup(Depot", "Drivers", "FullName" strfilter)

Ash

 

[hardyd44]

thank you llkhoutx

llkhoutx,

thank you
that worked perfectly - maybe a little more help if i may ask - I am a experienced user of excel and have used lookups a lot. any chance of explaining to me how that statment worked so I don't get stuck again, if you have not got time it is no problem and thank you again

Regards

Dean

PS:carash77ash - the first one worked so did not try yours but thank you anyway

 

[ejstefl]

It worked because you need to add quotes around the data to search for if it is text. Since FullName1 must be a text field, when you specify criteria for it you need to add quotes. So you would need to say

FullName1 = "Geroge"

NOT

FullName1 = George

With numbers, you don't need quotes. With dates, you need to use the # sign.

 

[hardyd44]

more help with a dlookup

this soooo frustrating

now using a lookup to check if a date is bank holiday and back at the same problem

Code Snippet:

'checks for weekends
If Weekday(counter, vbMonday) = 6 Then
counter = counter
ElseIf Weekday(counter, vbMonday) = 7 Then
counter = counter
'checks for Bank Holidays from Tbl_bank holidays'
'Tbl_bank holidays just contains the bank holiday dates and a masterkey
ElseIf counter = DLookup("date", "Tbl_bank holidays", "Date=" & counter) Then
counter = counter

Else



except the it does not work for bank holidays - I have to make everything idiot proof and very user friendly as I will not be maintaining the data base when finished and handing it over to not very IT literate people so need a table that bank holidays can be entered in to.

I am at your mercy again !!

 

[carash77ash]

You need to specify table and form.

Perhaps a little more info as to what is the table name, form name and the object that is on the form.

For example if weekday and counter are fields that you want to check on the form currently open. You would use
if me!weekday = 6 then
me!counter = me!counter.

and so on.

Hope this wasn't to confusing.

Ash

 

[jgrayek]

The 2nd problem is the same as the first, just a different data type.

this
DLookup("date", "Tbl_bank holidays", "Date=" & counter)

should be
DLookup("date", "[Tbl_bank holidays]", "Date = #" & counter & "#")

Critera strings need to be built according to the data type of the field you are testing.
Numbers need nothing special
"NumberField = 123" or "NumberField = " & numberVariable

Strings (text values) need to be wrapped in single quotes
"StringField = 'ABC' " or "StringField = '" & textVariable & "'"

Dates need to be wrapped in pound signs (#)
"DateField = #01/14/2006#" or "DateField = #" & dateVariable & "#"

Also your table name 'Tbl_bank holidays' contains a space, because of this it must be wrapped in square parenthesis [Tbl_bank holidays]. I strongly recommend against spaces in table or field names; maybe change it to something like 'Tbl_bank_holidays' or 'tblBankHolidays'

Jesse

 

[RuralGuy]

Using reserved words like Date *will* cause you grief as well!
List of reserved words in Access 2002 and Access 2003
List of Microsoft Jet 4.0 reserved words
Special characters that you must avoid when you work with Access databases

 

[hardyd44]

jgrayek

your amendment worked perfectly - thank you

I am now starting to understand how Dlookup works in VB (very slowly !!) I am so used to the excel way of doing things, it takes a while to get your head round something new.

Thank you to every one who replied to this post and hopefuly I will sort myself out next time (but I would not bank on it !!)

Regards

Dean