Getting subform to open/load with a new record

  • Thread starter Thread starter Mishanya
  • Start date Start date
M

Mishanya

When I select ClientID in the ParentForm, the subform (singleView) is
automatically loaded with the first relevant record.
I want it to load blank, ready for a new entry. Normally I'd put
DoCmd.GoToRecord , , acNewRec
in the OnOpen event, bur it seems to work only with independent forms, wich
are
loaded in the new window. What in the case of subform?
 
Mishanya,
I think you will have to set focus to the subform before you can tell it to
go to a new record.

Something like this untested air code in the after update event of the
ClientID control.

Form!MyFormName!MySubformName.Form!MySubformControlName.SetFocus
DoCmd.GoToRecord,,acNewRec

Good luck.
Mike
 
Thanks for caring, Mke!
This way it resets the ParentForm to tne new entry as well. Means the
ParentForm aplies
DoCmd.GoToRecord,,acNewRec
to itself(so all the subforms are reset also).
I want to reset the specific subform only. What do You say?
 
Mishanya,
Did you change the "MyFormName" and the others to your actual form and
control names.

I think it may be easier to have the code.

Me.SubformName.SetFocus
DoCmd.GoToRecord,, acNewRecord.

Replace "SubFormName" with actual name of your subform.

Where you place the code will depend on where the control ClientID is
located.

Where is the control ClientID located?

However your application is set up you still have to give the focus to the
subform before you can tell it to go to a new record.

Regards,
Mike
 
Mike,
I did change the actual names.
The control ClientID is located in the MainForm. It already has some code in
AfterUpdate event:
1) to set the whole MainForm and its subforms to the selected ClientID
recordset
2) to require the unbound cboSelector in the subform that we are talking
about to filter its underlying query to the selected ClientID.
So now the whole event looks like this:

Private Sub ClientID_AfterUpdate()

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientID] = " & Str(Nz(Me![ClientID], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me!CRM.Form!cboSelectCRM.Requery

Me!CRM.SetFocus
DoCmd.GoToRecord , , acNewRecord

End Sub

With the code that You've advised I get error message 2105 "You can't go to
the specifide record" and in the Debugger I have the line "DoCmd.GoToRecord ,
, acNewRecord" yellowed.

Can it be managed?
 
Mishanya,
The line to set focus should be Me.(DOT) not Me!(BANG)
Me.subformname.setfocus

Try that and see what happens.

What is the function of the line "Me!CRM.Form!cboSelectCRM.Requery?

This is what I setup.
I usually reserve the letters ID for my autonumber field. As in ClientID. I
then make a field ClientNumber to store the actual client number.

Private Sub cboClientNumber_AfterUpdate()

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientID] = " & Str(Nz(Me![cboClientNumber], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me.sfrmClientDetail.SetFocus
DoCmd.GoToRecord,, acNewRecord

End Sub

Where ClientID is an autonumber field and cboClientNumber is bound to the
field ClientNumber (text)
cboClientNumber is on the parent form

Personally I would place the cboClientNumber on an unbound menu form and
call the client form from there. But that's just me.


Mike

Mishanya said:
Mike,
I did change the actual names.
The control ClientID is located in the MainForm. It already has some code
in
AfterUpdate event:
1) to set the whole MainForm and its subforms to the selected ClientID
recordset
2) to require the unbound cboSelector in the subform that we are talking
about to filter its underlying query to the selected ClientID.
So now the whole event looks like this:

Private Sub ClientID_AfterUpdate()

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientID] = " & Str(Nz(Me![ClientID], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me!CRM.Form!cboSelectCRM.Requery

Me!CRM.SetFocus
DoCmd.GoToRecord , , acNewRecord

End Sub

With the code that You've advised I get error message 2105 "You can't go
to
the specifide record" and in the Debugger I have the line
"DoCmd.GoToRecord ,
, acNewRecord" yellowed.

Can it be managed?


Mike Revis said:
Mishanya,
Did you change the "MyFormName" and the others to your actual form and
control names.

I think it may be easier to have the code.

