DoCmd.GotoRecord problem

Hello,

The following piece of code....

            DoCmd.GoToRecord , , acFirst
            Debug.Print "Student Code " & [Student Code]

... as you can see intructs Access to go to the first record, which is student code '1'. As I step through the code, I actually see the record appear in the background but the above Debug.Print statement returns '0' and al the other fields are obviously null.
Do I need to load the record programmatically? How do I do it?

Thanks.
JohnSaintAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

stevbeCommented:
Is the form bound to a table or query?

this is newer syntax that MS has built into Access

DoCmd.RunCommand acCmdRecordsGoToFirst

I would refer to the control tnat is bound to the Student Code field.
Debug.Print Me.Controls("cboStudentCode").Value

my guess is that Access is adding an _ in place of the space in the field name so this might work ...

Debug.Print "Student Code " & Me.Student_Code

In general it is a good practice to not put spaces in the names of fields.

Steve
0
dannywarehamCommented:
JohnSaint...  What do you actually require the db to do?

Return the last/first record entered? Open the form at a new record? Open the form at a specific or random record?

In the above code, what is "student code"? IS that a field on the form, or a field in your table?

:-)
0
pique_techCommented:
I'm not sure exactly what you want to do, but the code you've provided above instructs a form to display the first record (that's probably why you "see the record appear in the background").

If you're wanting the value of something from that record, you do have to do *something* more to get it, but what that something is depends on the context of your code.  For example, WHERE is the code you provided above located?  (i.e., in the form's code module?  On another form?)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

JohnSaintAuthor Commented:
Yes, it is bound to a table, and is invoked On Current. Student code is the PK on the table and an object on the form.

This code follows....

If Not Me.NewRecord Then
        Me![UnEnroll].Enabled = True
        If [IsEnrolled] Then
           Me![UnEnroll].Caption = "UN-ENROL"
        Else
           Me![UnEnroll].Caption = "ENROL"
        End If
    Else
        Me![UnEnroll].Caption = "ENROL"
        Me![UnEnroll].Enabled = False
       
End If

It breaks down trying to evaluate [IsEnrolled] because it is null. If I want to perform any processing based on the contents of this record, I cannot, as it stands. This code works fine if I just move through my records normally. Only after that GoTo Record statement, does it have problems. Should I set focus or something?
0
JohnSaintAuthor Commented:
Increasing Points!
0
GreymanMSCCommented:
If it breaks down truing to evaluate IsEnrolled on a nullity case, try avoiding the issue with a null-zero function.

    If Not Me.NewRecord Then
       Me.UnEnroll.Enabled = True
        If Nz(Me.IsEnrolled.Value, False) Then
           Me.UnEnroll.Caption = "UN-ENROL"
        Else
           Me.UnEnroll.Caption = "ENROL"
        End If
    Else
        Me.UnEnroll.Caption = "ENROL"
        Me.UnEnroll.Enabled = False
    End If
0
harfangCommented:
If I put together:

> The following piece of code....
>           DoCmd.GoToRecord , , acFirst

And...

> "invoked On Current"

This would mean that you want to navigate records in the "On Current" event... This cannot work very well...

Else, the second comment is another piece... on which I can still comment:
* You want the caption of a command button (it seems) to change according to a yes/no field.
* You do not know what to do with the Null Value of a New Record...

Simplified:
    If IsEnrolled Then UnEnroll.Caption = "UN-ENROL" Else UnEnroll.Caption = "ENROL"
    UnEnroll.Enabled = Not Me.NewRecord

This will work because "If" treats Null like False. Of course, everything would look better with "Default Value: False" on the control [IsEnrolled], so that it will never be null. Without Null, you can also go:
    UnEnroll.Caption = IIf( IsEnroll, "UN-ENROL", "ENROL")


Still, it seems to me that the "DoCmd.GoToRecord , , acFirst" probably works by itself, but that things get confused by some other code, or by navigation within OnCurrent.

Best solution is to comment out all strange things and put "Exit Sub" at the start of most event procedures. Once you get this to work, put things back one by one.

Good Luck

