Jump to a Specific Record 3

[tnago]

Hi

After reading some of the other threads on this issue I tried to do use the following code to select or search a specific record from the database which can be viewed or edited by the user. However, I am unable to make it work. Can someone guide what I am doing wrong.
-------------------------------------------------------
Private Sub Text150_Change()
Me.Filter = "IR = '" & Me.Text150 & "'"
Me.FilterOn = True
If Not Me.RecordsetClone.EOF Then
Me.RecordsetClone.MoveLast
End If
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records found matching the criteria"
End If
End Sub
-----------------------------------------------------------
Basically I have textbox in which user can input the id which can be in lower or upper case once the id has been put then the form loads the record.

 

[jfgambit]

Get rid of the code and replace it with:

Dim rsID as Variant

rsID = DLookup("[ID]","TableName","[ID] = '" & Me.Text150 & "'"
If IsNull(rsID) or rsID = "" then
Msgbox "No records found matching the criteria",vbokonly,"No Record"
Else
Docmd.OpenForm "FormName",,,,"[ID] = '" & me.text150 & "'"
End if

Change the Form Name to the name of your form and Table name to the name of the Table that the ID resides in.




"I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?"

 

[jrbarnett]

Take it out of the change event (which is triggered every time a character is put in) and put it in the AfterUpdate event instead.
Also ensure the textbox isn't bound to any particular field.

John

 

[jrbarnett]

Revised code:

rsID = DLookup("[ID]","TableName","[ID] = '" & Me.Text150 & "'&quot

John

 

[tnago]

Hi JrBarnett

I have made the changes as indicated however, I am not getting any results.
-------------------------------------------------------
Private Sub Text150_AfterUpdate()
Dim rsID As Variant

rsID = DLookup("[ID]", "INCIDENTS", "[ID] = '" & Me.Text150 & "'&quot
If IsNull(rsID) Or rsID = "" Then
MsgBox "No records found matching the criteria", vbOKOnly, "No Record"
Else
DoCmd.OpenForm "AuditWorld", , , , "[ID] = '" & Me.Text150 & "'"
End If
End Sub
-----------------------------------------------------------
As per your instruction I have change the event to AfterUpdate and change the TableName as well the FormName. The textbox is unbounded.
Any suggestion. Thanks for all your help.

 

[jfgambit]

Oops...my bad to many commas....

Replace:
DoCmd.OpenForm "AuditWorld", , , , "[ID] = '" & Me.Text150 & "'"

With
DoCmd.OpenForm "AuditWorld", , , "[ID] = '" & Me.Text150 & "'"

Sorry...t..o...mu..ch...coff...e..e



"I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?"

 

[jfgambit]

Also...check out this post, I attached an example:

http://www.access-programmers.co.uk/forums/showthread.php?s=&postid=178710#post178710

"I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?"

 

[tnago]

Hi JrBarnett

Thanks for your help on this particular problem. Please accept 2 stars for your time and effort. Specially the sample database was very helpful in clearing any question that I had.

 

[judgehopkins]

jfgambit, I went to your link, downloaded the form and thought it was pretty nifty.

I also asked this question:

On your form, what code would I use if I wanted it to find a phone number, but I just knew part of it?



Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.

 

[jfgambit]

Judge:

See this post....

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=47551

"I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?"

 

[judgehopkins]

Thanks!

Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.

 

[tslee]

Hi,

I have a similar problem to search a specific record from the database with 500+ records. The requirement is ,however, the user to type in id in combo box instead of scroll down to search 500+ records.


Does anyone know if there is a quick way to do this?

Does this code also apply to my case? If yes, where should I put this in?

----------------------------------------------------------
Private Sub Text150_AfterUpdate()
Dim rsID As Variant

rsID = DLookup("[ID]", "INCIDENTS", "[ID] = '" & Me.Text150 & "'&quot
If IsNull(rsID) Or rsID = "" Then
MsgBox "No records found matching the criteria", vbOKOnly, "No Record"
Else
DoCmd.OpenForm "AuditWorld", , ,"[ID] = '" & Me.Text150 & "'"
End If
End Sub
-----------------------------------------------------------

 

[jbehrne]

Hi,

You can try this as an alternative to the dlookup. Should work just as well:

Me.RecordsetClone.FindFirst "[ID] = '" & Me.Text150 & "'"

If Me.RecordsetClone.NoMatch Then
MsgBox "No matching Vendors found! Please enter a valid vendor name.", vbOKOnly, "Value Not Found"
Me!Text150 = ""
Me!Text150.SetFocus
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
Me!Text150 = ""
Me!Text150.SetFocus
End If

If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations