Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

DoCmd.GotoRecord problem

Posted on 2004-11-07
17
Medium Priority
?
525 Views
Last Modified: 2008-01-09
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.
0
Comment
Question by:JohnSaint
17 Comments
 
LVL 39

Expert Comment

by:stevbe
ID: 12517723
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
 
LVL 26

Expert Comment

by:dannywareham
ID: 12517725
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
 
LVL 12

Expert Comment

by:pique_tech
ID: 12517732
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:JohnSaint
ID: 12517824
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
 

Author Comment

by:JohnSaint
ID: 12518024
Increasing Points!
0
 
LVL 16

Expert Comment

by:GreymanMSC
ID: 12519648
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
 
LVL 58

Expert Comment

by:harfang
ID: 12522162
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
 

Author Comment

by:JohnSaint
ID: 12524643
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
 
LVL 58

Expert Comment

by:harfang
ID: 12525784
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
 

Author Comment

by:JohnSaint
ID: 12534588
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
 
LVL 58

Accepted Solution

by:
harfang earned 1500 total points
ID: 12535303
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
 
LVL 58

Expert Comment

by:harfang
ID: 12535362
Of course, for the very first student, it would have to be:
    Nz( DMax("[Student Code]", "TblStudents") , 0 ) + 1

Good Luck
0
 

Author Comment

by:JohnSaint
ID: 12538013
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
 

Author Comment

by:JohnSaint
ID: 12539227
I think I have sorted this out. My database seems to get corrupted after I emailed it to myself.

Thanks for the advice.
0
 
LVL 58

Expert Comment

by:harfang
ID: 12541502
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
 

Author Comment

by:JohnSaint
ID: 12543439
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
 
LVL 58

Expert Comment

by:harfang
ID: 12544412
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

804 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question