ComboBox Goes Blank When Form's AfterUpdate Code Runs

  • Thread starter Thread starter Christopher Hickman via AccessMonster.com
  • Start date Start date
C

Christopher Hickman via AccessMonster.com

I have a form for entering monthly cost data for a bunch of equipment. The
table has an EquipID, a year, and 12 more columns for the months in the
year. The form is meant to display all the equipment in the database,
filtered by a given year and month. The way I do this is I have two combo
boxes, one for month, and one for year. The Month_Combo has a value list
row source listing the months, and the Year_Combo just pulls the years
available in the table (SELECT DISTINCT Year FROM CostData;).

Both combo boxes have code on their AfterUpdate event that simply calls the
AfterUpdate code for the form:

Private Sub Form_AfterUpdate()
If Not IsNull(Year_Combo) Then
'This sets the filter to only show me the selected year
Me.Filter = "[Year] = " & Year_Combo
Me.FilterOn = True
End If
If Not IsNull(Month_Combo) Then
'This sets the control source for the Cost text field
'so that it matches the selection from the month combo
'box (The column names match the entries in the Month_Combo)
Me.Cost.ControlSource = Month_Combo
End If
End Sub

This functions as I want, making the cost fields in the form display the
data from the appropriate column for the appropriate year. The problem is
that whenever this code is executed, Month_Combo goes blank. Year_Combo
goes blank if Month_Combo changes, too.

I've tried adding Me.Month_Combo.Requery to try to make it refresh, but
that didn't work. If I comment out the setting of the ControlSource, it
fixes the display problem (but of course my functionality is then gone).
This is driving me batty.

Does anyone have any idea how to make my combo boxes display their
selections?

Thanks,
Christopher
 
Christopher Hickman via AccessMonster.co said:
I have a form for entering monthly cost data for a bunch of equipment. The
table has an EquipID, a year, and 12 more columns for the months in the
year.
Click to expand...

Right there is the source of the problem. Storing data in fieldnames IS
SIMPLY WRONG, and will make it much harder to do what you want. If you have a
one to many relationship from equipment to costs, store it *as a one to many
relationship* in two tables, linked by EquipmentID. The second table would
contain a Date field to specify the cost for the month containing that date
(use the first of the month for example).

This would require *no code at all* - just a simple parameter query. And, it
would work.

John W. Vinson
 
