Access 2007

[Phreake]

Hi,

I am creating an invenotry database for our asset managment. for this
I am using Access 2007 on a Win XP Pro machine of which I am an
administrator.

In my Database I have a username field and a users country field (both
in a users table), what i want to do is auto populate the the users
country when the user is selected from a combo box.

A colleague knows how to do this by running an Event procedure on
"After Update". but to test this he put in the following code to see
if the After update works.

Public Function trythis()
MsgBox ("Hello")

End Function

but every time we add in a user name we get the following error:

"The expression After Update you entered as the event property setting
produced the following error: Object or class does not support the set
of events.
*The expression may not result in the name of a macro, the name of the
user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event or macro.

I originally had this DB on a network drive and then moved it to my
PC, i have also added the locations to the Trusted Locations in the
settings. I have even tried doing a run-as and used my domain admin
account, but still no joy.

I have re-installed, repaired access and office.


Please help as this is doing my nut in...i actually like office 2007
but it has been causing alot of new found issues recently!!

Thanks in advance

Zubair

 

[bcap]

Are you saying that the code you posted is all *inside* an AfterUpdate event
procedure? If so, then you are way wide of the mark: you can't declare a
function inside another procedure!

If you want to demonstrate that an AfterUpdate event procedure is working,
it should look like this:

Private Sub Form_AfterUpdate()

MsgBox "Here we are inside an AfterUpdate event procedure"

End Sub

 

[Phreake]

Are you saying that the code you posted is all *inside* an AfterUpdate event
procedure?  If so, then you are way wide of the mark: you can't declarea
function inside another procedure!

If you want to demonstrate that an AfterUpdate event procedure is working,
it should look like this:

Private Sub Form_AfterUpdate()

    MsgBox "Here we are inside an AfterUpdate event procedure"

End Sub

Hi,

I am creating an invenotry database for our asset managment. for this
I am using Access 2007 on a Win XP Pro machine of which I am an
administrator.

In my Database I have a username field and a users country field (both
in a users table), what i want to do is auto populate the the users
country when the user is selected from a combo box.

A colleague knows how to do this by running an Event procedure on
"After Update". but to test this he put in the following code to see
if the After update works.

Public Function trythis()
MsgBox ("Hello")

End Function

but every time we add in a user name we get the following error:

"The expression After Update you entered as the event property setting
produced the following error: Object or class does not support the set
of events.
*The expression may not result in the name of a macro, the name of the
user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event or macro.

I originally had this DB on a network drive and then moved it to my
PC, i have also added the locations to the Trusted Locations in the
settings. I have even tried doing a run-as and used my domain admin
account, but still no joy.

I have re-installed, repaired access and office.

Please help as this is doing my nut in...i actually like office 2007
but it has been causing alot of new found issues recently!!

Thanks in advance

Zubair- Hide quoted text -

- Show quoted text -

Hi bcap,

i was mistaken in what i wrote, basically that code was saved as a
module, and it was called in the after update for that particular
field.

i tried your code as well, with the same results but just at another
time, but the code is not really the problem, it's just the fact that
Access doesn't seem to auto update any fields or do anything
automatically, do you understand what i am trying to say? (i'm not
very good at explaining ;o) )

 

[bcap]

No, I don't really understand what you are trying to do. You say that you
want a user's country to be updated automatically, but on what basis?
Access doesn't know what country a user is in!

-

Hi bcap,

i was mistaken in what i wrote, basically that code was saved as a
module, and it was called in the after update for that particular
field.

i tried your code as well, with the same results but just at another
time, but the code is not really the problem, it's just the fact that
Access doesn't seem to auto update any fields or do anything
automatically, do you understand what i am trying to say? (i'm not
very good at explaining ;o) )

 

[Phreake]

No, I don't really understand what you are trying to do.  You say that you
want a user's country to be updated automatically, but on what basis?
Access doesn't know what country a user is in!


-

Hi bcap,

i was mistaken in what i wrote, basically that code was saved as a
module, and it was called in the after update for that particular
field.

i tried your code as well, with the same results but just at another
time, but the code is not really the problem, it's just the fact that
Access doesn't seem to auto update any fields or do anything
automatically, do you understand what i am trying to say? (i'm not
very good at explaining ;o) )

In the users table i have 3 fields User_ID (primary field, autonumber)
Username and Country. So for example one record would be {autonumber},
Joe Bloggs, UK. In the main form the username is a combox box which
stores the User_ID (but on the form you see the users name), then
there is another field which is linked to the users country. I want
this field to automatically put in the users country based on the
user_ID of the previous field. I think if i sent an picture of it you
would understand it a bit better.

 

[bcap]

OK, I've got it now.

The simplest way to do this is to set the ControlSource for the country text
box to this:

