Property ControlSource

[Guest]

I have a combo box that displays Tariff information.

I have 4 fields on this particular form, EDLITariff1NumberID,
EDLITariff2NumberID, EDLITariff3NumberID, EDLITariff4NumberID. Based on
which tariff number they are on, when the user clicks on the combo box, I
only want it to select the tariff information associated with that ID (the
tariff they are wanting to change/edit). So, one combo box to use for all 4
fields. The combo box is very large so I cannot have 4 of these on the form
or I get 'cannot open anymore databases . . .'. The user needs to beable to
select ALL tariffs for each tariff line (which is why I need this large
combo!)

ANYWAYS, to make a long story short(er), I am changing the rowsource in the
combo box to match the ID of the tariff number they are on. This works
slick, however, when the user (ME!) tries to change the tariff number the
error is "Control can't be edited; it's bound to expression '16057'".

Here is the code on the GotFocus property of the combo box:
If me.txtTariffNbr = 1 then
Me.cboTariffNumber.ControlSource = "=" & Me.EDLITariff1NumberID
End if
If me.txtTariffNbr = 2 then
Me.cboTariffNumber.ControlSource = "=" & Me.EDLITariff2NumberID
End if

and so on . . . . - oh then I do a me.cboTariffNumber.requery

Any advice or solutions (yes yes yes!) is GREATLY appreciated!

Thanks!!!!

Janis in MinneNO-SNOWta (yiipeeee it's raining!)

 

[Jeff Boyce]

Janis

I may not fully understand the data structure you are working from (you
described a form's 'controls', but not the underlying data).

When data (yes, I know, you mentioned the form, not the data) is organized
into repeating fields in a table (yes, I know...), the database would most
like benefit from further normalization. If you started out with a
spreadsheet (in which repeating fields are pretty much a necessity), then
only imported/copied the structure into Access, you will not get the best
(and easiest) use of Access' features/functions.

And, besides, you will encounter serious difficulty making Access do things
it isn't designed to handle easily (oops! that may be what you posted
about!).

Are you willing to describe a bit of how the underlying data is structured
(i.e., the fields in your tables)? Having this additional information could
lead to further suggestions...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Guest]

First I need to thank you for your time!!! And secondly, how the heck do I
describe this effeciently and MAKE sense? hmmm,

The table that each combo box uses is a table called Tariff with about
35,000 tariff numbers, effective dates . . . blah blah blah per Customs.

Because of the way we need to send data to US Customs, per line item there
can be up to 4 tariff numbers associated. The user needs to be able to
select each tariff number 3 different ways, thus, a total of 3 combo boxes
PER tariff (4 of them). This adds up to 12 dropdowns (3 per tariff). UGH!

Basically, in a nut shell, when I have all 4 tariff numbers with 3 combo
boxes next to each field, allowing them to select the appropriate tariff nbr,
I (a user) get the message telling me unable to open anymore databases. The
solution I was told? Rid the large dropdowns/combo boxes. I had 12 of them,
now down to 3. BUT, I need the remaining tariff numbers to utilize the
dropdowns/queries still.

I know what tariff number I am on - I just need the dropdown Control Source
to look at the TariffID appropriate. Can I set the Control Source, bind
it, depending on what tariff field I am on? The code I posted early was on
the GotFocus of the combo box.

Does this make sense? UGH!!! ha!

I hope you understand better! Thanks sooooo much!!!!

Janis

 

[Jeff Boyce]

Janis

You have described a "how", but still not a what. To make the best use of
what Access can to help, you need to start with the data and the
relationships. My sense is that you are still talking about forms (combo
boxes). If you haven't normalized your data, you will be "fighting" Access
to get it to do what you want.

I don't understand enough about tariffs to offer suggestions. Can you
describe the underlying data the way you would to an 80 year old
grandmother? You wouldn't be talking about combo boxes and lookup tables,
right?

--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Guest]

Yes I am talking about forms! To make this simple: I have a form with 4
fields that need lookups and they can all feed off of one lookup combo box.
Instead of setting the control source to a particular field, I want it empty,
and then depending on which tariff number I am on I will set the control
source in the combo box to that ID. That way when the user uses the dropdown
and selects a tariff, that ID will pop into my main form for the
corresponding field, and the Tariff Number will display in the tariff field.
After Update of the combo box I will update the necessary fields with the
information from the combo box.

The data? Well, the form is based on table EntryDetailLineItem and the
fields I need binding for the combo box are Me.EDLITariff1NumberID,
Me.EDLITariff2NumberID, Me.EDLITariff3NumberID, Me.EDLITariff4NumberID.
The Id's point to the table named Tariff and the key to the Tariff is
TariffID (which is the ID I want to store in the EntryDetailLineItem table).
I display the Tariff Number but behind the scenes I also store the ID.

If I had to explain this to my grandma she would be concerned that she would
have to touch a MOUSE! hahahha

Really what I'm trying to do doesn't sound difficult - instead of a bound
control source on a lookup, I want to bind it depending on which field I am
on . . . bind it with code instead of selecting the source control in the
combo box. That way instead of having many lookups on this form, I only have
a few.

Does THIS make any more sense??? I'm about to cry! hahahha JUST KIDDING!

Janis

 

[Jeff Boyce]

Janis

I am not trying to be difficult. I am trying to understand. From your
description, you have four fields named something like EDLITariffxNumberID.
My earlier point was that this is a spreadsheetly way of storing tariff
data.

You mentioned 4 separate tariff fields, all named the same (except for the
'x'/#). Do you have four different types of tariffs? Do you have 4
different situations in which any tariff might apply? Why are you using 4
fields instead of one?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Yes - I need 4 different Tariff ID's - a line item can include up to 4
different tariff numbers (which is why per line I need those combo boxes - 4
of them - but I get the database error). So I have 4 fields (4 diff tariff
ID's) per line. I am wanting to only use 1 combo box to find all 4 tariff
id's out.

Does this make sense? I'm sorry I'm so vague . . . I'm really trying
though! Thanks so very very much for helping!

Janis

 

[Jeff Boyce]

OK, I think we're getting closer.

If you only had a spreadsheet, you are right, you'd need to use a column for
each tariff number for a line item.

But think about an Order system. Some folks would call up and order one
item, so their Order would only need one column for the item (plus the
amount column, plus the ....). But the next person who calls orders 4
items, so the spreadsheet grows, adding new columns because of the
additional items.

Your "one-column-per-tariff" is just like the "one column per ordered item".
It works (and may be the only way it works) when you only have a
spreadsheet.

Access is a relational database, though, and doesn't work well by adding
columns for additional items. Here's why it doesn't make sense to limit
yourself to doing spreadsheety things in Access -- what will you do when
5(!) tariffs are required? Or the number is dropped to 3?! Do you really
want to have to go back through all your tables and queries and forms and
reports and code and change them all?

Instead, consider a table something like:

tblLineItemTariff
ItemID
TariffID

That's it. If a particular LineItem (found from the Item table by using its
ItemID) has only one tariff, this new table has only one row which uses that
ItemID. If it has 4, there are four rows. And if, at some future date, you
need 10 tariffs, you add rows, not columns.

Now for the form... instead of multiple combo boxes (add another 3, they
just bumped the number of tariffs up again!), use a main form to hold the
Item information, and a subform to hold any/all rows of related tariff info
(again, one row per Item/Tariff combination).

Best of luck on your project!

Jeff Boyce
Microsoft Office/Access MVP