Stopping 'No Current Record' message

[Guest]

Hi.

I have a small form, being used as a subform, with a Delete Record command
button (created using the Wizard). I also have a couple of text boxes which I
will make invisable when I've finished the design. When the command button is
used, it will delete just one subform record per main form record. When I use
it, I always get a message 'No Current Record' once the delete has taken
place, maybe because the focus is going to one of the text boxes and there's
no record to display. Is there a way of stopping this message appearing?
Would setting the focus to one of the main form fields work and if so, how
would I do it? Im using Access 2000 on XP.

Thanks, JohnB

 

[Guest]

Hi John

I expect you're right, you probably can't leave the focus on an empty
subform. Setting focus is easy, use:

[Forms]![Name of Form]![Name of Control].SetFocus

Incidentally, an empty textbox can accept the focus, that won't be a problem.

Cheers

David

 

[JohnB]

Thanks David.

Unfortnately I can't try this until tomorrow. I'll let you know how I get
on, either way. JohnB

Hi John

I expect you're right, you probably can't leave the focus on an empty
subform. Setting focus is easy, use:

[Forms]![Name of Form]![Name of Control].SetFocus

Incidentally, an empty textbox can accept the focus, that won't be a problem.

Cheers

David

Hi.

I have a small form, being used as a subform, with a Delete Record command
button (created using the Wizard). I also have a couple of text boxes which I
will make invisable when I've finished the design. When the command button is
used, it will delete just one subform record per main form record. When I use
it, I always get a message 'No Current Record' once the delete has taken
place, maybe because the focus is going to one of the text boxes and there's
no record to display. Is there a way of stopping this message appearing?
Would setting the focus to one of the main form fields work and if so, how
would I do it? Im using Access 2000 on XP.

Thanks, JohnB

 

[Guest]

Hi David. Sorry, but I did send you a holding reply yesterday - it seems to
have vanished.

Thanks for the suggestion but Iv'e just tried it without success. The code
I'm using is as follows, in the On Click event of the delete button. Just to
expand, this button is on a subform on form frmSECStudents and field
txtFirstName is the first field on frmSECStudents. When I use the command
button, I get the same results as before - the record is deleted and then I
get "No Current Record" and the focus goes to one of the fields on the
subform, as before.

By the way, I've discovered that I can't make all of the fields on the
subform invisable -if I do, the Delete button does nothing when clicked.
Unless I can find a different way to delete the record, I'll have to leave at
least one text field visable and make it tiny.

Anyway, here is the code I'm using. Am I doing this correctly? Thanks again,
JohnB

Private Sub cmdDeleteRecord_Click()

On Error GoTo Err_cmdDeleteRecord_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

[Forms]![frmSECStudents]![txtFirstName].SetFocus

Exit_cmdDeleteRecord_Click:

Exit Sub

Err_cmdDeleteRecord_Click:

MsgBox Err.Description

Resume Exit_cmdDeleteRecord_Click

End Sub

Hi John

I expect you're right, you probably can't leave the focus on an empty
subform. Setting focus is easy, use:

[Forms]![Name of Form]![Name of Control].SetFocus

Incidentally, an empty textbox can accept the focus, that won't be a problem.

Cheers

David

Hi.

I have a small form, being used as a subform, with a Delete Record command
button (created using the Wizard). I also have a couple of text boxes which I
will make invisable when I've finished the design. When the command button is
used, it will delete just one subform record per main form record. When I use
it, I always get a message 'No Current Record' once the delete has taken
place, maybe because the focus is going to one of the text boxes and there's
no record to display. Is there a way of stopping this message appearing?
Would setting the focus to one of the main form fields work and if so, how
would I do it? Im using Access 2000 on XP.

Thanks, JohnB

 

[Guest]

Hi John

I don't know what is causing your problem. I would not have implemented this
in the same way - everyone does it the way they have learned!

In general, I woudn't use DoCmd.DoMenuItem to delete a record from a subform
- mainly because I hadn't heard of it! I would create a recordset containing
the record I wanted to delete, delete it, and then requery the subform to
reflect the change.

To create the recordset, first create a SQL string which would select only
the record you want to delete, based on its primary key, for example:

Dim SQLString As String
SQLString = "SELECT [Person ID] FROM People WHERE [Person ID] = " &
[Forms]![Name of Form]![Name of Subform].Form![Name of Control Holding Person
ID]