=Dlookup("Country","Users","User_ID=" & [user_id])




In the users table i have 3 fields User_ID (primary field, autonumber)
Username and Country. So for example one record would be {autonumber},
Joe Bloggs, UK. In the main form the username is a combox box which
stores the User_ID (but on the form you see the users name), then
there is another field which is linked to the users country. I want
this field to automatically put in the users country based on the
user_ID of the previous field. I think if i sent an picture of it you
would understand it a bit better.

 

[boblarson]

You should NOT be storing the user's country again in the database. It is
stored in the user table and that is the ONLY place it should be stored. But
you can display it on the form by including it in the combo box's rowsource
and then in the after update event of the combo you can set the text box to:

Me.YourTextBoxNameHere = Me.YourComboBoxNameHere.Column(x)

where x is the column number which has the country data. Since the columns
are "zero-based" the second column over in the rowsource is referenced by
Column(1) and the third would be Column(2), etc.
--
Bob Larson

Tutorials at http://www.btabdevelopment.com

__________________________________

 

[boblarson]

Actually bcap, that is not the simplest way. The simplest way is the way I
described in that you just include the value in the combo box rowsource and
then refer to it.
--
Bob Larson

Tutorials at http://www.btabdevelopment.com

__________________________________

 

[bcap]

I disagree. What I described is the simplest way. Not the most efficient
way, but clearly the simplest.

Actually bcap, that is not the simplest way. The simplest way is the way
I
described in that you just include the value in the combo box rowsource
and
then refer to it.
--
Bob Larson

Tutorials at http://www.btabdevelopment.com

__________________________________

OK, I've got it now.

The simplest way to do this is to set the ControlSource for the country
text
box to this:

=Dlookup("Country","Users","User_ID=" & [user_id])




In the users table i have 3 fields User_ID (primary field, autonumber)
Username and Country. So for example one record would be {autonumber},
Joe Bloggs, UK. In the main form the username is a combox box which
stores the User_ID (but on the form you see the users name), then
there is another field which is linked to the users country. I want
this field to automatically put in the users country based on the
user_ID of the previous field. I think if i sent an picture of it you
would understand it a bit better.

 

[Phreake]

I disagree.  What I described is the simplest way.  Not the most efficient
way, but clearly the simplest.

Actually bcap, that is not the simplest way.  The simplest way is theway
I
described in that you just include the value in the combo box rowsource
and
then refer to it.

Tutorials athttp://www.btabdevelopment.com

__________________________________

OK, I've got it now.
The simplest way to do this is to set the ControlSource for the country
text
box to this:
=Dlookup("Country","Users","User_ID=" & [user_id])
In the users table i have 3 fields User_ID (primary field, autonumber)
Username and Country. So for example one record would be {autonumber},
Joe Bloggs, UK. In the main form the username is a combox box which
stores the User_ID (but on the form you see the users name), then
there is another field which is linked to the users country. I want
this field to automatically put in the users country based on the
user_ID of the previous field. I think if i sent an picture of it you
would understand it a bit better.- Hide quoted text -

- Show quoted text -

Bcap you are a genius!! i had a bit of a problem to begin with but
that was due to the form being corrupted. After i recreated another
form your suggestion worked perfectly.
Bob i see where you are coming from (and your idea has been noted),
but at the moment time is of the essence and i need to get this out
ASAP, it will hopefully be replaced by a managed system anyway.

But again thanks to both of you for your help!!

Zubair

 

[Larry Linson]

I disagree. What I described is the simplest way.
Not the most efficient way, but clearly the simplest.

Help me understand. Why is

Me.txtCountry = Dlookup("Country","Users","User_ID=" & [user_id])

simpler than is

Me.txtCountry = Me.cboUserId.Column(2) ?


Certainly it is more efficient to do the latter, because the information
has already been retrieved for the RowSource of the ComboBox, and I don't
understand what is "more complex" about it. Looks to me as if the DLookup
is equally, or more, complex, as well as requiring more disk accesses to
retrieve the desired data.

I'll also agree with Bob that there should be only one authoritative source
for each item of data... one case I can think of that would justify
saving the Country again would be if it changes over time... say a
sales-person
making calls on a sales route in Europe with stops in several different
countries and the user table is updated for location as the salesperson
travels.

Larry Linson
Microsoft Office Access MVP

 

[bcap]

Help me understand. Why is

Me.txtCountry = Dlookup("Country","Users","User_ID=" & [user_id])

simpler than is

Me.txtCountry = Me.cboUserId.Column(2) ?

It isn't, but then that isn't what I suggested. I recommend that if you
want to question my suggestion then you first read it properly.

 

[Larry Linson]

Help me understand. Why is

Me.txtCountry = Dlookup("Country","Users","User_ID=" & [user_id])

