Can’t enter new records

Topic

New Reply

I have inherited a database in which everything was originally set up in one table. It was huge. I split the table and set up a multi-page form. But, I have somehow made a mistake because now I can’t seem to enter any new records. I can enter info in fields on one page, but once I switch pages or try to add another record, I get an error message saying I can only enter Y/N in the field and it won’t allow me to proceed. I have to close the form to get out of it. I have checked validation rules and there are none. I have checked form properties and table properties. I am soo confused.

It is 332K in size (I deleted all but 2 of the records and all the queries and reports). Is anyone willing to let me e-mail the database to them so you can look at it and see what I’ve done wrong??

I realize things are named wrong and the thing is pretty ugly, but I am trying to fix it without having to re-do the whole thing. I am a beginner at Access and this is way over my head.

Thank you for any help you can give me.

Reply | Quote

Replies

Somehow you have got a boolean (yes/no) field in a table which is linked to a control which seems to be something else, or more likely there is a field in the table which is marked as required but you have not set up a control to give it a value.

I will try to look at the problem sometime if you email me the file on .david.grugeon@dcilgps.qld.gov.aubut I may not be able to do it straight off

Reply | Quote

Hi Melanie.

Thankyou for letting me have the sample database to have a go at.

I have Access 2000 not 97 and therefore could not absolutely reproduce your problem, but I think the following will hold good.

I have pinned this problem down to one of two fields. These are the two fields on the Use tab which are Y/N fields

They are actually text fields with validation rules requiring “Y” or “N”.

It is characteristic of the design of this database that a Boolean (Yes/No) field was not used although Boolean fields are used elsewhere!

In the case of the field “Land Use Classification” the validation is in the table definition. In the case of “Permit Use” the validation is in the control properties. WOW!

I am not sure exactly what is happening but it seems to be the Permit Use field. I would suggest changing this field to Boolean and making the control a checkbox. Probably worth doing that for the Land Use Classification field as well.

You will probably need to add these two fields as new fields in the table and then run an update query to set each new field to Yes if the old field is “Y” or No if the old Field is “N”. Check it has worked! and then delete the old fields. You then add them to the form, delete the orphan controls, correct the tab order, and all should be OK.

If you want to keep the fields as they are, I would definitely move the validation for Permit Use to the table and I would include a space in quotes ” ” as one of the permitted responses.

Good Luck

Reply | Quote