DataBase - field filled out based on option selection
Asked By SylvieB
08-Feb-10 12:43 PM
I have this situation
In a form, I have a combo box named Product line with 4 options to select
from: instrumentation, targetry, engineering, services. I also have a text
box called ???Participation%???. When the user makes a selection from the Product
line, the percentage gets entered in the "Participation%" field automatically.
Those have been defined in he table tblProductLine that contains the product
name associated to the percentage.
I don???t know how to do that. I don???t know much VBA. Any help will be greatly
appreciated.
In advance, thank you.
VBA
(1)
VB
(1)
ParticipationPct
(1)
TblProductLine
(1)
CboProdLine
(1)
ProductLine
(1)
ProdLine
(1)
ProdPct
(1)
Al Campagna replied to SylvieB
SylvieB,
Add the ParticipationPct (I avoid any symbols in names) column to
your combobox. I will call them ProdLine and PartPct for ease...
First Col Second Col
ProdLine PartPct
Are the Participation% values always the same, or might they change
for each ProductLine over time? I will assume that a ProdLine Targetry
today,
with 10% PartPct... could be 15% at some other time.
Use the AfterUpdate event of cboProdLine to set the value of your
PartPct control
Private Sub cboProdLine_AfterUpdate()
PartPct = cboProdLine.Column(1)
End Sub
When referring to combo column values, the columns are numbered
0, 1, 2, 3, etc... so the second column is Column(1)
That could be done with a Macro also...
--
hth
Al Campagna
Microsoft Access MVP 2006-2009
http://home.comcast.net/~cccsolutions/index.html
Sylvie replied to Al Campagna
ge
life."
ct
ext
Thank you Al. I like your idea but I am confused. I made the change on
the combo box like you suggested and created the event but it does not
pass the value of column1 (PartPct) to the text box Participation. I am
getting a run time error "2113" the value you entered is not valid for
this field.
Al Campagna replied to Sylvie
Sylvie,
Please top post if the person who is trying to assist you does so.
It makes the sequence of the threads much easier to follow.
No big deal... just a heads up...
Also, whenever you have code problems, always cut & paste the
code you used into your reply. Describe where and how you placed
that code. We need to see that code... exactly as you have it.
Sorry, but I have to ask... have you done VB module code before?
Also, please describe your combo columns, as you have them now,
in detail... just to make sure we are on the same page...
When referring to combo column values, they are numbered
0, 1, 2, 3, etc... from left to right. The leftmost would be Column(0),
the next to the right Column(1).. etc...
To be sure that the value you are trying to place into a control is
the correct one... place an unbound text control on the form, and set
the Control Source to...
= cboProdLine.Column(1)
Is that the value you want for Participation?
If not... try
= cboProdLine.Column(0)
etc.. til you get the proper value.
Note: When you added ProdPct in the combo, you should have
changed...
Column Count = 1 to Column Count = 2
Column Widths = 1" to Column Widths = 1" ; 1"
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html
Thank you Al. I like your idea but I am confused. I made the change on
the combo box like you suggested and created the event but it does not
pass the value of column1 (PartPct) to the text box Participation. I am
getting a run time error "2113" the value you entered is not valid for
this field.
Sylvie replied to Al Campagna
Sylvie replied to Sylvie
o.
?
l is
r life."
."
a
he
e
Thanks! Neil I should note that I am using the data macro to call a VBA function (by setting a local macro variable to the result of the function call). So this is not for the web, I did not see a problem with calling a VBA function (and various places I have seen online mentioned workarounds for calling VBA from a data macro). But, again, while it works when I insert a record in note? In it I added that I was using the AfterInsert macro to run some VBA code (by setting a local variable to the result of a function call). Maybe that maybe that is the problem Yes, in this case, you need a copy of the VBA in the front end. (for testing you can place it in both - since if you happen to open the back end table directly, then the VBA code is ALSO required since the front end might not even be open and the talk to that may be sitting on your computer. So when the trigger fires, that VBA code will be required. So, the VBA needs to exist in the front end if that all you opened. However, if you
reusable for more than one client. So we now develop to a office standard (and VBA). Our next and software for the cloud will be built to the sharepoint standard (at shape to be compatible for upload? But not with native Access forms. Not with standard VBA. This means it may be *similar* to Access (and thus very easy for us to web, and one for local. In fact you can even execute an open form from VBA and openform works fine if you feed it a web form name. as mentioned, you can have a mix of VBA forms, and web only forms in the same application. When you publish the application, those help you trace the interconnections between all the macros, the same way you can with VBA. This is my biggest concern about all of this, that by going to macros, you client and the web. However for some things like payroll processing etc then you need VBA. Obviously only the controller / accountant person in your organization will need the advance VBA part of the application. For employees to enter their time sheets and hours, check their not for a very, very long time (despite the false dawn of Office 97 with VBA as platform for developing complex meta-apps for small businesses). I do not quite understand
VBA vs Macros DataBase Let me preface this by saying it is a sincere question. I Using Access 2010 I note that help tells us to use macros in preference to VBA for web apps and for greater security. Also a VBA to macro converter is provided but not the other way around. Why so down on VBA? Once upon a time it was considered that for any serious development VBA was preferred to macros because it had a) error trapping (where untrapped errors would crash runtime installation of Access) and b) greater power and flexibility. Are these two advantages of VBA over macros still true with 2010? Other than for web apps (which I am not doing) should I change to macros or stick with VBA? When I took up Access I learned VBA and only did macros when it was the only way for certain operations. Other than
DataBase Absturz vom Speichern des VBA-Projeks Hallo allerseits! In einer Datenbank (im Dateiformat 2002-2003) ist es mir nun schon zum zweiten Mal passiert, daß sich Access 2003 (mit SP3) während des Speicherns des VBA-Projekts unsanft verabschiedet. Die Datenbank ist daraufhin beschädigt und kann auch nicht mehr über aus einem Backup wiederherstellen muß. Öfter noch passiert es, daß Access beim Kompilieren des VBA-Projekts abstürzt; hier sind die Folgen allerdings weniger dramatisch, denn ich verliere ja "nur" die mir aus geeignetste Massnahme ist ein regelmässiger, vorsorglicher Decompile. Die häufigste Ursache für VBA Korruptionen entsteht beim Debuggen und gleichzeitiger Codeänderung. Wenn Du während dem Debuggen irgendwelchen VBA Code änderst, dann solltest Du Dir angewöhnen jeweils vor dem weiterfahren (step-by step oder über komplett) den VBA Code im VBA Editor vorgängig zu kompilieren und zu speichern. Ein anderer Grund für koplett zerschossene VBA Projekte (vorallem gebundene Klassenmodule) habe ich schon mehrfach beobachtet, wenn Objekte kopiert werden. Wenn diese