Date fields showing error messages in form

[Rolfee]

Hi.

I have two date fields ('Last_contact' and 'CallBack_Date') in a subform (Contacts) that I believe I have set up identically and correctly, yet I am experiencing two problems;

1. They are showing different error messages - "#Name?" and "#Error", respectively.

2. Neither will let me enter data in the form view. I keep getting a system beep. I can enter data (short date format) in the table datasheet view, but it still comes up with the aforementioned error message when I go to the form view.

I am wondering whether the issue lies in the properties of the fields, and if the one solution will fix both problems. However, for the life of me, I cannot seem to find a place to change the field's setting that realises a fix. I have tried comparing the field properties to other fields in the table design view screen, but these are the only two date fields. All the others are Number and Text fields.

What I am doing wrong/where am I going wrong?

Frustrating, as I cannot progress with designing a report until I have these fields fixed.

Any help, please?

Thanks for reading my post.

 

[GolferGuy]

If you could post the two forms and the table(s) they are built on in a new database with just those objects, that would help. Table(s) do not need to have any data in it(them).
I would offer suggestions on what the problem could be, but I can't think of any. Seeing the objects always helps.

 

[Rolfee]

Thanks Vic. Sorry to ask what may be a really dumb question, but how/where do I post the forms and tables? How do I get them out of Access and on to the forum? Ed.

 

[GolferGuy]

Create a new database, and from that database use the File menu, Get External Data option, browse to the "real" database, and then select the forms and tables necessary. Before sending the database, go through the tables and make sure there is no data in the table that you should not be sending to the whole world. If all the data is that way, please put into these copies of the tables at least some sample data. When finished preparing the database to send to the forum, right click on the database from within Windows explorer, and select the Send To option. Within the Send To option, select Compresses (Zipped) Folder. Then within the forum, after using the Go Advanced button for your reply, scroll down just a little bit and you will see Additional Options. Within the Additional Options will be a button with "Manage Attachments" on it. Click it and follow the directions there.

 

[Rolfee]

Thanks Vic.

Unfortunately, even the cut-down and compressed version is over the forum's miniscule limit of 360kb. So I have taken the liberty of sending it to the e-mail address on your website.

I look forward to hearing back from you.

Rgds,

Ed.

 

[GolferGuy]

Ed, I did a compact and repair on the database you sent to me, then put it into a Zip file, and have now attached it to this post so others can look at it if they desire to help also. I did not delete anything, it was just the compact and repair that took out all the "air" that Microsoft leaves in the database when things are deleted from the database.

 

[GolferGuy]

The Last Contact field on your form gives you #Name? because you did not include this field from the table in your query. Add this field to the query to solve this problem.

The CallBack date gives you #Error because the name of the text box on the form is "CallBack_Date" and the name of the field you are asking to be displayed in this text box is "CallBack_Date". Access does not know which "CallBack_Date" you mean. The data from the query by this name, or the data in the text box on this form by the same name. The error happens when you don't just name the field from the query (or table) in the ControlSource, but put an equal "=" sign in front of the name. The equal sign means you want the data in the variable named after the equal sign to be displayed in the text box. Because of the equal sign, the data is NOT updateable. If you remove the equal sign from in front of "CallBack_Date" in the control source for Call Back, it should solve this problem.

 

[GolferGuy]

Ed, here is the further stuff you asked for in your email. I'm posting it here for others to learn from, and the answer will be in the next post.

If it’s not stretching the favour too much, I actually have two other issues relating to the form/subform relationship.

Firstly, I have the main form set up for 'Companies' and the sub-from set up for 'Contacts', and have a relationship set up between a 'Mastercode' field in both the form and sub-form. My objective in doing it this way is so that I can quickly tab through the companies for (e.g.) Company1, with four contacts, straight to Company2 with one contact, and then straight to Company3 with three records, with just three clicks - not eight clicks to transition from Company1Contact1 to Company3Contact3. I hope that makes sense as I am not experienced enough with Access to be able to articulate it better.

Secondly, why does my subform always present as a table by default and requires me to right-click on it, and select 'Subform - Form' to get it to present as a from? Surely, there is a way to lock the subform in form view?...

Hopefully, your ‘gracious guru-esque glance’ will be all it takes to identify the problem that a mere beginner cannot fathom

 

[GolferGuy]

I don't understand any of what you are trying to explain as a problem with your "Firstly" explanation. Right now, in the sample you sent it takes 9 clicks to get to the last record because you have 9 companies in the Company table. At least the first 4 look identical from the glance I gave them. If you truly had only 3 companies there, then 2 clicks would get you to the last of the 3 records.
Under "Secondly" that subform always coming up as a datasheet is because that form (the subform) had it's Default View property set to Datasheet. Access was only doing as you asked.

 

[Rolfee]

Thanks Vic. I have effected fixes for CallBack_date and subform default view, but hours of going through the table and subform to fix the error message #Name? in Last_contact has me beat. You say "because you did not include this field from the table in your query. Add this field to the query to solve this problem.", but I have not set up any queries - just tables and forms. Or is the query you refer to within the form, referencing the table? I have searched the MS Access help, Googled for hours and tried every option in the form desig view screen, but cannot seem to find where this query is. I have right clicked on the Last_contact text box and compared & replicated all settings in poperties to match those of the properties of CallBack_Date. Still no joy. I have also gone in to datasheet view of the form and tried all of the above in that screen, but to no avail. The only difference between the CallBack_Date and Last_contact columns is that Last_contact was not originally in the table. I added it manually whilst designing the Contacts subform as I realsied that I had omitted it form the beginning. Could this be the root of the problem?

 

[GolferGuy]

There is a query that the wizard must have built for you to access the data from the table for the form. Open the form in Design view, open the properties window for the form (press the F4 key), select the Data tab. Record Source is the first property listed here for the Form. Select it and a button should appear to the right of the line with three periods on it "...". Click that button and the query will be presented to you in design view. In the top half of the query will be a listing of all the fields in the table, find the missing field and double click it, OR drag it to the grid below and drop it. Close the query design view, answer Yes to the prompt to Save the changes. Save the form and try again. It should work.

 

[Rolfee]

Done! Sorted! So easy when you explained it. Funny thing is that I had been through that properties box dozens of times, but the fields table had never presented until I accessed it following your instructions. Thanks Vic. You've saved my sanity and enabled me to progress to designing the reports. I'm going to leave the issues described previously as 'Firstly' a I believe that I am expecting Access to be able to do something that it cannot. Thanks Vic for yuor invaluable help. Rgds, Ed.