After Update two different fields

[Guest]

Is it possible to set an After Update on my combo box for 2 things. I have a
form with a combo box of Client names from my Clients table. It's set up to
update the Client ID field after selecting the Client name.

Private Sub ClientNameCombo_AfterUpdate()
IDLookup = ClientNameCombo.Column(1)
End Sub

I need another combo box (actually I'm going to allow 10 entries of this) on
my form listing items from my Inventory table, but I would like to limit the
records to only the ones with that Client ID. I get an error when I add
another "After Update". I'm not sure if this isn't allowed or if I have a
syntax error. It will also follow that later in my Lots subform I will only
want to list the Inventory items selected above. Is this possible? If so,
how. Any help would be appreciated.

-- Cheryl

 

[Guest]

You can't have two after update events for the same control. You can do both
operations in the same event.

Private Sub ClientNameCombo_AfterUpdate()
IDLookup = ClientNameCombo.Column(1)
Me.InventoryCombo.RowSource = 'Create the rowsource here
Me.InventoryCombo.Requery
End Sub

 

[John Vinson]

Is it possible to set an After Update on my combo box for 2 things. I have a
form with a combo box of Client names from my Clients table. It's set up to
update the Client ID field after selecting the Client name.

Private Sub ClientNameCombo_AfterUpdate()
IDLookup = ClientNameCombo.Column(1)
End Sub

Um? Why not simply make the IDLookup the Bound Column of the combo?

I need another combo box (actually I'm going to allow 10 entries of this)

If you mean that you're storing ten fields in the table... STOP. Your
table structure may well be wrong.

on
my form listing items from my Inventory table, but I would like to limit the
records to only the ones with that Client ID. I get an error when I add
another "After Update". I'm not sure if this isn't allowed or if I have a
syntax error. It will also follow that later in my Lots subform I will only
want to list the Inventory items selected above. Is this possible? If so,
how. Any help would be appreciated.

Without knowing more about your code or your table structures it's
hard to advise. A combo box can have only one AfterUpdate event, but
the code executed by that event can be almost arbitrarily elaborate -
it can certainly do more than one thing!

Please explain the context; what are your table structures, and what
do you want this combo box to accomplish?

John W. Vinson[MVP]

 

[Guest]

Okay, here's what I'm running into now. I have 4 tables (Inventory, Clients,
Jobs, Lots). Inventory & Client tables will be populated/stored separately,
but my form is to enter new Jobs & the Lots that they break down into. On my
form, I have a combo box/drop down to select client by name & pull in the
associated Client_ID. Then, for the boxes I was talking about here, I want
to select which pieces from Inventory will be used for the job, based on the
Client_ID above....also pulling the description & quantity on hand from the
Inventory table. (the issue of adding & subtracting from Inventory will be
dealt with later) In the Jobs table I have allowed for 10 selections from
the Inventory table. Later in the Lots subform, I will want to select from
one of those 10 items from the Jobs table...allowing 10 entries in the Lots
table also. I finally got the list to filter by Client_ID, but now whenever
I get to the end of the form (I added the 2nd set of boxes out of 10 for
testing), it tells me "Index or Primary Key cannot contain a null
value".....but I don't believe I do (obviously I'm wrong..lol). My primary
keys in my Jobs table are Job Number (which I have made a text field...no
duplicates allowed) & Client_ID (duplicates allowed). My primary keys in my
Inventory table are Stock_Code & Client_ID. I shouldn't be adding to this
table either though, just selecting for my Jobs table. I'm not even getting
to the lots subform (in fact I deleted it for now while I'm trying to debug
the rest), but the primary keys there are JobNumber & Lot_Code. I'm
obviously doing something I don't realize I'm doing but have no clue how to
figure out what that is.
I have used Access for quite awhile, for basic things. This is the first
time I'm trying to tackle a large system like this & am getting VERY
frustrated. Let me know if you need any other information. Any help would
be GREATLY appreciated.

 

[John Vinson]

Okay, here's what I'm running into now. I have 4 tables (Inventory, Clients,
Jobs, Lots). Inventory & Client tables will be populated/stored separately,
but my form is to enter new Jobs & the Lots that they break down into. On my
form, I have a combo box/drop down to select client by name & pull in the
associated Client_ID. Then, for the boxes I was talking about here, I want
to select which pieces from Inventory will be used for the job, based on the
Client_ID above....also pulling the description & quantity on hand from the
Inventory table. (the issue of adding & subtracting from Inventory will be
dealt with later) In the Jobs table I have allowed for 10 selections from
the Inventory table. Later in the Lots subform, I will want to select from
one of those 10 items from the Jobs table...allowing 10 entries in the Lots
table also. I finally got the list to filter by Client_ID, but now whenever
I get to the end of the form (I added the 2nd set of boxes out of 10 for
testing), it tells me "Index or Primary Key cannot contain a null
value".....but I don't believe I do (obviously I'm wrong..lol). My primary
keys in my Jobs table are Job Number (which I have made a text field...no
duplicates allowed) & Client_ID (duplicates allowed). My primary keys in my
Inventory table are Stock_Code & Client_ID. I shouldn't be adding to this
table either though, just selecting for my Jobs table. I'm not even getting
to the lots subform (in fact I deleted it for now while I'm trying to debug
the rest), but the primary keys there are JobNumber & Lot_Code. I'm
obviously doing something I don't realize I'm doing but have no clue how to
figure out what that is.
I have used Access for quite awhile, for basic things. This is the first
time I'm trying to tackle a large system like this & am getting VERY
frustrated. Let me know if you need any other information. Any help would
be GREATLY appreciated.

It sounds like you're making the very common error of jumping too
quickly to Form design - or even designing your tables to fit your
forms rather than vice versa.

If you have ten *FIELDS* in the Jobs table for selection from the
Inventory table, *your table structure is simply WRONG*, and will give
you no end of trouble.

If each Job can have multiple Items, and each Item can be used in
multiple Jobs, you need a new table, related one-to-many to both the
Jobs and Inventory tables. You would use a Subform to add data to this
table.

I don't understand how the Client and the inventory are related - does
each client have rights to a particular subset of the inventory, or
what?

John W. Vinson[MVP]