Want to jump to a record via entering a value into a text box on a form

  • Thread starter Thread starter cecil36
  • Start date Start date
C

cecil36

This has been perplexing me for a long time. I have a form which
contains all the data pertaining to a client for a ficticious stock
broker (I'm doing this for fun). I would like to navigate the client
records by using a text box and a command button. My goal is to have
the user enter in the ID number of the client, then click on the
command button to jump to the selected record and have all the data
from linked tables appear in the sub form (in this case, the client's
holdings). How would I do this?
 
Not really sure which way you trying to do it, but first off, it's suggested
that you have a FE (Front End) and a BE (Back End) DB. The BE DB file will
contain all of your tables and data while the FE will contain everything
else including Linked Tables, if you use that as most do.

Given this suggestion, that will mean the table within the same DB file will
be knocked out. The remaining 3 ways of reaching the data is via:

Linked Tables
DAO Coding
ADO Coding

Now, I don't use bound forms, so I been using DAO coding instead, though I
could use Linked Tables or ADO Coding too. Here's a DAO sample code:

Dim dwsStock as DAO.WorkSpace, ddbClient as DAO.Database, drsClient as
DAO.RecordSet

'Must create a workspace for the Database object to be in.

Set dwsStock = CreateWorkspace("Stock", "Admin", "", dbUseJet)

'Now let's create a reference to the BE DB file and open it.

Set ddbClient = dwsStock.OpenDatabase("C:\My Documents\Client.mde", False,
False)

'Now create a table type recordset of the Client Table.

Set drsClient =
ddbClient.OpenRecordset("Client",dbOpenTable,dbSeeChanges,dbPessimistic)

'Set the Index field

drsClient.Index = "fldID"

'Now use the Seek Method to find the record, which can only be used on table
type recordsets. Any of the other recordsets, you use any of the Find
methods.

drsClient.Seek "=", CStr(Me.tbxID.Value)

Of course, this code is specific to unbound forms, and that's also
dependent, if your forms are bound or unbound.
 
Cecil,

The ComboBox Wizard will build a custom combobox for you
that will do just this. Make sure your Wizards are turned on
and then drop a combobox into the Header of your Form. When
the wizard starts choose "Find a Record on My Form based on
the value I selected...". Next, if all you want is the ID
you can do that, but you will also have the choice of adding
the client's name as well which would save you looking up
the ID#. If you do the second way, choose the option to Hide
the first column and then you can type in the name even
though it will look up the ID.

Gary Miller
Sisters, OR
 
Back
Top