Me.SubformName.SetFocus
DoCmd.GoToRecord,, acNewRecord.

Replace "SubFormName" with actual name of your subform.

Where you place the code will depend on where the control ClientID is
located.

Where is the control ClientID located?

However your application is set up you still have to give the focus to
the
subform before you can tell it to go to a new record.

Regards,
Mike
Click to expand...
Click to expand...
 
Mike
Indeed the selector is called SelectCLient - I've just ommited this fact for
the simplicity of explaining.

"What is the function of the line "Me!CRM.Form!cboSelectCRM.Requery?"
The subform "CRM" I'm dealing with has cboSelectCRM selector wich searchs
for the specific CRM-record. As it is unbound, it is not filtered by the
link-field (ClientID) of the mother-MainForm (as is the subform "CRM" by
definition), but unfold the list of all CRM-records. In order to filter it,
I've put "[Forms]![MainForm].[SelectCLientID]" in its query criteria and
complited this by putting ""Me!CRM.Form!cboSelectCRM.Requery" in the
AfterUpdate of the SelectCLient combo. It works fine.
Now I've tested Your last advice and put the code for the AfterUpdate of
SelectCLient as follows:

Private Sub SelectCLient_AfterUpdate()

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CLientID] = " & Str(Nz(Me![SelectCLient], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me!CRM.Form!cboSelectCRM.Requery

Me.CRM.SetFocus
DoCmd.GoToRecord , , acNewRecord
End Sub

Unfortunatley, I still get the same BANG, same yellow on the
"DoCmd.GoToRecord , , acNewRecord" and same error "You can't go to the
specified record". I've even erased the "Me!CRM.Form!cboSelectCRM.Requery"
line - to no effect.

If You have more patience - try to crack it. If not - still thank You for
the effort - I'll live without automatical "new" record.

Mike Revis said:
Mishanya,
The line to set focus should be Me.(DOT) not Me!(BANG)
Me.subformname.setfocus

Try that and see what happens.

What is the function of the line "Me!CRM.Form!cboSelectCRM.Requery?

This is what I setup.
I usually reserve the letters ID for my autonumber field. As in ClientID. I
then make a field ClientNumber to store the actual client number.

Private Sub cboClientNumber_AfterUpdate()

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientID] = " & Str(Nz(Me![cboClientNumber], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me.sfrmClientDetail.SetFocus
DoCmd.GoToRecord,, acNewRecord

End Sub

Where ClientID is an autonumber field and cboClientNumber is bound to the
field ClientNumber (text)
cboClientNumber is on the parent form

Personally I would place the cboClientNumber on an unbound menu form and
call the client form from there. But that's just me.


Mike

Mishanya said:
Mike,
I did change the actual names.
The control ClientID is located in the MainForm. It already has some code
in
AfterUpdate event:
1) to set the whole MainForm and its subforms to the selected ClientID
recordset
2) to require the unbound cboSelector in the subform that we are talking
about to filter its underlying query to the selected ClientID.
So now the whole event looks like this:

Private Sub ClientID_AfterUpdate()

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientID] = " & Str(Nz(Me![ClientID], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me!CRM.Form!cboSelectCRM.Requery

Me!CRM.SetFocus
DoCmd.GoToRecord , , acNewRecord

End Sub

With the code that You've advised I get error message 2105 "You can't go
to
the specifide record" and in the Debugger I have the line
"DoCmd.GoToRecord ,
, acNewRecord" yellowed.

Can it be managed?


Mike Revis said:
Mishanya,
Did you change the "MyFormName" and the others to your actual form and
control names.

I think it may be easier to have the code.

Me.SubformName.SetFocus
DoCmd.GoToRecord,, acNewRecord.

Replace "SubFormName" with actual name of your subform.

Where you place the code will depend on where the control ClientID is
located.

Where is the control ClientID located?

However your application is set up you still have to give the focus to
the
subform before you can tell it to go to a new record.

Regards,
Mike


