Subform Entry

[Sharon Hague]

Hi All

I am having diffuculty entering or changing data in a subform I have created.

I have basically 2 tables. One called "Date" and one called "Overtime". I have joined these as a 'one to many relationship' and set a primary key to my date table on a field called date so that no duplicates are allowed.

My thought is to be able to type in a date and then underneath that date, rather than entering it every time, type the employees intials and the hours they have worked for that date, which my subform does.

When I try to enter a new record or change an existing one it comes up with a message "The record cannot be deleted or changed because the table Overtime includes rlated records.

What am I doing wrong???

 

[Waltang]

Be sure that you are going to a new record. The error you got sounds like you tried to change the existing Date record, but that the relationship is not set to allow cascading updates.

I dont completely understand what your trying to accomplish, but It doesnt sound like you have the best possible Table structure.

Is your relationship built on a field with a date datatype?

 

[Sharon Hague]

Thanks for the reply.

What I am wanting to do is enter (daily) employee overtime to a table called "Overtime". This consists of three fields - 'Date' which is a date/time field, 'Initials' which is a text field and 'Overtime' which is a number field. We have a large number of employees and therefore I don't want to have to enter the date for that day for every record, which is why I thought of a subform.

Therefore I created a second table called "Date" which has two fields - 'Date' which is a date/time field and also the primary key and 'Salary Month' which is a number field.

I then created a one to many join from Date to Overtime on the date fields and set this relationship to Cascade Delete Related Records.

My subform was created through wizard which I selected the date field from my "Date" table and then Initials and Overtime from my "Overtime" table. Therefore when I go to view my form the date appears once at the top and then underneath all overtime for that date together with the employee initials is shown.

When I go to new enter a new record it comes up with the message mentioned before. If I type over the date at the top it also comes up with this message, however it still displays the overtime for that day.

Does this explain things any better?

 

[Rich]

Date is a reserved word in Access and shouldn't be used as a field name, you should also not name the table the same as a field within the table,
eg. tblDates, fldDateEntries or something like would be better.

 

[Sharon Hague]

Hi Rich

Thanks for your reply.

Unfortunately still no good.

I changed the name of my "Date" Table to "TblDate" and the date field in here to 'Entry Date' then in my "overtime" table I changed the Date field to 'Overtime date'.

I then created a new subform the same way as before and now I get a message 'The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship. Change the date in the field or field that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

I dont' want to unset the primary key in my "TblDate" Table as I don't want duplicate data going in here.

Any other ideas?

 

[pono1]

Sharon,

To make things slightly easier, I recommend that in both tables you call the two date fields by the same name, say, EnterDate.

Also, with the changes you've made, your relationships may need to be re-set.

Additionally, you might want to check a couple of settings on your subform. Do so by opening your main form in design view. Click the top edge of the subform (which should be embedded in your main form) -- if you don't see the properties dialog, click View-Properties.

Two important properties to check on your subform: Link Master Fields and Link Child Fields.

Both properties should be "EnterDate"; the primary key in your date table and the foreign key in your OT table.

Regards,
Tim

 

[Sharon Hague]

Hi Tim

Thanks for your reply but still no good. I have attached my database for you to see what is happening.

When you type over the date at the top it lists the initials of the employee and the amt of overtime they have done for that day in the subform part.

I want to be able to change an the hours on an exisiting record if necessary or type a brand new date in the top for me to add new entries.

I'd appreciate your thoughts on this.

Thanks

Sharon

 

[Waltang]

Is this what your looking for? I didnt have Acc97, so hopefully the libraries are working.

The problem that you were having seems to be that you were trying to use the Bound Field with the date as a form of search. By changing that date on the form, you were changing the underlying table, and thus unintentionally changing data.

I replaced the Bound date box with an unbound one that on afterupdate would search for a record with that value and jump to it. If that record doesnt exist, it jumps to a new record and sets the date field to be what you searched for.

 

[Sharon Hague]

Waltang

Thanks for your reply.

What you are saying is understandable and sounds like it would solve my problem but when I try and change the date on the TblDate form it comes up with a message as follows: -


Compile error: Can't find project or library.

When you press Ok, the error is on

Private Sub Text6_AfterUpdate()
Dim rst As DAO.Recordset


I am not too familiar with VBA and not sure what to change on your event proceedure.

Any advice?

I have got a little further with the existing database. The problem as you have advised before is with the Date field in my subform. Therefore, as a test, I thought I would create a macro called "open", set its Data Mode in Action Arguments to add so that when you select a command button from another form I created it opens my subform ready to enter a new record, let's me enter the details for that date and also saves the data. This works great.

However I now have a problem when I want to amend. I have added another command to my "Open" macro called amend and set its Data Mode in Action Arguments to edit, however when I want to amend a record, when the form is opened it automatically opens the first record, lets yop amend that but when you try to change the date I still have the same problems as before.

Can I set command buttons on my existing form to be able to change the date rather than using another form for entry?

Or have you any other suggestions?

I have attached the new database with these changes.

 

[Sharon Hague]

Waltang - Or anybody else

Another idea is to have another set of navigation buttons on the table above,next to the date to flick through and see all the entries.

Is this possible?

Please advise.

 

[Waltang]

See if this fixes it. Its been a while since I messed with Acc97.

Run the form, and when you get the compile error, hit ok. This should bring you to the Visual Basic Editor. On the menu at the top, goto Tools -> References.

now find the library called Microsoft DAO 3.x Object Library (I'm not sure what version is in Acc97)

Make sure the box on the left is checked, then using the Up arrow button on that window, move the priority up as high as it will let you.

See if the form then runs properly

 

[Sharon Hague]

Waltang

Thanks for your reply

I have tried to tick the box Microsoft DAO 3.0 Object Library but unfortuantely when I try to tick the box it comes up with a message "DAO version 3.0 is not compatible with this version of Microsoft Access" and won't save the tick in the box.

However it does let me tick Microsoft DAO 3.5 Object Library which is the next option but won't let me alter the priority levels. However, when I then click o.k it comes up with a message Name conflicts with existing module, project or object library. The only option I have got is o.k which does not save my tick in the box.

Any idea's.

Thanks

 

[Waltang]

What all libraries are checked when you goto references?

 

[Waltang]

Just for the heck of it....

Change the line that says

Dim rst As DAO.Recordset

to be just

Dim rst As Recordset