This is much easier to do by creating the query in the query editor, going
to SQL View, and copying and pasting the SQL out.

To create the recordset itself:

Dim RecordToDelete As DAO.Recordset
Set RecordToDelete = CurrentDb.OpenRecordset("SQLString")

You need to set a reference to the DAO library in order to do this. In the
VB Editor, go to Tools - References and tick Microsoft DAO 3.6 Object
Library. If you're not familiar with DAO, I recommend it as it makes
operations on records much easier.

Then delete the record:
With RecordToDelete
..Delete
..Close
End With

It's important to use the Close method, as otherwise the recordset is not
released and clogs up memory, or something (I'm not clear on the
technicalities).

Then requery the subform to reflect the change:

[Forms]![Name of Form]![Name of Subform].Form.Requery

I'm sorry I can't help you resolve the specific problem you have, but the
approach I have described above has always worked for me.

Others may well have better ways of doing this but it's the only way I know!

Let me know how you get on.

Cheers

David

Hi David. Sorry, but I did send you a holding reply yesterday - it seems to
have vanished.

Thanks for the suggestion but Iv'e just tried it without success. The code
I'm using is as follows, in the On Click event of the delete button. Just to
expand, this button is on a subform on form frmSECStudents and field
txtFirstName is the first field on frmSECStudents. When I use the command
button, I get the same results as before - the record is deleted and then I
get "No Current Record" and the focus goes to one of the fields on the
subform, as before.

By the way, I've discovered that I can't make all of the fields on the
subform invisable -if I do, the Delete button does nothing when clicked.
Unless I can find a different way to delete the record, I'll have to leave at
least one text field visable and make it tiny.

Anyway, here is the code I'm using. Am I doing this correctly? Thanks again,
JohnB

Private Sub cmdDeleteRecord_Click()

On Error GoTo Err_cmdDeleteRecord_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

[Forms]![frmSECStudents]![txtFirstName].SetFocus

Exit_cmdDeleteRecord_Click:

Exit Sub

Err_cmdDeleteRecord_Click:

MsgBox Err.Description

Resume Exit_cmdDeleteRecord_Click

End Sub

Hi John

I expect you're right, you probably can't leave the focus on an empty
subform. Setting focus is easy, use:

[Forms]![Name of Form]![Name of Control].SetFocus

Incidentally, an empty textbox can accept the focus, that won't be a problem.

Cheers

David

Hi.

I have a small form, being used as a subform, with a Delete Record command
button (created using the Wizard). I also have a couple of text boxes which I
will make invisable when I've finished the design. When the command button is
used, it will delete just one subform record per main form record. When I use
it, I always get a message 'No Current Record' once the delete has taken
place, maybe because the focus is going to one of the text boxes and there's
no record to display. Is there a way of stopping this message appearing?
Would setting the focus to one of the main form fields work and if so, how
would I do it? Im using Access 2000 on XP.

Thanks, JohnB

 

[Guest]

Hi Davis. Thanks for the quick reply.

Hmmmm - seems a lot of work just to delete the current record shown in the
subform. Perhaps if I explain further. My subform shows a 'Show Photo' and
'Delete Record' buttons together with an Image Control. The idea is that
users use a different form/subform to set a link to images for each Student
record. This creates a record in tblImageFiles which is linked to
tblSECStudents by StudentID. Only one image record is created for each
student. Then they use this subform on the main students records to view and,
if necessary, delete the relevant photo record. The code used in the delete
command button is what is created when you use the wizard to create a delete
record command button.

Could your code be simplified , bearing in mind that the delete button will
always delete just the one, current, record being shown on the subform? Or is
there different code I could use in the buttons On Click event, to delete the
current record (and of course avoid the "No Current Record" message)?

Thanks again for the help. JohnB

Hi John

I don't know what is causing your problem. I would not have implemented this
in the same way - everyone does it the way they have learned!

In general, I woudn't use DoCmd.DoMenuItem to delete a record from a subform
- mainly because I hadn't heard of it! I would create a recordset containing
the record I wanted to delete, delete it, and then requery the subform to
reflect the change.

To create the recordset, first create a SQL string which would select only
the record you want to delete, based on its primary key, for example:

Dim SQLString As String
SQLString = "SELECT [Person ID] FROM People WHERE [Person ID] = " &
[Forms]![Name of Form]![Name of Subform].Form![Name of Control Holding Person
ID]