Thanks for caring, Mke!
This way it resets the ParentForm to tne new entry as well. Means the
ParentForm aplies
DoCmd.GoToRecord,,acNewRec
to itself(so all the subforms are reset also).
I want to reset the specific subform only. What do You say?

:

Mishanya,
I think you will have to set focus to the subform before you can tell
it
to
go to a new record.

Something like this untested air code in the after update event of the
ClientID control.

Form!MyFormName!MySubformName.Form!MySubformControlName.SetFocus
DoCmd.GoToRecord,,acNewRec

Good luck.
Mike


When I select ClientID in the ParentForm, the subform (singleView)
is
automatically loaded with the first relevant record.
I want it to load blank, ready for a new entry. Normally I'd put
DoCmd.GoToRecord , , acNewRec
in the OnOpen event, bur it seems to work only with independent
forms,
wich
are
loaded in the new window. What in the case of subform?
Click to expand...
Click to expand...
Click to expand...
 
To complete step-by-step testing I've just tried to omit the
"DoCmd.GoToRecord,, acNewRecord" leaving just
Me!CRM.Form!cboSelectCRM.Requery
Me.CRM.SetFocus
Well - it perfectly filters the cboSelectCRM and puts the cursor in the
first CRM-subform textbox. But as I add the damn "DoCmd.GoToRecord,,
acNewRecord" - same BANG again!

Mike Revis said:
Mishanya,
The line to set focus should be Me.(DOT) not Me!(BANG)
Me.subformname.setfocus

Try that and see what happens.

What is the function of the line "Me!CRM.Form!cboSelectCRM.Requery?

This is what I setup.
I usually reserve the letters ID for my autonumber field. As in ClientID. I
then make a field ClientNumber to store the actual client number.

Private Sub cboClientNumber_AfterUpdate()

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientID] = " & Str(Nz(Me![cboClientNumber], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me.sfrmClientDetail.SetFocus
DoCmd.GoToRecord,, acNewRecord

End Sub

Where ClientID is an autonumber field and cboClientNumber is bound to the
field ClientNumber (text)
cboClientNumber is on the parent form

Personally I would place the cboClientNumber on an unbound menu form and
call the client form from there. But that's just me.


Mike

Mishanya said:
Mike,
I did change the actual names.
The control ClientID is located in the MainForm. It already has some code
in
AfterUpdate event:
1) to set the whole MainForm and its subforms to the selected ClientID
recordset
2) to require the unbound cboSelector in the subform that we are talking
about to filter its underlying query to the selected ClientID.
So now the whole event looks like this:

Private Sub ClientID_AfterUpdate()

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientID] = " & Str(Nz(Me![ClientID], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me!CRM.Form!cboSelectCRM.Requery

Me!CRM.SetFocus
DoCmd.GoToRecord , , acNewRecord

End Sub

With the code that You've advised I get error message 2105 "You can't go
to
the specifide record" and in the Debugger I have the line
"DoCmd.GoToRecord ,
, acNewRecord" yellowed.

Can it be managed?


Mike Revis said:
Mishanya,
Did you change the "MyFormName" and the others to your actual form and
control names.

I think it may be easier to have the code.

Me.SubformName.SetFocus
DoCmd.GoToRecord,, acNewRecord.

Replace "SubFormName" with actual name of your subform.

Where you place the code will depend on where the control ClientID is
located.

Where is the control ClientID located?

However your application is set up you still have to give the focus to
the
subform before you can tell it to go to a new record.

Regards,
Mike


Thanks for caring, Mke!
This way it resets the ParentForm to tne new entry as well. Means the
ParentForm aplies
DoCmd.GoToRecord,,acNewRec
to itself(so all the subforms are reset also).
I want to reset the specific subform only. What do You say?

:

Mishanya,
I think you will have to set focus to the subform before you can tell
it
to
go to a new record.

Something like this untested air code in the after update event of the
ClientID control.

Form!MyFormName!MySubformName.Form!MySubformControlName.SetFocus
DoCmd.GoToRecord,,acNewRec

Good luck.
Mike


