Solved

Recordset.Bookmark

Posted on 1998-02-05
10
190 Views
Last Modified: 2006-11-17
I have a tutorial program that consists of many forms which are all linked by control buttons ( next page, previous page, etc.).  I have a password textbox linked to a data control which querries and saves data to an Access database.
when the user enters the correct password, a time/date stamp in entered in the database (and corresponding textboxes) and the program opens.  Their passwords are "saved" are bookmarked by the following code:

       Dim CurrentPassword as String
       Current = Data1.Recordset.Bookmark
       Data1.Recordset.Edit
     
             If Data1.Recordset!TimeIn1 = "TimeIn" Then
                    Data1.Recordset!TimeIn1 = Now
                    Data1.Recordset.Update
                    NextForm Form1, FrmEntry
              Else
                   and so on . . . . .


.  When the user exits the program, they are prompted to enter their password once again and another time/date stamp is entered in the data base (and corresponding textboxes) using similiar code.  I have a special password to access the textboxes which are linked to the data control and which allow me to add/delete passwords and view the time/date stamps for each password.  This system allows me to keep track of the time the students spend on the program.  What I want to do is keep track of how much of the tutorial program the students complete.  I have attempeted this by adding code to the exit button for select parts of the program.  When they exit the comand button executes the following code:
    CurrentPassword = Form1.Data1.Recordset.Bookmark
    Form1.Data1.Recordset.Edit
    Form1.Data1.Recordset!Lesson1 = "Yes"
    Form1.Data1.Recordset.Update
  And on the next form, etc. . . .

By viewing the texboxes corresponding to the various lessons, I can tell which students have completed which lessons.  At long as their is only one password, this works great.  However, when I enter all the students passwords into the database, the program can't keep track of the passwords with the Data1.Recordset.Bookmark.  Instead,  the program assigns the "Yes" for the completed lesson to whichever password is at the "top" of the list in the database instead of searching out the password which is in use.  I've tried a lot of different ways to get the program to "remember" the password but to no avail.  Help!  Please bear in mind that I am a newbie programmer so keep you explanations simple or provide some code if possible. I'm sorry for such a long explanation but I wanted to communicate clearly what works and what doesn't.  I'm sure there is a simple solution for this problem.  Thank you in advance for you help.

Randy
orbrooks@garlic.com
             
0
Comment
Question by:orbrooks
  • 7
  • 2
10 Comments
 
LVL 8

Accepted Solution

by:
mrmick earned 100 total points
ID: 1455918
Try

With Form1.Data1.Recordset
 .Bookmark = Current
 .Edit
 !Lesson1 = "Yes"
 .Update
End With
0
 
LVL 8

Expert Comment

by:mrmick
ID: 1455919
One other point, you should store the bookmark in a Variant, not a String because a bookmark is actuall a variant containing a byte array.  For example,

Dim Current as Variant

Current = Data1.Recordset.Bookmark

0
 
LVL 9

Expert Comment

by:cymbolic
ID: 1455920
whoops, my mistake

After reading the docs, I realize you can store a bookmark in a string variable.
0
 
LVL 8

Expert Comment

by:mrmick
ID: 1455921
That code did not work.  The code is located on a form different from the form that contains data1 control.  The debug error 13 says that the Form1.Data1.Recordset.Bookmark = empty on the form where the code is located.  The problem is that the program is not saving the bookmark.  Would it help if I sent you the code from the Form1 (where the password is entered) and from the form where I'm trying to call the bookmark.  

Randy
0
 

Author Comment

by:orbrooks
ID: 1455922
Randy, If "current" is not declared as a public variable, it is out of scope in all other forms/modules.  There are a couple of solutions available to you:

1) Declare the variable as public.  For example:

Public pubCurrent As String ' In a code module

Note: Add the pub prefix and change all "current" references to include the prefix.

2) Create a procedure in the form/module where Current is defined (must be the same form/module where the bookmark is saved).  The procedure might look like this:

Sub CheckOut(strLesson as string)
With Form1.Data1.Recordset
 .Bookmark = Current
 .Edit
 .Fields(strLesson) = "Yes"
 .Update
