RecordsetClone

A

Amour

Hi I am new to Access.

I have a Single Form (frmAtt) that when I select a record I than can click
on a button to open another Continuous form (frmTrv) and it will select that
records information from frmAtt.

Example:
Opened form(frmatt) = Opening form(frmTrv)
frmatt.xyz = A frmtrv.xyz = A
frmatt.zzz = B frmtrv.zzz = B

This part works fine...

But when I close frmtrv this is what happens:
Opened form(frmatt) closed form(frmTrv)
frmtrv.xyz = A frmatt.xyz = someother record
frmtrv.zzz = B frmatt.zzz = B

THIS IS THE PROBLEM!



When I delete the record from frmtrv:
Opened form(frmatt) closed form(frmTrv)
frmtrv.xyz = A frmatt.xyz = A
frmtrv.zzz = B frmatt.zzz = B

This works fine!

I want the frmtrv to close with the current record on the previous opened
form showing.

Here is the code behind the close button on the frmtrv:

Private Sub btnCloseForm_Click()
On Error GoTo Err_btnCloseForm_Click

lngMyKey = Me.txtSSN
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[ssn] = " & lngMyKey
MsgBox Me!SSN
If rst.NoMatch Then
MsgBox "Error Find Record " & lngMyKey
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
DoCmd.Close
Exit_btnCloseForm_Click:
Exit Sub

Err_btnCloseForm_Click:
MsgBox Error$
Resume Exit_btnCloseForm_Click

End Sub


MsgBox Me!SSN = Ex: 000-00-6148
lngMyKey = Ex: 000-00-6148

Yet I get the msg: Error Find Record 00-00-6148

meaning that: rst.NoMatch is true

What am I doing wrong SSN does equal IngMyKey

Please help and thank you!
 
M

Maurice

Reading the post I think the SSN is a text value. Try rewriting the following
in your code:

rst.FindFirst "[ssn] = '" & lngMyKey & "'"

Being: single quote after the equal sign at the end Double quote, Single
Quote, Double quote.

Maurice
 
A

Amour

Thank you for your response. I tried what you said and it passed ok, it is
not doing what I want it to do.

What I want is for when the 2nd form (frmTrv) when closing. I want it to go
back to the same record and I thought that is what I was doing by
recordsetclone...

Please help me and thank you!

Maurice said:
Reading the post I think the SSN is a text value. Try rewriting the following
in your code:

rst.FindFirst "[ssn] = '" & lngMyKey & "'"

Being: single quote after the equal sign at the end Double quote, Single
Quote, Double quote.

Maurice

Amour said:
Hi I am new to Access.

I have a Single Form (frmAtt) that when I select a record I than can click
on a button to open another Continuous form (frmTrv) and it will select that
records information from frmAtt.

Example:
Opened form(frmatt) = Opening form(frmTrv)
frmatt.xyz = A frmtrv.xyz = A
frmatt.zzz = B frmtrv.zzz = B

This part works fine...

But when I close frmtrv this is what happens:
Opened form(frmatt) closed form(frmTrv)
frmtrv.xyz = A frmatt.xyz = someother record
frmtrv.zzz = B frmatt.zzz = B

THIS IS THE PROBLEM!



When I delete the record from frmtrv:
Opened form(frmatt) closed form(frmTrv)
frmtrv.xyz = A frmatt.xyz = A
frmtrv.zzz = B frmatt.zzz = B

This works fine!

I want the frmtrv to close with the current record on the previous opened
form showing.

Here is the code behind the close button on the frmtrv:

Private Sub btnCloseForm_Click()
On Error GoTo Err_btnCloseForm_Click

lngMyKey = Me.txtSSN
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[ssn] = " & lngMyKey
MsgBox Me!SSN
If rst.NoMatch Then
MsgBox "Error Find Record " & lngMyKey
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
DoCmd.Close
Exit_btnCloseForm_Click:
Exit Sub

Err_btnCloseForm_Click:
MsgBox Error$
Resume Exit_btnCloseForm_Click

End Sub


MsgBox Me!SSN = Ex: 000-00-6148
lngMyKey = Ex: 000-00-6148

Yet I get the msg: Error Find Record 00-00-6148

meaning that: rst.NoMatch is true

What am I doing wrong SSN does equal IngMyKey

Please help and thank you!
Click to expand...
Click to expand...
 
C

Carl Rapson

The code you posted is being executed in frmTrv, which means it is
positioning frmTrv just before it closes. Nothing is affecting the record in
frmAtt. What you need to do is something similar in frmAtt right after the
point where frmTrv closes.

