Return to viewed record after requery

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

Hi all. Please help!

My form allows add, delete, edit but not data entry. I use this form to
view and edit vacations and I wish to also be able to add a new vacation(s)
via a command button.

So...I call up the form, view existing vacations and click the button (data
entry = true) and make my entry ok.

Now, after adding record(s), I want to view all the records for this
employee with the new addition included.

My code:
Dim frm As Form
Set frm = Forms![3frmCaregiverVacations]

If Me.Dirty = True Then Me.Dirty = False

PostTest

Me.DataEntry = False

frm.Requery

With frm.RecordsetClone
.FindFirst "[CGID] = """ & Me![CGID] & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With

Set frm = Nothing

This code returns to viewing, displaying all records for all employees. I'm
guessing it is my .FindFirst line and the ID.

If I close the form and reopen it, all is good. The user may wish to add
several vacations for this one employee.

Where is my error in code? Logic? CGID is text type.

Thanks so much,
 
Hi all. Please help!

My form allows add, delete, edit but not data entry. I use this form to
view and edit vacations and I wish to also be able to add a new vacation(s)
via a command button.

So...I call up the form, view existing vacations and click the button (data
entry = true) and make my entry ok.

Now, after adding record(s), I want to view all the records for this
employee with the new addition included.

My code:
Dim frm As Form
Set frm = Forms![3frmCaregiverVacations]

If Me.Dirty = True Then Me.Dirty = False

PostTest

Me.DataEntry = False

frm.Requery

With frm.RecordsetClone
.FindFirst "[CGID] = """ & Me![CGID] & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With

Set frm = Nothing

This code returns to viewing, displaying all records for all employees. I'm
guessing it is my .FindFirst line and the ID.

If I close the form and reopen it, all is good. The user may wish to add
several vacations for this one employee.

Where is my error in code? Logic? CGID is text type.

Thanks so much,
Click to expand...

I don't quite understand your form setup.

DataEntry ONLY allows new record entry without the ability to see or
edit existing ones.

If your form is set to Allow Additions, Allow Deletions, and Allow
Edits, that's all you need to allow the entry of new records or the
editing or deleting of existing records.
Set the Form's DataEntry property to NO.

Then there is no need to refresh or requery the data. It's there as
soon as you enter it.
 
The code I provided previously is the attempt to return the form to viewing
all employee vacation records -- attached to a Post button. The code
attached to the Add button is:
Me.DataEntry = True
DoCmd.GoToRecord , , acNewRec
Me.Form!CGID = Forms![3frmCGDetails1].Form!CG1

Without setting to DE=true and providing CGID value I cannot add the record.
The form is set to add, delete, edit, each = yes, and data entry = no, It is
a continuous form. I do not display the navigation buttons.

The user's intended flow:
1. open the form (as set up)
2. either edit an employee's vacation record or add a new record (editing
existing records are not at issue).
3. if adding, click the Add button, the form opens to allow entry.
4. post the record, but not close the form. The code I gave earlier is
attached to a Post button and is to return the form to that employees records
including new one.
5. if the user is satisfied that the new record -- as well as all previous
records -- is correct, they close the form.

--
Chrissy


fredg said:
Hi all. Please help!

My form allows add, delete, edit but not data entry. I use this form to
view and edit vacations and I wish to also be able to add a new vacation(s)
via a command button.

So...I call up the form, view existing vacations and click the button (data
entry = true) and make my entry ok.

Now, after adding record(s), I want to view all the records for this
employee with the new addition included.

My code:
Dim frm As Form
Set frm = Forms![3frmCaregiverVacations]

If Me.Dirty = True Then Me.Dirty = False

PostTest

Me.DataEntry = False

frm.Requery

With frm.RecordsetClone
.FindFirst "[CGID] = """ & Me![CGID] & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With

Set frm = Nothing

This code returns to viewing, displaying all records for all employees. I'm
guessing it is my .FindFirst line and the ID.

If I close the form and reopen it, all is good. The user may wish to add
several vacations for this one employee.

Where is my error in code? Logic? CGID is text type.

Thanks so much,
Click to expand...

I don't quite understand your form setup.

DataEntry ONLY allows new record entry without the ability to see or
edit existing ones.

If your form is set to Allow Additions, Allow Deletions, and Allow
Edits, that's all you need to allow the entry of new records or the
editing or deleting of existing records.
Set the Form's DataEntry property to NO.

Then there is no need to refresh or requery the data. It's there as
soon as you enter it.
Click to expand...
 
