Moving to another record on a form

  • Thread starter Thread starter Joan
  • Start date Start date
J

Joan

Hi,
I have a form that is opened with the OpenForm method without any WHERE
criteria argument. When I delete a record on a subform in the form, the
form then moves to the first record in the recordset. I would like to set a
control on the main form to the value of a control in the subform footer for
the original record. How do I get the form to show the original record
again so that I can set the value of the control on the main form? I've
tried the following code to no avail.



Private Sub cmdDeleteOthChrg_Click()
On Error GoTo Err_cmdDeleteOthChrg_Click
Dim Myrs As Object
Dim InvNum As Long
Dim iAns As Integer

InvNum = Forms!EditDeleteInvoice!txtInvoiceNumber

iAns = MsgBox("This will delete " & Me!cboChargeCode & ". OK?", vbYesNo)
If iAns = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
End If
'It is at this point that the form moves to the first record of the
recordset when I don't want it to.

DoCmd.FindRecord InvNum

With Me.Parent
!txtDebit = ![InvoiceDetailsSubEdit]![ExtendedPriceSum]
.Recalc
End With

Exit_cmdDeleteOthChrg_Click:
Exit Sub

Err_cmdDeleteOthChrg_Click:
MsgBox Err.Description
Resume Exit_cmdDeleteOthChrg_Click

End Sub

What am I doing wrong?

Joan
 
----- Joan wrote: ----

Hi
I have a form that is opened with the OpenForm method without any WHER
criteria argument. When I delete a record on a subform in the form, th
form then moves to the first record in the recordset. I would like to set
control on the main form to the value of a control in the subform footer fo
the original record. How do I get the form to show the original recor
again so that I can set the value of the control on the main form? I'v
tried the following code to no avail



