Losing property values of variables when using a query

[accessbunnie]

Hello Access Users!

I am a bit of an Access novice and am currently creating a database and have come up against a huge (for me!) road block.

I do not write in Access code and instead, tend to just use design view and wizards to create my databases.

I created a form using a query I created composed of two different tables with the same unique identifier.

All things went fine while I dragged my variables from the field list to the form as I normally would to create a form. However, once I got many variables into the process, when I would drag my variables onto the form they lost all their properties. For example, I created display captions for the variables and instead their field name was displayed. Or a variable would be defined as a check box or combo box and instead turn up as a blank text box.

I have tried rewriting the queries, creating individual forms out of the two individual tables (which worked fine), and rewriting the variables and variable properties. It seems to me the problem is either in the query or the number of variables I have (although I tried creating a form with only a few variables from each table and still had the same problem).

I created the query in design view. The steps I completed are below:

1. Dragged the astriex (to include all variables) from table A into the first column
2. Dragged the astriex from table B into the second column
3. Dragged the ID number (used in table A and B) into the third column
4. Unchecked the box in the third column and changed 'sort' to 'ascending'.

Any help or suggestions what-so-ever will be greatly appreciated! I am completely at a loss.

Best,
Kate

[JustJim]

Others please jump in on this as available. I can't stay long.


Here are some ideas based on a quick reading of your problems. Firstly I'm going to assume that when you say "variables" you are talking about Fields in an Access table. Fields are defined as data types so that if a field is defined for example as Yes/No or True/False, Access is clever enough to make that a check box when you put it onto a form. The field itself is not defined as a check box.

Combo boxes are a different matter. You have to tell Access that you want a combo box either in code or by changing details in the properties sheet for that control on that form. The lookup wizard is designed to do this for you, but it does not do a very good job for various reasons. see here for details.

Anyway, to the crux of the problem. Are your tables Joined in the design view of your query? That is, is there a relationship line showing between the two tables?

Do the fields that are losing their properties have the same name as another field in the other table?

Drop in the answers to these, and I or another will try to help further.

Jim

[accessbunnie]

Hi Jim,

Thank you for your quick reply. To answer your questions:

* In the query design view there is relationship line connecting the two tables together (via the unique identifier which is the same field name in each table). I also created a relationship between the table via the 'relationships' window.

* The fields that drop their properties are unique, in that the same field name is only used once in the entire database.

Thanks again for everyone's help with this.
- Kate

[JustJim]

Hi Jim,

Thank you for your quick reply. To answer your questions:

* In the query design view there is relationship line connecting the two tables together (via the unique identifier which is the same field name in each table). I also created a relationship between the table via the 'relationships' window.

* The fields that drop their properties are unique, in that the same field name is only used once in the entire database.

Thanks again for everyone's help with this.
- Kate

So to double check, you have two tables and each has a field with the same name and you have made a relationship between them?

i.e. Table1!UniqueID<------->Table2!UniqueID

Are the unique ids the primary keys of their tables? This strikes me as an unusual arrangement. The more usual arrangement would be...

Table1!Table1UniqueID(Data type Autonumber)
Table1!ForeignID(Data type Long)<------>Table2!Table2UniqueID(Data type Autonumber)

But nevertheless... does the query work by itself? Does it return the records you expect? Do the column headings match what you put as captions for the fields in question?

Sorry for the delay, I'm in and out at all weird hours at the moment.

Good luck

Jim