2 subforms only one clears its record when new record selected

[naigy via AccessMonster.com]

Hi I have a form that is used for entering service details for a unit. The
main form is used for entering unit serial number, service date etc. Sub1 is
used for parts required to service unit and Sub2 is used for additional
services Performed. This Sub2 is the field I have a problem with.

Now my problem is if I am viewing the most recent record and click create new
record from the record selector at the bottom of the form Sub2 data does not
clear itself. Instead it still holds the data of the previous record. However
on my switchboard I have a button which opens this main form in add mode and
it opens with Sub2 data blank. Sub2 is linked to the main form with 2 fields
(Servicedate & SerialNumber).

Any help on how to clear the data in Sub2 when new record is created using
nav buttons would be appreciated. Thanks.

 

[tina]

hmm, that sounds strange. does the 2nd subform display the correct
associated records as you move from existing record to existing record in
the main form?

you might try explicitly requerying the 2nd subform when the main form moves
to a new record, by adding code to the main form's Current event, as

If Me.NewRecord Then Me!2ndSubformControlName.Form.Requery

the above goes all on one line.

hth

 

[naigy via AccessMonster.com]

Yes the second subform if you scoll through records (main form) always
displays correct data. If when you create a new record using the nav buttons
you input data into main form then go to a previous one and back again it
will then display the correct data.

Will try that code when I get a chance and post back my findings. Thanks for
the assistance.

hmm, that sounds strange. does the 2nd subform display the correct
associated records as you move from existing record to existing record in
the main form?

you might try explicitly requerying the 2nd subform when the main form moves
to a new record, by adding code to the main form's Current event, as

If Me.NewRecord Then Me!2ndSubformControlName.Form.Requery

the above goes all on one line.

hth

Hi I have a form that is used for entering service details for a unit. The
main form is used for entering unit serial number, service date etc. Sub1 is

[quoted text clipped - 10 lines]

Any help on how to clear the data in Sub2 when new record is created using
nav buttons would be appreciated. Thanks.

 

[tina]

you're welcome, good luck!

Yes the second subform if you scoll through records (main form) always
displays correct data. If when you create a new record using the nav buttons
you input data into main form then go to a previous one and back again it
will then display the correct data.

Will try that code when I get a chance and post back my findings. Thanks for
the assistance.

hmm, that sounds strange. does the 2nd subform display the correct
associated records as you move from existing record to existing record in
the main form?

you might try explicitly requerying the 2nd subform when the main form moves
to a new record, by adding code to the main form's Current event, as

If Me.NewRecord Then Me!2ndSubformControlName.Form.Requery

the above goes all on one line.

hth

Hi I have a form that is used for entering service details for a unit. The
main form is used for entering unit serial number, service date etc.

Sub1 is
[quoted text clipped - 10 lines]

Any help on how to clear the data in Sub2 when new record is created using
nav buttons would be appreciated. Thanks.

 

[naigy via AccessMonster.com]

Still no luck with that code. I have however gone to a previous backup a
month ago and the fault didn't occur, then to one a week ago and it did not
occur either. I then tried to copy the main form and the sub forms from the
old backup into the current database with the fault and the fault still
exists. Does this give any ideas where I should start searching. Thanks in
advance.

you're welcome, good luck!

Yes the second subform if you scoll through records (main form) always
displays correct data. If when you create a new record using the nav buttons

[quoted text clipped - 22 lines]

 

[tina]

well, it could be a corruption issue. suggest you try creating a new blank
database. if you're using Access 2000 or newer, immediately go to Tools |
Options | General tab and turn OFF the Name AutoCorrect option. then compact
the database. then import all the objects from your current database,
compact again, and test that form. if the problem persists, i might try
doing the above again, but import all the objects EXCEPT the problem main
form and subforms; then compact, back up the database, and rebuild those
objects from scratch - just to a minimally functional level where you can
test the linking and navigation, and see if the problem is fixed.

hth

