Link to home
Start Free TrialLog in
Avatar of orbrooks
orbrooks

asked on

Recordset.Bookmark

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
             
ASKER CERTIFIED SOLUTION
Avatar of mrmick
mrmick

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mrmick
mrmick

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

whoops, my mistake

After reading the docs, I realize you can store a bookmark in a string variable.
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
Avatar of orbrooks

ASKER

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")

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
       
       
       
       
   
   
   
   


       
   

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

Whoops, I mis-understood what your were doing, hold on a sec...
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

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