Chris,

I would generally set this up as a form/sub-form setup, where the employees
vacation history show up in the subform. I'd initially set the subforms
AllowAdditions property to false, and would put the add button in the
subforms footer. When they click Add, change the allow additions to True,
disable the Add button, and go to a new record. In the RecordsBeforeUpdate
event event, check to see if it is a newrecord and if so, reenable the Add
button. This way, you don't need to requery the subform since the data was
actually entered directly into it.

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Chris said:
The code I provided previously is the attempt to return the form to viewing
all employee vacation records -- attached to a Post button. The code
attached to the Add button is:
Me.DataEntry = True
DoCmd.GoToRecord , , acNewRec
Me.Form!CGID = Forms![3frmCGDetails1].Form!CG1

Without setting to DE=true and providing CGID value I cannot add the record.
The form is set to add, delete, edit, each = yes, and data entry = no, It is
a continuous form. I do not display the navigation buttons.

The user's intended flow:
1. open the form (as set up)
2. either edit an employee's vacation record or add a new record (editing
existing records are not at issue).
3. if adding, click the Add button, the form opens to allow entry.
4. post the record, but not close the form. The code I gave earlier is
attached to a Post button and is to return the form to that employees records
including new one.
5. if the user is satisfied that the new record -- as well as all previous
records -- is correct, they close the form.

--
Chrissy


fredg said:
Hi all. Please help!

My form allows add, delete, edit but not data entry. I use this form to
view and edit vacations and I wish to also be able to add a new vacation(s)
via a command button.

So...I call up the form, view existing vacations and click the button (data
entry = true) and make my entry ok.

Now, after adding record(s), I want to view all the records for this
employee with the new addition included.

My code:
Dim frm As Form
Set frm = Forms![3frmCaregiverVacations]

If Me.Dirty = True Then Me.Dirty = False

PostTest

Me.DataEntry = False

frm.Requery

With frm.RecordsetClone
.FindFirst "[CGID] = """ & Me![CGID] & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With

Set frm = Nothing

This code returns to viewing, displaying all records for all employees. I'm
guessing it is my .FindFirst line and the ID.

If I close the form and reopen it, all is good. The user may wish to add
several vacations for this one employee.

Where is my error in code? Logic? CGID is text type.

Thanks so much,
Click to expand...

I don't quite understand your form setup.

DataEntry ONLY allows new record entry without the ability to see or
edit existing ones.

If your form is set to Allow Additions, Allow Deletions, and Allow
Edits, that's all you need to allow the entry of new records or the
editing or deleting of existing records.
Set the Form's DataEntry property to NO.

Then there is no need to refresh or requery the data. It's there as
soon as you enter it.
Click to expand...
Click to expand...
 
To answer the actual question, the problem is you have to save the value in
the text box before you do the requery. As written, it will return to the
first record in the recordset and then do the find and navigate to it again.

Dim strCurrCGID As String

strCurrCGID = Me![CGID]

frm.Requery

With frm.RecordsetClone
.FindFirst "[CGID] = """ & strCGID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With
 
Thank you Dave. That is working for me except in one case -- new record.

When I open the Vacations form (viewing/editing) I test for records for this
employee. If a record exists I use the Add button and successfully add an
additional vacation record. When no vacation record exists, I go right to the
entry form.

At this point the viewing form is not open, so when I go to close the entry
from with the new (and first) record for this employee it fails at "Set frm =
Forms![3frmCaregiverVacations]" in the closing code.

Since it cannot find a form I have skipped (in the case of no record yet), I
tried to open the view/edit form if there was no record. I cannot make it
work.

Can you see error in code or offer a method to (1) ensure that I can
edit/view existing records and add a new one (when a record already exists)
and (2) add a new record when none yet exists?

Have I provided enough information?

Your guidance is greatly appreciated.


Calling code -- view/edit form or (if no record) entry form:
Dim stdocName, stdocName1, stLinkCriteria, msg, style, title, response As
String
stdocName = "3frmCaregiverVacations"
stdocName1 = "3frmDECaregiverVacations"
stLinkCriteria = "[CGID]=" & "'" & Me![CG1] & "'"
title = "No Record!"
style = vbYesNo
msg = "Caregiver has no vacations scheduled." & vbCrLf & _
" Would you like to add one now?"
'tests for existing record
If HasDataVacation(CG1) Then
'has a record already
DoCmd.OpenForm stdocName, , , stLinkCriteria
Else
'no record currently
response = MsgBox(msg, style, title)
If response = vbYes Then
DoCmd.OpenForm stdocName1, , , , acFormAdd
Forms![3frmDECaregiverVacations].Form![CGID] = Me.CG1
Forms![3frmDECaregiverVacations].Form!txtStartDate = Null
Forms![3frmDECaregiverVacations].Form!txtEndDate = Null
Else
Exit Sub
End If
End If

