The Value you Entered for this Field isn't Valid Error

[xian2]

Hi All,

I have been wroking on the databse for a while now and am on the stage of creating forms. I am trying to create a subform at the moment but am having some problems.

I am using the wizard to create the form and add the necessary fields as below:

The tables are as follows:

tblTourBookings
(Main Table linked to others)
Tour ID (Primary Key)
Client ID
(lots of other fields that are unimportant and unlinked)

tblTourDetails (this is kind of the equvalent of a shopping list and is the subform)
TourID (Composite Primary Key)
CostID (Composite Primary Key)
DayPrice

tblToursOffered (this is like the inventory of products - but in this case it is tours)
CostID (Primary Key)
TourName
Itinerary
DayPrice

There is a client table linked to ClientID and Credit card table linked to that in a one to one relationship.

I Create a form with the following tables in it:
TourID
ClientID
CostID
TourName
DayPrice

The latter three go into a subform but when I choose from a combo box for CostID (which currently only displays the autonumber as generated in tblToursOffered). The problem is that when I select a number i get an error message:

"The Value you Entered for this Field isn't Valid Error
For Example, you may have entered text in a numeric field or a number that is larger than the FieldSize setting permits."

It will not allow me to enter any value without displaying the error message.

Any advice or help would be much appreciated and the databse can be sent via e-mail is needed.

Many thanks in advance

Edd

[MMcCarthy]

Hi Edd

Your main form would be based on tblTourBookings. DayPrice should not be in both the tblTourDetails and tblToursOffered tables. Your subform would be based on a query based on tblTourDetails and tblToursOffered something like the following.

SELECT TourID, tblToursOffered.CostID, TourName, Itinerary, DayPrice
FROM tblTourDetails.CostID = tblToursOffered.CostID

Your subform would be tied to the main form based on TourID.

Mary

[xian2]

Dear Mary,

Many thanks for your kind response.

I tried it out with the following query:

SELECT [tblTourDetails].[TourID], [tblToursOffered].[CostID], [tblToursOffered].[TourName], [tblToursOffered].[DayPrice] FROM ([tblToursOffered] INNER JOIN [tblTourDetails] ON [tblToursOffered].[CostID] =[tblTourDetails].[CostID])

But there is still a problem with the subform. I can not select anything from any of the fields in the subform.

The CostID just displays (Autonumber) and does not display a list to select from and will not let me enter anything. It is enabled and not locked.

The TourName field is blank but does not provide a dropdown menu and will not let me enter any text.

The CostID just dings at me when i try to enter anything and the TourName field comes up with the following message:
Cannot add record(s); join key of table 'tblTourDetails not in recordset.

If you could offer some help or advice I would be very grateful.

Thanks in advance

Best wishes

Edd

[MMcCarthy]

Hi Edd

I think your problem lies with your table structures and relationships.

Firstly open the relationships window and click on the relationships between the three tables. They should have the Referential Integrity and Cascading update checkboxes ticked.

Mary

[xian2]

Hi Mary,

Once again many thanks for your help.

However, I am still having trouble, I have linked the tables together enforcing referential integrity and cascade update but still hit problems with no dropdown menu and an error if I try to enter anything.

I have scanned through my properties but could it be something wrong with them?

Many thanks again for all your help

Edd

[MMcCarthy]

Hi Mary,

Once again many thanks for your help.

However, I am still having trouble, I have linked the tables together enforcing referential integrity and cascade update but still hit problems with no dropdown menu and an error if I try to enter anything.

I have scanned through my properties but could it be something wrong with them?

Many thanks again for all your help

Edd

Edd

The problem isn't with your table it's with your query.

Run this query

SELECT [tblTourDetails].[TourID], [tblToursOffered].[CostID],
[tblToursOffered].[TourName], [tblToursOffered].[DayPrice] 
FROM ([tblToursOffered] INNER JOIN [tblTourDetails] 
ON [tblToursOffered].[CostID] =[tblTourDetails].[CostID])

And you will see you have the same problem.

Check the records in both tables. If there are any records in one table that don't have corresponding records in the other table then you will have a problem.

[xian2]

Hi Mary,

Once gain thank you for replying.

I did try that other query an you are right, once again it didn't work.

I have checked the tables and there are no entries so there are none that do not correspond.

I am really stuck with this now so any advice is very much appreciated.

Best wishes

Edd

[MMcCarthy]

tblTourDetails (this is kind of the equvalent of a shopping list and is the subform)
TourID (Composite Primary Key)
CostID (Composite Primary Key)

tblToursOffered (this is like the inventory of products - but in this case it is tours)
CostID (Primary Key)
TourName
Itinerary
DayPrice

Hi Edd

The above being the structure of your tables (with no other fields). Go to tblTourDetails and in the composite primary keys make them lookup fields to the other tables primary keys.

Make sure both have indexed property set to Yes (duplicates OK)

[xian2]

Dear Mary,

Thank you so much for your help, it worked perfectly.

I changed the fields to lookup and they worked exactly as desired in the form.

I hope this helps anyone who is stuck with a similar problem and if it is the same problem give me a shout and I will do my best to advise.

Thanks again and best wishes

Edd

[MMcCarthy]

Dear Mary,

Thank you so much for your help, it worked perfectly.

I changed the fields to lookup and they worked exactly as desired in the form.

I hope this helps anyone who is stuck with a similar problem and if it is the same problem give me a shout and I will do my best to advise.

Thanks again and best wishes

Edd

You're welcome Edd.

Glad you got it working.

Mary