This is much easier to do by creating the query in the query editor, going
to SQL View, and copying and pasting the SQL out.

To create the recordset itself:

Dim RecordToDelete As DAO.Recordset
Set RecordToDelete = CurrentDb.OpenRecordset("SQLString")

You need to set a reference to the DAO library in order to do this. In the
VB Editor, go to Tools - References and tick Microsoft DAO 3.6 Object
Library. If you're not familiar with DAO, I recommend it as it makes
operations on records much easier.

Then delete the record:
With RecordToDelete
.Delete
.Close
End With

It's important to use the Close method, as otherwise the recordset is not
released and clogs up memory, or something (I'm not clear on the
technicalities).

Then requery the subform to reflect the change:

[Forms]![Name of Form]![Name of Subform].Form.Requery

I'm sorry I can't help you resolve the specific problem you have, but the
approach I have described above has always worked for me.

Others may well have better ways of doing this but it's the only way I know!

Let me know how you get on.

Cheers

David

Hi David. Sorry, but I did send you a holding reply yesterday - it seems to
have vanished.

Thanks for the suggestion but Iv'e just tried it without success. The code
I'm using is as follows, in the On Click event of the delete button. Just to
expand, this button is on a subform on form frmSECStudents and field
txtFirstName is the first field on frmSECStudents. When I use the command
button, I get the same results as before - the record is deleted and then I
get "No Current Record" and the focus goes to one of the fields on the
subform, as before.

By the way, I've discovered that I can't make all of the fields on the
subform invisable -if I do, the Delete button does nothing when clicked.
Unless I can find a different way to delete the record, I'll have to leave at
least one text field visable and make it tiny.

Anyway, here is the code I'm using. Am I doing this correctly? Thanks again,
JohnB

Private Sub cmdDeleteRecord_Click()

On Error GoTo Err_cmdDeleteRecord_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

[Forms]![frmSECStudents]![txtFirstName].SetFocus

Exit_cmdDeleteRecord_Click:

Exit Sub

Err_cmdDeleteRecord_Click:

MsgBox Err.Description

Resume Exit_cmdDeleteRecord_Click

End Sub

Hi John

I expect you're right, you probably can't leave the focus on an empty
subform. Setting focus is easy, use:

[Forms]![Name of Form]![Name of Control].SetFocus

Incidentally, an empty textbox can accept the focus, that won't be a problem.

Cheers

David

:

Hi.

I have a small form, being used as a subform, with a Delete Record command
button (created using the Wizard). I also have a couple of text boxes which I
will make invisable when I've finished the design. When the command button is
used, it will delete just one subform record per main form record. When I use
it, I always get a message 'No Current Record' once the delete has taken
place, maybe because the focus is going to one of the text boxes and there's
no record to display. Is there a way of stopping this message appearing?
Would setting the focus to one of the main form fields work and if so, how
would I do it? Im using Access 2000 on XP.

Thanks, JohnB

 

[Guest]

Hi John

I agree it seems a little complicated for a simple operation, but if the
code provided by the wizard doesn't work, it's worth a try!!

Have a go with this code. You will need to ensure that the subform has the
StudentID field (although it can be invisible). You will also need to change
the form, subform and field names in the SQL code I've shown below to
whatever you've called them.

