Combo Box on form

B

BillD

I have a database with 120,000 records. I use a View/edit form to look up
records. I have an unbound combo box at the top of the Detail section of the
Form that I use to lookup a name. When I select the name in the Combo the
form fills with the persons info.The combo lists everyone's name which is
great if I have say under 62,000 names but when the database has over 65,000
names then the box only lists up to say 62,000 records. In this case I can't
scroll down past the 62,000 record number to select a record. I would like to
know if there is an easy way that I can limit the records that the Combo box
shows. ie.-Say I wanted to show names that start with S.
Is there an easy way to do this?
In the row source I have:
SELECT DISTINCTROW VoterInformationTable.ID, VoterInformationTable.[Last
Name], VoterInformationTable.[First Name], VoterInformationTable.[Middle
Name], VoterInformationTable.City, VoterInformationTable.[St#],
VoterInformationTable.Street, VoterInformationTable.[Street Type],
VoterInformationTable.[Apt#], VoterInformationTable.Poll,
VoterInformationTable.[Enum#], VoterInformationTable.[Home Phone] FROM

In the event After update I have:
Sub Combo48_AfterUpdate()
' Find the record that matches the control.'
If IsNull(Me![Combo48]) Then End
Me.RecordsetClone.FindFirst "[ID] = " & Me![Combo48]
Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub

Any help greatly appreciated.
Bill D.
 
B

BillD

Thanks for the suggestion. Allen Browne's setup seems complicated. I am not a
programmer. Dev Ashish & Arvin Meyer have published an article called "Forms:
Limit Content of Combo/List Boxes"
http://www.mvps.org/access/forms/frm0028.htm
This seems easier but I have not been able to set it up properly yet.
My current combo box that I use to look up names is called "Combo48" The
Control Source is empty, the Row Source Type is Table/Query and the Row
Source is:
"SELECT DISTINCTROW VoterInformationTable.ID, VoterInformationTable.[Last
Name], VoterInformationTable.[First Name], VoterInformationTable.[Middle
Name], VoterInformationTable.City, VoterInformationTable.[St#],
VoterInformationTable.Street, VoterInformationTable.[Street Type],
VoterInformationTable.[Apt#], VoterInformationTable.Poll,
VoterInformationTable.[Enum#], VoterInformationTable.[Home Phone] FROM"
Which method do you think would work the best?

Cheese_whiz said:
Hi BillD,

I suggest you take a look at this:
http://www.allenbrowne.com/ser-32.html

Allen has some excellent tips.

HTH,
CW

BillD said:
I have a database with 120,000 records. I use a View/edit form to look up
records. I have an unbound combo box at the top of the Detail section of the
Form that I use to lookup a name. When I select the name in the Combo the
form fills with the persons info.The combo lists everyone's name which is
great if I have say under 62,000 names but when the database has over 65,000
names then the box only lists up to say 62,000 records. In this case I can't
scroll down past the 62,000 record number to select a record. I would like to
know if there is an easy way that I can limit the records that the Combo box
shows. ie.-Say I wanted to show names that start with S.
Is there an easy way to do this?
In the row source I have:
SELECT DISTINCTROW VoterInformationTable.ID, VoterInformationTable.[Last
Name], VoterInformationTable.[First Name], VoterInformationTable.[Middle
Name], VoterInformationTable.City, VoterInformationTable.[St#],
VoterInformationTable.Street, VoterInformationTable.[Street Type],
VoterInformationTable.[Apt#], VoterInformationTable.Poll,
VoterInformationTable.[Enum#], VoterInformationTable.[Home Phone] FROM

In the event After update I have:
Sub Combo48_AfterUpdate()
' Find the record that matches the control.'
If IsNull(Me![Combo48]) Then End
Me.RecordsetClone.FindFirst "[ID] = " & Me![Combo48]
Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub

Any help greatly appreciated.
Bill D.
Click to expand...
Click to expand...
 
R

Rastro

I suggest to filter the query in the combo48
Like this:
"SELECT DISTINCTROW VoterInformationTable.ID,
VoterInformationTable.[Last Name],
VoterInformationTable.[First Name], VoterInformationTable.[Middle
Name], VoterInformationTable.City, VoterInformationTable.[St#],
VoterInformationTable.Street, VoterInformationTable.[Street Type],
VoterInformationTable.[Apt#], VoterInformationTable.Poll,
VoterInformationTable.[Enum#], VoterInformationTable.[Home Phone]
FROM VoterInformationTable
Where (Left(VoterInformationTable.[Last Name],1)
=[Forms].[YourForm].[ComboFirstLetter] ;

Now you have a filter for the selected letter on the other combo, ex.
"ComboFirstLetter" with this query:

SELECT Left(VoterInformationTable.[Last Name],1) AS FirstLetter,
Count(Left([VoterInformationTable.Last Name],1)) AS CountOfFirstLetter
FROM VoterInformationTable
GROUP BY (Left([VoterInformationTable.Last Name],1))
ORDER BY (Left(VoterInformationTable.[Last Name],1));

Next add this code to the afterupdate event on the ComboFirstLetter
Combo45.Requery

I hope this works

Rastro


BillD said:
Thanks for the suggestion. Allen Browne's setup seems complicated. I am
not a
programmer. Dev Ashish & Arvin Meyer have published an article called
"Forms:
Limit Content of Combo/List Boxes"
http://www.mvps.org/access/forms/frm0028.htm
This seems easier but I have not been able to set it up properly yet.
My current combo box that I use to look up names is called "Combo48" The
Control Source is empty, the Row Source Type is Table/Query and the Row
Source is:
"SELECT DISTINCTROW VoterInformationTable.ID, VoterInformationTable.[Last
Name], VoterInformationTable.[First Name], VoterInformationTable.[Middle
Name], VoterInformationTable.City, VoterInformationTable.[St#],
VoterInformationTable.Street, VoterInformationTable.[Street Type],
VoterInformationTable.[Apt#], VoterInformationTable.Poll,
VoterInformationTable.[Enum#], VoterInformationTable.[Home Phone] FROM"
Which method do you think would work the best?

Cheese_whiz said:
Hi BillD,

I suggest you take a look at this:
http://www.allenbrowne.com/ser-32.html

Allen has some excellent tips.

HTH,
CW

BillD said:
I have a database with 120,000 records. I use a View/edit form to look
up
records. I have an unbound combo box at the top of the Detail section
of the
Form that I use to lookup a name. When I select the name in the Combo
the
form fills with the persons info.The combo lists everyone's name which
is
great if I have say under 62,000 names but when the database has over
65,000
names then the box only lists up to say 62,000 records. In this case I
can't
scroll down past the 62,000 record number to select a record. I would
like to
know if there is an easy way that I can limit the records that the
Combo box
shows. ie.-Say I wanted to show names that start with S.
Is there an easy way to do this?
In the row source I have:
SELECT DISTINCTROW VoterInformationTable.ID,
VoterInformationTable.[Last
Name], VoterInformationTable.[First Name],
VoterInformationTable.[Middle
Name], VoterInformationTable.City, VoterInformationTable.[St#],
VoterInformationTable.Street, VoterInformationTable.[Street Type],
VoterInformationTable.[Apt#], VoterInformationTable.Poll,
VoterInformationTable.[Enum#], VoterInformationTable.[Home Phone] FROM

In the event After update I have:
Sub Combo48_AfterUpdate()
' Find the record that matches the control.'
If IsNull(Me![Combo48]) Then End
Me.RecordsetClone.FindFirst "[ID] = " & Me![Combo48]
Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub

Any help greatly appreciated.
Bill D.
Click to expand...
Click to expand...
Click to expand...
 
C

Cheese_whiz

Hi Bill,

Sorry I didn't get back to this sooner.

That link you posted is for cascading combo boxes. While it's a valuable
thing to know how to do, I don't think it's a common approach to your
problem. A cascading combo box setup might, for example, have one combo box
where someone could pick a car type (suv, luxury, economy, sport, etc) and
after that choice is made, the second combo box would display a list of JUST
those cars that are of the type chosen in the first combo box (so, if suv was
chosen in the first combo then the choices in the second might be Excursion,
Suburban, Expedition, etc etc).

I suppose you could could apply the same principles to your situation by
using some field that appears in the voter records as a way to narrow down
what appears in the combo box you are working on (or, for example, let a user
pick a letter in one combo box and then only those voters with a last name
that begins with that letter would be displayed). I just can't recall having
seen anyone work it that way. It might be worth trying though in the absence
of a better solution.

I still think the solution provided in the link I posted is a good option.
I know it sounds complicated but most of it is just cut and paste. That's
the great thing about working with access....there's a lot you can do early
on that doesn't require you to know a lot......just requires you have a
little bit of a 'can do' attitude about trying new things.

I want to comment on one more thing. The select query you posted for your
combo box has WAY too many fields. I'm not saying that is causing the
problem with displaying only a limited number of choices, but it WOULD slow
down the loading of the form.

Here's what I'd probably have for the recordset of that combo box:

Select ID, Trim([Last Name] & ", " & [First Name] & " " & [Middle Name]) as
FullName from [VoterInformationTable] Order by FullName, ID;

On the data tab of the properties window, set the 'bound column' to 1. On
the format tab, set the 'column count' to 2 and the 'column widths' to 0;1"
(that is zero semi-colon one inch). The zero length column width (which
corresponds to the ID column in your combo box) makes the column hidden to
users. They will only see the 'FullName'.

I hope my syntax is correct...

That should provide you with a combo box with a list of voter names in last
name/comma/first name/space/middle name format. It will trim off leading and
trailing spaces (spaces at the beginning or end of the full names).

I would use the same afterUpdate code you posted earlier. It should work
fine. The user will choose a name in the combo box and then that voter's
record will appear in the form.

The thing is, you don't need all those other fields in the combo box.
Ultimately, your select query for the combo box has to serve two purposes: 1.
provide the primary key of the record you select in the combo box so access
can look up that record and show it in the form (in your case, the [Id]
field). 2. Since your users aren't likely to memorize all the primary keys,
you need some other field (or combination of fields) that will actually
display in the combo box that the user can use to pick a record. In this
case, I've created the 'FullName' field, but in other cases you may be able
to use just one or two existing fields without having to create one like I
did with FullName.

Note: Since it's possible for voters to have the same names (even the same
FullName), you MAY want to add one additional field to your select query for
the rowsource of the combo box that would be helpful in differentiating
between two voters with the same name. What that field would be, I don't
know. It could be something like age or the first line of their address or
whatever. You would, of course, have to adjust both the query and the
properties for the combo box.

I realize that doesn't solve your immediate problem, but it is a benefit to
know, particularly when you have a lot of records. Including all those
fields when all you want to do is lookup a record is just adding more work to
achieve a less efficient end result which ultimately slows down your
application.

I hope something here is of benefit,
CW

BillD said:
Thanks for the suggestion. Allen Browne's setup seems complicated. I am not a
programmer. Dev Ashish & Arvin Meyer have published an article called "Forms:
Limit Content of Combo/List Boxes"
http://www.mvps.org/access/forms/frm0028.htm
This seems easier but I have not been able to set it up properly yet.
My current combo box that I use to look up names is called "Combo48" The
Control Source is empty, the Row Source Type is Table/Query and the Row
Source is:
"SELECT DISTINCTROW VoterInformationTable.ID, VoterInformationTable.[Last
Name], VoterInformationTable.[First Name], VoterInformationTable.[Middle
Name], VoterInformationTable.City, VoterInformationTable.[St#],
VoterInformationTable.Street, VoterInformationTable.[Street Type],
VoterInformationTable.[Apt#], VoterInformationTable.Poll,
VoterInformationTable.[Enum#], VoterInformationTable.[Home Phone] FROM"
Which method do you think would work the best?

Cheese_whiz said:
Hi BillD,

I suggest you take a look at this:
http://www.allenbrowne.com/ser-32.html

Allen has some excellent tips.

HTH,
CW

BillD said:
I have a database with 120,000 records. I use a View/edit form to look up
records. I have an unbound combo box at the top of the Detail section of the
Form that I use to lookup a name. When I select the name in the Combo the
form fills with the persons info.The combo lists everyone's name which is
great if I have say under 62,000 names but when the database has over 65,000
names then the box only lists up to say 62,000 records. In this case I can't
scroll down past the 62,000 record number to select a record. I would like to
know if there is an easy way that I can limit the records that the Combo box
shows. ie.-Say I wanted to show names that start with S.
Is there an easy way to do this?
In the row source I have:
SELECT DISTINCTROW VoterInformationTable.ID, VoterInformationTable.[Last
Name], VoterInformationTable.[First Name], VoterInformationTable.[Middle
Name], VoterInformationTable.City, VoterInformationTable.[St#],
VoterInformationTable.Street, VoterInformationTable.[Street Type],
VoterInformationTable.[Apt#], VoterInformationTable.Poll,
VoterInformationTable.[Enum#], VoterInformationTable.[Home Phone] FROM

In the event After update I have:
Sub Combo48_AfterUpdate()
' Find the record that matches the control.'
If IsNull(Me![Combo48]) Then End
Me.RecordsetClone.FindFirst "[ID] = " & Me![Combo48]
Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub

Any help greatly appreciated.
Bill D.
Click to expand...
Click to expand...
Click to expand...
 
B

BillD

I followed your suggestion as best I could. It worked the first time I used
it and it was fast with a table of 60,000 records. It limited the dropdown
list to only those records with the Letter I choose. One item needs to be
automated.
I would like the Enter Parameter Value box to not appear after I select a
letter from the Combo312 dropdown list and be able to go directly from
selecting a letter from the Combo312 list to the Combo48 list of records
witout having to type the letter in the Enter Parameter Value again.

Here is the existing setup I have. I have a "Combo48" on my Form that I used
to lookup names. When I selected a name from the dropdown list then that
persons record filled the Form.
Currently I have in the Row Source for Combo48 the following:
"SELECT DISTINCTROW VoterInformationTable.ID, VoterInformationTable.[Last
Name], VoterInformationTable.[First Name], VoterInformationTable.[Middle
Name], VoterInformationTable.City, VoterInformationTable.[St#],
VoterInformationTable.Street, VoterInformationTable.[Street Type],
VoterInformationTable.[Apt#], VoterInformationTable.Poll,
VoterInformationTable.[Enum#], VoterInformationTable.[Home Phone] FROM
VoterInformationTable WHERE (((Left([VoterInformationTable].[Last
Name],1))=[Forms].[VoterInformationTable].[ComboFirstLetter])) ORDER BY
VoterInformationTable.[Last Name], VoterInformationTable.[First Name],
VoterInformationTable.[Middle Name];"
In the AfterUpdate Event for Combo48 I have:
"Sub Combo48_AfterUpdate()
' Find the record that matches the control.'
If IsNull(Me![Combo48]) Then End
Me.RecordsetClone.FindFirst "[ID] = " & Me![Combo48]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub"
In Row Source for Combo312 I have:
"SELECT Left(VoterInformationTable.[Last Name],1) AS FirstLetter,
Count(Left([VoterInformationTable.Last Name],1)) AS CountOfFirstLetter
FROM VoterInformationTable
GROUP BY (Left([VoterInformationTable.Last Name],1))
ORDER BY (Left(VoterInformationTable.[Last Name],1));"
In the after update event for Combo 312 I have:
"Private Sub Combo312_AfterUpdate()
Combo48.Requery
End Sub"

If I first click on the dropdown list for Combo312, then I get a list of all
the Letters to choose from. If I choose "A" from the "Combo312" list then a
box called "Enter Parameter Value" shows up and I have to type in the letter
again in the box. Then Combo48 allows me to type in any number of letters
after A for a name and it brings up the current list as I type in more
letters.Ex.After I select a leter from the dropdownlist from Combo312 then
click the dropdown arrow for Combo48 I get a list of all records with "A" If
I type "Abb" in theCombo box for Combo48 then I get all "A" records starting
"Abb" etc. THIS IS GREAT.
I would like the Enter Parameter Value box to not appear and be able to go
directly from selecting a letter from the Combo312 letter list to the Combo48
list of records witout having to type the letter in again.
I hope I have explained this correctly. Have I followed your directions
properly.
Sorry for all the detail, but this is the only way we can be totally
coordinated.
Thanks again
Bill D.


Rastro said:
I suggest to filter the query in the combo48
Like this:
"SELECT DISTINCTROW VoterInformationTable.ID,
VoterInformationTable.[Last Name],
VoterInformationTable.[First Name], VoterInformationTable.[Middle
Name], VoterInformationTable.City, VoterInformationTable.[St#],
VoterInformationTable.Street, VoterInformationTable.[Street Type],
VoterInformationTable.[Apt#], VoterInformationTable.Poll,
VoterInformationTable.[Enum#], VoterInformationTable.[Home Phone]
FROM VoterInformationTable
Where (Left(VoterInformationTable.[Last Name],1)
=[Forms].[YourForm].[ComboFirstLetter] ;

Now you have a filter for the selected letter on the other combo, ex.
"ComboFirstLetter" with this query:

SELECT Left(VoterInformationTable.[Last Name],1) AS FirstLetter,
Count(Left([VoterInformationTable.Last Name],1)) AS CountOfFirstLetter
FROM VoterInformationTable
GROUP BY (Left([VoterInformationTable.Last Name],1))
ORDER BY (Left(VoterInformationTable.[Last Name],1));

Next add this code to the afterupdate event on the ComboFirstLetter
Combo45.Requery

I hope this works

Rastro


BillD said:
Thanks for the suggestion. Allen Browne's setup seems complicated. I am
not a
programmer. Dev Ashish & Arvin Meyer have published an article called
"Forms:
Limit Content of Combo/List Boxes"
http://www.mvps.org/access/forms/frm0028.htm
This seems easier but I have not been able to set it up properly yet.
My current combo box that I use to look up names is called "Combo48" The
Control Source is empty, the Row Source Type is Table/Query and the Row
Source is:
"SELECT DISTINCTROW VoterInformationTable.ID, VoterInformationTable.[Last
Name], VoterInformationTable.[First Name], VoterInformationTable.[Middle
Name], VoterInformationTable.City, VoterInformationTable.[St#],
VoterInformationTable.Street, VoterInformationTable.[Street Type],
VoterInformationTable.[Apt#], VoterInformationTable.Poll,
VoterInformationTable.[Enum#], VoterInformationTable.[Home Phone] FROM"
Which method do you think would work the best?

Cheese_whiz said:
Hi BillD,

I suggest you take a look at this:
http://www.allenbrowne.com/ser-32.html

Allen has some excellent tips.

HTH,
CW

:

I have a database with 120,000 records. I use a View/edit form to look
up
records. I have an unbound combo box at the top of the Detail section
of the
Form that I use to lookup a name. When I select the name in the Combo
the
form fills with the persons info.The combo lists everyone's name which
is
great if I have say under 62,000 names but when the database has over
65,000
names then the box only lists up to say 62,000 records. In this case I
can't
scroll down past the 62,000 record number to select a record. I would
like to
know if there is an easy way that I can limit the records that the
Combo box
shows. ie.-Say I wanted to show names that start with S.
Is there an easy way to do this?
In the row source I have:
SELECT DISTINCTROW VoterInformationTable.ID,
VoterInformationTable.[Last
Name], VoterInformationTable.[First Name],
VoterInformationTable.[Middle
Name], VoterInformationTable.City, VoterInformationTable.[St#],
VoterInformationTable.Street, VoterInformationTable.[Street Type],
VoterInformationTable.[Apt#], VoterInformationTable.Poll,
VoterInformationTable.[Enum#], VoterInformationTable.[Home Phone] FROM

In the event After update I have:
Sub Combo48_AfterUpdate()
' Find the record that matches the control.'
If IsNull(Me![Combo48]) Then End
Me.RecordsetClone.FindFirst "[ID] = " & Me![Combo48]
Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub

Any help greatly appreciated.
Bill D.
Click to expand...
Click to expand...
Click to expand...
 
B

BillD

Hi CW:
I have just responded to Rastro's response. Could you read this over. I can
now look at your solution suggested. I Need all the fields in the Combo48
dropdown so a user can look at the records without having to bring up each
individual form for a person. This is advantageous when looking at names at
the same address. It also allows us to look up a a lot of peoples poll number
quickly.
Your input has been of great benefit.
I want to have the easiest and most efficient search.

Thanks so much.

Bill D.


Cheese_whiz said:
Hi Bill,

Sorry I didn't get back to this sooner.

That link you posted is for cascading combo boxes. While it's a valuable
thing to know how to do, I don't think it's a common approach to your
problem. A cascading combo box setup might, for example, have one combo box
where someone could pick a car type (suv, luxury, economy, sport, etc) and
after that choice is made, the second combo box would display a list of JUST
those cars that are of the type chosen in the first combo box (so, if suv was
chosen in the first combo then the choices in the second might be Excursion,
Suburban, Expedition, etc etc).

I suppose you could could apply the same principles to your situation by
using some field that appears in the voter records as a way to narrow down
what appears in the combo box you are working on (or, for example, let a user
pick a letter in one combo box and then only those voters with a last name
that begins with that letter would be displayed). I just can't recall having
seen anyone work it that way. It might be worth trying though in the absence
of a better solution.

I still think the solution provided in the link I posted is a good option.
I know it sounds complicated but most of it is just cut and paste. That's
the great thing about working with access....there's a lot you can do early
on that doesn't require you to know a lot......just requires you have a
little bit of a 'can do' attitude about trying new things.

I want to comment on one more thing. The select query you posted for your
combo box has WAY too many fields. I'm not saying that is causing the
problem with displaying only a limited number of choices, but it WOULD slow
down the loading of the form.

Here's what I'd probably have for the recordset of that combo box:

Select ID, Trim([Last Name] & ", " & [First Name] & " " & [Middle Name]) as
FullName from [VoterInformationTable] Order by FullName, ID;

On the data tab of the properties window, set the 'bound column' to 1. On
the format tab, set the 'column count' to 2 and the 'column widths' to 0;1"
(that is zero semi-colon one inch). The zero length column width (which
corresponds to the ID column in your combo box) makes the column hidden to
users. They will only see the 'FullName'.

I hope my syntax is correct...

That should provide you with a combo box with a list of voter names in last
name/comma/first name/space/middle name format. It will trim off leading and
trailing spaces (spaces at the beginning or end of the full names).

I would use the same afterUpdate code you posted earlier. It should work
fine. The user will choose a name in the combo box and then that voter's
record will appear in the form.

The thing is, you don't need all those other fields in the combo box.
Ultimately, your select query for the combo box has to serve two purposes: 1.
provide the primary key of the record you select in the combo box so access
can look up that record and show it in the form (in your case, the [Id]
field). 2. Since your users aren't likely to memorize all the primary keys,
you need some other field (or combination of fields) that will actually
display in the combo box that the user can use to pick a record. In this
case, I've created the 'FullName' field, but in other cases you may be able
to use just one or two existing fields without having to create one like I
did with FullName.

Note: Since it's possible for voters to have the same names (even the same
FullName), you MAY want to add one additional field to your select query for
the rowsource of the combo box that would be helpful in differentiating
between two voters with the same name. What that field would be, I don't
know. It could be something like age or the first line of their address or
whatever. You would, of course, have to adjust both the query and the
properties for the combo box.

I realize that doesn't solve your immediate problem, but it is a benefit to
know, particularly when you have a lot of records. Including all those
fields when all you want to do is lookup a record is just adding more work to
achieve a less efficient end result which ultimately slows down your
application.

I hope something here is of benefit,
CW

BillD said:
Thanks for the suggestion. Allen Browne's setup seems complicated. I am not a
programmer. Dev Ashish & Arvin Meyer have published an article called "Forms:
Limit Content of Combo/List Boxes"
http://www.mvps.org/access/forms/frm0028.htm
This seems easier but I have not been able to set it up properly yet.
My current combo box that I use to look up names is called "Combo48" The
Control Source is empty, the Row Source Type is Table/Query and the Row
Source is:
"SELECT DISTINCTROW VoterInformationTable.ID, VoterInformationTable.[Last
Name], VoterInformationTable.[First Name], VoterInformationTable.[Middle
Name], VoterInformationTable.City, VoterInformationTable.[St#],
VoterInformationTable.Street, VoterInformationTable.[Street Type],
VoterInformationTable.[Apt#], VoterInformationTable.Poll,
VoterInformationTable.[Enum#], VoterInformationTable.[Home Phone] FROM"
Which method do you think would work the best?

Cheese_whiz said:
Hi BillD,

I suggest you take a look at this:
http://www.allenbrowne.com/ser-32.html

Allen has some excellent tips.

HTH,
CW

:

I have a database with 120,000 records. I use a View/edit form to look up
records. I have an unbound combo box at the top of the Detail section of the
Form that I use to lookup a name. When I select the name in the Combo the
form fills with the persons info.The combo lists everyone's name which is
great if I have say under 62,000 names but when the database has over 65,000
names then the box only lists up to say 62,000 records. In this case I can't
scroll down past the 62,000 record number to select a record. I would like to
know if there is an easy way that I can limit the records that the Combo box
shows. ie.-Say I wanted to show names that start with S.
Is there an easy way to do this?
In the row source I have:
SELECT DISTINCTROW VoterInformationTable.ID, VoterInformationTable.[Last
Name], VoterInformationTable.[First Name], VoterInformationTable.[Middle
Name], VoterInformationTable.City, VoterInformationTable.[St#],
VoterInformationTable.Street, VoterInformationTable.[Street Type],
VoterInformationTable.[Apt#], VoterInformationTable.Poll,
VoterInformationTable.[Enum#], VoterInformationTable.[Home Phone] FROM

In the event After update I have:
Sub Combo48_AfterUpdate()
' Find the record that matches the control.'
If IsNull(Me![Combo48]) Then End
Me.RecordsetClone.FindFirst "[ID] = " & Me![Combo48]
Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub

Any help greatly appreciated.
Bill D.
Click to expand...
Click to expand...
Click to expand...
 
R

Rastro

Bill:
Might be because it's taking as a parameter what it is not. I guess it's the
"WHERE (((Left([VoterInformationTable].[Last
Name],1))=[Forms].[VoterInformationTable].[ComboFirstLetter]))",
specifically [Forms].[VoterInformationTable].[ComboFirstLetter]

My recommendation is to check if "VoterInformationTable" is really the name
of form which have the combos 48 and 312.
If it yes, replace the name for the correct one.

If it isn't try this [Forms]![VoterInformation]![ComboFirstLetter].

Rastro


BillD said:
I followed your suggestion as best I could. It worked the first time I
used
it and it was fast with a table of 60,000 records. It limited the
dropdown
list to only those records with the Letter I choose. One item needs to be
automated.
I would like the Enter Parameter Value box to not appear after I select a
letter from the Combo312 dropdown list and be able to go directly from
selecting a letter from the Combo312 list to the Combo48 list of records
witout having to type the letter in the Enter Parameter Value again.

Here is the existing setup I have. I have a "Combo48" on my Form that I
used
to lookup names. When I selected a name from the dropdown list then that
persons record filled the Form.
Currently I have in the Row Source for Combo48 the following:
"SELECT DISTINCTROW VoterInformationTable.ID, VoterInformationTable.[Last
Name], VoterInformationTable.[First Name], VoterInformationTable.[Middle
Name], VoterInformationTable.City, VoterInformationTable.[St#],
VoterInformationTable.Street, VoterInformationTable.[Street Type],
VoterInformationTable.[Apt#], VoterInformationTable.Poll,
VoterInformationTable.[Enum#], VoterInformationTable.[Home Phone] FROM
VoterInformationTable WHERE (((Left([VoterInformationTable].[Last
Name],1))=[Forms].[VoterInformationTable].[ComboFirstLetter])) ORDER BY
VoterInformationTable.[Last Name], VoterInformationTable.[First Name],
VoterInformationTable.[Middle Name];"
In the AfterUpdate Event for Combo48 I have:
"Sub Combo48_AfterUpdate()
' Find the record that matches the control.'
If IsNull(Me![Combo48]) Then End
Me.RecordsetClone.FindFirst "[ID] = " & Me![Combo48]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub"
In Row Source for Combo312 I have:
"SELECT Left(VoterInformationTable.[Last Name],1) AS FirstLetter,
Count(Left([VoterInformationTable.Last Name],1)) AS CountOfFirstLetter
FROM VoterInformationTable
GROUP BY (Left([VoterInformationTable.Last Name],1))
ORDER BY (Left(VoterInformationTable.[Last Name],1));"
In the after update event for Combo 312 I have:
"Private Sub Combo312_AfterUpdate()
Combo48.Requery
End Sub"

If I first click on the dropdown list for Combo312, then I get a list of
all
the Letters to choose from. If I choose "A" from the "Combo312" list then
a
box called "Enter Parameter Value" shows up and I have to type in the
letter
again in the box. Then Combo48 allows me to type in any number of
letters
after A for a name and it brings up the current list as I type in more
letters.Ex.After I select a leter from the dropdownlist from Combo312 then
click the dropdown arrow for Combo48 I get a list of all records with "A"
If
I type "Abb" in theCombo box for Combo48 then I get all "A" records
starting
"Abb" etc. THIS IS GREAT.
I would like the Enter Parameter Value box to not appear and be able to go
directly from selecting a letter from the Combo312 letter list to the
Combo48
list of records witout having to type the letter in again.
I hope I have explained this correctly. Have I followed your directions
properly.
Sorry for all the detail, but this is the only way we can be totally
coordinated.
Thanks again
Bill D.


Rastro said:
I suggest to filter the query in the combo48
Like this:
"SELECT DISTINCTROW VoterInformationTable.ID,
VoterInformationTable.[Last Name],
VoterInformationTable.[First Name], VoterInformationTable.[Middle
Name], VoterInformationTable.City, VoterInformationTable.[St#],
VoterInformationTable.Street, VoterInformationTable.[Street Type],
VoterInformationTable.[Apt#], VoterInformationTable.Poll,
VoterInformationTable.[Enum#], VoterInformationTable.[Home Phone]
FROM VoterInformationTable
Where (Left(VoterInformationTable.[Last Name],1)
=[Forms].[YourForm].[ComboFirstLetter] ;

Now you have a filter for the selected letter on the other combo, ex.
"ComboFirstLetter" with this query:

SELECT Left(VoterInformationTable.[Last Name],1) AS FirstLetter,
Count(Left([VoterInformationTable.Last Name],1)) AS CountOfFirstLetter
FROM VoterInformationTable
GROUP BY (Left([VoterInformationTable.Last Name],1))
ORDER BY (Left(VoterInformationTable.[Last Name],1));

Next add this code to the afterupdate event on the ComboFirstLetter
Combo45.Requery

I hope this works

Rastro


BillD said:
Thanks for the suggestion. Allen Browne's setup seems complicated. I am
not a
programmer. Dev Ashish & Arvin Meyer have published an article called
"Forms:
Limit Content of Combo/List Boxes"
http://www.mvps.org/access/forms/frm0028.htm
This seems easier but I have not been able to set it up properly yet.
My current combo box that I use to look up names is called "Combo48"
The
Control Source is empty, the Row Source Type is Table/Query and the Row
Source is:
"SELECT DISTINCTROW VoterInformationTable.ID,
VoterInformationTable.[Last
Name], VoterInformationTable.[First Name],
VoterInformationTable.[Middle
Name], VoterInformationTable.City, VoterInformationTable.[St#],
VoterInformationTable.Street, VoterInformationTable.[Street Type],
VoterInformationTable.[Apt#], VoterInformationTable.Poll,
VoterInformationTable.[Enum#], VoterInformationTable.[Home Phone] FROM"
Which method do you think would work the best?

:

Hi BillD,

I suggest you take a look at this:
http://www.allenbrowne.com/ser-32.html

Allen has some excellent tips.

HTH,
CW

:

I have a database with 120,000 records. I use a View/edit form to
look
up
records. I have an unbound combo box at the top of the Detail
section
of the
Form that I use to lookup a name. When I select the name in the
Combo
the
form fills with the persons info.The combo lists everyone's name
which
is
great if I have say under 62,000 names but when the database has
over
65,000
names then the box only lists up to say 62,000 records. In this case
I
can't
scroll down past the 62,000 record number to select a record. I
would
like to
know if there is an easy way that I can limit the records that the
Combo box
shows. ie.-Say I wanted to show names that start with S.
Is there an easy way to do this?
In the row source I have:
SELECT DISTINCTROW VoterInformationTable.ID,
VoterInformationTable.[Last
Name], VoterInformationTable.[First Name],
VoterInformationTable.[Middle
Name], VoterInformationTable.City, VoterInformationTable.[St#],
VoterInformationTable.Street, VoterInformationTable.[Street Type],
VoterInformationTable.[Apt#], VoterInformationTable.Poll,
VoterInformationTable.[Enum#], VoterInformationTable.[Home Phone]
FROM

In the event After update I have:
Sub Combo48_AfterUpdate()
' Find the record that matches the control.'
If IsNull(Me![Combo48]) Then End
Me.RecordsetClone.FindFirst "[ID] = " & Me![Combo48]
Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub

Any help greatly appreciated.
Bill D.
Click to expand...
Click to expand...
Click to expand...
 
B

BillD

Found the problem.
"WHERE (((Left([VoterInformationTable].[Last
Name],1))=[Forms].[VoterInformationTable].[ComboFirstLetter]))",
Replaced [ComboFirstLetter] with [Combo312]
The combo that brings up the Letters List to select from was called "Combo312"
not [ComboFirstLetter]. I think I tried using ComboFirstLetter when I first
started but had problems with it working so used combo312 given by wizard.
Sorry, my mistake for not understanding programming yet.
Thank you so much for your help.
One last question. Is there an easy way that I could just use the one combo
and have the letter list show, then select the letter.

Bill D.


Rastro said:
Bill:
Might be because it's taking as a parameter what it is not. I guess it's the
"WHERE (((Left([VoterInformationTable].[Last
Name],1))=[Forms].[VoterInformationTable].[ComboFirstLetter]))",
specifically [Forms].[VoterInformationTable].[ComboFirstLetter]

My recommendation is to check if "VoterInformationTable" is really the name
of form which have the combos 48 and 312.
If it yes, replace the name for the correct one.

If it isn't try this [Forms]![VoterInformation]![ComboFirstLetter].

Rastro


BillD said:
I followed your suggestion as best I could. It worked the first time I
used
it and it was fast with a table of 60,000 records. It limited the
dropdown
list to only those records with the Letter I choose. One item needs to be
automated.
I would like the Enter Parameter Value box to not appear after I select a
letter from the Combo312 dropdown list and be able to go directly from
selecting a letter from the Combo312 list to the Combo48 list of records
witout having to type the letter in the Enter Parameter Value again.

Here is the existing setup I have. I have a "Combo48" on my Form that I
used
to lookup names. When I selected a name from the dropdown list then that
persons record filled the Form.
Currently I have in the Row Source for Combo48 the following:
"SELECT DISTINCTROW VoterInformationTable.ID, VoterInformationTable.[Last
Name], VoterInformationTable.[First Name], VoterInformationTable.[Middle
Name], VoterInformationTable.City, VoterInformationTable.[St#],
VoterInformationTable.Street, VoterInformationTable.[Street Type],
VoterInformationTable.[Apt#], VoterInformationTable.Poll,
VoterInformationTable.[Enum#], VoterInformationTable.[Home Phone] FROM
VoterInformationTable WHERE (((Left([VoterInformationTable].[Last
Name],1))=[Forms].[VoterInformationTable].[ComboFirstLetter])) ORDER BY
VoterInformationTable.[Last Name], VoterInformationTable.[First Name],
VoterInformationTable.[Middle Name];"
In the AfterUpdate Event for Combo48 I have:
"Sub Combo48_AfterUpdate()
' Find the record that matches the control.'
If IsNull(Me![Combo48]) Then End
Me.RecordsetClone.FindFirst "[ID] = " & Me![Combo48]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub"
In Row Source for Combo312 I have:
"SELECT Left(VoterInformationTable.[Last Name],1) AS FirstLetter,
Count(Left([VoterInformationTable.Last Name],1)) AS CountOfFirstLetter
FROM VoterInformationTable
GROUP BY (Left([VoterInformationTable.Last Name],1))
ORDER BY (Left(VoterInformationTable.[Last Name],1));"
In the after update event for Combo 312 I have:
"Private Sub Combo312_AfterUpdate()
Combo48.Requery
End Sub"

If I first click on the dropdown list for Combo312, then I get a list of
all
the Letters to choose from. If I choose "A" from the "Combo312" list then
a
box called "Enter Parameter Value" shows up and I have to type in the
letter
again in the box. Then Combo48 allows me to type in any number of
letters
after A for a name and it brings up the current list as I type in more
letters.Ex.After I select a leter from the dropdownlist from Combo312 then
click the dropdown arrow for Combo48 I get a list of all records with "A"
If
I type "Abb" in theCombo box for Combo48 then I get all "A" records
starting
"Abb" etc. THIS IS GREAT.
I would like the Enter Parameter Value box to not appear and be able to go
directly from selecting a letter from the Combo312 letter list to the
Combo48
list of records witout having to type the letter in again.
I hope I have explained this correctly. Have I followed your directions
properly.
Sorry for all the detail, but this is the only way we can be totally
coordinated.
Thanks again
Bill D.


Rastro said:
I suggest to filter the query in the combo48
Like this:
"SELECT DISTINCTROW VoterInformationTable.ID,
VoterInformationTable.[Last Name],
VoterInformationTable.[First Name], VoterInformationTable.[Middle
Name], VoterInformationTable.City, VoterInformationTable.[St#],
VoterInformationTable.Street, VoterInformationTable.[Street Type],
VoterInformationTable.[Apt#], VoterInformationTable.Poll,
VoterInformationTable.[Enum#], VoterInformationTable.[Home Phone]
FROM VoterInformationTable
Where (Left(VoterInformationTable.[Last Name],1)
=[Forms].[YourForm].[ComboFirstLetter] ;

Now you have a filter for the selected letter on the other combo, ex.
"ComboFirstLetter" with this query:

SELECT Left(VoterInformationTable.[Last Name],1) AS FirstLetter,
Count(Left([VoterInformationTable.Last Name],1)) AS CountOfFirstLetter
FROM VoterInformationTable
GROUP BY (Left([VoterInformationTable.Last Name],1))
ORDER BY (Left(VoterInformationTable.[Last Name],1));

Next add this code to the afterupdate event on the ComboFirstLetter
Combo45.Requery

I hope this works

Rastro


"BillD" <[email protected]> escribió en el mensaje de
noticias:[email protected]...
Thanks for the suggestion. Allen Browne's setup seems complicated. I am
not a
programmer. Dev Ashish & Arvin Meyer have published an article called
"Forms:
Limit Content of Combo/List Boxes"
http://www.mvps.org/access/forms/frm0028.htm
This seems easier but I have not been able to set it up properly yet.
My current combo box that I use to look up names is called "Combo48"
The
Control Source is empty, the Row Source Type is Table/Query and the Row
Source is:
"SELECT DISTINCTROW VoterInformationTable.ID,
VoterInformationTable.[Last
Name], VoterInformationTable.[First Name],
VoterInformationTable.[Middle
Name], VoterInformationTable.City, VoterInformationTable.[St#],
VoterInformationTable.Street, VoterInformationTable.[Street Type],
VoterInformationTable.[Apt#], VoterInformationTable.Poll,
VoterInformationTable.[Enum#], VoterInformationTable.[Home Phone] FROM"
Which method do you think would work the best?

:

Hi BillD,

I suggest you take a look at this:
http://www.allenbrowne.com/ser-32.html

Allen has some excellent tips.

HTH,
CW

:

I have a database with 120,000 records. I use a View/edit form to
look
up
records. I have an unbound combo box at the top of the Detail
section
of the
Form that I use to lookup a name. When I select the name in the
Combo
the
form fills with the persons info.The combo lists everyone's name
which
is
great if I have say under 62,000 names but when the database has
over
65,000
names then the box only lists up to say 62,000 records. In this case
I
can't
scroll down past the 62,000 record number to select a record. I
would
like to
know if there is an easy way that I can limit the records that the
Combo box
shows. ie.-Say I wanted to show names that start with S.
Is there an easy way to do this?
In the row source I have:
SELECT DISTINCTROW VoterInformationTable.ID,
VoterInformationTable.[Last
Name], VoterInformationTable.[First Name],
VoterInformationTable.[Middle
Name], VoterInformationTable.City, VoterInformationTable.[St#],
VoterInformationTable.Street, VoterInformationTable.[Street Type],
VoterInformationTable.[Apt#], VoterInformationTable.Poll,
VoterInformationTable.[Enum#], VoterInformationTable.[Home Phone]
FROM

In the event After update I have:
Sub Combo48_AfterUpdate()
' Find the record that matches the control.'
If IsNull(Me![Combo48]) Then End
Me.RecordsetClone.FindFirst "[ID] = " & Me![Combo48]
Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub

Any help greatly appreciated.
Bill D.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Change address fields in Table using Unbound Combo Box 18
Cascading Combo Boxes 3
Change Address Fields using Combo Dropdown List 1
Combo Box Lookup with over 62,000 records 17
Change Address Field data in a Table from a form using Combo Drop 7
combo box filter 1
Combo Box 3
Combo Box Problems 2

Top