Closing code -- entry form:
Dim frm As Form
Dim strCurrCGID As String
Set frm = Forms![3frmCaregiverVacations]
strCurrCGID = Me![CGID]
PostTest
frm.Requery
With frm.RecordsetClone
.FindFirst "[CGID] = """ & strCurrCGID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With
If Me.Dirty Then Me.Dirty = False
Set frm = Nothing
DoCmd.close








--
Chrissy


Klatuu said:
To answer the actual question, the problem is you have to save the value in
the text box before you do the requery. As written, it will return to the
first record in the recordset and then do the find and navigate to it again.

Dim strCurrCGID As String

strCurrCGID = Me![CGID]

frm.Requery

With frm.RecordsetClone
.FindFirst "[CGID] = """ & strCGID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With

--
Dave Hargis, Microsoft Access MVP


Chris said:
Hi all. Please help!

My form allows add, delete, edit but not data entry. I use this form to
view and edit vacations and I wish to also be able to add a new vacation(s)
via a command button.

So...I call up the form, view existing vacations and click the button (data
entry = true) and make my entry ok.

Now, after adding record(s), I want to view all the records for this
employee with the new addition included.

My code:
Dim frm As Form
Set frm = Forms![3frmCaregiverVacations]

If Me.Dirty = True Then Me.Dirty = False

PostTest

Me.DataEntry = False

frm.Requery

With frm.RecordsetClone
.FindFirst "[CGID] = """ & Me![CGID] & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With

Set frm = Nothing

This code returns to viewing, displaying all records for all employees. I'm
guessing it is my .FindFirst line and the ID.

If I close the form and reopen it, all is good. The user may wish to add
several vacations for this one employee.

Where is my error in code? Logic? CGID is text type.

Thanks so much,
Click to expand...
Click to expand...
 
Sorry, Chris, but I could not tell for sure where you were trying to
reference the form that wasn't there, but there is a way to see if a form is
loaded or not:

If CurrentProject.Allforms("FormNameHere").IsLoaded Then
'The Form is loaded

Now, you can also check to see what view it is in, but in a normal
application, it should not be in design view anyway, so that shouldn't be an
issue.
--
Dave Hargis, Microsoft Access MVP


Chris said:
Thank you Dave. That is working for me except in one case -- new record.

When I open the Vacations form (viewing/editing) I test for records for this
employee. If a record exists I use the Add button and successfully add an
additional vacation record. When no vacation record exists, I go right to the
entry form.

At this point the viewing form is not open, so when I go to close the entry
from with the new (and first) record for this employee it fails at "Set frm =
Forms![3frmCaregiverVacations]" in the closing code.

Since it cannot find a form I have skipped (in the case of no record yet), I
tried to open the view/edit form if there was no record. I cannot make it
work.

Can you see error in code or offer a method to (1) ensure that I can
edit/view existing records and add a new one (when a record already exists)
and (2) add a new record when none yet exists?

Have I provided enough information?

Your guidance is greatly appreciated.


Calling code -- view/edit form or (if no record) entry form:
Dim stdocName, stdocName1, stLinkCriteria, msg, style, title, response As
String
stdocName = "3frmCaregiverVacations"
stdocName1 = "3frmDECaregiverVacations"
stLinkCriteria = "[CGID]=" & "'" & Me![CG1] & "'"
title = "No Record!"
style = vbYesNo
msg = "Caregiver has no vacations scheduled." & vbCrLf & _
" Would you like to add one now?"
'tests for existing record
If HasDataVacation(CG1) Then
'has a record already
DoCmd.OpenForm stdocName, , , stLinkCriteria
Else
'no record currently
response = MsgBox(msg, style, title)
If response = vbYes Then
DoCmd.OpenForm stdocName1, , , , acFormAdd
Forms![3frmDECaregiverVacations].Form![CGID] = Me.CG1
Forms![3frmDECaregiverVacations].Form!txtStartDate = Null
Forms![3frmDECaregiverVacations].Form!txtEndDate = Null
Else
Exit Sub
End If
End If