Still no luck with that code. I have however gone to a previous backup a
month ago and the fault didn't occur, then to one a week ago and it did not
occur either. I then tried to copy the main form and the sub forms from the
old backup into the current database with the fault and the fault still
exists. Does this give any ideas where I should start searching. Thanks in
advance.

you're welcome, good luck!

Yes the second subform if you scoll through records (main form) always
displays correct data. If when you create a new record using the nav

buttons
[quoted text clipped - 22 lines]

Any help on how to clear the data in Sub2 when new record is created using
nav buttons would be appreciated. Thanks.

 

[naigy via AccessMonster.com]

Okay I am still in the middle of trying to copy everything across into a new
db file and reset my relationships etc. Which is turning out to be a big job.


I have however fiddled with the form slightly. I now have it so that when I
go to new record using nav selectors it will load a blank form but the entry
is not yet created. The second subform is blank. It is not until I start to
add data to the main form and the entry is created that the subform reverts
to the previous forms details But only assuming that the servicedate field
matches up (one of the 2 links between the forms). I am thinking is there a
way to delay the 2nd subform from displaying data until a textbox on the main
form has data and then do the requery or even have the subform not load until
this condition is meet. If this is possible I am positive it will clear my
problem. If so please advise the best way I could achieve this.

well, it could be a corruption issue. suggest you try creating a new blank
database. if you're using Access 2000 or newer, immediately go to Tools |
Options | General tab and turn OFF the Name AutoCorrect option. then compact
the database. then import all the objects from your current database,
compact again, and test that form. if the problem persists, i might try
doing the above again, but import all the objects EXCEPT the problem main
form and subforms; then compact, back up the database, and rebuild those
objects from scratch - just to a minimally functional level where you can
test the linking and navigation, and see if the problem is fixed.

hth

Still no luck with that code. I have however gone to a previous backup a
month ago and the fault didn't occur, then to one a week ago and it did not

[quoted text clipped - 10 lines]

 

[naigy via AccessMonster.com]

Further to my last reply if I use the following on a after update event for
the second linked field it clears my problem (and creates another).

DoCmd.RunCommand acCmdSaveRecord
Me.SRCleanQaEST.Requery

But if the serial number doesn't exist in the database yet it will give a
microsoft jet error. I do have on the same form a section where if the serial
number doesn't exist I can add it to the serial numbers table which has a
relationship to the service reports table based on Serial Number (main form).
In the Serial Numbers Table I have additional details like AssetID, Device,
Owner Etc.

If a serial Number exists in the database already then once I input the
serial number the rest of these fields will automatically populate. If the
unit doesn't exist I need to put this data in before I can save the record as
Serial Number is a required field and is part of a relationship.

Any way that I can catch the Microsoft Jet error and get it to do nothing on
an error. If so I will then add the same command to the last field required
for new units. That way I will have the best of both worlds. But I need to
hide the Microsft jet error message to do this effectively.

Okay I am still in the middle of trying to copy everything across into a new
db file and reset my relationships etc. Which is turning out to be a big job.

I have however fiddled with the form slightly. I now have it so that when I
go to new record using nav selectors it will load a blank form but the entry
is not yet created. The second subform is blank. It is not until I start to
add data to the main form and the entry is created that the subform reverts
to the previous forms details But only assuming that the servicedate field
matches up (one of the 2 links between the forms). I am thinking is there a
way to delay the 2nd subform from displaying data until a textbox on the main
form has data and then do the requery or even have the subform not load until
this condition is meet. If this is possible I am positive it will clear my
problem. If so please advise the best way I could achieve this.

well, it could be a corruption issue. suggest you try creating a new blank
database. if you're using Access 2000 or newer, immediately go to Tools |

[quoted text clipped - 13 lines]

 

[tina]

