Simple question: How to find a particular record?

[InterGladiator]

Hi,

I am new to program Access by Visual Basic. Please help me to solve the following task.

In my database I have a datatable containing lots of orders identified by their unique order numbers. I also have a form that contains three objects: a text box, a push button and a subform that shows the orders (order numbers, order dates etc.) in datasheet view. I would like the form work like this:

1) User enters an order number into the text box.
2) User clicks the search push button.
3) The subform jumps to the record containing the order number entered into the text box if founds it or displays "not found" error message if does not.

Please write me the simplest possible code to include in the Click Event of the push button. I guess I should use the Dataset.Seek method but I don't know how.

Thank you in advance,
Attila

 

[modest]

Make the subform based on a query.

Perform a search here on "SQL" and "WHERE clause" to discover how to find the order, for instance:

SELECT [Column Names] FROM [Table Name] WHERE [Order Number]= forms![Form Name]![Text Box Name on Form]

 

[InterGladiator]

Thank you, that would be a good solution, but I need all orders to be shown on that subform. In the very most cases users will browse those orders one by one but I want to give them an extra option to quickly position the cursor to a certain order. With your solution all but that one records would be filtered out - if I understand well.

 

[InterGladiator]

Thank you, that would be a good solution, but I need all orders to be shown on that subform. In the very most cases users will browse those orders one by one but I want to give them an extra option to quickly position the cursor to a certain order. With your solution all but that one records would be filtered out - if I understand well.

 

[Kelemit]

do something along the same lines. code for on click.
(Things in () are explanations, don't put them in the code)

***Subformname is the NAME GIVEN by the main form. Highlight the subform and find out what its name is

if Txtboxname & "" = "" then    (If the txt box is empty)
    me.subformname.recordsource = "SELECT tablename.* FROM Tablename ORDER BY tablename.fieldyouwanttoorder;"   

else
  me.subformname.recordsource = "SELECT tablename.* FROM Tablename WHERE (((Tablename.Ordernumber)=" & txtboxname & ")) ORDER BY Tablename.fieldyouwanttoorder;"
endif

With this code, whenever the search button is clicked, the information will be displayed depending on the txtbox number. If the txtbox is empty, it will show everything, if the txtbox is full, it will only show that record.

You may want to make a "clear" button so that they can empty the txt box with a push of the button and reset the form to show all records.

IE: Clear button_onclick

txtboxname = ""
me.subformname.recordsource = "SELECT tablename.* FROM Tablename ORDER BY tablename.fieldyouwanttoorder;"

 

[InterGladiator]

Thank you Kelemit, this is an interesting solution.

However, I still curious about my idea: can I just somehow reposition the cursor? I guess there must be something like mydatabase.mytable.seek("=","thenameofhtefieldtoIlookin","thevalueIlookfor") ... Could you please show me something like this?

On the other hand, I will have to write a macro that makes certain operations with certain records. For this my code will have to step thorugh one by one on a number of records. How to step onto the next record from VB code? (At all, how can I refer to a table from VB code? I don't know.)

 

[InterGladiator]

Any other solution? (please see my previous reply)
Now I see the problem is I don't know how to refer to a data table from VB code. Could any of you, please, help?

 

[birgerii]

Have u looked into the Find-method?

 

[InterGladiator]

Yes, but I found no working sample code. And I cannot find a "table" object in VB Access object model. How to use these?