Dim PhotoSQL As String
PhotoSQL = "SELECT tblImageFiles.[StudentID]
FROM tblImageFiles
WHERE (((tblImageFiles.[StudentID])=" & [Forms]![Name of Form]![Name of
subform].Form![Name of Control Containing StudentID field] & "));"

Dim PhotoToDelete As DAO.Recordset
Set PhotoToDelete = CurrentDb.OpenRecordset(PhotoSQL)
With PhotoToDelete
..Delete
..Close
End With

[Forms]![Name of Form]![Name of subform].Form.Requery

How's this working for you?

Cheers

David

Hi Davis. Thanks for the quick reply.

Hmmmm - seems a lot of work just to delete the current record shown in the
subform. Perhaps if I explain further. My subform shows a 'Show Photo' and
'Delete Record' buttons together with an Image Control. The idea is that
users use a different form/subform to set a link to images for each Student
record. This creates a record in tblImageFiles which is linked to
tblSECStudents by StudentID. Only one image record is created for each
student. Then they use this subform on the main students records to view and,
if necessary, delete the relevant photo record. The code used in the delete
command button is what is created when you use the wizard to create a delete
record command button.

Could your code be simplified , bearing in mind that the delete button will
always delete just the one, current, record being shown on the subform? Or is
there different code I could use in the buttons On Click event, to delete the
current record (and of course avoid the "No Current Record" message)?

Thanks again for the help. JohnB

Hi John

I don't know what is causing your problem. I would not have implemented this
in the same way - everyone does it the way they have learned!

In general, I woudn't use DoCmd.DoMenuItem to delete a record from a subform
- mainly because I hadn't heard of it! I would create a recordset containing
the record I wanted to delete, delete it, and then requery the subform to
reflect the change.

To create the recordset, first create a SQL string which would select only
the record you want to delete, based on its primary key, for example:

Dim SQLString As String
SQLString = "SELECT [Person ID] FROM People WHERE [Person ID] = " &
[Forms]![Name of Form]![Name of Subform].Form![Name of Control Holding Person
ID]

This is much easier to do by creating the query in the query editor, going
to SQL View, and copying and pasting the SQL out.

To create the recordset itself:

Dim RecordToDelete As DAO.Recordset
Set RecordToDelete = CurrentDb.OpenRecordset("SQLString")

You need to set a reference to the DAO library in order to do this. In the
VB Editor, go to Tools - References and tick Microsoft DAO 3.6 Object
Library. If you're not familiar with DAO, I recommend it as it makes
operations on records much easier.

Then delete the record:
With RecordToDelete
.Delete
.Close
End With

It's important to use the Close method, as otherwise the recordset is not
released and clogs up memory, or something (I'm not clear on the
technicalities).

Then requery the subform to reflect the change:

[Forms]![Name of Form]![Name of Subform].Form.Requery

I'm sorry I can't help you resolve the specific problem you have, but the
approach I have described above has always worked for me.

Others may well have better ways of doing this but it's the only way I know!

Let me know how you get on.

Cheers

David

Hi David. Sorry, but I did send you a holding reply yesterday - it seems to
have vanished.

Thanks for the suggestion but Iv'e just tried it without success. The code
I'm using is as follows, in the On Click event of the delete button. Just to
expand, this button is on a subform on form frmSECStudents and field
txtFirstName is the first field on frmSECStudents. When I use the command
button, I get the same results as before - the record is deleted and then I
get "No Current Record" and the focus goes to one of the fields on the
subform, as before.

By the way, I've discovered that I can't make all of the fields on the
subform invisable -if I do, the Delete button does nothing when clicked.
Unless I can find a different way to delete the record, I'll have to leave at
least one text field visable and make it tiny.

Anyway, here is the code I'm using. Am I doing this correctly? Thanks again,
JohnB

Private Sub cmdDeleteRecord_Click()

On Error GoTo Err_cmdDeleteRecord_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

[Forms]![frmSECStudents]![txtFirstName].SetFocus

Exit_cmdDeleteRecord_Click:

Exit Sub

Err_cmdDeleteRecord_Click:

MsgBox Err.Description

Resume Exit_cmdDeleteRecord_Click

End Sub


:

Hi John

I expect you're right, you probably can't leave the focus on an empty
subform. Setting focus is easy, use:

[Forms]![Name of Form]![Name of Control].SetFocus

Incidentally, an empty textbox can accept the focus, that won't be a problem.

Cheers

David

:

Hi.

I have a small form, being used as a subform, with a Delete Record command
button (created using the Wizard). I also have a couple of text boxes which I
will make invisable when I've finished the design. When the command button is
used, it will delete just one subform record per main form record. When I use
it, I always get a message 'No Current Record' once the delete has taken
place, maybe because the focus is going to one of the text boxes and there's
no record to display. Is there a way of stopping this message appearing?
Would setting the focus to one of the main form fields work and if so, how
would I do it? Im using Access 2000 on XP.

Thanks, JohnB

 

[Guest]

Thanks David.

OK I'll have a go at it, although it may be tomorrow before I get back to
you. I presume I should remove the two "DoCmd DoMenuItem" lines from the
existing Delete Record buton and replace them with your code, suitably
modified with my form/subform/field names?

Thanks again, JohnB

Hi John

I agree it seems a little complicated for a simple operation, but if the
code provided by the wizard doesn't work, it's worth a try!!

Have a go with this code. You will need to ensure that the subform has the
StudentID field (although it can be invisible). You will also need to change
the form, subform and field names in the SQL code I've shown below to
whatever you've called them.

Dim PhotoSQL As String
PhotoSQL = "SELECT tblImageFiles.[StudentID]
FROM tblImageFiles
WHERE (((tblImageFiles.[StudentID])=" & [Forms]![Name of Form]![Name of
subform].Form![Name of Control Containing StudentID field] & "));"

Dim PhotoToDelete As DAO.Recordset
Set PhotoToDelete = CurrentDb.OpenRecordset(PhotoSQL)
With PhotoToDelete
.Delete
.Close
End With

[Forms]![Name of Form]![Name of subform].Form.Requery

How's this working for you?

Cheers

David

Hi Davis. Thanks for the quick reply.

Hmmmm - seems a lot of work just to delete the current record shown in the
subform. Perhaps if I explain further. My subform shows a 'Show Photo' and
'Delete Record' buttons together with an Image Control. The idea is that
users use a different form/subform to set a link to images for each Student
record. This creates a record in tblImageFiles which is linked to
tblSECStudents by StudentID. Only one image record is created for each
student. Then they use this subform on the main students records to view and,
if necessary, delete the relevant photo record. The code used in the delete
command button is what is created when you use the wizard to create a delete
record command button.

Could your code be simplified , bearing in mind that the delete button will
always delete just the one, current, record being shown on the subform? Or is
there different code I could use in the buttons On Click event, to delete the
current record (and of course avoid the "No Current Record" message)?

Thanks again for the help. JohnB

Hi John

I don't know what is causing your problem. I would not have implemented this
in the same way - everyone does it the way they have learned!

In general, I woudn't use DoCmd.DoMenuItem to delete a record from a subform
- mainly because I hadn't heard of it! I would create a recordset containing
the record I wanted to delete, delete it, and then requery the subform to
reflect the change.

To create the recordset, first create a SQL string which would select only
the record you want to delete, based on its primary key, for example:

Dim SQLString As String
SQLString = "SELECT [Person ID] FROM People WHERE [Person ID] = " &
[Forms]![Name of Form]![Name of Subform].Form![Name of Control Holding Person
ID]

This is much easier to do by creating the query in the query editor, going
to SQL View, and copying and pasting the SQL out.

To create the recordset itself:

Dim RecordToDelete As DAO.Recordset
Set RecordToDelete = CurrentDb.OpenRecordset("SQLString")

You need to set a reference to the DAO library in order to do this. In the
VB Editor, go to Tools - References and tick Microsoft DAO 3.6 Object
Library. If you're not familiar with DAO, I recommend it as it makes
operations on records much easier.

Then delete the record:
With RecordToDelete
.Delete
.Close
End With

It's important to use the Close method, as otherwise the recordset is not
released and clogs up memory, or something (I'm not clear on the
technicalities).

Then requery the subform to reflect the change:

[Forms]![Name of Form]![Name of Subform].Form.Requery

I'm sorry I can't help you resolve the specific problem you have, but the
approach I have described above has always worked for me.

Others may well have better ways of doing this but it's the only way I know!

Let me know how you get on.

Cheers

David

:

Hi David. Sorry, but I did send you a holding reply yesterday - it seems to
have vanished.

Thanks for the suggestion but Iv'e just tried it without success. The code
I'm using is as follows, in the On Click event of the delete button. Just to
expand, this button is on a subform on form frmSECStudents and field
txtFirstName is the first field on frmSECStudents. When I use the command
button, I get the same results as before - the record is deleted and then I
get "No Current Record" and the focus goes to one of the fields on the
subform, as before.

By the way, I've discovered that I can't make all of the fields on the
subform invisable -if I do, the Delete button does nothing when clicked.
Unless I can find a different way to delete the record, I'll have to leave at
least one text field visable and make it tiny.

Anyway, here is the code I'm using. Am I doing this correctly? Thanks again,
JohnB

Private Sub cmdDeleteRecord_Click()

On Error GoTo Err_cmdDeleteRecord_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

[Forms]![frmSECStudents]![txtFirstName].SetFocus

Exit_cmdDeleteRecord_Click:

Exit Sub

Err_cmdDeleteRecord_Click:

MsgBox Err.Description

Resume Exit_cmdDeleteRecord_Click

End Sub


:

Hi John

I expect you're right, you probably can't leave the focus on an empty
subform. Setting focus is easy, use:

[Forms]![Name of Form]![Name of Control].SetFocus

Incidentally, an empty textbox can accept the focus, that won't be a problem.

Cheers

David

:

Hi.

I have a small form, being used as a subform, with a Delete Record command
button (created using the Wizard). I also have a couple of text boxes which I
will make invisable when I've finished the design. When the command button is
used, it will delete just one subform record per main form record. When I use
it, I always get a message 'No Current Record' once the delete has taken
place, maybe because the focus is going to one of the text boxes and there's
no record to display. Is there a way of stopping this message appearing?
Would setting the focus to one of the main form fields work and if so, how
would I do it? Im using Access 2000 on XP.

Thanks, JohnB

 

[Guest]

Hi John

Yes, that's right. Replace your code with the code I suggested. The only
fiddly part of this code is getting the SQL right. The SQL must select the
record in the table that is shown in the subform. Hopefully the syntax I gave
you is correct since I used the SQL editor to produce it. However, I needed
to change the table and field names so might have mucked it up. But it should
be fine!!!

Best of luck!

David

Thanks David.

OK I'll have a go at it, although it may be tomorrow before I get back to
you. I presume I should remove the two "DoCmd DoMenuItem" lines from the
existing Delete Record buton and replace them with your code, suitably
modified with my form/subform/field names?

Thanks again, JohnB

Hi John

I agree it seems a little complicated for a simple operation, but if the
code provided by the wizard doesn't work, it's worth a try!!

Have a go with this code. You will need to ensure that the subform has the
StudentID field (although it can be invisible). You will also need to change
the form, subform and field names in the SQL code I've shown below to
whatever you've called them.

Dim PhotoSQL As String
PhotoSQL = "SELECT tblImageFiles.[StudentID]
FROM tblImageFiles
WHERE (((tblImageFiles.[StudentID])=" & [Forms]![Name of Form]![Name of
subform].Form![Name of Control Containing StudentID field] & "));"

Dim PhotoToDelete As DAO.Recordset
Set PhotoToDelete = CurrentDb.OpenRecordset(PhotoSQL)
With PhotoToDelete
.Delete
.Close
End With

[Forms]![Name of Form]![Name of subform].Form.Requery

How's this working for you?

Cheers

David

Hi Davis. Thanks for the quick reply.

Hmmmm - seems a lot of work just to delete the current record shown in the
subform. Perhaps if I explain further. My subform shows a 'Show Photo' and
'Delete Record' buttons together with an Image Control. The idea is that
users use a different form/subform to set a link to images for each Student
record. This creates a record in tblImageFiles which is linked to
tblSECStudents by StudentID. Only one image record is created for each
student. Then they use this subform on the main students records to view and,
if necessary, delete the relevant photo record. The code used in the delete
command button is what is created when you use the wizard to create a delete
record command button.

Could your code be simplified , bearing in mind that the delete button will
always delete just the one, current, record being shown on the subform? Or is
there different code I could use in the buttons On Click event, to delete the
current record (and of course avoid the "No Current Record" message)?

Thanks again for the help. JohnB



:

Hi John

I don't know what is causing your problem. I would not have implemented this
in the same way - everyone does it the way they have learned!

In general, I woudn't use DoCmd.DoMenuItem to delete a record from a subform
- mainly because I hadn't heard of it! I would create a recordset containing
the record I wanted to delete, delete it, and then requery the subform to
reflect the change.

To create the recordset, first create a SQL string which would select only
the record you want to delete, based on its primary key, for example:

Dim SQLString As String
SQLString = "SELECT [Person ID] FROM People WHERE [Person ID] = " &
[Forms]![Name of Form]![Name of Subform].Form![Name of Control Holding Person
ID]

This is much easier to do by creating the query in the query editor, going
to SQL View, and copying and pasting the SQL out.

To create the recordset itself:

Dim RecordToDelete As DAO.Recordset
Set RecordToDelete = CurrentDb.OpenRecordset("SQLString")

You need to set a reference to the DAO library in order to do this. In the
VB Editor, go to Tools - References and tick Microsoft DAO 3.6 Object
Library. If you're not familiar with DAO, I recommend it as it makes
operations on records much easier.

Then delete the record:
With RecordToDelete
.Delete
.Close
End With

It's important to use the Close method, as otherwise the recordset is not
released and clogs up memory, or something (I'm not clear on the
technicalities).

Then requery the subform to reflect the change:

[Forms]![Name of Form]![Name of Subform].Form.Requery

I'm sorry I can't help you resolve the specific problem you have, but the
approach I have described above has always worked for me.

Others may well have better ways of doing this but it's the only way I know!

Let me know how you get on.

Cheers

David

:

Hi David. Sorry, but I did send you a holding reply yesterday - it seems to
have vanished.

Thanks for the suggestion but Iv'e just tried it without success. The code
I'm using is as follows, in the On Click event of the delete button. Just to
expand, this button is on a subform on form frmSECStudents and field
txtFirstName is the first field on frmSECStudents. When I use the command
button, I get the same results as before - the record is deleted and then I
get "No Current Record" and the focus goes to one of the fields on the
subform, as before.

By the way, I've discovered that I can't make all of the fields on the
subform invisable -if I do, the Delete button does nothing when clicked.
Unless I can find a different way to delete the record, I'll have to leave at
least one text field visable and make it tiny.

Anyway, here is the code I'm using. Am I doing this correctly? Thanks again,
JohnB

Private Sub cmdDeleteRecord_Click()

On Error GoTo Err_cmdDeleteRecord_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

[Forms]![frmSECStudents]![txtFirstName].SetFocus

Exit_cmdDeleteRecord_Click:

Exit Sub

Err_cmdDeleteRecord_Click:

MsgBox Err.Description

Resume Exit_cmdDeleteRecord_Click

End Sub


:

Hi John

I expect you're right, you probably can't leave the focus on an empty
subform. Setting focus is easy, use:

[Forms]![Name of Form]![Name of Control].SetFocus

Incidentally, an empty textbox can accept the focus, that won't be a problem.

Cheers

David

:

Hi.

I have a small form, being used as a subform, with a Delete Record command
button (created using the Wizard). I also have a couple of text boxes which I
will make invisable when I've finished the design. When the command button is
used, it will delete just one subform record per main form record. When I use
it, I always get a message 'No Current Record' once the delete has taken
place, maybe because the focus is going to one of the text boxes and there's
no record to display. Is there a way of stopping this message appearing?
Would setting the focus to one of the main form fields work and if so, how
would I do it? Im using Access 2000 on XP.

Thanks, JohnB

 

[Guest]

Success!

This was easier than I expected. I pasted your code into my On Click event
and removed the other code. Then I had the usual trouble getting the subform
and field names just right (subform names and subform control names on forms
won't catch me out again!). Then it worked. As a bonus, I can make the
StudentID field invisable, so a great result all round. Thank you very much
for your help, David. I don't know what I'd do without the effort of you and
others on this Discussion group.

Cheers, JohnB

Hi John

Yes, that's right. Replace your code with the code I suggested. The only
fiddly part of this code is getting the SQL right. The SQL must select the
record in the table that is shown in the subform. Hopefully the syntax I gave
you is correct since I used the SQL editor to produce it. However, I needed
to change the table and field names so might have mucked it up. But it should
be fine!!!

Best of luck!

David

Thanks David.

OK I'll have a go at it, although it may be tomorrow before I get back to
you. I presume I should remove the two "DoCmd DoMenuItem" lines from the
existing Delete Record buton and replace them with your code, suitably
modified with my form/subform/field names?

Thanks again, JohnB

Hi John

I agree it seems a little complicated for a simple operation, but if the
code provided by the wizard doesn't work, it's worth a try!!

Have a go with this code. You will need to ensure that the subform has the
StudentID field (although it can be invisible). You will also need to change
the form, subform and field names in the SQL code I've shown below to
whatever you've called them.

Dim PhotoSQL As String
PhotoSQL = "SELECT tblImageFiles.[StudentID]
FROM tblImageFiles
WHERE (((tblImageFiles.[StudentID])=" & [Forms]![Name of Form]![Name of
subform].Form![Name of Control Containing StudentID field] & "));"

Dim PhotoToDelete As DAO.Recordset
Set PhotoToDelete = CurrentDb.OpenRecordset(PhotoSQL)
With PhotoToDelete
.Delete
.Close
End With

[Forms]![Name of Form]![Name of subform].Form.Requery

How's this working for you?

Cheers

David


:

Hi Davis. Thanks for the quick reply.

Hmmmm - seems a lot of work just to delete the current record shown in the
subform. Perhaps if I explain further. My subform shows a 'Show Photo' and
'Delete Record' buttons together with an Image Control. The idea is that
users use a different form/subform to set a link to images for each Student
record. This creates a record in tblImageFiles which is linked to
tblSECStudents by StudentID. Only one image record is created for each
student. Then they use this subform on the main students records to view and,
if necessary, delete the relevant photo record. The code used in the delete
command button is what is created when you use the wizard to create a delete
record command button.

Could your code be simplified , bearing in mind that the delete button will
always delete just the one, current, record being shown on the subform? Or is
there different code I could use in the buttons On Click event, to delete the
current record (and of course avoid the "No Current Record" message)?

Thanks again for the help. JohnB



:

Hi John

I don't know what is causing your problem. I would not have implemented this
in the same way - everyone does it the way they have learned!

In general, I woudn't use DoCmd.DoMenuItem to delete a record from a subform
- mainly because I hadn't heard of it! I would create a recordset containing
the record I wanted to delete, delete it, and then requery the subform to
reflect the change.

To create the recordset, first create a SQL string which would select only
the record you want to delete, based on its primary key, for example:

Dim SQLString As String
SQLString = "SELECT [Person ID] FROM People WHERE [Person ID] = " &
[Forms]![Name of Form]![Name of Subform].Form![Name of Control Holding Person
ID]

This is much easier to do by creating the query in the query editor, going
to SQL View, and copying and pasting the SQL out.

To create the recordset itself:

Dim RecordToDelete As DAO.Recordset
Set RecordToDelete = CurrentDb.OpenRecordset("SQLString")

You need to set a reference to the DAO library in order to do this. In the
VB Editor, go to Tools - References and tick Microsoft DAO 3.6 Object
Library. If you're not familiar with DAO, I recommend it as it makes
operations on records much easier.

Then delete the record:
With RecordToDelete
.Delete
.Close
End With

It's important to use the Close method, as otherwise the recordset is not
released and clogs up memory, or something (I'm not clear on the
technicalities).

Then requery the subform to reflect the change:

[Forms]![Name of Form]![Name of Subform].Form.Requery

I'm sorry I can't help you resolve the specific problem you have, but the
approach I have described above has always worked for me.

Others may well have better ways of doing this but it's the only way I know!

Let me know how you get on.

Cheers

David

:

Hi David. Sorry, but I did send you a holding reply yesterday - it seems to
have vanished.

Thanks for the suggestion but Iv'e just tried it without success. The code
I'm using is as follows, in the On Click event of the delete button. Just to
expand, this button is on a subform on form frmSECStudents and field
txtFirstName is the first field on frmSECStudents. When I use the command
button, I get the same results as before - the record is deleted and then I
get "No Current Record" and the focus goes to one of the fields on the
subform, as before.

By the way, I've discovered that I can't make all of the fields on the
subform invisable -if I do, the Delete button does nothing when clicked.
Unless I can find a different way to delete the record, I'll have to leave at
least one text field visable and make it tiny.

Anyway, here is the code I'm using. Am I doing this correctly? Thanks again,
JohnB

Private Sub cmdDeleteRecord_Click()

On Error GoTo Err_cmdDeleteRecord_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

[Forms]![frmSECStudents]![txtFirstName].SetFocus

Exit_cmdDeleteRecord_Click:

Exit Sub

Err_cmdDeleteRecord_Click:

MsgBox Err.Description

Resume Exit_cmdDeleteRecord_Click

End Sub


:

Hi John

I expect you're right, you probably can't leave the focus on an empty
subform. Setting focus is easy, use:

[Forms]![Name of Form]![Name of Control].SetFocus

Incidentally, an empty textbox can accept the focus, that won't be a problem.

Cheers

David

:

Hi.

I have a small form, being used as a subform, with a Delete Record command
button (created using the Wizard). I also have a couple of text boxes which I
will make invisable when I've finished the design. When the command button is
used, it will delete just one subform record per main form record. When I use
it, I always get a message 'No Current Record' once the delete has taken
place, maybe because the focus is going to one of the text boxes and there's
no record to display. Is there a way of stopping this message appearing?
Would setting the focus to one of the main form fields work and if so, how
would I do it? Im using Access 2000 on XP.

Thanks, JohnB