Drop down box changing my table entries!!!

[Guest]

Okay. This DB will be the death of me!!!
I am attempting to manage equipment maintenance in a military unit. I have
5 guys who enter data, and I need a LOT of reports. The one I'm working on
right now has been built fairly well...I thought!
The guys log in, enter the maintenance using my form. The data is entered
into a table, and a column for 'month' is autofilled based on
=Format(date(),"mmmm yy"). This part is working. I then test the query
report (which is run on parameters which look up the magical "Month" column).
However, I have entered data for june, july, august, and September to test
the DB. The issue I'm running into: When I select any month, it changes the
'month' column from July to June! It does this randomly!!! If I enter data
for four completely different months, it still changes one of the months.
It's killing me. Can someone help?!?!?!

 

[Pat Hartman \(MVP\)]

Search/criteria fields should not be bound. It sounds like yours is.
BTW - you need more than just month for this type of lookup unless you
intend to restrict your table to a single calendar year.

 

[Guest]

Thanks for the quick reply.
In my table, Column 1 is ID, Column 2 is Date, Column 3 is Month. I then
edited the default value in the Month column to the Format command
=Format(date(), "mmmm yy").
I used the article on using parameters with reports and queries (MS Help) to
create the following:
Report based on a query. The query, under the Month Column, directs to the
parameter defined in the form for Month. The form has a dropdown that pulls
the unique values out of the table, specifically in the month column.
In theory (and I'm a newbie to this so I may have it totally wrong) the form
filters the query based on the month selected (which it does) and spits out
the report with all entries for that month (which it also does). What it
ISN'T supposed to do is edit entries in the Month Column.
I'm not understanding the search/criteria reference. Unless my parameter
is, in fact, a search. I just did what the article told me to. I'm not sure
I really understand it ((sheepish grin)).
The one good thing I was able to do is put everything tied to the Month
Column into the "mmmm yy" format so (again in theory...and PLEASE let me know
if I'm wrong)...I should be able to view October 08 separately from October
07....

I can email screenshots if it will help. I've been beating my head against
a brick wall every day just to get this far. One would think I might be able
to squeeze some Access training outa the Army for this!!!

 

[Pat Hartman \(MVP\)]

Look at the properties for the control that is giving you trouble. If it
has a field name in the ControlSource property on the Data tab, it is bound.
In an unbound control, it will be empty or hold an expression that starts
with the = sign. That means that whatever value you place in that field
will update the underlying bound record. If the form is used for editing as
well as searching, you need separate controls to use for the parameter so
that changing the value will not modify data.

 

[Guest]

In my data tab, it is bound. it only updates one value,a nd only does it one
time. Specifically, once it changed June to July, it didn't change anything
else, no matter what report I ran. It didn't change any more records or do
anything any more times. So I'd click August. It would do the June/July
Switch. I could then reselect any month as many times as I wanted without
any further changes.
Previously (in my last test round), it changed October to semptember, so
it's not month specific. However, if I go back and correct the change, it
always repeats the same change for the first report I run after re-updating
the data.

Here's what I'm seeing in my data tab:
Control Source: Month. When I deleted Month, it reflected a blank
combobox.
Row Source: SELECT DISTINCT [AreaRae Full Calibration].Month FROM [AreaRae
Full Calibration];
This was the only way to delete 9,000 occurrences of September. Each month
has at least 12 entries, so I want the report to pull up all instances of
September. This actually successfully does that.

 

[Pat Hartman \(MVP\)]

You have a field called date and a field called month. Why is month a
separate field? You can use the Month() function to extract the month part
of a date or you can use the Format() function to extract whatever you want
in what ever format you want. Dates are stored internally as the number of
days since Dec 30, 1899. Dates prior to that date are negative values,
subsequent dates are positive values. The common mm/dd/yyyy format is just
that - a string reformatting the actual numeric value to something human
understandable.

Check the ControlSource for the control. It MUST be blank. If it contains
a column name, the control is bound and changing the control will
automatically change the underlying table value.

PS Date and Month are both the names of functions and therefore should not
be used as column names. If you have to do any VBA coding, you will likely
run into confusion (yours and Access') when trying to reference those
fields.