Closing code -- entry form:
Dim frm As Form
Dim strCurrCGID As String
Set frm = Forms![3frmCaregiverVacations]
strCurrCGID = Me![CGID]
PostTest
frm.Requery
With frm.RecordsetClone
.FindFirst "[CGID] = """ & strCurrCGID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With
If Me.Dirty Then Me.Dirty = False
Set frm = Nothing
DoCmd.close








--
Chrissy


Klatuu said:
To answer the actual question, the problem is you have to save the value in
the text box before you do the requery. As written, it will return to the
first record in the recordset and then do the find and navigate to it again.

Dim strCurrCGID As String

strCurrCGID = Me![CGID]

frm.Requery

With frm.RecordsetClone
.FindFirst "[CGID] = """ & strCGID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With

--
Dave Hargis, Microsoft Access MVP


Chris said:
Hi all. Please help!

My form allows add, delete, edit but not data entry. I use this form to
view and edit vacations and I wish to also be able to add a new vacation(s)
via a command button.

So...I call up the form, view existing vacations and click the button (data
entry = true) and make my entry ok.

Now, after adding record(s), I want to view all the records for this
employee with the new addition included.

My code:
Dim frm As Form
Set frm = Forms![3frmCaregiverVacations]

If Me.Dirty = True Then Me.Dirty = False

PostTest

Me.DataEntry = False

frm.Requery

With frm.RecordsetClone
.FindFirst "[CGID] = """ & Me![CGID] & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With

Set frm = Nothing

This code returns to viewing, displaying all records for all employees. I'm
guessing it is my .FindFirst line and the ID.

If I close the form and reopen it, all is good. The user may wish to add
several vacations for this one employee.

Where is my error in code? Logic? CGID is text type.

Thanks so much,
Click to expand...
Click to expand...
Click to expand...
 
When no record exists I have to add a new one. The view/edit form does not
get loaded. (It does however when a record does exist.)

Thus, when no record exists I skip the view/edit form going straight to the
data entry form. Then when closing the data entry form after entering this
first employee record, I throw a runtime error 2450, can't find the form
"frm3CaregiverVacations".

This is the form set to "frm =" in the data entry close code.

Note that it opens in the calling code for edit/view when a record does
exist; does not open when none exists.

Therein lies my dilemma. It is not open when called in the close code.

Help!

--
Chrissy


Klatuu said:
Sorry, Chris, but I could not tell for sure where you were trying to
reference the form that wasn't there, but there is a way to see if a form is
loaded or not:

If CurrentProject.Allforms("FormNameHere").IsLoaded Then
'The Form is loaded

Now, you can also check to see what view it is in, but in a normal
application, it should not be in design view anyway, so that shouldn't be an
issue.
--
Dave Hargis, Microsoft Access MVP


Chris said:
Thank you Dave. That is working for me except in one case -- new record.

When I open the Vacations form (viewing/editing) I test for records for this
employee. If a record exists I use the Add button and successfully add an
additional vacation record. When no vacation record exists, I go right to the
entry form.

At this point the viewing form is not open, so when I go to close the entry
from with the new (and first) record for this employee it fails at "Set frm =
Forms![3frmCaregiverVacations]" in the closing code.

Since it cannot find a form I have skipped (in the case of no record yet), I
tried to open the view/edit form if there was no record. I cannot make it
work.

Can you see error in code or offer a method to (1) ensure that I can
edit/view existing records and add a new one (when a record already exists)
and (2) add a new record when none yet exists?

Have I provided enough information?

Your guidance is greatly appreciated.


Calling code -- view/edit form or (if no record) entry form:
Dim stdocName, stdocName1, stLinkCriteria, msg, style, title, response As
String
stdocName = "3frmCaregiverVacations"
stdocName1 = "3frmDECaregiverVacations"
stLinkCriteria = "[CGID]=" & "'" & Me![CG1] & "'"
title = "No Record!"
style = vbYesNo
msg = "Caregiver has no vacations scheduled." & vbCrLf & _
" Would you like to add one now?"
'tests for existing record
If HasDataVacation(CG1) Then
'has a record already
DoCmd.OpenForm stdocName, , , stLinkCriteria
Else
'no record currently
response = MsgBox(msg, style, title)
If response = vbYes Then
DoCmd.OpenForm stdocName1, , , , acFormAdd
Forms![3frmDECaregiverVacations].Form![CGID] = Me.CG1
Forms![3frmDECaregiverVacations].Form!txtStartDate = Null
Forms![3frmDECaregiverVacations].Form!txtEndDate = Null
Else
Exit Sub
End If
End If

