A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

ComboBox is for looking up records



 
 
Thread Tools Display Modes
  #1  
Old December 13th, 2006, 05:54 AM posted to microsoft.public.access.forms
TizTIz
external usenet poster
 
Posts: 3
Default ComboBox is for looking up records

I have a form that want to add a combo box that will show my list of clients
(client names located in a table), when you select a client it will show all
records for the particular client.

This is most likely a very simple process, but I'm having a heck of a time
figuring out this out. How would I do this?


  #2  
Old December 13th, 2006, 09:54 AM posted to microsoft.public.access.forms
scubadiver
external usenet poster
 
Posts: 1,673
Default ComboBox is for looking up records


I have something similar to what you request. For all customer addresses in
my main form I send them to a query which is the source for a combo box. On
the subform I have a reference combo which lists the addresses for that
customer.

What my suggestion would be is to send all your clients to a query which is
the source for a combo box. In the subform have either a datasheet or
continuous form which will select the records for that client.

There are plenty of messages on here on the SQL for ta cascading combo.

I hope this gives you a start.

--
How many buildings collapsed on 9/11?

I can tell you the answer isn''t 2 !!


"TizTIz" wrote:

I have a form that want to add a combo box that will show my list of clients
(client names located in a table), when you select a client it will show all
records for the particular client.

This is most likely a very simple process, but I'm having a heck of a time
figuring out this out. How would I do this?


  #3  
Old December 13th, 2006, 09:57 AM posted to microsoft.public.access.forms
scubadiver
external usenet poster
 
Posts: 1,673
Default ComboBox is for looking up records


What I will add is that you have a second query which lists all the records
for each client. The subform acts as the second part of the cascading combo.

"TizTIz" wrote:

I have a form that want to add a combo box that will show my list of clients
(client names located in a table), when you select a client it will show all
records for the particular client.

This is most likely a very simple process, but I'm having a heck of a time
figuring out this out. How would I do this?


  #4  
Old December 13th, 2006, 02:55 PM posted to microsoft.public.access.forms
missinglinq via AccessMonster.com
external usenet poster
 
Posts: 545
Default ComboBox is for looking up records

First you must have your form set up to display the record you want to
retrieve, i.e. you must have fields set up with the appropriate Control
Sources. Then simply:

Add a combo box to your form. The Combobox Wizard will pop up
Select "Find a record based on the value I selected in my combobox."
Hit Next.
Click on the field you're searching by (from the Record Source of the form)
to move it to the right side.
Hit Next.
Size the column appropriately.
Hit Next.
Name the combobox.
Hit Finish.

Now you can drop the combobox down and scroll down to the item to search by,
or you can start to enter the item, and the combobox will "autofill" as you
type.
Hit Enter and the record will be retrieved.


TizTIz wrote:
I have a form that want to add a combo box that will show my list of clients
(client names located in a table), when you select a client it will show all
records for the particular client.

This is most likely a very simple process, but I'm having a heck of a time
figuring out this out. How would I do this?


--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via http://www.accessmonster.com

  #5  
Old December 13th, 2006, 03:25 PM posted to microsoft.public.access.forms
Klatuu
external usenet poster
 
Posts: 7,074
Default ComboBox is for looking up records

The combo box should be an unbound field used only for searching.

Create a Select statment for the row source property of the combo.
Something like:
SELECT ClientID From ClientTable;

This will give you a drop down list of all the client's in the table.

Now use the combo's After Update event to locate and display the selected
client:

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "[ClientID] = " & Me.cboClientSearch
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing

That will take care of looking up existing clients. Once you get that
working, you may want to explore how to handle adding new clients when the
client doesn't exist in the table, but get this working, first.

Now, another thing that will probably pop up is that something like ClientID
may not be meaningful to the user - it may even be an Autonumber field. In
this case, you will still need the field to do the search, but you will want
to display the client name. Add the name to the combo's row source query:

SELECT ClientID, ClientName From ClientTable;

Set the following properties of the combo:
Bound Column 1
Column Count 2
Column Width 0";1.5" (This will make the ID invisible and show only the name)

This will not change The After Update event code.

Post back if you have further questions.
"TizTIz" wrote:

I have a form that want to add a combo box that will show my list of clients
(client names located in a table), when you select a client it will show all
records for the particular client.

This is most likely a very simple process, but I'm having a heck of a time
figuring out this out. How would I do this?


  #6  
Old December 13th, 2006, 03:29 PM posted to microsoft.public.access.forms
Douglas J. Steele
external usenet poster
 
Posts: 9,313
Default ComboBox is for looking up records

I believe that needs to be

Dim rst As DAO.Recordset

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Klatuu" wrote in message
...
The combo box should be an unbound field used only for searching.

Create a Select statment for the row source property of the combo.
Something like:
SELECT ClientID From ClientTable;

This will give you a drop down list of all the client's in the table.

Now use the combo's After Update event to locate and display the selected
client:

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "[ClientID] = " & Me.cboClientSearch
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing

That will take care of looking up existing clients. Once you get that
working, you may want to explore how to handle adding new clients when the
client doesn't exist in the table, but get this working, first.

Now, another thing that will probably pop up is that something like
ClientID
may not be meaningful to the user - it may even be an Autonumber field.
In
this case, you will still need the field to do the search, but you will
want
to display the client name. Add the name to the combo's row source query:

SELECT ClientID, ClientName From ClientTable;

Set the following properties of the combo:
Bound Column 1
Column Count 2
Column Width 0";1.5" (This will make the ID invisible and show only the
name)

This will not change The After Update event code.

Post back if you have further questions.
"TizTIz" wrote:

I have a form that want to add a combo box that will show my list of
clients
(client names located in a table), when you select a client it will show
all
records for the particular client.

This is most likely a very simple process, but I'm having a heck of a
time
figuring out this out. How would I do this?




  #7  
Old December 13th, 2006, 03:49 PM posted to microsoft.public.access.forms
Klatuu
external usenet poster
 
Posts: 7,074
Default ComboBox is for looking up records

Forgive my antiquity, I often forget to do that.

"Douglas J. Steele" wrote:

I believe that needs to be

Dim rst As DAO.Recordset

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Klatuu" wrote in message
...
The combo box should be an unbound field used only for searching.

Create a Select statment for the row source property of the combo.
Something like:
SELECT ClientID From ClientTable;

This will give you a drop down list of all the client's in the table.

Now use the combo's After Update event to locate and display the selected
client:

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "[ClientID] = " & Me.cboClientSearch
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing

That will take care of looking up existing clients. Once you get that
working, you may want to explore how to handle adding new clients when the
client doesn't exist in the table, but get this working, first.

Now, another thing that will probably pop up is that something like
ClientID
may not be meaningful to the user - it may even be an Autonumber field.
In
this case, you will still need the field to do the search, but you will
want
to display the client name. Add the name to the combo's row source query:

SELECT ClientID, ClientName From ClientTable;

Set the following properties of the combo:
Bound Column 1
Column Count 2
Column Width 0";1.5" (This will make the ID invisible and show only the
name)

This will not change The After Update event code.

Post back if you have further questions.
"TizTIz" wrote:

I have a form that want to add a combo box that will show my list of
clients
(client names located in a table), when you select a client it will show
all
records for the particular client.

This is most likely a very simple process, but I'm having a heck of a
time
figuring out this out. How would I do this?





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:29 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.