When I select ClientID in the ParentForm, the subform (singleView)
is
automatically loaded with the first relevant record.
I want it to load blank, ready for a new entry. Normally I'd put
DoCmd.GoToRecord , , acNewRec
in the OnOpen event, bur it seems to work only with independent
forms,
wich
are
loaded in the new window. What in the case of subform?
Click to expand...
Click to expand...
Click to expand...
 
Mishanya,
I am at a loss. I would think that once the subform has the focus you should
be able to go to a new record on the subform.

Best of luck.
Mike

Mishanya said:
To complete step-by-step testing I've just tried to omit the
"DoCmd.GoToRecord,, acNewRecord" leaving just
Me!CRM.Form!cboSelectCRM.Requery
Me.CRM.SetFocus
Well - it perfectly filters the cboSelectCRM and puts the cursor in the
first CRM-subform textbox. But as I add the damn "DoCmd.GoToRecord,,
acNewRecord" - same BANG again!

Mike Revis said:
Mishanya,
The line to set focus should be Me.(DOT) not Me!(BANG)
Me.subformname.setfocus

Try that and see what happens.

What is the function of the line "Me!CRM.Form!cboSelectCRM.Requery?

This is what I setup.
I usually reserve the letters ID for my autonumber field. As in ClientID.
I
then make a field ClientNumber to store the actual client number.

Private Sub cboClientNumber_AfterUpdate()

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientID] = " & Str(Nz(Me![cboClientNumber], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me.sfrmClientDetail.SetFocus
DoCmd.GoToRecord,, acNewRecord

End Sub

Where ClientID is an autonumber field and cboClientNumber is bound to the
field ClientNumber (text)
cboClientNumber is on the parent form

Personally I would place the cboClientNumber on an unbound menu form and
call the client form from there. But that's just me.


Mike

Mishanya said:
Mike,
I did change the actual names.
The control ClientID is located in the MainForm. It already has some
code
in
AfterUpdate event:
1) to set the whole MainForm and its subforms to the selected ClientID
recordset
2) to require the unbound cboSelector in the subform that we are
talking
about to filter its underlying query to the selected ClientID.
So now the whole event looks like this:

Private Sub ClientID_AfterUpdate()

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientID] = " & Str(Nz(Me![ClientID], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me!CRM.Form!cboSelectCRM.Requery

Me!CRM.SetFocus
DoCmd.GoToRecord , , acNewRecord

End Sub

With the code that You've advised I get error message 2105 "You can't
go
to
the specifide record" and in the Debugger I have the line
"DoCmd.GoToRecord ,
, acNewRecord" yellowed.

Can it be managed?


:

Mishanya,
Did you change the "MyFormName" and the others to your actual form and
control names.

I think it may be easier to have the code.

Me.SubformName.SetFocus
DoCmd.GoToRecord,, acNewRecord.

Replace "SubFormName" with actual name of your subform.

Where you place the code will depend on where the control ClientID is
located.

Where is the control ClientID located?

However your application is set up you still have to give the focus to
the
subform before you can tell it to go to a new record.

Regards,
Mike


Thanks for caring, Mke!
This way it resets the ParentForm to tne new entry as well. Means
the
ParentForm aplies
DoCmd.GoToRecord,,acNewRec
to itself(so all the subforms are reset also).
I want to reset the specific subform only. What do You say?

:

Mishanya,
I think you will have to set focus to the subform before you can
tell
it
to
go to a new record.

Something like this untested air code in the after update event of
the
ClientID control.

Form!MyFormName!MySubformName.Form!MySubformControlName.SetFocus
DoCmd.GoToRecord,,acNewRec

Good luck.
Mike


When I select ClientID in the ParentForm, the subform
(singleView)
is
automatically loaded with the first relevant record.
I want it to load blank, ready for a new entry. Normally I'd put
DoCmd.GoToRecord , , acNewRec
in the OnOpen event, bur it seems to work only with independent
forms,
wich
are
loaded in the new window. What in the case of subform?
Click to expand...
Click to expand...
Click to expand...
 
