Seeing all records on a form when re-opening

[Sam]

Hello All,
We want our Call Service Representatives to be able to go back and view
all the records in a table on a form. When closing and re-opening the
form the form starts out as record 1 of 1 even though there are records
already entered into the table via the form. Is there anyway to have
the form start out after the last record in the table and allow the CSR
to go back and view what has already been entered into the table? For
example if there are 5 records in the table, after closing and
re-opening the form the form will start out record 6 and the CSR will
be able to go back and view the previously entered records.

Thank you in advance.

[Allen Browne]

Set the form's Data Entry property to No.
It now loads with all records.

Add this to the form's Load event procedure:
If Not Me.NewRecord Then
RunCommand acCmdRecordsGotoNew
End If
The form now loads with all records, but goes to the new one.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Sam" <ar************@pearson.com> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.com...

Hello All,
We want our Call Service Representatives to be able to go back and view
all the records in a table on a form. When closing and re-opening the
form the form starts out as record 1 of 1 even though there are records
already entered into the table via the form. Is there anyway to have
the form start out after the last record in the table and allow the CSR
to go back and view what has already been entered into the table? For
example if there are 5 records in the table, after closing and
re-opening the form the form will start out record 6 and the CSR will
be able to go back and view the previously entered records.

Thank you in advance.

[Sam]

Thanks Allen! That worked great.

[Sam]

Allen (or whoever),
I have one more question. When opening the form with the code above in
the load event procedure the form automatically goes into write mode
not allowing the users to go back and view previous records without
filling in required fields first. Is there anyway around this? Is
there anyway to take the form out of write mode once the code is ran?

Thanks.

[Allen Browne]

There were 2 solutions.

The first is for adding new records only.

The second allows you to step back to previous records.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sam" <ar************@pearson.com> wrote in message
news:11********************@g10g2000cwb.googlegrou ps.com...

Allen (or whoever),
I have one more question. When opening the form with the code above in
the load event procedure the form automatically goes into write mode
not allowing the users to go back and view previous records without
filling in required fields first. Is there anyway around this? Is
there anyway to take the form out of write mode once the code is ran?

Thanks.

[Sam]

Allen,
Yes I was aware of that. And I used both as I want the form to open
ready to add a new record but still be able to step back to through
previous records. I should have explained this better the first time
as there's more than just this code in the load event - see code below

strQuery = "SELECT * FROM [Get_CSR_Profile]"

Set qdfDef = CurrentDb().CreateQueryDef("", strQuery)
Set rsRecordset = qdfDef.OpenRecordset(dbOpenDynaset)

strContReason = rsRecordset!CONTINGENCY_REASON
strSiteID = rsRecordset!SITE_ID
strLocation = rsRecordset!LOCATION
strCSR_Type = rsRecordset!CSR_TYPE
If rsRecordset.RecordCount <> 0 Then
'Me.CONT_REASON.Value = sRecordset!CONTINGENCY_REASON
Me.SITE_ID_Combo.Value = rsRecordset!SITE_ID
'Me.CUSTOM_LOCATION.Value = rsRecordset!LOCATION
'Me.CSR_TYPE_Combo.Value = sRecordset!CSR_TYPE
End If

The code above populates some fields on the form so the user doesn't
have to re-enter it every time they open thier DB (they all have their
own copy of the DB). Becuase I'm populateing these fields the form
goes into write mode right away. The user is then not allowed to
scroll back through previous records because there are other fields on
the form that are required. My question is, is there anyway around
this? Is there a way to take the form off write mode? Or any other
way?

Thanks and sorry for the confusion.

[Sam]

Hi Allen,
I've been doing some research and found you can use the DefaultValue to
set the fields and the form won't go into write mode. But I'm getting
and a runtime error 2447: "There is an invalid use of the .(dot) or !
operator or invalid parentheses". I found some info on this error that
deals with what I'm doing and it says to put quotes around the string.

From another post:

After kicking things around a bit, I realized that I was
seeing a problem with delimiters. I was handling dates
correctly:

txt_Date.DefaultValue = "#" & dtm_MyDate & "#"

but NOT strings:

txt_Name.DefaultValue = MyName

which SHOULD be:

txt_Name.DefaultValue = """ & MyName & """

How can I do this when I'm using values brought in from a table (rather
than a constant string)?

Thanks.

[Allen Browne]

I am not sure what you mean by "write mode"

If you put this code into the Load event, then presumably you are dirtying
the new record as soon as you open the form. Is this what you mean by write
mode? That the new record is dirtied, and so the user must complete the
entry or undo it before proceeding to another new or old record?

If you don't want it to do that, it would make sense to remove the code from
the Load event.

If you want to have the code anyway, yet let the user out sometimes, they
just have to press the <Esc> key twice to undo the record.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sam" <ar************@pearson.com> wrote in message
news:11*********************@i40g2000cwc.googlegro ups.com...

Allen,
Yes I was aware of that. And I used both as I want the form to open
ready to add a new record but still be able to step back to through
previous records. I should have explained this better the first time
as there's more than just this code in the load event - see code below

strQuery = "SELECT * FROM [Get_CSR_Profile]"

Set qdfDef = CurrentDb().CreateQueryDef("", strQuery)
Set rsRecordset = qdfDef.OpenRecordset(dbOpenDynaset)

strContReason = rsRecordset!CONTINGENCY_REASON
strSiteID = rsRecordset!SITE_ID
strLocation = rsRecordset!LOCATION
strCSR_Type = rsRecordset!CSR_TYPE
If rsRecordset.RecordCount <> 0 Then
'Me.CONT_REASON.Value = sRecordset!CONTINGENCY_REASON
Me.SITE_ID_Combo.Value = rsRecordset!SITE_ID
'Me.CUSTOM_LOCATION.Value = rsRecordset!LOCATION
'Me.CSR_TYPE_Combo.Value = sRecordset!CSR_TYPE
End If

The code above populates some fields on the form so the user doesn't
have to re-enter it every time they open thier DB (they all have their
own copy of the DB). Becuase I'm populateing these fields the form
goes into write mode right away. The user is then not allowed to
scroll back through previous records because there are other fields on
the form that are required. My question is, is there anyway around
this? Is there a way to take the form off write mode? Or any other
way?

Thanks and sorry for the confusion.