Closing code -- entry form:
Dim frm As Form
Dim strCurrCGID As String
Set frm = Forms![3frmCaregiverVacations]
strCurrCGID = Me![CGID]
PostTest
frm.Requery
With frm.RecordsetClone
.FindFirst "[CGID] = """ & strCurrCGID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With
If Me.Dirty Then Me.Dirty = False
Set frm = Nothing
DoCmd.close








--
Chrissy


Klatuu said:
To answer the actual question, the problem is you have to save the value in
the text box before you do the requery. As written, it will return to the
first record in the recordset and then do the find and navigate to it again.

Dim strCurrCGID As String

strCurrCGID = Me![CGID]

frm.Requery

With frm.RecordsetClone
.FindFirst "[CGID] = """ & strCGID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With

--
Dave Hargis, Microsoft Access MVP


:

Hi all. Please help!

My form allows add, delete, edit but not data entry. I use this form to
view and edit vacations and I wish to also be able to add a new vacation(s)
via a command button.

So...I call up the form, view existing vacations and click the button (data
entry = true) and make my entry ok.

Now, after adding record(s), I want to view all the records for this
employee with the new addition included.

My code:
Dim frm As Form
Set frm = Forms![3frmCaregiverVacations]

If Me.Dirty = True Then Me.Dirty = False

PostTest

Me.DataEntry = False

frm.Requery

With frm.RecordsetClone
.FindFirst "[CGID] = """ & Me![CGID] & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With

Set frm = Nothing

This code returns to viewing, displaying all records for all employees. I'm
guessing it is my .FindFirst line and the ID.

If I close the form and reopen it, all is good. The user may wish to add
several vacations for this one employee.

Where is my error in code? Logic? CGID is text type.

Thanks so much,
Click to expand...
Click to expand...
Click to expand...
 
I'm not absolutely sure, but try this:
Dim frm As Form
Dim strCurrCGID As String

If CurrentProject.Allforms("3frmCareGiverVacations").IsLoaded Then
Set frm = Forms![3frmCaregiverVacations]
strCurrCGID = Me![CGID]
PostTest
frm.Requery
With frm.RecordsetClone
.FindFirst "[CGID] = """ & strCurrCGID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With
Set frm = Nothing
End If
If Me.Dirty Then Me.Dirty = False
DoCmd.close

--
Dave Hargis, Microsoft Access MVP


Chris said:
When no record exists I have to add a new one. The view/edit form does not
get loaded. (It does however when a record does exist.)

Thus, when no record exists I skip the view/edit form going straight to the
data entry form. Then when closing the data entry form after entering this
first employee record, I throw a runtime error 2450, can't find the form
"frm3CaregiverVacations".

This is the form set to "frm =" in the data entry close code.

Note that it opens in the calling code for edit/view when a record does
exist; does not open when none exists.

Therein lies my dilemma. It is not open when called in the close code.

Help!

--
Chrissy


Klatuu said:
Sorry, Chris, but I could not tell for sure where you were trying to
reference the form that wasn't there, but there is a way to see if a form is
loaded or not:

If CurrentProject.Allforms("FormNameHere").IsLoaded Then
'The Form is loaded

Now, you can also check to see what view it is in, but in a normal
application, it should not be in design view anyway, so that shouldn't be an
issue.
--
Dave Hargis, Microsoft Access MVP


Chris said:
Thank you Dave. That is working for me except in one case -- new record.

When I open the Vacations form (viewing/editing) I test for records for this
employee. If a record exists I use the Add button and successfully add an
additional vacation record. When no vacation record exists, I go right to the
entry form.

At this point the viewing form is not open, so when I go to close the entry
from with the new (and first) record for this employee it fails at "Set frm =
Forms![3frmCaregiverVacations]" in the closing code.

Since it cannot find a form I have skipped (in the case of no record yet), I
tried to open the view/edit form if there was no record. I cannot make it
work.

Can you see error in code or offer a method to (1) ensure that I can
edit/view existing records and add a new one (when a record already exists)
and (2) add a new record when none yet exists?

Have I provided enough information?

Your guidance is greatly appreciated.


