SubForm will not hold new record

[JimN]

In a time clock there is a form, from a table, that has staff ID# and name in
a drop down box that is supposed to link to a subform new record that clocks
in time , date to a table. Both Form and subform have DataEntry property set
to yes, but when an ID# is selected the subform shows all clock-in records
for that staff person. Have tried DoCmd.OpenForm "StaffClockIn";
DoCmd.GoToRecord,, acNewRec, set AllowAdditions to Yes. That is all I could
think off. Is there any way to force the subform to a new record after a
selection in the form is made?

 

[tina]

when you say "subform", are you talking about a subform within the main
form? or do you mean that you're clicking a command button to open a
*separate* form, in a separate window?

 

[JimN]

Hey Tina,
Thanks for the response. It was tried both ways, as a form and subform,
which is the preferred way, and as a form opening a second form. The second
way requires more coding to make sure both forms are closed before the next
staff member clocks in.

 

[tina]

okay. the reason i asked is that it should work fine as a form/subform. i
did a test of main form (bound to Staff table) and subform (bound to
StaffTimeClock table), linked on a common StaffID field. i set my main form
to DataEntry = No, and subform to DataEntry = Yes, with no other changes
required. it worked as expected: multiple records returned in the main
form, and subform returning new record only for each main form record -
regardless of how many subform table records actually matched the main form
record.

i did another test with main form unbound, and having an unbound combo box
with RowSource based on the Staff table, with BoundColumn set to the StaffID
column. main form / subform were linked on the combobox / StaffID. DataEntry
settings same as noted above. combobox on main form used to select specific
records from Staff table, for data entry into subform. results same as noted
above.

i'm at a loss as to what to suggest that you check "next". is there anything
unusual about your form/subform setup, either in Property settings or in VBA
code, that you can tell us about? - there must be something else going on
that's causing the results you're seeing. and btw, what version of Acess are
you using?

 

[JimN]

Hey Tina,
I am using Office XP Professional. I will go back through forms again, might
send copy if possible. will be tomorrow as I am out of here in 10. Have a
good evening.

 

[tina]

okay. i think Office XP Pro includes Access 2002. i am willing to look at
your db, because i'm totally stumped, though i'm guessing it'll turn out to
be an easy fix. but *don't* attach the db to a post - that's not acceptable
use of the forum. if you want to send it to me, post your email address,
*disguised* so you don't get spammed, and i'll email you.

 

[JimN]

Tina,
What is between a Lieutenant and a Major, concatenated to my first name, at,
scorching letters, period, kom?

 

[tina]

LOL
well, i only know military ranks from watching years of M*A*S*H, so i hope
you're referring to Toni Tennille's honey. <g>
email sent, whether it arrives remains to be seen.

 

[tina]

**
if anyone is still following this thread - especially MVPs - and has any
comments on the following, i'd be glad to see them because i'm completely
confounded. <g>
**

well, Jim, i worked on your db for nearly three hours. i got it fixed in
about an hour, then spent the next two hours trying to duplicate the fix -
but i couldn't! it's the darnedest thing i've ever seen.

i started out by reviewing your forms. i found that when the main form is
initially opened, the subform's DataEntry setting is True, and the subform
behaves as expected. when you move to another record in the main form, the
subform's DataEntry setting changes to False, and stays that way until the
form is closed. i couldn't find any settings or code that might cause the
problem. thinking one of the forms might be corrupted, i created new forms
and copied/pasted the objects, and duplicated the settings. no joy.

in your database, Name AutoCorrect is turned on. i turned it off and
compacted/repaired. no joy. that option is sometimes hard to "shake off", so
i created a new database, immediately turned off Name AutoCorrect,
compacted/repaired, and imported the objects from your db, including the new
forms i had created. no change.

i imported the "test" table/form/subform setup i had used before answering
your original post. they worked, but your tables/forms didn't. thinking that
perhaps one of your tables might be corrupted, i created new blank tables
and copied/pasted all the fields from your tables. then dumped the data from
your tables into the new ones. tested your forms again, and also the new
forms i'd earlier built. no luck.

i built copies of your tables from scratch, using proper naming conventions,
setting Subdatasheet to [None}, creating a primary key field for table
TimeClock (which your original table doesn't have), and setting the
relationship in the Relationships window. i created new forms based on the
new tables, and they worked! so i tried tweaking your forms to run with the
new table/field names - *now* they worked!

okay. so i figured Access didn't like something specific about your
table/field settings. i set about changing one thing at a time in your
tables, to match "my version" of your tables, then testing your forms (with
the single change supported). if a change didn't make the forms work, i
changed it back, and then went on to the next thing.

in the end, your forms worked when all i changed in your tables was adding
the primary key field to table TimeClock. so i should be able to duplicate
the fix by opening an untouched copy of your db, and adding a primary key
field to table TimeClock. but this time, your forms didn't work. so i tried
creating a new db, turning off Name AutoCorrect, compacting/repairing,
importing all the objects from the untouched copy of your db, and adding the
primary key field to table TimeClock. still no joy.

no matter what i tried, i was not able to duplicate the fix i achieved. so i
have to say it beats the heck out of me exactly what was wrong, or exactly
how i fixed it. but i did email a copy of your db back to you with the
working tables/forms. hopefully they'll still work when you get them.

if they don't, you can *force* the subform to hold the DataEntry setting by
adding a single line of code to the main form's Current event, as

Private Sub Form_Current()

Me!TimeClockInData.Form.DataEntry = True

End Sub

hth