"What exactly is the problem you are having?"
HA HA! You'll wished you never asked!!!!! It goes something like this...
I created a database for stock taking. There is a table in the database for
recording stock counted during a stock taking exercise. There’s nothing I can
see wrong with the table itself, it has a PK to uniquely identify an instance
of each item recorded. It has a field to record the ID number of a stock item
which corresponds with the PK from another table which holds every item of
stock known to have been purchased. It has a YES/NO field to record whether
or not the item is new or used. It has a date field to record the date the
stock item was counted, and finally, it has a quantity field to record the
numbers of each item found.
I have a form for recording items of stock onto the stock count table. It
started life as an auto form created from the stock count table but has been
customised as follows:
Tab 0: This is a combo box which is based on a parameter query. The
parameter query asks the user to type in part of a stock item name and thus
returns a list of results in the combo box drop-down list to choose from.
Because there are hundreds of stock items, this was the only way I could
think of generating a list to a manageable number of items to choose from and
it works well – at least as a parameter query. Behind the query, each stock
item has its PK number written to the stock count table which does appear to
work.
Tab 1: Simple enough, this asks the user whether the item is new or used and
displays a “Yes/No†option.
Tab 2: This is to record the date the item was counted.
Tab 3: This records the quantity of an item counted.
NOW HERE’S THE REASON I ASKED MY QUESTION!
Tab 4: This is an ‘Add New Record’ command button. When this is used, I am
assuming that the form saves the record to the stock count table and then
moves to a fresh record in the table. (By the way, the ‘Data Entry’ option on
the form has been set so that the form opens on a new record every time it’s
opened, thus hiding any previous entries.) But then I realised I’d have a
problem…..
The form saves the record and moves back to Tab 0. Remember, Tab 0 is the
combo box which is now displaying the results of a parameter query which will
be confined to a handful of items which may or may not contain the next item
I want to record in the stock take! So I had to figure out a way of making
the parameter query run again on the new record so I could find a different
stock item to count. The simplest way I could think of doing it was to add
another command button (Tab stop = No) which refreshed the form. Indeed, this
forced the parameter query to run again and I could find other items to add
to the stock count table.
However, I believe that something is flawed in this method as after running
a report on the stock count table I was finding that there were random
instances of items with zero quantities which had definitely been recorded
with a number quantity. Furthermore, some of the Yes/No choices had been
recorded incorrectly; despite me being 99.9% sure they’d been recorded
correctly. Therefore, that’s why I asked the question about the ‘Add New
Record’ command button as I wondered if it did actually permanently record an
item to the table and then move onto the next record. However, I’m now
starting to believe that using a ‘refresh’ button on the form to force the
parameter query to run again has seriously messed things up. I did try a
similar method on another form using a query for a combo box to generate
items for entry on the stock count table, but this query was a ‘run once’
query that brought up a list of items in a particular category and thus,
didn’t change from record to record. Everything I needed to record was
generated in the combo box as soon as the form was opened.
I guess my underlying problem is that of how to create a form with a search
facility on it so I can query the stock item table for every new record I
want to add to the stock count table! No doubt that’s a separate question
elsewhere on this forum – but you did ask!!!!
