Add new record and move back and forth using Navigation bar in MS Access 2000

  • Thread starter Thread starter James P.
  • Start date Start date
J

James P.

Hello,

I have a form with RecordSelectors = Yes showing all records in the
table. When I click a record from that vertical bar list, it will
open a new form showing this record selected using this command:

DoCmd.OpenForm "ServiceRequestsForm", acNormal, , "SRNumber = " &
g_SRNumber, acFormEdit, acWindowNormal.

The new form is now loaded with this one "SRNumber". The recordSource
for this new form uses the same table. The filter now has this
"SRNumber". I have a navigation bar for the form.

My question is how can I while showing only one record on the screen
using that filter, but also be able to use the navigation bar to move
back and forth between all records within the table. In addition, I
would like to use this form to add a new record too.

Any help is greatly appreciated.

James
 
If you want to be able to navigate between records, then you cannot open the
form filtered to the one record. Instead, you will have to open the form and
move to the desired record.

To do that, you could pass the condition in the OpenArgs of OpenForm:
DoCmd.OpenForm "ServiceRequestsForm", OpenArgs:="SRNumber = " &
g_SRNumber

Then use the Load event of ServiceRequestsForm to find the desired record:

Private Sub Form_Load()
If Len(Nz(Me.OpenArgs, vbNullString)) > 0 Then
With Me.RecordsetClone
.FindFirst Me.OpenArgs
If .NoMatch Then
MsgBox "Not found"
Else
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub
 
Allen Browne said:
If you want to be able to navigate between records, then you cannot open the
form filtered to the one record. Instead, you will have to open the form and
move to the desired record.

To do that, you could pass the condition in the OpenArgs of OpenForm:
DoCmd.OpenForm "ServiceRequestsForm", OpenArgs:="SRNumber = " &
g_SRNumber

Then use the Load event of ServiceRequestsForm to find the desired record:

Private Sub Form_Load()
If Len(Nz(Me.OpenArgs, vbNullString)) > 0 Then
With Me.RecordsetClone
.FindFirst Me.OpenArgs
If .NoMatch Then
MsgBox "Not found"
Else
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub
Click to expand...

Allen,

Thank you very much for helping. I tried your code. When it executed
the statement ".FindFirst Me.OpenArgs", I could see the OpenArgs =
"SRNumber = 2020", which is the SRNumber selected, but it could not
find that (SRNumber) record selected and displayed the "Not found"
message.

That SRNumber does exist in the table. I tried to go to Form Design
and cleared out Filter before I ran it but still it could not find the
SR. I can't figure out what else I did wrong.

Could you think of any other reason it does not work?

Thanks a lot,
James
BTW, your web site has a lot of good tips.
 
If SRNumber is a Text type field (not a Number field when you view it in
Table Design), you need extra quotes:

DoCmd.OpenForm "ServiceRequestsForm", _
OpenArgs:="SRNumber = """ & g_SRNumber & """"
 
Allen Browne said:
If SRNumber is a Text type field (not a Number field when you view it in
Table Design), you need extra quotes:

DoCmd.OpenForm "ServiceRequestsForm", _
OpenArgs:="SRNumber = """ & g_SRNumber & """"
Click to expand...


Allen,

Again, thanks for responding. No, the SRNumber is a key field in SQL
and its identity = Yes. So, I tried it anyway with your suggestion,
but the .NoMatch return "true". It's really weird. Everything looks
right but it could not find the record. If you can think of anything
else, please let me know.

Thanks in advance,
James
 
Allen,

Again, thanks for responding. No, the SRNumber is a key field in SQL
and its identity = Yes. So, I tried it anyway with your suggestion,
but the .NoMatch return "true". It's really weird. Everything looks
right but it could not find the record. If you can think of anything
else, please let me know.

Thanks in advance,
James
Click to expand...

Allen,

Just want to let you know that I just tried what you showed me before
and suddenly it worked. It's weird but now it works the way it's
supposed to be. Again, thanks a lot.

James
 
Back
Top