Combo box the value you enter isn`t valid

[vladtc]

Hello everyone,

I'm trying to make a many to many relationship in access.
TABLE A.ID and TABLE B.ID are AutoNumbers.
In TABLE A_B the A_ID and B_ID are Numbers. The fieldSize is Long Integer.
Now, in the TABLE A form i'm adding a subform with a query from TABLE B.

My problem is that whatever i'm doing i`m getting this error message that "The value you entered is not valid for this field".
I tried to put CInt in all places: Default, Control field, the sql statments...
I tried to convert to a text field, list, etc. None is working.
Without the default value i have the exact same issue. Whatever i write in "IDComanda" field it will give me this error.

Do you have any ideas how to solve this?
Please let me know if something is not clear or any informations are needed.
Here is a screenshot with the subform:

Thank you!

 

[The_Doc_Man]

First, a little bit of advice in describing your problem: You state that you have tried several things but "none is working." Are they ALL failing in the same way? I.e. does everything you try result in the same error message?

The query might look like this (with two parts)

Qry1:

SELECT CA.Client AS CLIENT, CA.IdComanda AS IDComanda, J.IDMedicament AS MEDICAMENT, J.Cantitate AS CANTITATE, <<anything else you need from the COMANDA and COMANDA_MEDICAMENT tables>>
FROM COMANDA AS CA LEFT JOIN COMANDA_MEDICAMENT AS J
ON CA.IDComanda = J.IDComanda ;

Qry2:

SELECT Q1.CLIENT, Q1.MEDICAMENT, Q1.Cantitate, MT.Denumire, <<anything else you need from the MEDICAMENT table>>
FROM MEDICAMENT AS MT RIGHT JOIN Qry1 AS Q1
ON MT.IDMedicament = Q1.IDMedicament ;

This might not be exact, but your answer will look similar to this. Remember to pick up fields from the place where needed.

Your diagram might work, but I think there is a relationship error in that it APPEARS that you are using INNER JOIN in a place where I would have expected a LEFT or RIGHT JOIN. It also seems that they are one/one relationships which would lead to INNER JOINs being generated for the SQL of your query.

Just to see what is going on, in the query design view, you can show the grid - or you can select SQL view. That would perhaps tell you what is going wrong by showing you the SQL you are trying to generate.

 

[Uncle Gizmo]

I'm trying to make a many to many relationship in access.

There's a Blog here with some general information about many-to-many which might be useful

Many to Many Relationship – Nifty Access

www.niftyaccess.com

 

[oleronesoftwares]

i generally avoid many to many relationships except when there is no other option.
You can check the link posted by @Uncle Gizmo to learn more about creating many to many relationships

 

[theDBguy]

Hi. Welcome to AWF!

Now, in the TABLE A form i'm adding a subform with a query from TABLE B.

Usually, you would base the subform on the junction table.

 

[arnelgp]

you add a Master/Child Link fields (IDComanda) to your Subform.
no need to have a Default value for that textbox.

 

[bastanu]

Have you set up lookup fields for the IDMedicament in the Medicament or Comanda_Medicament table (or for any other fields for that matter)? If yes I think that is your problem, please review the info in this link:

The Access Web - The Evils of Lookup Fields in Tables

Remove the lookup from the field and just use the combo on the form with the first (bound) column hidden (Columns=2, Column Widths: 0';2') and it should work as expected without any need for data type conversions.

Cheers,
Vlad

 

[Uncle Gizmo]

I thought this blog I just happened upon might be of interest:-

What Is a Many-to-Many Relationship in a Database? An Explanation with Three Examples

Learn what many-to-many relationships are. The article explains theory and practice using three data model examples.

vertabelo.com

 

[oleronesoftwares]

I thought this blog I just happened upon might be of interest:

Thank you for sharing

 

[Pat Hartman]

i generally avoid many to many relationships except when there is no other option.

That's just it. You don't choose the relationship. The relationship chooses you. There are multiple ways to portray the relationship when building forms, but the relationships between tables are what they are.

Here's a simple example that shows a m-m from both directions. The view from one direction shows the relationship as a subform. The view from the other direction shows the relationship using a popup form Both are valid. Both can be represented using either display method. One view might make more sense than the other in a particular situation.

One method I would never use is a single form that joins all three tables. The main form can show the left table and the subform can show the junction table or the main form can show the right table and the subform can show the "other" field from the junction table. OR instead of a subform, the "other" side can be shown with a popup.

If you use one form based on a table that joins the three tables, you will have trouble updating.