End With

You would call CheckOut from within Lesson1 like this:
  Form1.CheckOut("Lesson1")

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 8

Expert Comment

by:mrmick
ID: 1455923
mrmick. I tried you code and it improved the situation but it still doesn't work.  Added the checkOut code to the module for the program which contains lots of subs.  When the sub checkout is activated I get a run time error '3159' that says "not a valid bookmark".  When I debug the following line is highlighted:

                 .Bookmark = pubCurrentPassword

  If I enter the cursor into the line somewhere and then click on the run button to continue the program, the rest works fine and the Lesson1 textbox gets the "yes".  After this, it may work fine with the other passwords until I exit and reenter the program.  Well, we're almost there.  In case it helps, here is the code associated with entering the password:

Public Sub Enter_Click()

Enter.Visible = False
Dim Template As String

Template = InputBox("Enter your Password")
 
 
 
 
           
            If Template = "Master" Then
            TitleLblEntry.Top = 750
 TitleLblEntry.Visible = False
 TitleLblEntrysub.Visible = False
        Command1(0).Visible = True
        Command1(1).Visible = True
        Command1(2).Visible = True
        Command1(3).Visible = True
        Command1(4).Visible = True
        Data1.Visible = True
        Text1.Visible = True
        TimeIn(0).Visible = True
TimeIn(1).Visible = True
TimeIn(2).Visible = True
TimeIn(3).Visible = True
TimeIn(4).Visible = True
TimeIn(5).Visible = True
TimeIn(6).Visible = True
TimeIn(7).Visible = True
TimeIn(8).Visible = True
TimeIn(9).Visible = True
TimeOut(0).Visible = True
TimeOut(1).Visible = True
TimeOut(2).Visible = True
TimeOut(3).Visible = True
TimeOut(4).Visible = True
TimeOut(5).Visible = True
TimeOut(6).Visible = True
TimeOut(7).Visible = True
TimeOut(8).Visible = True
TimeOut(9).Visible = True
        Label1.Visible = True
        Label2.Visible = True
        Label3.Visible = True
        Enter.Visible = False
     
  Label4(0).Visible = True
  Label4(1).Visible = True
  Label4(2).Visible = True
  Label4(3).Visible = True
  Label4(4).Visible = True
  Label4(5).Visible = True
  Label4(6).Visible = True
  Label4(7).Visible = True
  Label4(8).Visible = True
  Label4(9).Visible = True
  Label4(10).Visible = True
  Label4(11).Visible = True
  Label4(12).Visible = True
  Label4(13).Visible = True
  Label4(14).Visible = True
 
  Text2(0).Visible = True
  Text2(1).Visible = True
  Text2(2).Visible = True
  Text2(3).Visible = True
  Text2(4).Visible = True
  Text2(5).Visible = True
  Text2(6).Visible = True
  Text2(7).Visible = True
  Text2(8).Visible = True
  Text2(9).Visible = True
  Text2(10).Visible = True
  Text2(11).Visible = True
  Text2(12).Visible = True
  Text2(13).Visible = True
  ExitProgram.Visible = False
 
 
 Else
   

   
   
   
    Template = "[StudentPasswords]= " & Chr$(34) & Template & Chr$(34)
    Data1.Recordset.FindFirst Template
   
    If Data1.Recordset.NoMatch = True Then
        MsgBox ("Invalid Password")
       
        End
       
    Else
   
     If Data1.Recordset.NoMatch = False Then
                 
         
               pubCurrentPassword = Data1.Recordset.Bookmark
                    Data1.Recordset.Edit
                   
                     
                    If Data1.Recordset!TimeIn1 = "TimeIn" Then
                    Data1.Recordset!TimeIn1 = Now
                    Data1.Recordset.Update
                    NextForm Form1, FrmEntry
                   
             Else
                 
                   If Data1.Recordset!TimeIn2 = "TimeIn" Then
                    Data1.Recordset!TimeIn2 = Now
                    Data1.Recordset.Update
                    NextForm Form1, FrmEntry
                   
                   
            Else
                 
                   If Data1.Recordset!TimeIn3 = "TimeIn" Then
                    Data1.Recordset!TimeIn3 = Now
                    Data1.Recordset.Update
                    NextForm Form1, FrmEntry
                   
            Else
                 
                   If Data1.Recordset!TimeIn4 = "TimeIn" Then
                    Data1.Recordset!TimeIn4 = Now
                    Data1.Recordset.Update
                    NextForm Form1, FrmEntry
                   
            Else
                 
                   If Data1.Recordset!TimeIn5 = "TimeIn" Then
                    Data1.Recordset!TimeIn5 = Now
                    Data1.Recordset.Update
                    NextForm Form1, FrmEntry
                   
            Else
                 
                   If Data1.Recordset!TimeIn6 = "TimeIn" Then
                    Data1.Recordset!TimeIn6 = Now
                    Data1.Recordset.Update
                    NextForm Form1, FrmEntry
                   
           Else
                 
                   If Data1.Recordset!TimeIn7 = "TimeIn" Then
                    Data1.Recordset!TimeIn7 = Now
                    Data1.Recordset.Update
                    NextForm Form1, FrmEntry
                   
            Else
                 
                   If Data1.Recordset!TimeIn8 = "TimeIn" Then
                    Data1.Recordset!TimeIn8 = Now
                    Data1.Recordset.Update
                     NextForm Form1, FrmEntry
                     
            Else
                 
                   If Data1.Recordset!TimeIn9 = "TimeIn" Then
                    Data1.Recordset!TimeIn9 = Now
                    Data1.Recordset.Update
                    NextForm Form1, FrmEntry
           
          Else
                 
                   If Data1.Recordset!TimeIn10 = "TimeIn" Then
                    Data1.Recordset!TimeIn10 = Now
                    Data1.Recordset.Update
                    NextForm Form1, FrmEntry
                   
            Else
                 
                   If Data1.Recordset!TimeIn10 <> "TimeIn" Then
                    NextForm Form1, FrmEntry
                    Form1.Data1.Recordset.Bookmark = pubCurrentPassword
         End If
             End If
             End If
             End If
             End If
             End If
             End If
             End If
             End If
             End If
             End If
             End If
             
           
           
               
           
             End If

   