Finally, you do not need to
0
JohnSaintAuthor Commented:
Yes Harfang, the problem is that the GotoRecord is in the on current event. This does look to be a problem. I don't think there is a problem with any other code. A regards the null value. I don't think I can fudge it. The core of the problem is that I do not have access to any of the values in any of the fields of the last record of by set. If I needed to use 'Student Name' or 'Address' or any of the other fields, I couldn't using this method. I just tried exit sub and it didn't quite work.
I need to leave for work but will get back to this. I think you are on the right track.
0
harfangCommented:
The best would be to show the whole Form_Current() procedure, possibly with a plain text comment on what you want to acheive. We'll go on from there.
0
JohnSaintAuthor Commented:
Okay. It nearly works now! The Exit Sub was the key. Thanks. The only problem is now that I now get the message "You are not allwed to go to specified record" just before I move to the record. I'm not sure why this happens. It goes on the place me at the correct record!

What I am trying to achieve is this. The user is not allowed to create their own PK (Student Code). The application generates it using Dmax, as seen below. In addition, a couple pf default values and timestamps and things are set.

So the PK generation code is triggered On Current AND when we find ourselves at a new record.
The user is offered a choice of whether to generate a new PK or not (They may have moved to new record by accident). If they choose YES, everything is fine and dandy. If they choose NO, I do not want the user to be placed at a new zero PK record, which is what happens. I therefore thought I would be able to merely go to the first record. The code below actually works
 
Private Sub Form_Current()

    If Me.NewRecord And Me.Tag = "" Then
        If MsgBox("Click YES to generate new Student Code : " & DMax("[Student Code]", "TblStudents") + 1, _
                    vbYesNo) = vbYes Then
            Me.[Student Code] = DMax("[Student Code]", "TblStudents") + 1
            Me.[Entry TimeStamp] = Now
            Me.[User] = CurrentUser
            Me.[Business Code] = 0
            Me.[Ethnicity] = "Not known / Not provided"
            Me.[Disability] = 99
            Me.[Religion] = 99
            DoCmd.RunCommand acCmdSaveRecord
        Else
            DoCmd.GoToRecord , , acFirst
            Exit Sub
            Debug.Print "Student Code1 " & [Student Code]
        End If
    End If
   
    Me![Student Code].Enabled = Me.NewRecord
    If Not Me.NewRecord Then
        Me![UnEnroll].Enabled = True
        If [IsEnrolled] Then
           Me![UnEnroll].Caption = "UN-ENROL"
        Else
           Me![UnEnroll].Caption = "ENROL"
        End If
    Else
        Me![UnEnroll].Caption = "ENROL"
        Me![UnEnroll].Enabled = False
       
    End If
   
    If [Have_Disability] Then
        Me![ComboDisability].Enabled = True
    Else
        Me![ComboDisability].Enabled = False
    End If
End Sub
0
harfangCommented:
OK, let's clean up.

You want to insert your own number, but you do that as soon as the user surfs to the "new" record. That is not the best place. Try instead:

Private Sub Form_BeforeInsert(Cancel As Integer)

        If MsgBox("Click YES to generate new Student Code : " & DMax("[Student Code]", "TblStudents") + 1, _
                    vbYesNo) = vbYes Then
            Me.[Student Code] = DMax("[Student Code]", "TblStudents") + 1
            Me.[Entry TimeStamp] = Now
            Me.[User] = CurrentUser
            Me.[Business Code] = 0
            Me.[Ethnicity] = "Not known / Not provided"
            Me.[Disability] = 99
            Me.[Religion] = 99
        Else
            Cancel = True
        End If

End Sub

Notice that the record is still not saved. If several users add students at the same time, there will be conflicts...
Another solution: silent adding of student number:

Private Sub Form_BeforeUpdate(Cancel As Integer)

        If Me.NewRecord Then
            Me.[Student Code] = DMax("[Student Code]", "TblStudents") + 1
            Me.[Entry TimeStamp] = Now
            Me.[User] = CurrentUser
        End If

End Sub

And use default values for [Business Code], [Ethnicity], [Disability], and [Religion] ...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
harfangCommented:
Of course, for the very first student, it would have to be:
    Nz( DMax("[Student Code]", "TblStudents") , 0 ) + 1

Good Luck
0
JohnSaintAuthor Commented:
Can I restart. I am losing sight of my original problem.