this shouldn't be a big job, it should only take a few minutes, at most. if
you open the "new, blank" database to the database window and, from the menu
bar, click File | Get External Date | Import, then the Import dialog opens.
simply click the Select All button on each tab (Tables, Queries, etc). the
relationships should be copied over automatically; to check on this, click
the Options button and you'll see an Import section, bottom left of the
dialog window. the Relationships box should be checkmarked by default.

hth

Okay I am still in the middle of trying to copy everything across into a new
db file and reset my relationships etc. Which is turning out to be a big job.


I have however fiddled with the form slightly. I now have it so that when I
go to new record using nav selectors it will load a blank form but the entry
is not yet created. The second subform is blank. It is not until I start to
add data to the main form and the entry is created that the subform reverts
to the previous forms details But only assuming that the servicedate field
matches up (one of the 2 links between the forms). I am thinking is there a
way to delay the 2nd subform from displaying data until a textbox on the main
form has data and then do the requery or even have the subform not load until
this condition is meet. If this is possible I am positive it will clear my
problem. If so please advise the best way I could achieve this.

well, it could be a corruption issue. suggest you try creating a new blank
database. if you're using Access 2000 or newer, immediately go to Tools |
Options | General tab and turn OFF the Name AutoCorrect option. then compact
the database. then import all the objects from your current database,
compact again, and test that form. if the problem persists, i might try
doing the above again, but import all the objects EXCEPT the problem main
form and subforms; then compact, back up the database, and rebuild those
objects from scratch - just to a minimally functional level where you can
test the linking and navigation, and see if the problem is fixed.

hth

Still no luck with that code. I have however gone to a previous backup a
month ago and the fault didn't occur, then to one a week ago and it did

not
[quoted text clipped - 10 lines]

Any help on how to clear the data in Sub2 when new record is created using
nav buttons would be appreciated. Thanks.

 

[tina]

well, frankly, i didn't follow most of that. you shouldn't have to go
through hoops just to have synchonized views of parent/child records
displayed in a mainform/subform format. i can only suggest you re-examine
the relationships you've set up between the parent/child tables in the
Relationships window, to make sure they're correct and enforced, and then
examine the LinkChildFields/LinkMasterFields properties of your subform
control to verify that they match the table relationships.

hth

Further to my last reply if I use the following on a after update event for
the second linked field it clears my problem (and creates another).

DoCmd.RunCommand acCmdSaveRecord
Me.SRCleanQaEST.Requery

But if the serial number doesn't exist in the database yet it will give a
microsoft jet error. I do have on the same form a section where if the serial
number doesn't exist I can add it to the serial numbers table which has a
relationship to the service reports table based on Serial Number (main form).
In the Serial Numbers Table I have additional details like AssetID, Device,
Owner Etc.

If a serial Number exists in the database already then once I input the
serial number the rest of these fields will automatically populate. If the
unit doesn't exist I need to put this data in before I can save the record as
Serial Number is a required field and is part of a relationship.

Any way that I can catch the Microsoft Jet error and get it to do nothing on
an error. If so I will then add the same command to the last field required
for new units. That way I will have the best of both worlds. But I need to
hide the Microsft jet error message to do this effectively.

Okay I am still in the middle of trying to copy everything across into a new
db file and reset my relationships etc. Which is turning out to be a big job.

I have however fiddled with the form slightly. I now have it so that when I
go to new record using nav selectors it will load a blank form but the entry
is not yet created. The second subform is blank. It is not until I start to
add data to the main form and the entry is created that the subform reverts
to the previous forms details But only assuming that the servicedate field
matches up (one of the 2 links between the forms). I am thinking is there a
way to delay the 2nd subform from displaying data until a textbox on the main
form has data and then do the requery or even have the subform not load until
this condition is meet. If this is possible I am positive it will clear my
problem. If so please advise the best way I could achieve this.

well, it could be a corruption issue. suggest you try creating a new blank
database. if you're using Access 2000 or newer, immediately go to Tools

|
[quoted text clipped - 13 lines]

Any help on how to clear the data in Sub2 when new record is created using
nav buttons would be appreciated. Thanks.