Never mind, I'll leave to the Access misbehave.
Thank You for trying.

Mike Revis said:
Mishanya,
I am at a loss. I would think that once the subform has the focus you should
be able to go to a new record on the subform.

Best of luck.
Mike

Mishanya said:
To complete step-by-step testing I've just tried to omit the
"DoCmd.GoToRecord,, acNewRecord" leaving just
Me!CRM.Form!cboSelectCRM.Requery
Me.CRM.SetFocus
Well - it perfectly filters the cboSelectCRM and puts the cursor in the
first CRM-subform textbox. But as I add the damn "DoCmd.GoToRecord,,
acNewRecord" - same BANG again!

Mike Revis said:
Mishanya,
The line to set focus should be Me.(DOT) not Me!(BANG)
Me.subformname.setfocus

Try that and see what happens.

What is the function of the line "Me!CRM.Form!cboSelectCRM.Requery?

This is what I setup.
I usually reserve the letters ID for my autonumber field. As in ClientID.
I
then make a field ClientNumber to store the actual client number.

Private Sub cboClientNumber_AfterUpdate()

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientID] = " & Str(Nz(Me![cboClientNumber], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me.sfrmClientDetail.SetFocus
DoCmd.GoToRecord,, acNewRecord

End Sub

Where ClientID is an autonumber field and cboClientNumber is bound to the
field ClientNumber (text)
cboClientNumber is on the parent form

Personally I would place the cboClientNumber on an unbound menu form and
call the client form from there. But that's just me.


Mike

Mike,
I did change the actual names.
The control ClientID is located in the MainForm. It already has some
code
in
AfterUpdate event:
1) to set the whole MainForm and its subforms to the selected ClientID
recordset
2) to require the unbound cboSelector in the subform that we are
talking
about to filter its underlying query to the selected ClientID.
So now the whole event looks like this:

Private Sub ClientID_AfterUpdate()

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientID] = " & Str(Nz(Me![ClientID], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me!CRM.Form!cboSelectCRM.Requery

Me!CRM.SetFocus
DoCmd.GoToRecord , , acNewRecord

End Sub

With the code that You've advised I get error message 2105 "You can't
go
to
the specifide record" and in the Debugger I have the line
"DoCmd.GoToRecord ,
, acNewRecord" yellowed.

Can it be managed?


:

Mishanya,
Did you change the "MyFormName" and the others to your actual form and
control names.

I think it may be easier to have the code.

Me.SubformName.SetFocus
DoCmd.GoToRecord,, acNewRecord.

Replace "SubFormName" with actual name of your subform.

Where you place the code will depend on where the control ClientID is
located.

Where is the control ClientID located?

However your application is set up you still have to give the focus to
the
subform before you can tell it to go to a new record.

Regards,
Mike


Thanks for caring, Mke!
This way it resets the ParentForm to tne new entry as well. Means
the
ParentForm aplies
DoCmd.GoToRecord,,acNewRec
to itself(so all the subforms are reset also).
I want to reset the specific subform only. What do You say?

:

Mishanya,
I think you will have to set focus to the subform before you can
tell
it
to
go to a new record.

Something like this untested air code in the after update event of
the
ClientID control.

Form!MyFormName!MySubformName.Form!MySubformControlName.SetFocus
DoCmd.GoToRecord,,acNewRec

Good luck.
Mike


When I select ClientID in the ParentForm, the subform
(singleView)
is
automatically loaded with the first relevant record.
I want it to load blank, ready for a new entry. Normally I'd put
DoCmd.GoToRecord , , acNewRec
in the OnOpen event, bur it seems to work only with independent
forms,
wich
are
loaded in the new window. What in the case of subform?
Click to expand...
Click to expand...
Click to expand...
 
Hi Mike
This morning I've put EXACTLY THE SAME CODE as yesterday - suddenly it's
working, no errors, I can even put Me.SetFocus in the end and return the
focus to the mainform. I can swear that I've changed nothing - just compacted
and repaired the DB (but I did so yesterday numerous times as we were
communicating) and continued to work with new copy of DB (again, did so
yesterday 3 or 4 times, as I always do after every minor change to the DB I
work on, so I can always roll back if the change has made some damage).
I just can not understand the Access manners.