The PK generation seems to work okay but I am having problems with deleting records now.

If I could repaste more of my code...

Private Sub Form_Current()
   
    If Me.NewRecord And Me.Tag = "" Then
        MsgBox "Generating new Student Code : " & DMax("[Student Code]", "TblStudents") + 1
        Me.[student code] = DMax("[Student Code]", "TblStudents") + 1
        Me.[Entry TimeStamp] = Now
        Me.[User] = CurrentUser
        Me.[Business Code] = 0
        Me.[Ethnicity] = "Not known / Not provided"
        Me.[Disability] = 99
        Me.[Religion] = 99
        DoCmd.RunCommand acCmdSaveRecord
    End If
   
    Debug.Print "STUDENT CODE " & [student code]
    Me![student code].Enabled = Me.NewRecord
    If Not Me.NewRecord Then
        Me![UnEnroll].Enabled = True
        If [IsEnrolled] Then
           Me![UnEnroll].Caption = "UN-ENROL"
        Else
           Me![UnEnroll].Caption = "ENROL"
        End If
        If [Have_Disability] Then
            Me![ComboDisability].Enabled = True
        Else
            Me![ComboDisability].Enabled = False
        End If
    End If
       
End Sub

I have a delete button which is causing me some horrible problems. I amended some wizard-generated code so that the PK generation code would not kick in after a delete. I also put in some code that moved me to the last record only when I was deleting the last record in the table. This code did work at one time.  

Private Sub Command95_Click()
'On Error GoTo Err_Command95_Click
   
    Debug.Print "student code in del" & [student code]
    Debug.Print "rec count " & Me.Recordset.RecordCount
    If Me.CurrentRecord = Me.Recordset.RecordCount Then
       
        Me.Tag = "Deleting"
        DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
        DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
        DoCmd.GoToRecord , , acLast
        Me.Tag = ""
    Else
        Me.Tag = ""
        DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
        DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
    End If
   
Exit_Command95_Click:
    Exit Sub

Err_Command95_Click:
    MsgBox Err.Description
    Resume Exit_Command95_Click
End Sub

Now, when I try to delete any record using the button, I get:
 
"Run-time Error 2008. You can't delete object '|' while it's open" What is '|'?
AAAAAAAH. Now I'm getting:
"Run-time Error '2455'  You entered an expression that has an invalid reference to propert '|'

(I have taken out the ON ERROR statement to get these)

When I try to delete the last record I get:
"Run-time error 3709. The search key was not found in any record"

The thing is, it does seem to be deleting the records. Previously, the ON Error statement was preventing deletion.

I am stumped now! Can you shed anymore light.

Many Thanks.




0
JohnSaintAuthor Commented:
I think I have sorted this out. My database seems to get corrupted after I emailed it to myself.

Thanks for the advice.
0
harfangCommented:
Sorry, I don't think I can debug the "wizard generated" code. Why not run the wizard again, it that is the problem?

But please do try to move the StudenID generation to either Form_BeforeInsert or to Form_BeforeUpdate, as I suggested. This has nothing to do in the Form_Current event.

Good Luck!
0
JohnSaintAuthor Commented:
In this example, I needed the PK generation code to trigger as soon as someone moves to  a new record. I cannot allow them to mess about with the PK themselves. I am however thinking of disallowing any access to a new record at all. I will make it so the user needs to press a button to create a new record, and place the PK generation code in this section. Many thanks, though! I will probably be needing you again.

While I'm here, I have noticed something odd. I made a copy of my application and took it into the office to work on it. It bombed out trying to evaluate a null value. The thing is, that didn't happen in the version at home. What is that all about? I am going mad or is it down to library refernces or something. I can put this on aanother question if you want?

Thanks!
0
harfangCommented:
Well, I have no good idea anyway, so you might want to try with another question, sure.
Also explain "bombed out" ... :)

As for PK generation, I'm not suggesting to let people "mess" with it. In fact, the field can be disabled and locked. But as long as you leave the new record accessible, you should generate either BeforeInsert or BeforeUpdate, your choice. Of course, a custom [>*] button works as well. Simply remove .AllowAdditions for the form, add the new record when needed, refresh the form and locate the new record... A little more complex for you, that's all...

Cheers!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.