Calling code -- view/edit form or (if no record) entry form:
Dim stdocName, stdocName1, stLinkCriteria, msg, style, title, response As
String
stdocName = "3frmCaregiverVacations"
stdocName1 = "3frmDECaregiverVacations"
stLinkCriteria = "[CGID]=" & "'" & Me![CG1] & "'"
title = "No Record!"
style = vbYesNo
msg = "Caregiver has no vacations scheduled." & vbCrLf & _
" Would you like to add one now?"
'tests for existing record
If HasDataVacation(CG1) Then
'has a record already
DoCmd.OpenForm stdocName, , , stLinkCriteria
Else
'no record currently
response = MsgBox(msg, style, title)
If response = vbYes Then
DoCmd.OpenForm stdocName1, , , , acFormAdd
Forms![3frmDECaregiverVacations].Form![CGID] = Me.CG1
Forms![3frmDECaregiverVacations].Form!txtStartDate = Null
Forms![3frmDECaregiverVacations].Form!txtEndDate = Null
Else
Exit Sub
End If
End If

Closing code -- entry form:
Dim frm As Form
Dim strCurrCGID As String
Set frm = Forms![3frmCaregiverVacations]
strCurrCGID = Me![CGID]
PostTest
frm.Requery
With frm.RecordsetClone
.FindFirst "[CGID] = """ & strCurrCGID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With
If Me.Dirty Then Me.Dirty = False
Set frm = Nothing
DoCmd.close








--
Chrissy


:

To answer the actual question, the problem is you have to save the value in
the text box before you do the requery. As written, it will return to the
first record in the recordset and then do the find and navigate to it again.

Dim strCurrCGID As String

strCurrCGID = Me![CGID]

frm.Requery

With frm.RecordsetClone
.FindFirst "[CGID] = """ & strCGID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With

--
Dave Hargis, Microsoft Access MVP


:

Hi all. Please help!

My form allows add, delete, edit but not data entry. I use this form to
view and edit vacations and I wish to also be able to add a new vacation(s)
via a command button.

So...I call up the form, view existing vacations and click the button (data
entry = true) and make my entry ok.

Now, after adding record(s), I want to view all the records for this
employee with the new addition included.

My code:
Dim frm As Form
Set frm = Forms![3frmCaregiverVacations]

If Me.Dirty = True Then Me.Dirty = False

PostTest

Me.DataEntry = False

frm.Requery

With frm.RecordsetClone
.FindFirst "[CGID] = """ & Me![CGID] & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With

Set frm = Nothing

This code returns to viewing, displaying all records for all employees. I'm
guessing it is my .FindFirst line and the ID.

If I close the form and reopen it, all is good. The user may wish to add
several vacations for this one employee.

Where is my error in code? Logic? CGID is text type.

Thanks so much,
Click to expand...
Click to expand...
Click to expand...
 
That got past the error.

So I added an else to your code opening the view/edit form back up and my
world is good again.

From my perspective ... brilliant! I am learning (for only my purposes) at
a snail's pace, but I am learning. Thank you so much.

--
Chrissy


Klatuu said:
I'm not absolutely sure, but try this:
Dim frm As Form
Dim strCurrCGID As String

If CurrentProject.Allforms("3frmCareGiverVacations").IsLoaded Then
Set frm = Forms![3frmCaregiverVacations]
strCurrCGID = Me![CGID]
PostTest
frm.Requery
With frm.RecordsetClone
.FindFirst "[CGID] = """ & strCurrCGID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With
Set frm = Nothing
End If
If Me.Dirty Then Me.Dirty = False
DoCmd.close

--
Dave Hargis, Microsoft Access MVP


Chris said:
When no record exists I have to add a new one. The view/edit form does not
get loaded. (It does however when a record does exist.)

Thus, when no record exists I skip the view/edit form going straight to the
data entry form. Then when closing the data entry form after entering this
first employee record, I throw a runtime error 2450, can't find the form
"frm3CaregiverVacations".

This is the form set to "frm =" in the data entry close code.

Note that it opens in the calling code for edit/view when a record does
exist; does not open when none exists.

Therein lies my dilemma. It is not open when called in the close code.

Help!

--
Chrissy


Klatuu said:
Sorry, Chris, but I could not tell for sure where you were trying to
reference the form that wasn't there, but there is a way to see if a form is
loaded or not:

If CurrentProject.Allforms("FormNameHere").IsLoaded Then
'The Form is loaded

Now, you can also check to see what view it is in, but in a normal
application, it should not be in design view anyway, so that shouldn't be an
issue.
--
Dave Hargis, Microsoft Access MVP


:

Thank you Dave. That is working for me except in one case -- new record.

