Next Record after changing a sorted field

[coopah]

Hi guys, I need a little help on next record stuff...

Can someone please explain how I can make my database actually go to the "Next" record after I update the "LName" field on my form? My database is sorted on "LName". After I update the "LName" field and save the record, the sort order is messed up. If I requery the form in the sub routine, the database goes to the first record. I need it to go to the "Next" record (the one that would have actually come next before I changed the LName). For example, if my database contains these names:

Baker
Doe
Franklin
Goodwin
Johnson
Jones
Smith
Taylor

and I change the current record's LName from Franklin to Phranklin, I expect the database to go to Goodwin (the record that would have followed Franklin) after pressing my next record command button. Likewise, if I changed Phranklin to Franklin, I expect the database to go to Smith (the record that would have followed Phranklin) after pressing my next record command button.

I've tried different versions of FINDFIRST on this site, but can't get it to work. I would like to find the next record based on my key field (autonumber) named "rec_id".

Thanks, and luv ya in advance!

-carol
http://profiles.yahoo.com/c_coop2005;)

 

[RuralGuy]

I would push the RecordSetClone to the next record and retrieve the key you need for the FindFirst. Referring to a Field in the Previous Record or Next Record

 

[coopah]

I would push the RecordSetClone to the next record and retrieve the key you need for the FindFirst. Referring to a Field in the Previous Record or Next Record

Thanks Rural. I looked at the MS page but afraid I don't understand it all. My table name is "people" and my form name is "personnel". The key field I need to search on is "rec_id". Beyond that, I don't know what else I'm supposed to put for "[ID]" in the two sets of brackets below?

=DLookUp("[rec_id]","people","[ID]=Forms![personnel]![ID]+1")

-carol
http://profiles.yahoo.com/c_coop2005

 

[RuralGuy]

Hi Carol,
Did you scroll down to the section that has the FUNCTION: NextRecVal()? That is the one I would choose. Just paste it in a standard module and use your field name. Post back if you need further assistance.

 

[coopah]

Admitting Defeat on this...

Hey RG and the rest of y'all,

I tried the module thing (see it at the end of this post) and couldn't figure it out. I added a field on my form to show the primary key, "rec_id" (the key I need to search on later to go to the next record). Then I tried changing "FieldNameToGet" to the name of my primary key field, "rec_id". Next I tried changing "KeyName" to the name of my primary key field "rec_id". Finally, I changed both "FieldNameToGet" and "KeyName" to "rec_id". None of this seemed to work. Am I doing this right? Do I need to change something else or have I changed too much in the functions?

Maybe it has something do do with how I've tried to tell my subroutine to go to the next and previous records using the functions. What exactly do I need to put in the subroutine? Do I need to "call" the functions from the subroutine? If so, how?

I think I've tried every flippin' way I know to get my database to actually go to the "Next" record (see first post in this string) with no success whatsoever. I guess I'll just chalk this up to dumb girl syndrome unless someone is willing to take me by the hand and walk me through it. I don't know why I'm finding this simple thing so hard to do. Sorry to be so dumb and dependent!

If this helps:
Table Name: people
Form Name: personnel
Primary Key: rec_id
Module with NextRecVal and PrevRecVal functions: RcrdFind

-Carol
http://profiles.yahoo.com/c_coop2005
--------------------------------
Option Compare Database

'*************************************************************
' FUNCTION: PrevRecVal()
' PURPOSE: Retrieve a value from a field in the previous form
' record.
' PARAMETERS:
' F - The form from which to get the previous value.
' KeyName - The name of the form's unique key field.
' KeyValue - The current record's key value.
' FieldNameToGet - The name of the field in the previous
' record from which to retrieve the value.
' RETURNS: The value in the field FieldNameToGet from the
' previous form record.
' EXAMPLE:
' =PrevRecVal(Form,"ID",[ID],"OdometerReading")
'**************************************************************
Function PrevRecVal(F As Form, ask_id As String, KeyValue, ask_id As String)
Dim RS As DAO.Recordset

On Error GoTo Err_PrevRecVal

' The default value is zero.
PrevRecVal = 0

