I got stuck converting a claris works database from an old mac to access on
a pc. I'm a C++ programmer and don't know a lot about access. This is an
inventory database where the store owner basically just had a record for
each item and he would add a new month field when they would update the
inventory. They just tracked if an item was in stock at the time of the
inventory.

What I want to do is present a form with a subset of the fields that contain
the description information and the month field which would be selected from
a combobox in a datasheet view. I can create a query using the value passed
from the list, but I can't get the month field to display correctly.


For example, if the fields in the initial query is:

select tbl1.ID, tbl1.Description, tbl1.[02/04] as QTY from tbl1

Using Me shows 02/04 as one of the form fields.

If i change the query to select tbl1.ID, tbl1.Description, tbl1.[02/05] as
QTY from tbl1 I get $NAME error for the month field because it is still
looking for the 02/04 field as the record source.

How can i change the query to select a different set of fields when the user
changes the month they want to view? The user also needs to be able to
update the information displayed in the list.
..

Jim:

If I understand what you have said then the fellow who designed this
was an idiot. The first rule of relational design is "no repeating
groups". You can not have an array in a record, which is what this
guy did.
This is why you are going nuts.

Goto to the query and look at the SQL view.

Copy the SQL statement.

In code you can replace tbl1.[02/04] with tbl1.[02/05]
You can then replace the datasource for the form with your new SQL
string, and replace the control source for the field with tbl1.[02/05]
and do Me.requery.

This is ugly and you get into more trouble later but it will work.

I would create a new month table, link things together and do it the
way Access was designed to work, but, I realize, you may not have the
time.

Good Luck
Ira Solomon



On Mon, 7 Mar 2005 07:53:34 GMT, jim.bello (AT) eracent (DOT) com wrote: