custom button for next "non recurring record"

[domcatanzarite]

How would one create a button that on click advances the form to the
next "non recurring record" as opposed to the next record.

The field the button needs to que from has groups of duplicate values.
I need it to advance the form to the next group of duplicate values not
just the next record.

[Allen Browne]

Assuming the form is sorted by this field, you could FindFirst in the
RecordsetClone of the form.

This kind of thing:

Dim rs As DAO.Recordset
Dim strWhere As String
If Me.Dirty Then 'Save before move.
Me.Dirty = False
End If
If Not Me.NewRecord Then
strWhere = "[Field1] > " & Nz(Me.[Field1],0)
Set rs = Me.RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then 'Go to a new record.
RunCommand acCmdRecordsGotoNew
Else 'Display the matched record.
Me.Bookmark = rs.Bookmark
End If
End If
Set rs = Nothing

Note: if Field1 is a Text field, you need extra quotes:
strWhere = "[Field1] > """ & [Field1] & """"

--
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.

<do************@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

How would one create a button that on click advances the form to the
next "non recurring record" as opposed to the next record.

The field the button needs to que from has groups of duplicate values.
I need it to advance the form to the next group of duplicate values not
just the next record.

[domcatanzarite]

your code looks like this ...

Private Sub Next_Record_Click()
Dim rs As DAO.Recordset
Dim strWhere As String
If Me.Dirty Then 'Save before move.
Me.Dirty = False
End If
If Not Me.NewRecord Then
strWhere = "[MEDICAL RECORD NUMBER] > '''" & Nz(Me.[MEDICAL
RECORD NUMBER], 0)
Set rs = Me.RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then 'Go to a new record.
RunCommand acCmdRecordsGoToNew
Else 'Display the matched record.
Me.Bookmark = rs.Bookmark
End If
End If
Set rs = Nothing
'Note: if Field1 is a Text field, you need extra quotes:
' strWhere = "[Field1] > """ & [Field1] & """"
End Sub

I could not get it to work however. I am also including this further
discription and would love to email my dbase to you for review if poss
let me know if I could send to you and where. I will try to email it to
the group but I doubt it will post.

I tried to only use numbers (no text) but it didn't work. I tried
adding extra """ because it "is" a text field but still
couldn't get it to work. I'm new at this and while I know I will
need to fix many many things in this database I feel that if I could
just get this part to work I can take the time to rebuild this database
and
Follow all the do's and don'ts (i.e. no spaces in the table names
and separate out / split the data into data and user forms and queries.

I have tryed to includ the database so you could have a better grasp of
what I'm trying to achieve here (the patient demographics data is not
the real data to protect the patients privacy).

In this example - When the Process Management form opens it would
start at medical record number RXJ00526434 with the associated
PHARMACEUTICAL NAME - FILGRASTIM. Ideally when the next button was
clicked the user would advance to the same medical record number
RXJ00526434 but with the associated PHARMACEUTICAL NAME - GRANISITRON
because that is the first instance where the PHARMACEUTICAL NAME
changed to another drug. The next click would take the user to the next
medical record number RXJ00510129 with the associated PHARMACEUTICAL
NAME - GEMCITABINE then GRANISITRON then FILGRASTIM then CARBOPLATIN.
The next click would advance to the next medical record number.

To simplify the button I seek that is to be on the Application Tracking
tab .....

If the identical medical record number has 16 instances of 4 like drugs
then it would take 4 clicks to advance to the next medical record
number.

Note The next tab on the form is there to show all 16 instances when
the need to see all instances is required.

Thanks,

Dom

[Allen Browne]

If [MEDICAL RECORD NUMBER] is a Text field, you need:

strWhere = "[MEDICAL RECORD NUMBER] > """ & _
Me.[MEDICAL RECORD NUMBER] & """"

Please do not email the database. I hope you can understand that we cannot
examine everyone's database for free and still get any work done.

--
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.

<do************@gmail.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...

your code looks like this ...

Private Sub Next_Record_Click()
Dim rs As DAO.Recordset
Dim strWhere As String
If Me.Dirty Then 'Save before move.
Me.Dirty = False
End If
If Not Me.NewRecord Then
strWhere = "[MEDICAL RECORD NUMBER] > '''" & Nz(Me.[MEDICAL
RECORD NUMBER], 0)
Set rs = Me.RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then 'Go to a new record.
RunCommand acCmdRecordsGoToNew
Else 'Display the matched record.
Me.Bookmark = rs.Bookmark
End If
End If
Set rs = Nothing
'Note: if Field1 is a Text field, you need extra quotes:
' strWhere = "[Field1] > """ & [Field1] & """"
End Sub

I could not get it to work however. I am also including this further
discription and would love to email my dbase to you for review if poss
let me know if I could send to you and where. I will try to email it to
the group but I doubt it will post.

I tried to only use numbers (no text) but it didn't work. I tried
adding extra """ because it "is" a text field but still
couldn't get it to work. I'm new at this and while I know I will
need to fix many many things in this database I feel that if I could
just get this part to work I can take the time to rebuild this database
and
Follow all the do's and don'ts (i.e. no spaces in the table names
and separate out / split the data into data and user forms and queries.

I have tryed to includ the database so you could have a better grasp of
what I'm trying to achieve here (the patient demographics data is not
the real data to protect the patients privacy).

In this example - When the Process Management form opens it would
start at medical record number RXJ00526434 with the associated
PHARMACEUTICAL NAME - FILGRASTIM. Ideally when the next button was
clicked the user would advance to the same medical record number
RXJ00526434 but with the associated PHARMACEUTICAL NAME - GRANISITRON
because that is the first instance where the PHARMACEUTICAL NAME
changed to another drug. The next click would take the user to the next
medical record number RXJ00510129 with the associated PHARMACEUTICAL
NAME - GEMCITABINE then GRANISITRON then FILGRASTIM then CARBOPLATIN.
The next click would advance to the next medical record number.

To simplify the button I seek that is to be on the Application Tracking
tab .....

If the identical medical record number has 16 instances of 4 like drugs
then it would take 4 clicks to advance to the next medical record
number.

Note The next tab on the form is there to show all 16 instances when
the need to see all instances is required.