Carl Rapson

Amour said:
Thank you for your response. I tried what you said and it passed ok, it
is
not doing what I want it to do.

What I want is for when the 2nd form (frmTrv) when closing. I want it to
go
back to the same record and I thought that is what I was doing by
recordsetclone...

Please help me and thank you!

Maurice said:
Reading the post I think the SSN is a text value. Try rewriting the
following
in your code:

rst.FindFirst "[ssn] = '" & lngMyKey & "'"

Being: single quote after the equal sign at the end Double quote, Single
Quote, Double quote.

Maurice

Amour said:
Hi I am new to Access.

I have a Single Form (frmAtt) that when I select a record I than can
click
on a button to open another Continuous form (frmTrv) and it will select
that
records information from frmAtt.

Example:
Opened form(frmatt) = Opening form(frmTrv)
frmatt.xyz = A frmtrv.xyz = A
frmatt.zzz = B frmtrv.zzz = B

This part works fine...

But when I close frmtrv this is what happens:
Opened form(frmatt) closed form(frmTrv)
frmtrv.xyz = A frmatt.xyz = someother record
frmtrv.zzz = B frmatt.zzz = B

THIS IS THE PROBLEM!



When I delete the record from frmtrv:
Opened form(frmatt) closed form(frmTrv)
frmtrv.xyz = A frmatt.xyz = A
frmtrv.zzz = B frmatt.zzz = B

This works fine!

I want the frmtrv to close with the current record on the previous
opened
form showing.

Here is the code behind the close button on the frmtrv:

Private Sub btnCloseForm_Click()
On Error GoTo Err_btnCloseForm_Click

lngMyKey = Me.txtSSN
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[ssn] = " & lngMyKey
MsgBox Me!SSN
If rst.NoMatch Then
MsgBox "Error Find Record " & lngMyKey
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
DoCmd.Close
Exit_btnCloseForm_Click:
Exit Sub

Err_btnCloseForm_Click:
MsgBox Error$
Resume Exit_btnCloseForm_Click

End Sub


MsgBox Me!SSN = Ex: 000-00-6148
lngMyKey = Ex: 000-00-6148

Yet I get the msg: Error Find Record 00-00-6148

meaning that: rst.NoMatch is true

What am I doing wrong SSN does equal IngMyKey

Please help and thank you!
Click to expand...
Click to expand...
Click to expand...
 
A

Amour

Thank you for your response. I was wondering the same thing about this code
being executed in frmTrv. But did you notice one section in the posting were
I mention when the user presses the delete button witnin frmTrv and when it
closes it refers back to the current record on frmAtt:
When I delete the record from frmtrv:

Opened form(frmatt) closed form(frmTrv)
frmtrv.xyz = A frmatt.xyz = A
frmtrv.zzz = B frmatt.zzz = B

This works fine!

This is the code behind the delete button:

Private Sub btnDelete_Click()
On Error GoTo Err_btnDelete_Click
Dim dbs As Database
Dim myrs As Recordset

Set dbs = CurrentDb()

Set myrs = Me.RecordsetClone
If myrs.RecordCount > 0 Then
delRec = MsgBox("You are about to delete 1 record. " & Chr(13) &
Chr(10) & Chr(13) & Chr(10) & "If you click Yes, you won't be able to undo
this Delete Operation. " & Chr(13) & Chr(10) & "Delete Current Record?",
vbExclamation + vbYesNo, "Delete Record")
If delRec = 6 Then
xmeet_num = Me!MEET_NUM
xssn = Me!SSN
strSql = "delete * from tblTravel where meet_num = '" &
xmeet_num & "' and ssn = '" & xssn & "'"
dbs.Execute strSql

Forms!frmAttendanceMeeting!EST_TRVL = 500
Forms!frmAttendanceMeeting!ACT_TRVL = 0
End If
End If
DoCmd.Close
Exit_btnDelete_Click:
Exit Sub

Err_btnDelete_Click:
MsgBox Error$
Resume Exit_btnDelete_Click

End Sub



Please help and thank you!





Carl Rapson said:
The code you posted is being executed in frmTrv, which means it is
positioning frmTrv just before it closes. Nothing is affecting the record in
frmAtt. What you need to do is something similar in frmAtt right after the
point where frmTrv closes.

Carl Rapson

Amour said:
Thank you for your response. I tried what you said and it passed ok, it
is
not doing what I want it to do.

What I want is for when the 2nd form (frmTrv) when closing. I want it to
go
back to the same record and I thought that is what I was doing by
recordsetclone...

Please help me and thank you!