simpler than is

Me.txtCountry = Me.cboUserId.Column(2) ?

It isn't, but then that isn't what I suggested. I recommend that if you
want to question my suggestion then you first read it properly.

I'm reading just as "properly" as I can manage. Where did I go astray?

I copied and pasted directly from the following post... I added a Text Box
(there wasn't one in what you posted) but I don't see that it's any simpler
if used as the Control Source (you have to come up with the DLookup
statement code, wherever you use it):

OK, I've got it now.

The simplest way to do this is to set the ControlSource for the country text
box to this:

=Dlookup("Country","Users","User_ID=" & [user_id])

On the other, referring to the Column, I used Bob Larson's suggestion, but
assigned names that I thought were more obvious for the objects.

Am I missing a post in which you recommended something simpler?

Larry Linson
Microsoft Office Access MVP

 

[bcap]

Help me understand. Why is

Me.txtCountry = Dlookup("Country","Users","User_ID=" & [user_id])

simpler than is

Me.txtCountry = Me.cboUserId.Column(2) ?

It isn't, but then that isn't what I suggested. I recommend that if you
want to question my suggestion then you first read it properly.

I'm reading just as "properly" as I can manage. Where did I go astray?

I copied and pasted directly from the following post... I added a Text Box
(there wasn't one in what you posted) but I don't see that it's any
simpler if used as the Control Source (you have to come up with the
DLookup statement code, wherever you use it):

<sigh>

You went astray by adding the Text Box, thereby implying the need to create
an event procedure. There wasn't a Text Box in what I posted because what I
posted wasn't a code statement, it was merely an expression to be used as a
ControlSource for a calculated control. This was perfectly clear in my
post, and the OP seems to have had no difficulty in understanding it.

The OP didn't have to come up with the Dlookup expression, I gave it to him.

To use the Dlookup expression *as the ControlSource*, the OP has to do *one*
simple thing, namely to copy the expression provided and paste it into the
Control Source property in the properties window.

Conversely, the other suggestion is riddled with implicit assumptions,
namely:

- That the OP knows how to create a combo box with multiple columns
- That he knows how to specify which column is bound and which is displayed
- That he knows how and where to create an event procedure in which to paste
the provided code statement.
- That he knows to disable or lock the text box so that confused users don't
type into it.

Simple enough to you and me, but reading the original post suggests that the
OP may struggle with these things (particularly since all he was given was a
throwaway code line with no help on how to do these various implied
actions).

Anyway, the OP evidently understood my suggestion, has implemented it and is
happy. I'm done here.

 

[Larry Linson]

Help me understand. Why is

Me.txtCountry = Dlookup("Country","Users","User_ID=" & [user_id])

simpler than is

Me.txtCountry = Me.cboUserId.Column(2) ?



It isn't, but then that isn't what I suggested. I recommend that if you
want to question my suggestion then you first read it properly.

I'm reading just as "properly" as I can manage. Where did I go astray?

I copied and pasted directly from the following post... I added a Text
Box (there wasn't one in what you posted) but I don't see that it's any
simpler if used as the Control Source (you have to come up with the
DLookup statement code, wherever you use it):

<sigh>

You went astray by adding the Text Box, thereby implying the need to
create an event procedure. There wasn't a Text Box in what I posted
because what I posted wasn't a code statement, it was merely an expression
to be used as a ControlSource for a calculated control. This was
perfectly clear in my post, and the OP seems to have had no difficulty in
understanding it.

The OP didn't have to come up with the Dlookup expression, I gave it to
him.

To use the Dlookup expression *as the ControlSource*, the OP has to do
*one* simple thing, namely to copy the expression provided and paste it
into the Control Source property in the properties window.

Conversely, the other suggestion is riddled with implicit assumptions,
namely:

- That the OP knows how to create a combo box with multiple columns
- That he knows how to specify which column is bound and which is
displayed
- That he knows how and where to create an event procedure in which to
paste the provided code statement.
- That he knows to disable or lock the text box so that confused users
don't type into it.

Simple enough to you and me, but reading the original post suggests that
the OP may struggle with these things (particularly since all he was given
was a throwaway code line with no help on how to do these various implied
actions).

Anyway, the OP evidently understood my suggestion, has implemented it and
is happy. I'm done here.

OK, acknowledged. I never thought about adding a Text Box would complicate
the situation so. I extend my apology for any unintended insult.

Partly, my response was due to my viewing newsgroup responses as a
"teaching/learning" experience, and that others, besides the o.p., would,
indeed, have to create their own DLookup. And, unless there are vast
numbers of Records, the performance penalty for using DLookup would probably
not be discernable as you sit at the keyboard and use the application, so
wouldn't be a factor in the choice.

Larry Linson
Microsoft Office Access MVP