' Get the form recordset.
Set RS = F.RecordsetClone

' Find the current record.
Select Case RS.Fields(ask_id).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, DB_DOUBLE, DB_BYTE
RS.FindFirst "[" & ask_id & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
RS.FindFirst "[" & ask_id & "] = #" & KeyValue & "#"
'Find using text data type key value?
Case DB_TEXT
RS.FindFirst "[" & ask_id & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select

' Move to the previous record.
RS.MovePrevious

' Return the result.
PrevRecVal = RS(ask_id)

Bye_PrevRecVal:
Exit Function
Err_PrevRecVal:
Resume Bye_PrevRecVal
End Function

'************************************************************
' FUNCTION: NextRecVal()
' PURPOSE: Retrieve a value from a field in the next form
' record.
'*************************************************************
Function NextRecVal(F As Form, ask_id As String, KeyValue, ask_id As String)
Dim RS As DAO.Recordset

On Error GoTo Err_NextRecVal

' The default value is zero.
NextRecVal = 0

' Get the form recordset.
Set RS = F.RecordsetClone

' Find the current record.
Select Case RS.Fields(ask_id).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, DB_DOUBLE, DB_BYTE
RS.FindFirst "[" & ask_id & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
RS.FindFirst "[" & ask_id & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
RS.FindFirst "[" & ask_id & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select

' Move to the next record.
RS.MoveNext

' Return the result.
NextRecVal = RS(ask_id)

Bye_NextRecVal:
Exit Function
Err_NextRecVal:
Resume Bye_NextRecVal
End Function
--------------------------------
Private Sub Next_Record_Click()
On Error GoTo Err_Next_Record_Click
If Me.Dirty Then
Me.tbProperSave.Value = "No"
End If
If Me.tbProperSave.Value = "No" Then
Select Case MsgBox("Do you want to save your changes to the current record?" & vbCrLf & vbLf & " Yes: Saves Changes" & vbCrLf & " No: Reset (Undo) Changes" & vbCrLf, vbYesNo + vbQuestion, "Save Current Record?")
Case vbYes: ' Save changes and go to Next record
Me.tbProperSave.Value = "Yes"
If Validate = "True" Then
DoCmd.RunCommand acCmdSaveRecord
Me.Requery
RunCommand acCmdRecordsGoToNext
End If
Case vbNo: 'Undo the changes
DoCmd.RunCommand acCmdUndo
Me.tbProperSave.Value = "Yes"
Case Else: 'Default case to trap any errors
'Do nothing
End Select
Else
DoCmd.GoToRecord , , acNext
End If
Exit_Next_Record_Click:
Exit Sub
Err_Next_Record_Click:
MsgBox Err.Description
Resume Exit_Next_Record_Click
End Sub

 

[RuralGuy]

Not to worry Carol, the solution is not very difficult. First we need to save the Key for the "next" record before we make any change to the name. We can do this in the BeforeUpdate event of the form. Since we know the key is a LongInteger (AutoNumber) we can eliminate some of the code.

Option Compare Database
[b][COLOR="Red"]Option Explicit[/COLOR][/b]

Public NextID As Long

'*************************************************
' FUNCTION: NextRecVal()
' PURPOSE: Retrieve a value from a field in the next form record.
'************************************************* 
Function NextRecVal() As Long

On Error GoTo Err_NextRecVal

NextRecVal = 0   '-- Initialize the variable

' Move the RecordSetClone to the current record.
Me.RecordsetClone.FindFirst "[rec_id] = " & [rec_id]
If Not Me.RecordsetClone.NoMatch Then
    Me.RecordsetClone.MoveNext
    NextRecVal = Me.RecordsetClone.rec_id
End If

Bye_NextRecVal:
Exit Function

Err_NextRecVal:
   MsgBox "Error No:    " & Err.Number & vbCr & _
   "Description: " & Err.Description
   Resume Bye_NextRecVal
End Function

Private Sub Form_BeforeUpdate(Cancel As Integer)

NextID = NextRecVal()

End Sub

