T
thedrumdoctor
I have created a database for stock taking. There is a table in the database
(StockCountTable) 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 numeric field
to record the ID number of a stock item which corresponds with the PK from
another table (NewStockTable) which holds every item of stock known to have
been purchased.
Other fields on my StockCountTable are as follows:
There’s a YES/NO value list field to record whether or not the item is new
or used. There’s a date field to record the date the stock item was counted,
and finally, there’s a quantity field to record the numbers of each item
found.
I have a form for recording items of stock onto the StockCountTable. It
started life as an auto form created from StockCountTable but has been
customised as follows:
Combo box on Tab 0: This combo box 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 (from the NewStockTable) has its PK number recorded to the
StockCountTable and all does appear to work.
Yes/No drop-down list on Tab 1: Simple enough, this asks the user whether
the item is new or used and displays a “Yes/No†option.
A Date entry field on Tab 2: This is to record the date the item was counted.
A quantity field on Tab 3: This record the quantity of an item counted.
An ‘Add New Record’ button on Tab 4: This is a command button. When this is
used, I am assuming that the form saves the record to the StockCountTable 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 a
'Refresh Form' command button (with a Tab stop value = 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. 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
with the previous records I’ve recorded.
I did try a similar method on another form using a query for a combo box to
generate items for entry on the StockCountTable, 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. But this didn’t solve the problem
of needing to search for absolutely any item from the NewStockTable for form
entry, hence my approach of using a parameter query with a combo box.
I’m sure there are other ways of trying to achieve what I want, but being
fairly new to Access I just don’t know how, which is why I’m asking here. By
the way, I know absolutely nothing about VBA either (just for the record)
though hopefully this will change in the near future. Any wise ideas from you
seasoned pros are welcome here!
(StockCountTable) 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 numeric field
to record the ID number of a stock item which corresponds with the PK from
another table (NewStockTable) which holds every item of stock known to have
been purchased.
Other fields on my StockCountTable are as follows:
There’s a YES/NO value list field to record whether or not the item is new
or used. There’s a date field to record the date the stock item was counted,
and finally, there’s a quantity field to record the numbers of each item
found.
I have a form for recording items of stock onto the StockCountTable. It
started life as an auto form created from StockCountTable but has been
customised as follows:
Combo box on Tab 0: This combo box 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 (from the NewStockTable) has its PK number recorded to the
StockCountTable and all does appear to work.
Yes/No drop-down list on Tab 1: Simple enough, this asks the user whether
the item is new or used and displays a “Yes/No†option.
A Date entry field on Tab 2: This is to record the date the item was counted.
A quantity field on Tab 3: This record the quantity of an item counted.
An ‘Add New Record’ button on Tab 4: This is a command button. When this is
used, I am assuming that the form saves the record to the StockCountTable 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 a
'Refresh Form' command button (with a Tab stop value = 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. 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
with the previous records I’ve recorded.
I did try a similar method on another form using a query for a combo box to
generate items for entry on the StockCountTable, 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. But this didn’t solve the problem
of needing to search for absolutely any item from the NewStockTable for form
entry, hence my approach of using a parameter query with a combo box.
I’m sure there are other ways of trying to achieve what I want, but being
fairly new to Access I just don’t know how, which is why I’m asking here. By
the way, I know absolutely nothing about VBA either (just for the record)
though hopefully this will change in the near future. Any wise ideas from you
seasoned pros are welcome here!