469,282 Members | 2,034 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

MS Access go to specific record when open a form vba

Hi All,

Good morning! I am working on a form that is bound to a table with user’s name. I need to be able to go to specific record base on a text box value which contain user login name, if there is no match then go to the new record for new entry. I want this to happen when the form open.

The purpose of this is due to a multi-user environment application that I want the user to see his/her profile when open the form.

Below is the code I have so far, but not working for what I am looking for. Any help would be greatly appreciated.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2. Dim db As DAO.Database
  3. Dim rs As DAO.Recordset
  5. Set db = CurrentDb
  7. Set rs = Me.Recordset
  8.     rs.FindFirst "[LonginName] = " & Me.txtLoginName
  9.     If rs.NoMatch = False Then
  10.         Me.Bookmark = rs.Bookmark
  11.     Else
  12.         DoCmd.GoToRecord acActiveDataObject, , acNewRec
  13.     End If
  14. End sub
Mar 10 '11 #1
4 5297
579 512MB
Hi Joe,

How is your code not working? Are you receiving an error message? Or is it just not giving you the expected results.

One thing I see right off the bat is that you don't have the text box encapsulated by quotations properly for the log in field. If the value in the text box that is used by the log in field in the DAO search is a string, then you have to put quotations around the text box when you write the code.

It will be hard to see when I rewrite your code below, but try to notice the extra single quotation mark I've added on either side of Me.txtLoginName below (I've bolded and underlined it to try and make it stand out):
Expand|Select|Wrap|Line Numbers
  1. rs.FindFirst "[LonginName] = '" & Me.txtLoginName & "'"
Hope this helps and good luck,
Mar 10 '11 #2
32,173 Expert Mod 16PB
A perfect answer Beacon.

Joe, you need to explain what happens in your question post. We shouldn't be needing to ask you for details of what went wrong and where.

I think Beacon managed to answer it anyway though. Good work.
Mar 11 '11 #3

My apologies for missing information. Yes, my code generate the following error msg. I am not sure what seems to be the problem.

Run-time error '3070'
The Microsoft Jet Database engine does not recognize 'LoginName' as valid name or expression.

One more piece of info is that the form I want to open has the txtLoginName to be check before opening. Any help is greatly appreciated.

Mar 11 '11 #4
32,173 Expert Mod 16PB
Joe, what happened when you tried Beacon's suggestion?
Mar 14 '11 #5

Post your reply

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

Similar topics

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