Ah, fundamental problems. See, they dropped this project on me because I
have a programming background, but I'm not a database guy (I guess that is
embarrassingly obvious). And last night I thought I'd come up with the
solution to my design issue: Add even more columns. :(

I had decided that I would simply remove the year field from the table, and
add twelve columns per year needed, one for each month in the year. That
would give me a one to one for all the equipment. Unfortunately I don't
quite grok the one to many concept, I guess.

You are suggesting that I make my CostData table be just EquipID and Date,
then have another table (let's call it MonthlyCosts) with what? Date,
Cost, and EquipID? Then I can query for Cost in MonthlyCosts where the
EquipID and Date are equal to the EquipID and Date in CostData? That gives
me one table where the EquipID is key, and another where it is not. Is
that how you make the one to many?

Sorry if it is a dumb question...
 
Ah, fundamental problems. See, they dropped this project on me because I
have a programming background, but I'm not a database guy (I guess that is
embarrassingly obvious). And last night I thought I'd come up with the
solution to my design issue: Add even more columns. :(

I had decided that I would simply remove the year field from the table, and
add twelve columns per year needed, one for each month in the year. That
would give me a one to one for all the equipment. Unfortunately I don't
quite grok the one to many concept, I guess.
Click to expand...

"Fields are expensive; records are cheap". If you have many of the
same kind of thing (e.g. monthly costs) then create a table for which
each record holds one of those things, and add as many records to that
table as you have things.
You are suggesting that I make my CostData table be just EquipID and Date,
then have another table (let's call it MonthlyCosts) with what?
Click to expand...

No, I am not suggesting that, though I can see that this was unclear!

You would have two tables: a table of Equipment, with EquipID as its
Primary Key.

You would have a second table, MonthlyCosts, with fields:

MonthlyCostID Autonumber (Primary Key)
EquipID <link to the Equipment table, this is the one to many>
CostDate Date/Time <e.g. the first day of the month>
EquipCost Currency <cost for this equipment for this month>


John W. Vinson[MVP]
 
I tried that, just without the autonumber key. I understand that would
keep the information I need, unfortunately I *don't* understand how to bend
that design into a form that will show my users what they need to see.

What I ended up having to go with was this: I have a seperate CostData
table for each year, with a form built on top of each of those. I have the
following design:

EquipID (Text) (Primary Key)
Jan (Currency)
Feb (Currency)
Mar (Currency)
Apr (Currency)
May (Currency)
Jun (Currency)
Jul (Currency)
Aug (Currency)
Sep (Currency)
Oct (Currency)
Nov (Currency)
Dec (Currency)

Since this way I have a one-to-one, it is easy to make a form that shows
all of the equipment, even if there is currently no CostData record for
that equipment, and it creates the record on the fly if the user enters
cost data. Rather than screw around trying to show one month at a time, I
have a tabular form that shows all twelve months at once for each record.
Not the ideal form I had in mind, but it is simple. Hopefully one day I'll
understand how to properly design the view for the one-to-many model.
Until then, I'll just have to dumb it down to my level. ;)

Thanks for taking the time to help me.
 
I tried that, just without the autonumber key. I understand that would
keep the information I need, unfortunately I *don't* understand how to bend
that design into a form that will show my users what they need to see.

What I ended up having to go with was this: I have a seperate CostData
table for each year, with a form built on top of each of those. I have the
following design:
Click to expand...

Christopher, you're not getting it.

You're not even reading what I'm suggesting.

THIS DESIGN IS WRONG.

Storing data in fieldnames (months) *is bad design*.
Storing data in tablenames (years) IS EVEN WORSE.
One to one relationships (unless you're doing
Subclassing/Superclassing or some other highly technical tricks) is
almost never a good idea.

Please consider this design. You would have two tables:

Equipment
EquipmentID <Primary Key>
EquipmentName
<other information about the equipment>

MonthlyCost
EquipmentID <NOT a primary key, it's just used as a link>
CostDate Date/Time
Cost Currency

You can ctrl-click EquipmentID and CostDate and click the Primary Key
icon to make these *two* fields a joint Primary Key (this will prevent
you from mistakenly entering the same equipment item for the same
month twice).

The MonthlyCost table will contain records like

123; 1/1/2005; $240
123; 2/1/2005; $115
123; 4/1/2005; $200

Note that if there is no cost in March, you simply don't need to enter
a record for March.

The user interface for this may answer your concern about usability:
you would create a Form based on Equipment, and on it put a continuous
Subform based on MonthlyCost. The EquipmentID would be the
master/child link of the subform, so that the subform would show (and
allow entry of) data for only the one piece of equipment shown on the
main form, and keep in synch as you move from item to item. You can
set the Format property of CostDate to "mmmm" to show just the month
name (you'll need to enter a real date though).

This form and subform will show you the information that you need,
allow you to enter the information that you get, and still let you
search for data and generate totals for a quarter, a year, annual
totals across multiple years, etc. etc. Your current design WILL NOT
let you do this.

John W. Vinson[MVP]
 
Ok, I designed it like you said and I am able to do a subform that shows
the cost for each month for each device. However, my users want a form
where they can select a year and see Jan, Feb, Mar, etc. for each device.
I managed to make a form that *shows* that, but it won't allow entry (the
recordset is not updatable). Here is the query that my form uses for a
RecordSource:

SELECT DISTINCT Inventory.EquipID
, Inventory.[Cell-Phone#]
, (SELECT Cost FROM CostData WHERE CostData.EquipID = Inventory.EquipID AND
Date = DateValue("1/1/2004")) AS Jan
, (SELECT Cost FROM CostData WHERE CostData.EquipID = Inventory.EquipID AND
Date = DateValue("2/1/2004")) AS Feb
, (SELECT Cost FROM CostData WHERE CostData.EquipID = Inventory.EquipID AND
Date = DateValue("3/1/2004")) AS Mar
, (SELECT Cost FROM CostData WHERE CostData.EquipID = Inventory.EquipID AND
Date = DateValue("4/1/2004")) AS Apr
, (SELECT Cost FROM CostData WHERE CostData.EquipID = Inventory.EquipID AND
Date = DateValue("5/1/2004")) AS May
, (SELECT Cost FROM CostData WHERE CostData.EquipID = Inventory.EquipID AND
Date = DateValue("6/1/2004")) AS Jun
, (SELECT Cost FROM CostData WHERE CostData.EquipID = Inventory.EquipID AND
Date = DateValue("7/1/2004")) AS Jul
, (SELECT Cost FROM CostData WHERE CostData.EquipID = Inventory.EquipID AND
Date = DateValue("8/1/2004")) AS Aug
, (SELECT Cost FROM CostData WHERE CostData.EquipID = Inventory.EquipID AND
Date = DateValue("9/1/2004")) AS Sep
, (SELECT Cost FROM CostData WHERE CostData.EquipID = Inventory.EquipID AND
Date = DateValue("10/1/2004")) AS Oct
, (SELECT Cost FROM CostData WHERE CostData.EquipID = Inventory.EquipID AND
Date = DateValue("11/1/2004")) AS Nov
, (SELECT Cost FROM CostData WHERE CostData.EquipID = Inventory.EquipID AND
Date = DateValue("12/1/2004")) AS Dec
FROM Inventory LEFT JOIN CostData ON Inventory.EquipID = CostData.EquipID
WHERE (((Inventory.EquipID) Like "C-*") AND ((Year([Date]))="2004" Or (Year
([Date])) Is Null))

This query is built programatically when the form loads (On Open) and when
a new year is selected from the combo box (On Changed).

The Inventory table has all my inventory info (I use EquipID and Cell-
Phone# from that table in this query). EquipIDs that start with "C-" are
cell phones. EquipID is primary key in Inventory table.

The CostData table holds EquipID, Date, and Cost. The date is always the
first day of a given month (e.g. 1/1/2005, 5/1/2004, etc.). EquipID and
Date are joint primary keys in the CostData table.

How can I make this recordset updatable so that my users will see the cost
data for each month *and* be able to edit it?
 
Ok, I found out that the DISTINCT keyword will make the recordset not
updatable. I also realized that I no longer needed that in my query. I
took it out, but it still does not give me an updatable recordset.
 
I'm wrong! :(

I *do* need the DISTINCT to avoid dupe records. So now I'm screwed. :(
How the heck am I going to do this????
 
Ok, I figured out that I can pull the information I need for a given cell
with this query:

(SELECT CostData.Cost, CostData.EquipID, CostData.Date FROM Inventory INNER
JOIN CostData ON Inventory.EquipID = CostData.EquipID WHERE ((
(CostData.EquipID)="C-123-456-7890") AND ((CostData.Date)=DateValue
("1/1/2005"))) AS Jan)

C-123-456-7890 is the EquipID. I built this query and ran it and it gives
me the specific data that I want to show in the form, AND it is updatable.
The problem is that when I build the query programmatically and set it as
the Control Source of my text box, it (a) doesn't work (shows "#Name?"),
and (b) puts "C-123-456-7890" in EVERY row of the continuous form (I want
it to reference the EquipID that is in that row).

Here's my code:

Private Sub UpdateControlSource()

Dim sqlQuery As String
Dim monthList As Variant
Dim kQuote As String
Dim selectedYear As String

monthList = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug",
"Sep", "Oct", "Nov", "Dec")
cDQuote = Chr(34) '34 is the ASCII code for a double quote character

If Not Me.Year_Combo = vbNullString Then
'Year_Combo is where the user selects the year they want to view
selectedYear = Me.Year_Combo
Else
'Default to this year if no year is selected in Year_Combo
selectedYear = Year(Now())
End If

For i = 0 To 11 'Cycle through the months in the array
sqlQuery = ""
sqlQuery = sqlQuery & "=(SELECT CostData.Cost"
sqlQuery = sqlQuery & " FROM Inventory INNER JOIN CostData ON
Inventory.EquipID = CostData.EquipID"
sqlQuery = sqlQuery & " WHERE ((CostData.EquipID = " & cDQuote &
Me.EquipID & cDQuote & ")"
sqlQuery = sqlQuery & " AND (CostData.Date = DateValue("
sqlQuery = sqlQuery & cDQuote & i + 1 & "/1/" & selectedYear & cDQuote &
"))))"
'The fields in the form are named "Jan" "Feb" "Mar" etc. (Just like the
contents of monthList)
Me(monthList(i)).ControlSource = sqlQuery
Next

End Sub

Any idea what's wrong? Why doesn't Me.EquipID reevaluate for each one? Is
there a way to write it in so it DOES reevaluate?

Thanks for any help.
 
Ok, I designed it like you said and I am able to do a subform that shows
the cost for each month for each device. However, my users want a form
where they can select a year and see Jan, Feb, Mar, etc. for each device.
I managed to make a form that *shows* that, but it won't allow entry (the
recordset is not updatable). Here is the query that my form uses for a
RecordSource:

SELECT DISTINCT Inventory.EquipID
, Inventory.[Cell-Phone#]
Click to expand...

You're making something very easy into something incredibly difficult.

Create a Form based on the Devices table.

Create a continuous Subform based on the monthly costs table.

Set the Master Link Field and the Child Link Field to EquipID.

Open the form.

No query is needed, but you can base the Subform on a query selecting
a particular year if you like.

Access provides a lot of capabilities "out of the box"; you don't NEED
to program everything, either in SQL or in VBA. You can if you wish of
course, but as a rule, I start with the native functionality and add
bells and whistles with code, rather than starting out reinventing the
wheel!

John W. Vinson[MVP]
 
I appreciate all your help John. I have done what you suggested. It
works, as you say, with no code. It does not, however, meet my users'
requirements. They are a demanding bunch, and they want to see a year at a
time, and they want a blank for each month in that year, whether or not
there is a record in the database.

I've "seen the light" and have set my model design exactly as you
described. I am now simply struggling to make the interface conform to my
users' needs. Since my last message, I've actually come up with a fairly
elegant form design that actually duplicates the interface I originally
intended (you know, back when I had a really #$%&*@ up model). It is based
off of a much less complex query that is built programatically and placed
in the RecordSource property when the form opens and whenever a new month
or year is selected.

Here is the query that it builds when the selected month and year are Jan
and 2005:

SELECT Inventory.[Cell-Phone#], CostData.* FROM Inventory LEFT JOIN
CostData ON Inventory.EquipID = CostData.EquipID WHERE (((Month([Date]))=1)
AND ((Year([Date]))= 2005) AND ((Inventory.EquipID) Like 'C-*')) OR ((
(Inventory.EquipID) Like 'C-*') AND ((CostData.Date) Is Null))

This provides my form with a recordsource that gives my users a full list
of all the cell phones for the selected month and year, including the ones
that have not yet had their data entered. It is very nearly perfect. The
only problem I am having is that when the user enters a cost for one of the
ones that does not yet have a record for that month and year, I get a key
violation on Date, since it cannot be null. If I can simply jump in
programmatically before the record is saved and fill in the Date with an
appropriate value, I would be golden.

The problem is, none of the events that I expect to go off are going off.
Typing a cost in the field does not trigger the field's On Change, On
Dirty, or Before Update events. It does not trigger the form's Before
Insert event.

In which event should my code to fill in the Date field before the record
saves be placed?

Here is the code I've tried (but never gets called):

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.Date = Null Then
Me.Date = "1-" & Me.Month_Combo & Me.Year_Combo
End If
End Sub

Thanks,
Christopher
 
Ah ha! I figured it out. VB is one of those languages where nothing is
equal to Null, not even Null!

I realized that my code WAS getting called, but I had my stop inside the If
statement, and the If was always evaluating to False. It worked after I
changed my code to this:

Private Sub Cost_BeforeUpdate(Cancel As Integer)
If IsNull(Me.Date) Then
Me.Date = "1-" & Me.Month_Combo & "-" & Me.Year_Combo
End If
End Sub

Thanks again for all your help, John. It was invaluable.
 
Back
Top