When I open the Vacations form (viewing/editing) I test for records for this
employee. If a record exists I use the Add button and successfully add an
additional vacation record. When no vacation record exists, I go right to the
entry form.

At this point the viewing form is not open, so when I go to close the entry
from with the new (and first) record for this employee it fails at "Set frm =
Forms![3frmCaregiverVacations]" in the closing code.

Since it cannot find a form I have skipped (in the case of no record yet), I
tried to open the view/edit form if there was no record. I cannot make it
work.

Can you see error in code or offer a method to (1) ensure that I can
edit/view existing records and add a new one (when a record already exists)
and (2) add a new record when none yet exists?

Have I provided enough information?

Your guidance is greatly appreciated.


Calling code -- view/edit form or (if no record) entry form:
Dim stdocName, stdocName1, stLinkCriteria, msg, style, title, response As
String
stdocName = "3frmCaregiverVacations"
stdocName1 = "3frmDECaregiverVacations"
stLinkCriteria = "[CGID]=" & "'" & Me![CG1] & "'"
title = "No Record!"
style = vbYesNo
msg = "Caregiver has no vacations scheduled." & vbCrLf & _
" Would you like to add one now?"
'tests for existing record
If HasDataVacation(CG1) Then
'has a record already
DoCmd.OpenForm stdocName, , , stLinkCriteria
Else
'no record currently
response = MsgBox(msg, style, title)
If response = vbYes Then
DoCmd.OpenForm stdocName1, , , , acFormAdd
Forms![3frmDECaregiverVacations].Form![CGID] = Me.CG1
Forms![3frmDECaregiverVacations].Form!txtStartDate = Null
Forms![3frmDECaregiverVacations].Form!txtEndDate = Null
Else
Exit Sub
End If
End If

Closing code -- entry form:
Dim frm As Form
Dim strCurrCGID As String
Set frm = Forms![3frmCaregiverVacations]
strCurrCGID = Me![CGID]
PostTest
frm.Requery
With frm.RecordsetClone
.FindFirst "[CGID] = """ & strCurrCGID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With
If Me.Dirty Then Me.Dirty = False
Set frm = Nothing
DoCmd.close








--
Chrissy


:

To answer the actual question, the problem is you have to save the value in
the text box before you do the requery. As written, it will return to the
first record in the recordset and then do the find and navigate to it again.

Dim strCurrCGID As String

strCurrCGID = Me![CGID]

frm.Requery

With frm.RecordsetClone
.FindFirst "[CGID] = """ & strCGID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With

--
Dave Hargis, Microsoft Access MVP


:

Hi all. Please help!

My form allows add, delete, edit but not data entry. I use this form to
view and edit vacations and I wish to also be able to add a new vacation(s)
via a command button.

So...I call up the form, view existing vacations and click the button (data
entry = true) and make my entry ok.

Now, after adding record(s), I want to view all the records for this
employee with the new addition included.

My code:
Dim frm As Form
Set frm = Forms![3frmCaregiverVacations]

If Me.Dirty = True Then Me.Dirty = False

PostTest

Me.DataEntry = False

frm.Requery

With frm.RecordsetClone
.FindFirst "[CGID] = """ & Me![CGID] & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With

Set frm = Nothing

This code returns to viewing, displaying all records for all employees. I'm
guessing it is my .FindFirst line and the ID.

If I close the form and reopen it, all is good. The user may wish to add
several vacations for this one employee.

Where is my error in code? Logic? CGID is text type.

Thanks so much,
Click to expand...
Click to expand...
Click to expand...
 
Glad I could help.

--
Dave Hargis, Microsoft Access MVP


Chris said:
That got past the error.

So I added an else to your code opening the view/edit form back up and my
world is good again.

From my perspective ... brilliant! I am learning (for only my purposes) at
a snail's pace, but I am learning. Thank you so much.

--
Chrissy


Klatuu said:
I'm not absolutely sure, but try this:
Dim frm As Form
Dim strCurrCGID As String

If CurrentProject.Allforms("3frmCareGiverVacations").IsLoaded Then
Set frm = Forms![3frmCaregiverVacations]
strCurrCGID = Me![CGID]
PostTest
frm.Requery
With frm.RecordsetClone
.FindFirst "[CGID] = """ & strCurrCGID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With
Set frm = Nothing
End If
If Me.Dirty Then Me.Dirty = False
DoCmd.close

--
Dave Hargis, Microsoft Access MVP


