My first attempt at VBA in Access OK but........

[Mellstock]

I have just written my first bit of successful code in Access. On
entering a partner's intials onto an invoice form the code fills in the
partners profile in another field as it will appear on the invoice. The
initials field is used 1) to identify partners on internal reports and
2) to speed up data entry. The code on updating the initials with say
WGD enters Mr W G Doherty into the profile field. So far so good but I
have noticed two problems so far and there may of course be more.

1) The code only works if I use the lookup list to enter the details
into the initials field. If I type W the initials field fills out WGD
(quite useful) but the form does not update the profile it only does it
if I select WGD from the lookup menu. I find it quite useful to just
enter the single initial as it often identifies the correct partner
quickly.

2) If I enter the wrong partner say WGD from the lookup list first time
and want to change it to say IRR (me) the profile field previously
entered automatically does not change from Mr W G Doherty I have to
manually delete and change it.

Can anybody help? Thank you.

 

[Ken Snell \(MVP\)]

Tell us more about how you set up this programming. Which event for the
first control (is it a combo box?) are you using to write data into the
second control (is it a textbox?)? What is the code that you're using?

 

[ruralguy via AccessMonster.com]

This issue is pretty easy to resolve but it could be several problems
depending on how the lookup was implemented. I would personally have the
Invoice and Partner tables joined in the underlying query for the form. Then
whenever you select the Partner, the rest of the data you want would just
appear as if by magic. The Partner selection can still be accomplished with
a ComboBox as you have.

Is it a bound ComboBox? Does it have code in the AfterUpdate event and if so,
what is it.

 

[Mellstock]

It is written as an event after update on the initials field. It uses

Select Case Initials.Value
Case "WGD"
Profile.Value = "Mr W G Doherty"
etc for all the partners

Interestingly going back to my dummy database (a copy of the active
database) to work on this again the initials problem is still there.
However so long as I use the Lookup list in initials if I make a
mistake I can re-enter and the profile gets changed correctly.

In both databases I have had to take off a profile lookup list (my
original strategy before today) but I think I may have done it slightly
differently in each database.

 

[Ken Snell \(MVP\)]

Post the entire code for the AfterUpdate event of that control. You didn't
answer my question about whether it's a combo box or not? If it's a combo
box, there is an easier way to do what you seek, I think.

When you say you type IRR into the control and nothing happens, did you tab
out of the control and then see if the other control is updated?

 

[Mellstock]

I think the not correcting is something to do with how I took off the
previous ? controls off of the profile field as it is OK in the dummy
development database. The live database still shows an arrow (which I
cannot seem to be able to remove) looking for a Combobox connected to a
table of profiles now deleted as I have switched methods.

There is no Combobox for the profiles just all the possible cases (good
job there are only 13 of them). Clumbsy programming maybe but at least
a start. There is a Combobox for the Initials field. The mandatory use
of the drop down box would be a problem for a third party user as they
would probably think it OK if WGD came up after typing W but not
necessarily noticing what is happening (or not happening) in the
profile field.

I would like to know more as there are other developments in the
database where I could not use select case as there would be to many
cases.

Have you seen my "entering data on forms" thread ? again trying to get
data automatically and accurately entered.

 

[Ken Snell \(MVP\)]

Answers/comments inline:

--

Ken Snell
<MS ACCESS MVP>

I think the not correcting is something to do with how I took off the
previous ? controls off of the profile field as it is OK in the dummy
development database. The live database still shows an arrow (which I
cannot seem to be able to remove) looking for a Combobox connected to a
table of profiles now deleted as I have switched methods.

I'm sorry, but I do not understand the above. I am not familiar with your
form's contents, layout, structure, or code. What is an arrow that is
looking for a combo box?

It's important that you give us clear, detailed information about your form
so that we can "see" it in our mind as we try to debug the problem with you.

There is no Combobox for the profiles just all the possible cases (good
job there are only 13 of them). Clumbsy programming maybe but at least
a start. There is a Combobox for the Initials field. The mandatory use
of the drop down box would be a problem for a third party user as they
would probably think it OK if WGD came up after typing W but not
necessarily noticing what is happening (or not happening) in the
profile field.

You are using a combo box for the Initials data? And you want to fill in a
textbox (profile data) based on the selection in the Initials combo box? See
this article at The ACCESS Web for ways to do this:
http://www.mvps.org/access/forms/frm0058.htm

I would like to know more as there are other developments in the
database where I could not use select case as there would be to many
cases.

What would be "too many cases"? I'm obviously not understanding why you
don't want to use a combo box, instead relying on the user having to
remember / enter manually the data?

Have you seen my "entering data on forms" thread ? again trying to get
data automatically and accurately entered.

No, I have not found this thread.