Mike Revis said:
Mishanya,
I am at a loss. I would think that once the subform has the focus you should
be able to go to a new record on the subform.

Best of luck.
Mike

Mishanya said:
To complete step-by-step testing I've just tried to omit the
"DoCmd.GoToRecord,, acNewRecord" leaving just
Me!CRM.Form!cboSelectCRM.Requery
Me.CRM.SetFocus
Well - it perfectly filters the cboSelectCRM and puts the cursor in the
first CRM-subform textbox. But as I add the damn "DoCmd.GoToRecord,,
acNewRecord" - same BANG again!

Mike Revis said:
Mishanya,
The line to set focus should be Me.(DOT) not Me!(BANG)
Me.subformname.setfocus

Try that and see what happens.

What is the function of the line "Me!CRM.Form!cboSelectCRM.Requery?

This is what I setup.
I usually reserve the letters ID for my autonumber field. As in ClientID.
I
then make a field ClientNumber to store the actual client number.

Private Sub cboClientNumber_AfterUpdate()

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientID] = " & Str(Nz(Me![cboClientNumber], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me.sfrmClientDetail.SetFocus
DoCmd.GoToRecord,, acNewRecord

End Sub

Where ClientID is an autonumber field and cboClientNumber is bound to the
field ClientNumber (text)
cboClientNumber is on the parent form

Personally I would place the cboClientNumber on an unbound menu form and
call the client form from there. But that's just me.


Mike

Mike,
I did change the actual names.
The control ClientID is located in the MainForm. It already has some
code
in
AfterUpdate event:
1) to set the whole MainForm and its subforms to the selected ClientID
recordset
2) to require the unbound cboSelector in the subform that we are
talking
about to filter its underlying query to the selected ClientID.
So now the whole event looks like this:

Private Sub ClientID_AfterUpdate()

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientID] = " & Str(Nz(Me![ClientID], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me!CRM.Form!cboSelectCRM.Requery

Me!CRM.SetFocus
DoCmd.GoToRecord , , acNewRecord

End Sub

With the code that You've advised I get error message 2105 "You can't
go
to
the specifide record" and in the Debugger I have the line
"DoCmd.GoToRecord ,
, acNewRecord" yellowed.

Can it be managed?


:

Mishanya,
Did you change the "MyFormName" and the others to your actual form and
control names.

I think it may be easier to have the code.

Me.SubformName.SetFocus
DoCmd.GoToRecord,, acNewRecord.

Replace "SubFormName" with actual name of your subform.

Where you place the code will depend on where the control ClientID is
located.

Where is the control ClientID located?

However your application is set up you still have to give the focus to
the
subform before you can tell it to go to a new record.

Regards,
Mike


Thanks for caring, Mke!
This way it resets the ParentForm to tne new entry as well. Means
the
ParentForm aplies
DoCmd.GoToRecord,,acNewRec
to itself(so all the subforms are reset also).
I want to reset the specific subform only. What do You say?

:

Mishanya,
I think you will have to set focus to the subform before you can
tell
it
to
go to a new record.

Something like this untested air code in the after update event of
the
ClientID control.

Form!MyFormName!MySubformName.Form!MySubformControlName.SetFocus
DoCmd.GoToRecord,,acNewRec

Good luck.
Mike


When I select ClientID in the ParentForm, the subform
(singleView)
is
automatically loaded with the first relevant record.
I want it to load blank, ready for a new entry. Normally I'd put
DoCmd.GoToRecord , , acNewRec
in the OnOpen event, bur it seems to work only with independent
forms,
wich
are
loaded in the new window. What in the case of subform?
Click to expand...
Click to expand...
Click to expand...
 
The mysteries of Access.
Happy to hear it works.
Mike

