retrieving data by entering item number on a form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sunev - 26 Oct 2005 15:53 GMT

I have no experience on Database building, but I need to create one so that I
can make my job easier and more accurate. Although Access is pretty self
explanatory I am having problems with the way I want things to work in my
database. One of my main concerns is retrieving information for a particular
record just by entering that record or item number, on the search field or
Item # on my form. I don't want the from to display any record unless an I
tem number is giveng and you hit the enter button or another specified key.
Please HELP!!!!!

Reply to this Message

Rich Wills - 26 Oct 2005 17:22 GMT

Private Sub MyField_AfterUpdate()
Dim MyO As Object
Set MyO = Me.Recordset.Clone
MyO.FindFirst "[ID] = " & Me.MyField
Me.Bookmark = MyO.Bookmark
End Sub

Hey Suney

The above will go to a record based on a NUMBER that you enter in the field.
You will have to change the "MyField" to whatever your field name is that you
are entering the data you want to use to navigate, and you will have to
change the "[ID]" to what ever FIELD Name you are looking to search in. For
example my code is based on a textbox called "MyField" and within my table of
information I will be searching for all the records which are the same as
"MyField" in the Field Name of "ID".

I hope this is ok for you.

Let me know how it goes

Rich

>I have no experience on Database building, but I need to create one so that I
>can make my job easier and more accurate. Although Access is pretty self
[quoted text clipped - 4 lines]
>tem number is giveng and you hit the enter button or another specified key.
>Please HELP!!!!!

Reply to this Message

Sunev - 26 Oct 2005 18:31 GMT

Dear Rich:

I tried two times the first with the Combo Box and the second one withouh,
but nothing seems to work here is the error I am getting:
Compiled Errro:
Sub or Funtion not Defined!

Private Sub Text242_AfterUpdate()
Dim MyO As Object
Set MyO = Me.Recordset.Clone
MyOFindFirst "[ID] = " & Me.ItemNumber
Me.Bookmark = MyO.Bookmark
End Sub

Private Sub Text242_AfterUpdate()
Dim MyO As Object
Set MyO = Me.Recordset.Clone
MyOFindFirst "[ID] = " & Me.Text242
Me.Bookmark = MyO.Bookmark
End Sub

Text 242 is the name of the combo box and item number is the name of the
field we are trying to draw records from. Also I am Getting a Compile Error:

> Private Sub MyField_AfterUpdate()
> Dim MyO As Object
[quoted text clipped - 27 lines]
> >tem number is giveng and you hit the enter button or another specified key.
> >Please HELP!!!!!

Reply to this Message

John Vinson - 26 Oct 2005 23:39 GMT

> MyOFindFirst "[ID] = " & Me.ItemNumber

Should be

MyO.FindFirst

You've left out an important period.

John W. Vinson[MVP]

Reply to this Message

John Vinson - 26 Oct 2005 21:53 GMT

Since Access Tables don't HAVE "item numbers" or "record numbers",
your request may require some restating.

Do you have a field in your table - perhaps an Autonumber - which you
are using as an "item number"? If so, you can use the Combo Box Wizard
to create a combo box to retrieve a record based on the selected value
from the combo.

John W. Vinson[MVP]

Reply to this Message