Maurice said:
Reading the post I think the SSN is a text value. Try rewriting the
following
in your code:

rst.FindFirst "[ssn] = '" & lngMyKey & "'"

Being: single quote after the equal sign at the end Double quote, Single
Quote, Double quote.

Maurice

:

Hi I am new to Access.

I have a Single Form (frmAtt) that when I select a record I than can
click
on a button to open another Continuous form (frmTrv) and it will select
that
records information from frmAtt.

Example:
Opened form(frmatt) = Opening form(frmTrv)
frmatt.xyz = A frmtrv.xyz = A
frmatt.zzz = B frmtrv.zzz = B

This part works fine...

But when I close frmtrv this is what happens:
Opened form(frmatt) closed form(frmTrv)
frmtrv.xyz = A frmatt.xyz = someother record
frmtrv.zzz = B frmatt.zzz = B

THIS IS THE PROBLEM!



When I delete the record from frmtrv:
Opened form(frmatt) closed form(frmTrv)
frmtrv.xyz = A frmatt.xyz = A
frmtrv.zzz = B frmatt.zzz = B

This works fine!

I want the frmtrv to close with the current record on the previous
opened
form showing.

Here is the code behind the close button on the frmtrv:

Private Sub btnCloseForm_Click()
On Error GoTo Err_btnCloseForm_Click

lngMyKey = Me.txtSSN
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[ssn] = " & lngMyKey
MsgBox Me!SSN
If rst.NoMatch Then
MsgBox "Error Find Record " & lngMyKey
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
DoCmd.Close
Exit_btnCloseForm_Click:
Exit Sub

Err_btnCloseForm_Click:
MsgBox Error$
Resume Exit_btnCloseForm_Click

End Sub


MsgBox Me!SSN = Ex: 000-00-6148
lngMyKey = Ex: 000-00-6148

Yet I get the msg: Error Find Record 00-00-6148

meaning that: rst.NoMatch is true

What am I doing wrong SSN does equal IngMyKey

Please help and thank you!
Click to expand...
Click to expand...
Click to expand...
 
C

Carl Rapson

I can't explain why your record positioning works in one place but not
another. All I'm saying is that the record positioning code you posted
earlier is executing in frmTrv, so there is no way it can affect the record
displayed in frmAtt. Execute the same (or similar) code in frmAtt just after
the point where frmTrv closes, and you should be able to display the record
you want.

Carl Rapson

Amour said:
Thank you for your response. I was wondering the same thing about this
code
being executed in frmTrv. But did you notice one section in the posting
were
I mention when the user presses the delete button witnin frmTrv and when
it
closes it refers back to the current record on frmAtt:
When I delete the record from frmtrv:

Opened form(frmatt) closed form(frmTrv)
frmtrv.xyz = A frmatt.xyz = A
frmtrv.zzz = B frmatt.zzz = B

This works fine!

This is the code behind the delete button:

Private Sub btnDelete_Click()
On Error GoTo Err_btnDelete_Click
Dim dbs As Database
Dim myrs As Recordset

Set dbs = CurrentDb()

Set myrs = Me.RecordsetClone
If myrs.RecordCount > 0 Then
delRec = MsgBox("You are about to delete 1 record. " & Chr(13) &
Chr(10) & Chr(13) & Chr(10) & "If you click Yes, you won't be able to undo
this Delete Operation. " & Chr(13) & Chr(10) & "Delete Current Record?",
vbExclamation + vbYesNo, "Delete Record")
If delRec = 6 Then
xmeet_num = Me!MEET_NUM
xssn = Me!SSN
strSql = "delete * from tblTravel where meet_num = '" &
xmeet_num & "' and ssn = '" & xssn & "'"
dbs.Execute strSql

Forms!frmAttendanceMeeting!EST_TRVL = 500
Forms!frmAttendanceMeeting!ACT_TRVL = 0
End If
End If
DoCmd.Close
Exit_btnDelete_Click:
Exit Sub

Err_btnDelete_Click:
MsgBox Error$
Resume Exit_btnDelete_Click

End Sub



Please help and thank you!
Click to expand...

<snipped>
 
A

Amour

Thank you for your help. I found what the problem was and fixed it.

What I was doing was requery the form frmAtt on closing frmTrv

What I did to correct it was to just requery one of the fields on frmAtt
when closing frmTrv.

Thank You!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Keeping current record 0
Linking forms 2
Record control 0
type mismatch error in macro in excel 0
Message appears twice. 4
Problems with Recordset.clone Combo Box 2
A couple of (hopefully) quick questions 4
RecordSetClone Error 3

Top