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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|