End Sub


The first part is all about what happens when you enter "Master" as a password.  This shows all the textboxes and such associated with the data control.  If you don't enter "master", it evaluates your password.  I know it must be something really simple at this point and thank you in advance for all your help.

Randy
       
       
       
       
   
   
   
   


       
   

0
 

Author Comment

by:orbrooks
ID: 1455924
Randy,

After you've found the match, use this:

If Data1.Recordset.NoMatch = False Then
 Data1.Recordset.MoveFirst
 pubCurrentPassword = Data1.Recordset.Bookmark
 Data1.Recordset.Edit
 etc...

0
 
LVL 8

Expert Comment

by:mrmick
ID: 1455925
Whoops, I mis-understood what your were doing, hold on a sec...
0
 
LVL 8

Expert Comment

by:mrmick
ID: 1455926
Randy, scratch that last suggestion.  The FindFirst makes the first match (if found) the current record.  From what I'm looking at, I don't know.

Have you tried setting a breakpoint and verifying that the bookmark has been assigned?

Did you declare the pubCurrentPassword as follows:

Public pubCurrentPassword As String

???
0
 
LVL 8

Expert Comment

by:mrmick
ID: 1455927
mrmick,

Thank you for your help.  The program still doesn't work but at least it's closer to working.  The problem is still that the bookmark is not saved when switching from one from to another.  I placed text boxes in each form with code so the text for each textbox = the password which is currently up.  After a couple of froms, the password goes to the default value (the top of the list of passwords).  When I call the bookmark, the error implies that either the password was not saved for that the bookmarked password doesn't contain any records.  There must be something wrong with my code somewhere but it's a pretty complex mess and I'm not experienced with databases.

Randy
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

759 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now