Alternative to recordset bookmark?

[deko]

I have an Access 2003 mdb using DAO. I have a form with a listbox, some
textboxes, and a few subforms. The Link Child/Link Master fields are set
linking the subforms to the main form, so subforms follow the Customer_ID on
the main form.

When a user clicks on a customer listed in the listbox, I want the
corresponding data in the subforms to move to the record that was selected
in the listbox. So I've been using this:

Dim rst As DAO.Recordset
Dim strCust As String
strCust = "Customer_ID = " & Me!lstCustomer
Set rst = Me.RecordsetClone
rst.FindFirst strCust
If rst.NoMatch Then
MsgBox " Customer Not Found"
Else
Me.Bookmark = rst.Bookmark
End If

Is there any other way to do this? The problem is the rst is getting kind
of big.

I suppose I could assign a new RecordSource to the main form which contains
only the selected record, but then users couldn't use PageUp/PageDown to
scroll through the database... anyway, looking for alternatives...

Thanks in advance.

 

[Dirk Goldgar]

I have an Access 2003 mdb using DAO. I have a form with a listbox, some
textboxes, and a few subforms. The Link Child/Link Master fields are set
linking the subforms to the main form, so subforms follow the Customer_ID
on the main form.

When a user clicks on a customer listed in the listbox, I want the
corresponding data in the subforms to move to the record that was selected
in the listbox. So I've been using this:

Dim rst As DAO.Recordset
Dim strCust As String
strCust = "Customer_ID = " & Me!lstCustomer
Set rst = Me.RecordsetClone
rst.FindFirst strCust
If rst.NoMatch Then
MsgBox " Customer Not Found"
Else
Me.Bookmark = rst.Bookmark
End If

Is there any other way to do this? The problem is the rst is getting kind
of big.

What do you mean by "big"? The code you posted is the standard way to do
this, and it should run very quickly if the Customer_ID field is indexed.
If the main form's recordset is big, then I would guess that the number of
rows in the list box is also getting very big, possibly bordering on
unmanageability unless you've used some tricks to filter it on the fly.

I suppose I could assign a new RecordSource to the main form which
contains only the selected record, but then users couldn't use
PageUp/PageDown to scroll through the database... anyway, looking for
alternatives...

Can we take it the main form also displays data for the customers, so you do
in fact have to position it to the selected record? If the main form is
only used to host the list box and the subforms, you don't need to bind the
main form to a table or query at all -- you can just use the list box as the
Link Master Field for your subforms, eliminating any need to navigate the
main form. But if the main form has to show data for each record, you can't
do that.

 

[deko]

Hi and thanks for the reply.

What do you mean by "big"? The code you posted is the standard way to do
this, and it should run very quickly if the Customer_ID field is indexed.

Yes, Customer_ID is indexed.

If the main form's recordset is big, then I would guess that the number of
rows in the list box is also getting very big, possibly bordering on
unmanageability unless you've used some tricks to filter it on the fly.

that's basically the problem - the list is getting too big. I guess I could
put a 'Next' button on there somewhere and throttle it to 1000 or something
(?)

Can we take it the main form also displays data for the customers, so you
do in fact have to position it to the selected record?

The main form does display Customer data.

The other thing that I'm thinking about is upgrading to Office 2007. Will
that code work with Office 12? As far as I know, MS has no plans to abandon
DAO...

 

[Paul Shapiro]

When the number of rows gets too big to perform adequately, you can change
the way your form works. Instead of loading all the customers, use a record
source like Select * From Customers Where 1=0, so no rows are selected. When
the user chooses a customer from the find-a-row combo box, update the form's
record source to "Select * From Customers Where customerID=" &
Me.cboFind.Value, to load just the one selected customer. You can also add
code to the combo box so it doesn't load the customer list until the user
has typed at least 2 or 3 characters. Then you assign a row source to the
combo box that includes the filter for the characters already entered, so
the list will be a manageable length.

 

[Dirk Goldgar]

that's basically the problem - the list is getting too big. I guess I
could put a 'Next' button on there somewhere and throttle it to 1000 or
something (?)

See Paul Shapiro's post for ideas about how to deal with this situation.
You can do the same thing with a text box/list box combination.

The other thing that I'm thinking about is upgrading to Office 2007. Will
that code work with Office 12? As far as I know, MS has no plans to
abandon DAO...

The code will work fine in Access 2007. That is, so long as you set things
up properly, security-wise, to allow VBA code to run.