Access keeps changing my Joins

[Phonik]

Good day all

I have a simple query linking two tables using the primary keys from each. The problem is that every time I go into design mode of the form that uses the query to populate a list box I get a Data Type Mismatch error and when I look at the query, Access has changed the join from the Pk in one table to the field after the PK in the other field. I have attached an image to show the change. Note that the join should be from the first fields on each table. I am really stuck with this guys, it is gettin me down and preventing me from developing the database further and my boss is on my case. Can anyone please help me? I have checked all the table relationships and they are fine. Thank you.

Gareth

 

[KenHigg]

Any chance you can zip and post the db (or a sample)?

 

[Phonik]

Hi Ken

Thanks for taking the time to respond to me. I am posting the zip file now should be here in a second. If you open up the form "NewDataDetail" in design view and then form view it, you should get the same error message as me. It is the first list box on the left of the newdatadetail form that the problem is with. All the others work.

Thanks again.
Gareth

 

[Phonik]

Sorry Ken, the Db is FE/BE so I had to copy all the tables back into the FE or it would not have worked. I posted the db without the relationships setup but have uploaded again now. Sorry mate.

 

[KenHigg]

Worked fine on my side:

 

[Phonik]

Hi Ken.

Does it work constantly, even if you go into design mode and then back out again?
How about if you open up the main menu and then open List of Reports. Do you get a list full of primary keys or text?

Thanks again.

Gareth

 

[KenHigg]

Hum... I guess it did. I went back a forth a couple times... Did you look at the sample I posted back?

 

[Phonik]

You downloaded the version before I had put the relationships in. Could you re-download the file from the first post. I had to add all the relationships in again as per my post. Sorry to pester you mate but I am really up a creek here. Thank you.

 

[KenHigg]

I just opened the db from the first post and there were no relationships defined. Al the tables were in the window but no relationships... Is there supposed to be?

 

[Phonik]

What a nightmare. I have uploaded it again. (327.7 KB). Sorry mate.

 

[KenHigg]

Looks like I still get it to change and stick...

 

[Phonik]

Sorry for delay in replying. I think the server went down. Im not sure what you are saying. Are you saying it all works fine on your PC?

 

[Phonik]

Ok, I have a suggestion. Form the menu, click on the button View Measures. When you get to the list, double click on the record named Staff effectiveness. This will take you to the detail screen for this record. You will see a list box on the far left with the heading reports. If there is no data in there...it is not working. If you go into design mode for the form, double click the list box to get the properties and then look at the row source you will see what I mean. The tables shown will show a link between Report Title and Report ID when it should be Report ID to Report ID. If you delete the join, create the correct one and then close the query window so it inserts the SQL into the row source box in the listbox propeties, run the form and check it...if will fail. If it doesnt straight away, return to the menu and open View measures again and double click on Staff effectiveness again and it will fail. You should either get a message saying Data Type Mismatch or you will not get any data or column headings in the listbox. When going back into design mode of the form again, you will notice that Access has changed the joins from what you previously chose.
Thanks
Gareth

 

[Phonik]

Hi

Thanks for all your help. After 2 days of trying to figure this out I discovered that I had spaces in the names of the field in the SQL code. Thanks for all your efforts.

Gareth