Mishanya said:
Hi Mike
This morning I've put EXACTLY THE SAME CODE as yesterday - suddenly it's
working, no errors, I can even put Me.SetFocus in the end and return the
focus to the mainform. I can swear that I've changed nothing - just
compacted
and repaired the DB (but I did so yesterday numerous times as we were
communicating) and continued to work with new copy of DB (again, did so
yesterday 3 or 4 times, as I always do after every minor change to the DB
I
work on, so I can always roll back if the change has made some damage).
I just can not understand the Access manners.

Mike Revis said:
Mishanya,
I am at a loss. I would think that once the subform has the focus you
should
be able to go to a new record on the subform.

Best of luck.
Mike

Mishanya said:
To complete step-by-step testing I've just tried to omit the
"DoCmd.GoToRecord,, acNewRecord" leaving just
Me!CRM.Form!cboSelectCRM.Requery
Me.CRM.SetFocus
Well - it perfectly filters the cboSelectCRM and puts the cursor in the
first CRM-subform textbox. But as I add the damn "DoCmd.GoToRecord,,
acNewRecord" - same BANG again!

:

Mishanya,
The line to set focus should be Me.(DOT) not Me!(BANG)
Me.subformname.setfocus

Try that and see what happens.

What is the function of the line "Me!CRM.Form!cboSelectCRM.Requery?

This is what I setup.
I usually reserve the letters ID for my autonumber field. As in
ClientID.
I
then make a field ClientNumber to store the actual client number.

Private Sub cboClientNumber_AfterUpdate()

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientID] = " & Str(Nz(Me![cboClientNumber], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me.sfrmClientDetail.SetFocus
DoCmd.GoToRecord,, acNewRecord

End Sub

Where ClientID is an autonumber field and cboClientNumber is bound to
the
field ClientNumber (text)
cboClientNumber is on the parent form

Personally I would place the cboClientNumber on an unbound menu form
and
call the client form from there. But that's just me.


Mike

Mike,
I did change the actual names.
The control ClientID is located in the MainForm. It already has some
code
in
AfterUpdate event:
1) to set the whole MainForm and its subforms to the selected
ClientID
recordset
2) to require the unbound cboSelector in the subform that we are
talking
about to filter its underlying query to the selected ClientID.
So now the whole event looks like this:

Private Sub ClientID_AfterUpdate()

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientID] = " & Str(Nz(Me![ClientID], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me!CRM.Form!cboSelectCRM.Requery

Me!CRM.SetFocus
DoCmd.GoToRecord , , acNewRecord

End Sub

With the code that You've advised I get error message 2105 "You
can't
go
to
the specifide record" and in the Debugger I have the line
"DoCmd.GoToRecord ,
, acNewRecord" yellowed.

Can it be managed?


:

Mishanya,
Did you change the "MyFormName" and the others to your actual form
and
control names.

I think it may be easier to have the code.

Me.SubformName.SetFocus
DoCmd.GoToRecord,, acNewRecord.

Replace "SubFormName" with actual name of your subform.

Where you place the code will depend on where the control ClientID
is
located.

Where is the control ClientID located?

However your application is set up you still have to give the focus
to
the
subform before you can tell it to go to a new record.

Regards,
Mike


Thanks for caring, Mke!
This way it resets the ParentForm to tne new entry as well. Means
the
ParentForm aplies
DoCmd.GoToRecord,,acNewRec
to itself(so all the subforms are reset also).
I want to reset the specific subform only. What do You say?

:

Mishanya,
I think you will have to set focus to the subform before you can
tell
it
to
go to a new record.

Something like this untested air code in the after update event
of
the
ClientID control.

Form!MyFormName!MySubformName.Form!MySubformControlName.SetFocus
DoCmd.GoToRecord,,acNewRec

Good luck.
Mike


When I select ClientID in the ParentForm, the subform
(singleView)
is
automatically loaded with the first relevant record.
I want it to load blank, ready for a new entry. Normally I'd
put
DoCmd.GoToRecord , , acNewRec
in the OnOpen event, bur it seems to work only with
independent
forms,
wich
are
loaded in the new window. What in the case of subform?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top