'--------------------------------
Private Sub Next_Record_Click()
On Error GoTo Err_Next_Record_Click
If Me.Dirty Then
   If MsgBox("Do you want to save your changes to the current record?" & _
      vbCrLf & vbLf & " Yes: Saves Changes" & vbCrLf & _
      " No: Reset (Undo) Changes" & _
      vbCrLf, vbYesNo + vbQuestion, "Save Current Record?") = vbYes Then
      ' Save changes and go to Next record
      If Validate = "True" Then
         DoCmd.RunCommand acCmdSaveRecord
         Me.Requery
         ' Move the RecordSetClone to the "other" next record.
         Me.RecordsetClone.FindFirst "[rec_id] = " & NextID 
         If Not Me.RecordsetClone.NoMatch Then
            Me.Bookmark = Me.RecordsetClone.Bookmark
         End If
      End If
   Else
      'Undo all the changes
      Me.UnDo
   End If
Else
   DoCmd.GoToRecord , , acNext
End If

Exit_Next_Record_Click:
Exit Sub

Err_Next_Record_Click:
   MsgBox "Error No:    " & Err.Number & vbCr & _
   "Description: " & Err.Description
   Resume Exit_Next_Record_Click
End Sub

This is AIR CODE and untested so it may have some typo's and other errors but give it a try and post back with the results.

 

[coopah]

RG,

First, thank you for your patience, you're a doll! I did exactly as you said and received the error: "compile error: external name not defined" and this line was highlighted in yellow: "Function NextRecVal() As Long"

-Carol [feel like crying]

 

[RuralGuy]

Phooie!
Change it to:
Private Function NextRecVal() As Long

 

[coopah]

Different Error..."Sub or Function not defined"

Phooie!
Change it to:
Private Function NextRecVal() As Long

RG,

I recived another Compile Error:"Sub or Function not defined" and the text highlighted in the "Private Sub Form_BeforeUpdate(Cancel As Integer)" subroutine, I think for the "NextID = NextRecVal()"

-Carol

 

[RuralGuy]

Is your db small enough to zip and post with private stuff removed?

 

[coopah]

Is your db small enough to zip and post with private stuff removed?

Rg,
Here's part of the database I've been trying to figure out. This is a tiny subset of a much larger database I've been working on. The error is replicated in the attached file.

One other problem you may be able to help me out with is the project numbers. You'll notice there are fields for three projects on the form that are derived from another table in the database. I can't figure out how to get the project numbers to show up. I've tried adding the "Projects" table to the qryMain query but when I do, the query lists as many records for each individual as there are projects and the filed in my form says "#Error".

If you can help me get over these two problems, I may be home free with this database.

I appreciate your time and patience with me. [wishing there was a smiley face to show how frazzled I am with this]

-Carol
"Dammit Jim, I'm a chemist, not a programmer...I'm giving her all she's got!"

 

[RuralGuy]

Hi Carol,
I expected you to leave all of the code in the module for the form. I put it back there and it seems to function as you wished. As for the Project Numbers, what relationship do they have to the records in tblMain? I added some Primary Keys to your other tables for later but have no idea how to display the Project numbers on your frmRecordManagement without understanding the relationship.

 

[coopah]

Too Perfect

Hi Carol,
I expected you to leave all of the code in the module for the form. I put it back there and it seems to function as you wished. As for the Project Numbers, what relationship do they have to the records in tblMain? I added some Primary Keys to your other tables for later but have no idea how to display the Project numbers on your frmRecordManagement without understanding the relationship.

RG, you rock me dude!

Here I thought the code went inside a model object (something like the ajbcalendar). How embarrassing

As for the projects table, it contains numbers that identify various government and industry contracts for scientific/medical research, developement, and other studies. Project numbers match personnel against these studies for tracking hours, resources, etcetera.

Just so you know, this is part of a much larger database that workers have on their laptops to record data while conducting field experiments. The data is exported to Excel, which is then used to feed a much more complicated database at our research center. This little code will make it easier to record that data in the field and export it to the larger database. Currently, this is all done using paper forms [yuck] which someone else must transcribe [yuck++] to Excel, then export it.

Luv ya!
-Carol

 

[RuralGuy]

Mornin' Carol,

