Open form on specific record via a string based field

[Adam Tippelt]

I've got a form that allows users to "save the current record to drafts" if they haven't finished writing all the data for that record. This saves it in it's current state, which puts it in a temporary table. The record is also tagged with the name of the user who created it.

What I'm trying to do is to force the form to open up and load this record when the form is loaded/opened. The structure of the database only allows users to have one draft at once, as it should be dealt with next time the form is opened, so there should only be 1 record in this temporary table that is specific to that user.

I've tried doing:

Dim rst As DAO.Recordset
Dim strCriteria As String
 
Set rst = Me.RecordsetClone
strCriteria = ("[LastEditedBy]='" & GUsername & "'")
 
rst.FindFirst (strCriteria)
If rst.NoMatch Then
    MsgBox "No entry found."
Else
   Me.Form.Bookmark = Me.RecordsetClone.Bookmark
End If

but this just always returns the "no entry found" message, and then goes to a new record.

I've also tried sticking the criteria into the code for opening the form from the main menu, but again that just opens it to a new record.

I have disabled any old code on the On_Load event that used to tell the form to go to a new record.

Can anyone see something wrong with the code I've used?
Can FindFirst only be used for numerical values? If so, is there an alternative for string based fields?


This seems like such a stupidly simple thing to be stuck on, and yet I can't figure it out!

Thanks.

Adam.

[TheSmileyCoder]

Question, You say it always goes to a new record, but do you always get the msgbox of No Entry Found?

If this is called locally from your form, you could use:
Me.RecordSet.FindFirst strCriteria
instead of going through the recordsetclone (unless you have some reason not shown here)

[Lysander]

Where is GUsername coming from?

Have you tried single stepping through the code and checking that strCriteria holds the correct value?

[Adam Tippelt]

Yes it always gives the No Entry Found msgbox.

Me.RecordSet.FindFirst strCriteria just took me to a new record.

GUsername is a global variable that captures the name of the user when they log in.

Trying to step through code doesn't appear to do anything - I've not really done much debugging with code stepping, so not sure what I should expect to see/do when I click Step Into, or whether I need to 'prep' the code for debugging, so to speak (add breaks etc.).

[Adam Tippelt]

*sigh* Just found a solution and it was as stupidly obvious as I thought it would be.

Private Sub Form_Load()
Me.Filter = "[LastEditedBy]='" & GUsername & "'"
Me.FilterOn = True

[TheSmileyCoder]

As far as I can tell your first code should work.

Remember however that when your using a variable such a GUserName that if you meet an unhandled error, the GUserName will loose its value.

Therefore to check that it has a value (When debugging), add on the line before either:

Debug.Print "GUserName:" & GUSerName

Or

Msgbox "GUserName:" & GUSerName

I have a way to solve the problem of the variable loosing its value, if you want it.

[Adam Tippelt]

The main menu for my database has a field that holds the name of the current user, AKA GUsername. I've been using that as an indication of whether the global variables have lost their values as the Current User field goes blank.
I always go back to the main menu when testing new functionality, firstly to check that the global variables are still holding their values, and secondly to make sure that the form operates correctly, and I don't encounter any unusual behaviour by going straight from Design View to Form View.

I don't know why the first code didn't work, as to me it made logical sense, and I've used similar code on a different form which didn't have any problems. Weird :S