By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,365 Members | 3,095 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,365 IT Pros & Developers. It's quick & easy.

Open form on specific record via a string based field

P: 137
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
Share this Question
Share on Google+
6 Replies

Expert Mod 100+
P: 2,321
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

Expert 100+
P: 344
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

P: 137
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

P: 137
*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

Expert Mod 100+
P: 2,321
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

P: 137
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.