As for the projects table, it contains numbers that identify various government and industry contracts for scientific/medical research, developement, and other studies. Project numbers match personnel against these studies for tracking hours, resources, etcetera.

There is nothing in either to indicate the relationship. There needs to be a ForeignKey field in one of the tables to relate the other table. Can one Project involve many individuals? Can one individual be involved in many projects? Or is it many to many? There is nothing in the project table to indicate which project records to display on the form.

 

[coopah]

Project Numbers Explained

Mornin' Carol,

There is nothing in either to indicate the relationship. There needs to be a ForeignKey field in one of the tables to relate the other table. Can one Project involve many individuals? Can one individual be involved in many projects? Or is it many to many? There is nothing in the project table to indicate which project records to display on the form.

Good Afternoon RG,

Yes one project can relate to many individuals; and, one individual can be involved in many projects.

The project numbers actually identify thousands of projects in a series. Project numbers also identify it as industry, scientific, medical, research, developement, etcetera, as well as what phase a particular project is in and type of work to be carried out. Individuals are assigned certain projects and depending on the phase and work to be done these can change quite often. What I'm actually trying to do is to create a default setup option so each department head can set the project assignments for each individual. That way, when the individual takes the laptop to the field they will only see the project numbers on the form set by the department head in the default setup. Individuals will not be able to change the defaults.

It wouldn't be practical to list every project in each individual record so a seperate table is built just for project numbers. My problem, as mentioned earlier, is that when I tried adding the "Projects" table to the qryMain query the form showed as many individuals as there are projects. Not what I want, especially when the project table will have thousands of project numbers.

Hope you're not spending your entire Sunday coddling me as I grope my way through this thing. I can't tell ya how much I'm learning and REALLY appreciate your help <mmmmmmwaaaaahhh>!!!

-Carol

 

[RuralGuy]

Normally we display/edit a 1:M relationship with a SubForm. This only works if there is a PrimaryKey in the SubForm query as a ForeignKey. Perhaps what you are trying to accomplish would be best done with a ComboBox with a query of the Project table as the RowSource of the ComboBox.

 

[coopah]

Previous button is gettin' me now

RG,

Thanks again! I'm working on the sub-form now. Adding a sub-form seems like a lot just to get the value of three fields from one table to display with a form based on another table...but what do I know I have to ask though, is there another way (maybe a function) to grab the data from one table to display it on a form not based on the same table?

I thought about adding the combo box like you said, but that would give each individuals the ability to change work assignments. It would do the job, but not as I intended. I could lock the combo box I suppose to keep it from being changed.

Back to the original subject, the NEXT record function works like a charm! I thought the same code would work for the PREVIOUS button if I only changed the references from "next" to previous and added the public variable Public PreviousID As Long. I've included it in the module for the form just like the NEXT code, but no joy for me. If I change a name then hit the previous button, it jumps to the first record, not the "other previous" record. Am I cursed or just plain dumb??

-Carol

 

[RuralGuy]

...is there another way (maybe a function) to grab the data from one table to display it on a form not based on the same table?

You could use the DLookup() function if you knew what you were looking for.

I thought the same code would work for the PREVIOUS button if I only changed the references from "next" to previous and added the public variable Public PreviousID As Long.

It is a little more complicated than that as you have discovered. The BeforeUpdate event will need to know which direction you were going in order to know which Key value to save.

 

[coopah]

Wow, I'm Impressed RG!

You anticipated that I would forget the BeforeUpdate Event <teehee>. Both NEXT and PREVIOUS are working great! BIG Thanks once again.

I'm researching the DLOOKUP thing. I experimented with a list box, instead of the combo box to show the projects. That will work since each project on an individual's record will only show the top three priority assignments. Whatever the department head sets in the default can easily be shown in the list box since there can only be one assignment/project in each box. That at least solves the simple problem of listing of the projects on the individuals record. But now it's about to get really dicey...I will have to tie those projects into the form (probably a sub-form) so that I can perform calculations to track time and resources on the projects.

RG, you are an AWESOME help <claps exuberantly>!

-Carol

 

[RuralGuy]

Glad to hear you got things working. Lots of success with the rest of your project.