Chris said:
When no record exists I have to add a new one. The view/edit form does not
get loaded. (It does however when a record does exist.)

Thus, when no record exists I skip the view/edit form going straight to the
data entry form. Then when closing the data entry form after entering this
first employee record, I throw a runtime error 2450, can't find the form
"frm3CaregiverVacations".

This is the form set to "frm =" in the data entry close code.

Note that it opens in the calling code for edit/view when a record does
exist; does not open when none exists.

Therein lies my dilemma. It is not open when called in the close code.

Help!

--
Chrissy


:

Sorry, Chris, but I could not tell for sure where you were trying to
reference the form that wasn't there, but there is a way to see if a form is
loaded or not:

If CurrentProject.Allforms("FormNameHere").IsLoaded Then
'The Form is loaded

Now, you can also check to see what view it is in, but in a normal
application, it should not be in design view anyway, so that shouldn't be an
issue.
--
Dave Hargis, Microsoft Access MVP


:

Thank you Dave. That is working for me except in one case -- new record.

When I open the Vacations form (viewing/editing) I test for records for this
employee. If a record exists I use the Add button and successfully add an
additional vacation record. When no vacation record exists, I go right to the
entry form.

At this point the viewing form is not open, so when I go to close the entry
from with the new (and first) record for this employee it fails at "Set frm =
Forms![3frmCaregiverVacations]" in the closing code.

Since it cannot find a form I have skipped (in the case of no record yet), I
tried to open the view/edit form if there was no record. I cannot make it
work.

Can you see error in code or offer a method to (1) ensure that I can
edit/view existing records and add a new one (when a record already exists)
and (2) add a new record when none yet exists?

Have I provided enough information?

Your guidance is greatly appreciated.


Calling code -- view/edit form or (if no record) entry form:
Dim stdocName, stdocName1, stLinkCriteria, msg, style, title, response As
String
stdocName = "3frmCaregiverVacations"
stdocName1 = "3frmDECaregiverVacations"
stLinkCriteria = "[CGID]=" & "'" & Me![CG1] & "'"
title = "No Record!"
style = vbYesNo
msg = "Caregiver has no vacations scheduled." & vbCrLf & _
" Would you like to add one now?"
'tests for existing record
If HasDataVacation(CG1) Then
'has a record already
DoCmd.OpenForm stdocName, , , stLinkCriteria
Else
'no record currently
response = MsgBox(msg, style, title)
If response = vbYes Then
DoCmd.OpenForm stdocName1, , , , acFormAdd
Forms![3frmDECaregiverVacations].Form![CGID] = Me.CG1
Forms![3frmDECaregiverVacations].Form!txtStartDate = Null
Forms![3frmDECaregiverVacations].Form!txtEndDate = Null
Else
Exit Sub
End If
End If

Closing code -- entry form:
Dim frm As Form
Dim strCurrCGID As String
Set frm = Forms![3frmCaregiverVacations]
strCurrCGID = Me![CGID]
PostTest
frm.Requery
With frm.RecordsetClone
.FindFirst "[CGID] = """ & strCurrCGID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With
If Me.Dirty Then Me.Dirty = False
Set frm = Nothing
DoCmd.close








--
Chrissy


:

To answer the actual question, the problem is you have to save the value in
the text box before you do the requery. As written, it will return to the
first record in the recordset and then do the find and navigate to it again.

Dim strCurrCGID As String

strCurrCGID = Me![CGID]

frm.Requery

With frm.RecordsetClone
.FindFirst "[CGID] = """ & strCGID & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With

--
Dave Hargis, Microsoft Access MVP


:

Hi all. Please help!

My form allows add, delete, edit but not data entry. I use this form to
view and edit vacations and I wish to also be able to add a new vacation(s)
via a command button.

So...I call up the form, view existing vacations and click the button (data
entry = true) and make my entry ok.

Now, after adding record(s), I want to view all the records for this
employee with the new addition included.

My code:
Dim frm As Form
Set frm = Forms![3frmCaregiverVacations]

If Me.Dirty = True Then Me.Dirty = False

PostTest

Me.DataEntry = False

frm.Requery

With frm.RecordsetClone
.FindFirst "[CGID] = """ & Me![CGID] & """"
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With

Set frm = Nothing

This code returns to viewing, displaying all records for all employees. I'm
guessing it is my .FindFirst line and the ID.

If I close the form and reopen it, all is good. The user may wish to add
several vacations for this one employee.

Where is my error in code? Logic? CGID is text type.

Thanks so much,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top