469,167 Members | 1,976 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,167 developers. It's quick & easy.

Open form on specific record via a string based field

137 100+
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:

Expand|Select|Wrap|Line Numbers
  1. Dim rst As DAO.Recordset
  2. Dim strCriteria As String
  4. Set rst = Me.RecordsetClone
  5. strCriteria = ("[LastEditedBy]='" & GUsername & "'")
  7. rst.FindFirst (strCriteria)
  8. If rst.NoMatch Then
  9.     MsgBox "No entry found."
  10. Else
  11.    Me.Form.Bookmark = Me.RecordsetClone.Bookmark
  12. 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!


Mar 9 '11 #1
6 2892
2,321 Expert Mod 2GB
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)
Mar 9 '11 #2
344 Expert 100+
Where is GUsername coming from?

Have you tried single stepping through the code and checking that strCriteria holds the correct value?
Mar 9 '11 #3
Adam Tippelt
137 100+
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.).
Mar 9 '11 #4
Adam Tippelt
137 100+
*sigh* Just found a solution and it was as stupidly obvious as I thought it would be.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. Me.Filter = "[LastEditedBy]='" & GUsername & "'"
  3. Me.FilterOn = True
Mar 9 '11 #5
2,321 Expert Mod 2GB
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:
Expand|Select|Wrap|Line Numbers
  1. Debug.Print "GUserName:" & GUSerName
Expand|Select|Wrap|Line Numbers
  1. Msgbox "GUserName:" & GUSerName
I have a way to solve the problem of the variable loosing its value, if you want it.
Mar 9 '11 #6
Adam Tippelt
137 100+
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
Mar 9 '11 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.