Whats wrong with my code?

  • Thread starter Thread starter Adam
  • Start date Start date
A

Adam

Hi All,

I'm attempting some VB coding on my Access Database, its going slow so
far.

I have a field on my form called 'Text17' (I know, it will be renamed)
and I want it to lookup the values I select from the combobox called
'MonthSelect'.

I want it to lookup those values in the table 'Working Days in Month
tbl' by the Month field and pull out the value in the field 'NoOfDays'.

I've made the below code which is on the combobox:

=============
Private Sub MonthSelect_Change()

Me.Text17.Value = DLookup("NoOfDays", "Working Days in Month tbl",
"Month=" & Me.MonthSelect.Value)

End Sub
=============

Its not working :(

Could anyone explain why please?


Regards,
Adam
 
I'm attempting some VB coding on my Access Database, its going slow so far.
Click to expand...

The process - or the code? ;-)

I have a field on my form called 'Text17' (I know, it will be renamed)
and I want it to lookup the values I select from the combobox called
'MonthSelect'.

I want it to lookup those values in the table 'Working Days in Month
tbl' by the Month field and pull out the value in the field 'NoOfDays'.

Me.Text17.Value = DLookup("NoOfDays", "Working Days in Month tbl",
"Month=" & Me.MonthSelect.Value)
Click to expand...

Nice try! Very close. But since your table name contains some spaces,
you must enclose the name in square brackets: "[Working Days in Month
tbl]"

For that reason, where-ever the syntax allows it, I /always/ enclose my
field & table names in those brackets, whether they need them or not.
Then you won't forget, when they /do/. However, that there are certain
places where the brackets aren't allowed..

PS. "Working Days in Month tbl" is a terrible name for a table! Truly.
Imagine if you write a complex query, and you have have to type than
name 5 times in the query SQL! Try something like tblWorkDaysInMonth,
instead.

Do not use embedded spaces in the names of tables or fields. Start all
table names with "tbl", all forms with "frm", all queries with "qry",
and so on. That's a very common standard. It makes your code look more
consistent.

If 20 forms are named like "frm...", and one is named like "fm...", the
odd man out is instantly visible. But if all of the names are totally
different, typos will be harder to find, because they will not stand
out from the crowd.

HTH,
TC (MVP Access)
http://tc2.atspace.com
 
Hmm, I've made the changes and it says

"Run-time error '2001':

You canceled the previous operation.

Any ideas?
 
There's no way a DLookup() would say that AFAIK.

Show me all the code concerned, and exactly what line the error occurs
on. There are just too many causes of these errors, to be able to
guess, without seeing the code.

HTH,
TC (MVP Access)
http://tc2.atspace.com
 
This is all the code I have in there:

Private Sub MonthSelect_AfterUpdate()

Me.AttendanceActual.Value = DLookup("NoOfDays",
"tblWorkingDaysinMonth", "Month=" & Me.MonthSelect.Value)

End Sub
 
[email protected] said:
This is all the code I have in there:

Private Sub MonthSelect_AfterUpdate()

Me.AttendanceActual.Value = DLookup("NoOfDays",
"tblWorkingDaysinMonth", "Month=" & Me.MonthSelect.Value)

End Sub
Click to expand...

"Month" is a reserved word in Access/VBA as it is the name of a function. Try
putting square brackets around that (better yet change the name of the field).
 
One thing: "Month" is a reserved word in Access. Look up "reserved
words" in online help. You need to enclose that name in ... wait for it
.... square brackets! ..., "[Month]=" & ...

If that doesn't fix it - and I doubt that it will - you just need to
divide & conquor: a standard debugging technique.

(1) Comment out the whole dlookup line. (Ie. leave the event procedure
there, but with no active statements within it.) If that STILL FAILS,
the problem is nothing to do with the dlookup. If it WORKS:

(2) Set Me.AttendanceActual.Value to a fixed value, eg. 99. If that
FAILS, it's something to do with setting a value into that field; it's
nothing to do with the dlookup. If it WORKS:

(3) It sure looks like the dlookup!

HTH,
TC (MVP Access)
http://tc2.atspace.com
(off for the day soon)
 
Hey guys,

I finally got this working!

It was due to the "Month" naming of field thing.

Many Thanks for your help!

The database is looking v nice!

Adam
 
Back
Top