Reading a Table and Searching for a Value

[pcdaveh]

I'm kind of guessing on how to phrase this question. (1) How do you "I imagine", Open a recordset / table and choose a column lets say InvestorID and search for the value of 25, lets say that there are unique values or no duplicates in InvestorID? (2) Also if there are multiple occurances of the value 25 how do you return all of the instances?

 

[jebry]

Hi!

Try this:

Dim dbs as DAO.Database
Dim rst as DAO.Recordset
Dim sql as String

sql = "Select InvestorID, other columns of interest here From TableName Where InvestorID = 25"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sql, dbOpenSnapshot) or dbOpenDynaset depending on what you want to do

Do your stuff with InvestorID

Set rst = Nothing
Set dbs = Nothing

To make the code more flexible you can use the following for the sql variable:

sql = "Select InvestorId, etc From TableName Where Investor ID = " & SomeControl

I hope that is what you where looking for!

Jeff Bridgham

 

[Maquis]

How are you planning to use this?

You could have a form with a record source of a query which prompts the user for an InvestorID and then populate the form. (Select * from table with investorId = [Enter Investor])

Or perhaps you could have a find button on an already existing form which could goto the records matching your investor id. The wizard can create a find button for you.

Or, no forms just a parameter query. Maq B-)
<insert witty signature here>

 

[pcdaveh]

I have a table that has an autonumber field. I always want the autonumber to begin with the number 1. So if that record has been deleted. It halts the operation of the program. The form forces the user to delete the records and compact the data base so that the counter resets itself. &quot;By the way this may be useful to some of you. Compacting a Database resets Autonumbering sequencing if the data in the table has be deleted&quot;. So I'm in reality searching for the value of 1 in the autonumber field. If my code can't find that value. The form never loads preventing the operation of the database at the form level.