Private Sub cmdDeleteOthChrg_Click(
On Error GoTo Err_cmdDeleteOthChrg_Clic
Dim Myrs As Objec
Dim InvNum As Lon
Dim iAns As Intege

InvNum = Forms!EditDeleteInvoice!txtInvoiceNumbe

iAns = MsgBox("This will delete " & Me!cboChargeCode & ". OK?", vbYesNo
If iAns = vbYes The
DoCmd.RunCommand acCmdDeleteRecor
End I
'It is at this point that the form moves to the first record of th
recordset when I don't want it to

DoCmd.FindRecord InvNu

With Me.Paren
!txtDebit = ![InvoiceDetailsSubEdit]![ExtendedPriceSum
.Recal
End Wit

Exit_cmdDeleteOthChrg_Click
Exit Su

Err_cmdDeleteOthChrg_Click
MsgBox Err.Descriptio
Resume Exit_cmdDeleteOthChrg_Clic

End Su

What am I doing wrong

Joa


Hi Joan, when you have deleted a record there's no going back to it - it doesn't exist any more. If you want to use a value in a record that is to be deleted, get the value first, then delete it

Luc
Jonatha
 
Jonathan,

This is what I tried to do setting variable InvNum =
Forms!EditDeleteInvoice!txtInvoiceNumber
at the beginning of my code. Then later after I have deleted the record in
the subform, I try to go back to the main record in the main form by
putting: DoCmd.FindRecord InvNum in my code. What am I doing wrong?
Why is DoCmd.FindRecord InvNum not working?

Joan

Jonathan Parminter said:
----- Joan wrote: -----

Hi,
I have a form that is opened with the OpenForm method without any WHERE
criteria argument. When I delete a record on a subform in the form, the
form then moves to the first record in the recordset. I would like to set a
control on the main form to the value of a control in the subform footer for
the original record. How do I get the form to show the original record
again so that I can set the value of the control on the main form? I've
tried the following code to no avail.



Private Sub cmdDeleteOthChrg_Click()
On Error GoTo Err_cmdDeleteOthChrg_Click
Dim Myrs As Object
Dim InvNum As Long
Dim iAns As Integer

InvNum = Forms!EditDeleteInvoice!txtInvoiceNumber

iAns = MsgBox("This will delete " & Me!cboChargeCode & ". OK?", vbYesNo)
If iAns = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
End If
'It is at this point that the form moves to the first record of the
recordset when I don't want it to.

DoCmd.FindRecord InvNum

With Me.Parent
!txtDebit = ![InvoiceDetailsSubEdit]![ExtendedPriceSum]
.Recalc
End With

Exit_cmdDeleteOthChrg_Click:
Exit Sub

Err_cmdDeleteOthChrg_Click:
MsgBox Err.Description
Resume Exit_cmdDeleteOthChrg_Click

End Sub

What am I doing wrong?

Joan


Hi Joan, when you have deleted a record there's no going back to it -
Click to expand...
it doesn't exist any more. If you want to use a value in a record that is to
be deleted, get the value first, then delete it.
 
-----Original Message-----
Jonathan,

This is what I tried to do setting variable InvNum =
Forms!EditDeleteInvoice!txtInvoiceNumber
at the beginning of my code. Then later after I have deleted the record in
the subform, I try to go back to the main record in the main form by
putting: DoCmd.FindRecord InvNum in my code. What am I doing wrong?
Why is DoCmd.FindRecord InvNum not working?

Joan
Click to expand...
Hi Joan, sorry that I missed that in your post. You only
have part of the criteria. That is, in what field
is 'Access' to look for InvNum and using which comparative
test (=, <, <=, <>, >=, >). Try

DoCmd.FindRecord "[FieldName]=" & InvNum

This assumes the value stored in InvNum is numeric. If the
value stored in the variable InvNum is a date then use

DoCmd.FindRecord "[FieldName]=#" & InvNum
& "#"

If the value stored in the variable InvNum is a text
string then combine with quotes using

DoCmd.FindRecord "[FieldName]=" & chr(34) & InvNum
& chr(34)

Luck
Jonathan
 
Jonathan,

I tried what you suggested, using DoCmd.FindRecord "[Invoice Number]=" &
InvNum in my code and it didn't work. So then I tried the following line
instead: DoCmd.FindRecord "Invoices.[Invoice Number]=" & InvNum. It
doesn't return to the main record in the main form either. The record
source for the EditDeleteInvoice is the Invoices table. What else should I
check or be doing?

Joan

Here is the code that I am running:
Private Sub cmdDeleteOthChrg_Click()
On Error GoTo Err_cmdDeleteOthChrg_Click
Dim InvNum As Long
Dim Myrs As Object
Dim iAns As Integer

InvNum = Forms!EditDeleteInvoice!txtInvoiceNumber

iAns = MsgBox("This will delete " & Me!cboChargeCode & ". OK?",
vbYesNo)
If iAns = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
End If

DoCmd.FindRecord "Invoices.[Invoice Number]=" & InvNum
Me.Recalc

With Me.Parent
!txtDebit = ![InvoiceDetailsSubEdit]![ExtendedPriceSum]
.Recalc
End With


Exit_cmdDeleteOthChrg_Click:
Exit Sub

Err_cmdDeleteOthChrg_Click:
MsgBox Err.Description
Resume Exit_cmdDeleteOthChrg_Click

End Sub


Jonathan Parminter said:
-----Original Message-----
Jonathan,

This is what I tried to do setting variable InvNum =
Forms!EditDeleteInvoice!txtInvoiceNumber
at the beginning of my code. Then later after I have deleted the record in
the subform, I try to go back to the main record in the main form by
putting: DoCmd.FindRecord InvNum in my code. What am I doing wrong?
Why is DoCmd.FindRecord InvNum not working?

Joan
Click to expand...
Hi Joan, sorry that I missed that in your post. You only
have part of the criteria. That is, in what field
is 'Access' to look for InvNum and using which comparative
test (=, <, <=, <>, >=, >). Try

DoCmd.FindRecord "[FieldName]=" & InvNum

This assumes the value stored in InvNum is numeric. If the
value stored in the variable InvNum is a date then use

DoCmd.FindRecord "[FieldName]=#" & InvNum
& "#"

If the value stored in the variable InvNum is a text
string then combine with quotes using

DoCmd.FindRecord "[FieldName]=" & chr(34) & InvNum
& chr(34)

Luck
Jonathan
Click to expand...
 
-----Original Message-----
Jonathan,

I tried what you suggested, using
Click to expand...
DoCmd.FindRecord "[Invoice Number]=" &
InvNum in my code and it didn't work. So then I tried the following line
instead: DoCmd.FindRecord "Invoices.[Invoice Number]=" & InvNum. It
doesn't return to the main record in the main form either. The record
source for the EditDeleteInvoice is the Invoices table. What else should I
check or be doing?

Joan
Click to expand...

Joan, where is the code running? That is, is the code
located on the subform. If 'yes' then the docmd.findrecord
line is looking to find a record in the subform
recordsource.

use the following as an example to search from the subform
for a main form record.

dim frm as form

set frm = forms("main form name")
frm.recordsetclone.findfirst [Invoice Number]=" & InvNum
frm.bookmark = frm.recordsetclone.bookmark

If the code is located on the main form, then the record
is deleted from the recordsource of the main form - could
this also be the record that you are searching for?

Luck
Jonathan
Here is the code that I am running:
Private Sub cmdDeleteOthChrg_Click()
On Error GoTo Err_cmdDeleteOthChrg_Click
Dim InvNum As Long
Dim Myrs As Object
Dim iAns As Integer

InvNum = Forms!EditDeleteInvoice!txtInvoiceNumber

iAns = MsgBox("This will delete " & Me! cboChargeCode & ". OK?",
vbYesNo)
If iAns = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
End If

DoCmd.FindRecord "Invoices.[Invoice Number]=" & InvNum
Me.Recalc

With Me.Parent
!txtDebit = ![InvoiceDetailsSubEdit]! [ExtendedPriceSum]
.Recalc
End With


Exit_cmdDeleteOthChrg_Click:
Exit Sub

Err_cmdDeleteOthChrg_Click:
MsgBox Err.Description
Resume Exit_cmdDeleteOthChrg_Click

End Sub


"Jonathan Parminter"
-----Original Message-----
Jonathan,

This is what I tried to do setting variable InvNum =
Forms!EditDeleteInvoice!txtInvoiceNumber
at the beginning of my code. Then later after I have deleted the record in
the subform, I try to go back to the main record in the main form by
putting: DoCmd.FindRecord InvNum in my code.
Click to expand...
What
am I doing wrong?
Why is DoCmd.FindRecord InvNum not working?

Joan
Click to expand...
Hi Joan, sorry that I missed that in your post. You only
have part of the criteria. That is, in what field
is 'Access' to look for InvNum and using which comparative
test (=, <, <=, <>, >=, >). Try

DoCmd.FindRecord "[FieldName]=" & InvNum

This assumes the value stored in InvNum is numeric. If the
value stored in the variable InvNum is a date then use

DoCmd.FindRecord "[FieldName]=#" & InvNum
& "#"

If the value stored in the variable InvNum is a text
string then combine with quotes using

DoCmd.FindRecord "[FieldName]=" & chr(34) & InvNum
& chr(34)

Luck
Jonathan
Click to expand...


.
Click to expand...
 
Jonathan,

I tried using your example to search from the subform for a main form
record. Below is the code.
However, I get a run-time error on the line: !txtDebit =
![InvoiceDetailsSubEdit]![ExtendedPriceSum]
The error states: "Update or CancelUpdate without AddNew or Edit." What
does this mean?

Private Sub cmdDeleteOthChrg_Click()
On Error GoTo Err_cmdDeleteOthChrg_Click
Dim InvNum As Long
Dim Myrs As Object
Dim iAns As Integer
Dim frm As Form

Set frm = Forms("EditDeleteInvoice")

InvNum = Forms!EditDeleteInvoice!txtInvoiceNumber
iAns = MsgBox("This will delete " & Me!cboChargeCode & ". OK?", vbYesNo)

If iAns = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
End If
frm.RecordsetClone.FindFirst [Invoice Number] = " & InvNum"
frm.Bookmark = frm.RecordsetClone.Bookmark

With Me.Parent
!txtDebit = ![InvoiceDetailsSubEdit]![ExtendedPriceSum]
.Recalc
End With


Exit_cmdDeleteOthChrg_Click:
Exit Sub

Err_cmdDeleteOthChrg_Click:
MsgBox Err.Description
Resume Exit_cmdDeleteOthChrg_Click

End Sub

Joan


Jonathan Parminter said:
-----Original Message-----
Jonathan,

I tried what you suggested, using
Click to expand...
DoCmd.FindRecord "[Invoice Number]=" &
InvNum in my code and it didn't work. So then I tried the following line
instead: DoCmd.FindRecord "Invoices.[Invoice Number]=" & InvNum. It
doesn't return to the main record in the main form either. The record
source for the EditDeleteInvoice is the Invoices table. What else should I
check or be doing?

Joan
Click to expand...

Joan, where is the code running? That is, is the code
located on the subform. If 'yes' then the docmd.findrecord
line is looking to find a record in the subform
recordsource.

use the following as an example to search from the subform
for a main form record.

dim frm as form

set frm = forms("main form name")
frm.recordsetclone.findfirst [Invoice Number]=" & InvNum
frm.bookmark = frm.recordsetclone.bookmark

If the code is located on the main form, then the record
is deleted from the recordsource of the main form - could
this also be the record that you are searching for?

Luck
Jonathan
Here is the code that I am running:
Private Sub cmdDeleteOthChrg_Click()
On Error GoTo Err_cmdDeleteOthChrg_Click
Dim InvNum As Long
Dim Myrs As Object
Dim iAns As Integer

InvNum = Forms!EditDeleteInvoice!txtInvoiceNumber

iAns = MsgBox("This will delete " & Me! cboChargeCode & ". OK?",
vbYesNo)
If iAns = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
End If

DoCmd.FindRecord "Invoices.[Invoice Number]=" & InvNum
Me.Recalc

With Me.Parent
!txtDebit = ![InvoiceDetailsSubEdit]! [ExtendedPriceSum]
.Recalc
End With


Exit_cmdDeleteOthChrg_Click:
Exit Sub

Err_cmdDeleteOthChrg_Click:
MsgBox Err.Description
Resume Exit_cmdDeleteOthChrg_Click

End Sub


"Jonathan Parminter"
-----Original Message-----
Jonathan,

This is what I tried to do setting variable InvNum =
Forms!EditDeleteInvoice!txtInvoiceNumber
at the beginning of my code. Then later after I have
deleted the record in
the subform, I try to go back to the main record in the
main form by
putting: DoCmd.FindRecord InvNum in my code. What
am I doing wrong?
Why is DoCmd.FindRecord InvNum not working?

Joan

Hi Joan, sorry that I missed that in your post. You only
have part of the criteria. That is, in what field
is 'Access' to look for InvNum and using which comparative
test (=, <, <=, <>, >=, >). Try

DoCmd.FindRecord "[FieldName]=" & InvNum

This assumes the value stored in InvNum is numeric. If the
value stored in the variable InvNum is a date then use

DoCmd.FindRecord "[FieldName]=#" & InvNum
& "#"

If the value stored in the variable InvNum is a text
string then combine with quotes using

DoCmd.FindRecord "[FieldName]=" & chr(34) & InvNum
& chr(34)

Luck
Jonathan
Click to expand...


.
Click to expand...
Click to expand...
 
Jonathan,

Please read my other post first. I got the code to work without the
run-time error by using the .Edit method on frm.Recordset before trying to
set the value of Forms!EditDeleteInvoice!txtDebit. See below:
.......
frm.RecordsetClone.FindFirst "[Invoice Number] = " & InvNum
frm.Bookmark = frm.RecordsetClone.Bookmark
With frm.Recordset
.Edit
End With
With Me.Parent
!txtDebit = ![InvoiceDetailsSubEdit]![ExtendedPriceSum]
.Recalc
End With
.........

Thanks so much for all of your help.

Joan

Jonathan Parminter said:
-----Original Message-----
Jonathan,

I tried what you suggested, using
Click to expand...
DoCmd.FindRecord "[Invoice Number]=" &
InvNum in my code and it didn't work. So then I tried the following line
instead: DoCmd.FindRecord "Invoices.[Invoice Number]=" & InvNum. It
doesn't return to the main record in the main form either. The record
source for the EditDeleteInvoice is the Invoices table. What else should I
check or be doing?

Joan
Click to expand...

Joan, where is the code running? That is, is the code
located on the subform. If 'yes' then the docmd.findrecord
line is looking to find a record in the subform
recordsource.

use the following as an example to search from the subform
for a main form record.

dim frm as form

set frm = forms("main form name")
frm.recordsetclone.findfirst [Invoice Number]=" & InvNum
frm.bookmark = frm.recordsetclone.bookmark

If the code is located on the main form, then the record
is deleted from the recordsource of the main form - could
this also be the record that you are searching for?

Luck
Jonathan
Here is the code that I am running:
Private Sub cmdDeleteOthChrg_Click()
On Error GoTo Err_cmdDeleteOthChrg_Click
Dim InvNum As Long
Dim Myrs As Object
Dim iAns As Integer

InvNum = Forms!EditDeleteInvoice!txtInvoiceNumber

iAns = MsgBox("This will delete " & Me! cboChargeCode & ". OK?",
vbYesNo)
If iAns = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
End If

DoCmd.FindRecord "Invoices.[Invoice Number]=" & InvNum
Me.Recalc

With Me.Parent
!txtDebit = ![InvoiceDetailsSubEdit]! [ExtendedPriceSum]
.Recalc
End With


Exit_cmdDeleteOthChrg_Click:
Exit Sub

Err_cmdDeleteOthChrg_Click:
MsgBox Err.Description
Resume Exit_cmdDeleteOthChrg_Click

End Sub


"Jonathan Parminter"
-----Original Message-----
Jonathan,

This is what I tried to do setting variable InvNum =
Forms!EditDeleteInvoice!txtInvoiceNumber
at the beginning of my code. Then later after I have
deleted the record in
the subform, I try to go back to the main record in the
main form by
putting: DoCmd.FindRecord InvNum in my code. What
am I doing wrong?
Why is DoCmd.FindRecord InvNum not working?

Joan

Hi Joan, sorry that I missed that in your post. You only
have part of the criteria. That is, in what field
is 'Access' to look for InvNum and using which comparative
test (=, <, <=, <>, >=, >). Try

DoCmd.FindRecord "[FieldName]=" & InvNum

This assumes the value stored in InvNum is numeric. If the
value stored in the variable InvNum is a date then use

DoCmd.FindRecord "[FieldName]=#" & InvNum
& "#"

If the value stored in the variable InvNum is a text
string then combine with quotes using

DoCmd.FindRecord "[FieldName]=" & chr(34) & InvNum
& chr(34)

Luck
Jonathan
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top