Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 

Forums     

Microsoft Access Forum
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

 
This forum is for the discussion of Microsoft Access.

Access is my personal specialty. Have questions? Comments? Want to discuss how to do something? Post it here. Also, I get a LOT of questions sent to me in Email from people around the world. I'll post the interesting ones in here. Feel free to comment on them.

Click Here To Subscribe to this forum and receive an email update whenever new posts are added, just scroll down to the bottom of this page and enter your email address in the comment form.

Permanent Link
Keywords: microsoft access forum
Post New Topic

Read Combo Box by Mohammad H @ 9/25/2019
my database is a bit different than the example you shown. I have some companies who are my clients and there are some employees working in different positions within these companies(clients). One client can have many employees whom I want to create a contact history. now I created two tables one for companies and the other for their employees. How to create a combo box showing both company name and its employee on the contact history form. I cannot figure out this.
Show Just This Thread        Post Reply
Read Merging 2 columns of list in 1 Combo box by Emad A @ 7/22/2019
Hi, If I have Columns has list of any kind of list, and I have another list in the same table or in another table, How can I merge 2 columns lists in 1 combo box?
Show Just This Thread        Post Reply
Read Combo Box Issue by Christopher V @ 8/27/2018
I am using Microsoft Office Professional 2016.  When trying to create a combo box on my form the combo box wizard only gives me two options.
"I want the combo box to get the values from another table or query"  and
"I will type the values that I want". I am not getting the third option like there is in the video "Find a record on my forum based on the value I selected in my combo box". Why am I missing that third option?


Reply from Alex Hedley:

Combo Box Find Record Missing
Show Just This Thread        Post Reply
Read Conditional look up in combo box by Liz J @ 2/24/2018
Hello, May I ask which lesson covers how to update combo box lookups (where one combo box determines the options in another) on a form, please? Thank you.


Reply from Alex Hedley:

Cascading Combo Boxes
Show Just This Thread        Post Reply
Read Combo box Search by Naser A @ 2/4/2018
I put the combo box on the page header of the Form.  It appears only on design mode but does not show up in Form View or Layout mode.


Reply from Alex Hedley:

Put it in the FORM header not the PAGE header.
Show Just This Thread        Post Reply
Read Combo box Search by Naser A @ 2/3/2018
As per Beginners' Level 8, Lesson 6 I have created a Combo box to search a particular record in the header but while run the Form nothing appears on the top.  Please help.


Reply from Alex Hedley:

Is there and AfterUpdate event on the combobox?
Show Just This Thread        Post Reply
Read Combo Box Updating One Record of Source Table by Jackie M @ 11/29/2017
The field is unbound. The selection in the combo box populates 4 additional fields on the form from a single table. I am setting up a customer form to select a customer to be used when the contact button is selected. Much like the example provided in the tutorial. I have stepped through the process several times and my results are not quite the same so there is something I am doing a little differently than your example.


Reply from Alex Hedley:

Can your reproduce the example?
Show Just This Thread        Post Reply
Read Combo Box Updating One Record of Source Table by Jackie M @ 11/15/2017
I have a combo box that keeps updating the first record of my source table. Any ideas how to keep that from happening? I have recreated the combo box a couple of times and check my preferences and don't seem to be able to locate the issue. I did select the save for later option not the store in a specific column for my combo results.


Reply from Alex Hedley:

Is this combo bound to a field on your form and the form is bound to a Table?
Are you opening this Form to a given record or a new (*) one?
Show Just This Thread        Post Reply
Read Dynamic List Combo Box by Margaret C @ 10/27/2017
In my data base, I need to list the members of a project team.  I have a multi-select combo box that works well.  However, If someone is marked as "no longer current", they are deleted from the list and also deleted from any previous projects.  Can you design a dynamic List/Combo box that does not change the records?


Reply from Alex Hedley:

You'd create a table that links to this Member by a MemberId. That is then set from the combo choice.
The Member Table could have an Active column. If they are "no longer current" toggle the "Active" column.
Now only have combo show Active members.
Show Just This Thread        Post Reply
Read How use multiple combo boxes as progressive filter by tom s @ 3/22/2017
Like to put many combo boxes on a form header that act like compound, progressive filters to drill down a large table and select only the records where each combo box is true.

Want to do it that way in order to give to non access user ability to extract data and not have to write Access Queries.

Am aware how to do it with ONE combobox that filters on ONE field, but I am really looking to add extra filters for the other fields.

How does this work?
Tom


Reply from Alex Hedley:

The Search Seminar covers this and much much more.
Show Just This Thread        Post Reply
Read restricting values in combo boxes by Adam Smith @ 2/19/2017
I'm trying to create mt first database and quickly finding I'm out of my depth so thanks in advance

i have a table of competitors and a table of sections (for a competition) the sections have min and max age and the competitors have an age
I'm trying to build a form to input entry's i have two combo boxes to select competitors so want a third combo box to only display relevant sections that the competitors can enter

i have been playing with creations a query to only show the available sections but cant get this right

please help

Adam


Reply from Alex Hedley:

You can create a Parameter Query and pass the values from your chosen combos to this Query to return the list for the third combo.

- Microsoft Access Parameter Query
- Get a Value from an Open Form Field
Show Just This Thread        Post Reply
Read Data in Combo box and query by Yasir Abbas @ 11/27/2016
Thanks for reply.
But when I run a query for specific sales person, I have to mention his "ID" instead of his name. I want Access to Save SalesPerson's name instead of ID. Is it possible?


Reply from Alex Hedley:

That's not the advised way.

Why not create a Form and set the data to your Query with a parameter, create another form that lists your Sales People, click on the person and open this form.

Searching by Name instead of ID isn't a good database design.
Show Just This Thread        Post Reply
Read Data in Combo box and query by Yasir @ 11/19/2016
I have a relational table named "Sales Persons" and the entries are as below;
SalesPersonID: Autonumber
SalesPersonName: Text

and the table looks like as below;

1) Mr. John
2) Mr. Alan
3) Miss. Jennifer

THEN:
I created a form with Combo Box where Combo box lets me select the sales person from above table.

My Question:
When I make and run a query about any particular "Sales Person", query works only when I put "SalesPersonID" instead of "SalesPersonName".

For example, If I want to pull up the details of Miss. Jennifer, I have to put following in the query;

Sales Person: 3

---

Please advise how can I make a query where I just put SalesPersonName to run a query (instead of their ID).

Please assist.

Thank you


Reply from Alex Hedley:

Create a Combo that has the Sales people then use the choice (ID) you pick as the parameter to filter the other data that has the ID against it.
This is the correct way to do it, you show a human readable name for the user to pick from but you tell access to use the ID as that what it's good at.
Show Just This Thread        Post Reply
Read Search form using text and combo boxes by Eric M @ 11/2/2016
Was trying to create a (search) form that used a combo box and two text boxes and a command button.  The button would open a form using content from the combo and text boxes.  I had to abandon that approach (too technical for me at my level of knowledge.)
  What I ended up doing is created a form based on a query.  And the query based on my search form with criteria Froms!searchF!combo and text.
  It works but I am having trouble with criteria when data is not entered in all (for instance leave combo and one text box blank so I search using only one combo box.)


Reply from Alex Hedley:

The Search Seminar would be a good course to take.

You'll need to check for Null - IsNull or for and empty string x = "" or x <> "".
Show Just This Thread        Post Reply
Read Search form using text and combo boxes by Eric M @ 9/29/2016
my code to open a form using test boxes:

Private Sub Command12_Click()
  DoCmd.OpenForm "PackageF", acNormal, , "CwpSerialNumber='" & Me.CwpSerialNumber & "' and [CwpYear]='" & Me.CwpYear & "'"

And code for combo box:

Private Sub Command42_Click()
  DoCmd.OpenForm "PackageF", acNormal, , "[IDCwpCommand]=" & [HullNumberCombo]

But I can't figure how to merge the two into one command button.

Originally I tried a macro based on a query, the query ran fine but I could not figure the code for the where statement in the macro.

What am I getting wrong?


Reply from Alex Hedley:

You want to open two forms at the same time?
Show Just This Thread        Post Reply
Read Combo Box by Anamaria @ 8/24/2016
Alex:
1)I suppose it would be the number of items, I would like to add more but it seems there are restrictions and I have more room if I change it to a memo instead of text box.

2)If I understand correctly then I suppose it is, it is part of the Control Source Partner

3)It is a Value list



Reply from Alex Hedley:

Why do you need such a long list, this seems like it would be hard to manage, could you not use cascading combos to better filter what you are looking for.
I'd create a Table instead of using a value list and set the combo to the Table then you can add as many records to the table as you'd need.
Show Just This Thread        Post Reply
Read Combo Box by Anamaria @ 8/22/2016
I have a form we have created to keep track of contracts, which also means in the combo box for partners is a long one and from time to time we have new ones that need to be added.

My problem is it seems that I have maxed the amount of words I can put in the combo box..

Is there a way to make it unlimited so I can add whatever?

I was told I could change it into a memo which allows more words, but how do I change this?


Reply from Alex Hedley:

Do you mean length of string in a combo box row or number of items in a combo box?
Is the row bound to a Field in a Table.
What data type is this field?
Show Just This Thread        Post Reply
Read empty fields in combo box by Arjan @ 8/9/2016
Dear Richard,
I have the following problem with access:
I have a table with members.  Members can be active or non-active.  I made a query to select only active members.  
I organise meetings. For each meeting I use a continuous form to enlist members who visited that meeting.  In this  form I use a combobox to select members from the query.  
Now when I change a member to non-active, then i get blank fields on forms of meetings this member visited in the past. This  member is still registered to these meetings, because when I print the meeting report, his name  is on the report.  It  just dont show anymore in the combo-boxes on the forms.
Have you  covered  this in one of your lessons?  Or do you know how to solve this problem?
Thanks in advance.
Arjan



Reply from Alex Hedley:

The Form can have a query that uses only Active members but when it comes to your reports don't filter on this if you want to see them historically.
Show Just This Thread        Post Reply
Read combo box search in form by Gabriel @ 5/27/2016
Hello friends,
I am new in MS Access and I want to ask you if it is possible in that search combo box to see the results sorted alphabeticaly?
Thanks a lot.


Reply from Alex Hedley:

Just set your Query to have a Sort By or Order By
Show Just This Thread        Post Reply
Read Relational Combo Box by Tiziano F @ 5/26/2016
In reference to your video n. 4 of the Access 2010 Expert 1 lessons series, at the time 6  41  you affirm that the value will be put in in the field CustomerID in the ContactT.
As result of it, I would expect to find in the table ContactT the CustomerID number replaced by the FirstName string; but this doesn t succeed.
Where am I wrong?
Best regards.


Reply from Alex Hedley:

Why would it be a name and not the CustomerID?
CustomerID is the bound field in the ComboBox, it's hidden. The Name shows just for Usability reasons.
You link on IDs as this is the best way to create relationships. Strings are error prone and Names can change but IDs as AutoNumbers shouldn't
Show Just This Thread        Post Reply
Read Default values on a combo box list by Peter Songe @ 4/5/2016
Thanks so much
Show Just This Thread        Post Reply
Read Default values on a combo box list by Peter Songe @ 3/28/2016
How can I set a default value in a form that contains a field with combo box list? The list in the combo box is generated using a lookup wizard from a table while in design view. Can you help me with this please?


Reply from Alex Hedley:

Don't use Lookup wizards in a Table, you should use Relationships with other Tables. See Expert Level 1 for more info

Tips
Default Value
Combo Box Default Value
Show Just This Thread        Post Reply
Read Combo Box Edits by Katheryn H @ 3/11/2016
OK - I found the lesson that covers how to edit values in a combo box. Does anyone know how to make it default to the list item that the user just added or edited? Thanks!


Reply from Alex Hedley:

MS Article
Forms!frmInvoice!PaymentMethod.DefaultValue = """Cash"""
Show Just This Thread        Post Reply
Read Combo box refresh by Katheryn H @ 3/11/2016
I am using a command button to a form that has a combo box. The command button opens a form to allow user to add an entry into the table on which the combo box is displaying values from. But when user closes the table, the added entry doesn't display in the combo box - user has to close form and reopen. I added RefreshRecord to the event macro both when opening the table and closing it but that doesn't work. Does anyone know which lesson this was covered in or otherwise can tell me how to fix? Thanks!


Reply from Alex Hedley:

Do you mind using VBA instead of Macros
You need to call a COMBOBOX.Requery referencing the original form when you close the one you added to.
Show Just This Thread        Post Reply
Read Value from another Combo Box by Brent @ 3/10/2016
I have created a form where you click a button and it grabs the jobid field of the already opened form and asigns that to the jobid field of the new form being opened, but when the code runs it say i can not assign the value to the object,, would you know why it would say this?


Reply from Alex Hedley:

Have you set it as the Default Value?
Show Just This Thread        Post Reply
Read Combo Box only showing 2 options by Elizabeth R @ 3/9/2016
Thanks. I changed the controlsource to just the table name...and it worked. Many thanks Alex! (and Richard)
Show Just This Thread        Post Reply
Read Combo Box only showing 2 options by Elizabeth R @ 3/9/2016
Yes. It is a "select statement"...but so are the forms when I created dummy forms in FORM and Form Wizard...
Show Just This Thread        Post Reply
Read Combo Box only showing 2 options by Elizabeth R @ 3/9/2016
Ok. After doing some testing of data and forms..what I discovered is that if I create the form with form design...I cannot use all 3 choices for combo box (is this a bug??) Combo box works properly with Form (ie. allow Access to just create a form), and form wizard. It doesn't work with creating the form with form design, or blank form...??? This seems wrong, but when I test it out that is what I found :(
Show Just This Thread        Post Reply
Read Combo Box only showing 2 options by Elizabeth R @ 3/9/2016
I don't know why this is happening. I am trying to create a combo box in the header of a form to select a record and filter the detail ie. search and pull up just that record. for some reason when I create the combo box in the header I'm only getting 2 choices not 3. I get I want the combo box to get values from a table or I will type in the values that I want. I don't get the 3rd option. To find a record based on the value I select. I have a sample database that I created with Richard in his tutorials and it works fine with that database, but doesn't in the one I'm working on...what am I doing wrong?


Reply from Alex Hedley:

See this tip.

See this thread.
Have you set the RecordSource of your Form to a SQL statement
Show Just This Thread        Post Reply
Read Find Combo Box by Darrell B @ 10/23/2015
I added a combo box to "find" a customer.  However, the resulting display of customers in not in alphabetical order.  I have over 100 customers so it is hard to find the one that I want easily.  How can I display the customer list alphabetically when using the Combo Box "find" feature?  Thank you.  Darrell


Reply from Alex Hedley:

Is the Form's data source sorted already?
Show Just This Thread        Post Reply
Read Combo Box by randy r @ 5/28/2015
When you say Foreign Key, Should that be in the "Support Table" and replaces the AuotID?


Reply from Alex Hedley:

You have two tables
They both have AutoNumbers as their ID Fields.
You want to relate them to each other so you add another ID Field to the Table, this time it is a number not an AutoNumber.
You the put the AutoNumber from one Table into the Number Field of the other Table.
You can then join them using a Query.
Show Just This Thread        Post Reply
Read Combo box by Carolyn C @ 5/26/2015
Can you force Uppercase in a ComboBox?


Reply from Alex Hedley:

You could run an AfterUpdate Event that passes the value through the UCASE Function.
Show Just This Thread        Post Reply
Read Combo box query from two separate tables by Neetiay Abbott @ 5/15/2015
The tables have all the same fields except the company table has company name, reg number etc whereas the individual table has first name, last name.

The type field is in both tables to allow the user to query the data for year end dates and filing deadlines which only apply to companies and not individuals.


Reply from Alex Hedley:

You could create an event that opens a specific form given the value in the Field.
If (comboname.Column(x) = "table" Then
  DoCmd.OpenForm "x", , , "ID="&comboname
Else
  DoCmd.OpenForm "y", , , "ID="&comboname
End If
Show Just This Thread        Post Reply
Read Combo Box by randy r @ 5/15/2015
Every time I build a report with a combo box, I always get the AutoID number instead of the value from the text field. When I look at my tables all the text fields for which the combo box's are connected to will only show the AutoID numbers.I am using "support tables" with an AutoID as the key field.Is there anyway to get text from the combo box to display versus the AutoID number?


Reply from Alex Hedley:

It's always best to store the Foreign Key as a Number.
You can then create a Query that JOINS the two together, you can then use the Name Field in your Report.
Show Just This Thread        Post Reply
Read Combo box query from two separate tables by Neetiay @ 5/15/2015
Hello,

Here is what I have:

One table for company client data (Limited).

One table for individual client data (individual).

To assist with queries in the future I created a combo box in both the above tables for the user to identify whether the record is for an individual or a limited company.

a combo table with "Limited" and "Individual" in them for the above.

I want to create a combo box on my main screen with a list of all clients in the database to allow the user to open the relevant client record.

But the records are stored in separate tables by type of client.

How do I create the link using the two client tables in combo box?  I presume it is handy I have created the "type of client" combo box in each record and table?

Thanks,

Neetiay


Reply from Alex Hedley:

I'm a little confused by the set up, if they are in different Tables why do you need to distinguish them with a field of Type.
If they have the same Fields why not store them in the same Table with an extra column of Type which is either of the two.
If this isn't an option but they have the same Fields why not create a UNION query to join both Tables together.
Show Just This Thread        Post Reply
Read Sub Form Combo Box by Ramona W @ 5/14/2015
Is it possible to have a combo box determine what subform to open on parent form?

Eg.  RequestF contains requesttypecombo. then you have a subform on the requestF.

You have 3 subforms (New, Terminate, NameChange). If I choose terminate from the combobox on the requestF, can you write code so that the correct subform (terminate sub form) opens in the requestF.


Reply from Alex Hedley:

You can set the SourceObject of a SubForm in code.
Me.CONTROLNAME.SourceObject = "FORMNAME"

When you drag your SubForm onto a Form get the NAME of it.
Change CONTROLNAME to the above NAME.
Now use the name of the Form in the NAVIGATION PANE to be the "FORMNAME"

You change set this in your AfterUpdate event on the Combo.
Show Just This Thread        Post Reply
Read Button and Combo box by vicki H @ 3/26/2015
The Else statement worked.  I had tried that but put the OpenForm in the wrong place.  Thanks so much for your help! 1 more question.  which takes priority, the build event or the VB code? When I change orer in Build event then "View Code". It doesn't seem to match.Thanks again!


Reply from Alex Hedley:

You can only pick from 1 of the 3 options for an event.
So there's Expression Builder, Macro Builder and Code Builder.
If you choose Macro Builder you can then convert it to VBA.
When it's VBA you should get the [Event Procedure] next to the Event in the Property Sheet.
If you click the "..." It will open it back up in the VBA editor where you can make amendments to your code.
Make sure to save this, save and close the Form then reopen it for it to take effect.
Show Just This Thread        Post Reply
Read Button and Combo box by vicki H @ 3/25/2015
I did add the End If statement and it still opens the form before selecting the event.
Show Just This Thread        Post Reply
Read Button and Combo box by vicki H @ 3/25/2015
I did add the End If statement and it still opens the form before selecting the event.  I think I may need to go through some more lessons to help myself on this. :-) I am at expert13 and thought i was ready to start building this db. thanks for the help


Reply from Alex Hedley:

I missed that this was your button click, you need an if else, try this:

Private Sub CreateRideEntriesButton_Click()
On Error GoTo Err_CreateRideEntriesButton_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    If IsNull(EventNameCombo) = True Then
       MsgBox ("Please select an Event")
       EventNameCombo.SetFocus
       EventNameCombo.Dropdown
       Exit Sub
     Else
         'Open Form here...
    End If
End Sub
Show Just This Thread        Post Reply
Read Button and Combo box by vicki H @ 3/23/2015
Private Sub EventNameCombo_Click()
End Sub
Private Sub CreateRideEntriesButton_Click()
On Error GoTo Err_CreateRideEntriesButton_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    If IsNull(EventNameCombo) = True Then
       MsgBox ("Please select an Event")
       EventNameCombo.SetFocus
       EventNameCombo.Dropdown  
End Sub
Private Sub Form_Load()
End Sub

I did use the BUILD EVENT builder to add the If statement.  No sure if it matters.
Both the combo and button are on MainMenuF. Whatever they do they will do for that one specific event all from the Main Menu.  
Thanks :-)


Reply from Alex Hedley:

Can you add an
End If
After the .Dropdown
Show Just This Thread        Post Reply
Read Button and Combo box by vicki H @ 3/22/2015
At this point it prompts the user to "Please Select an Event", they click "ok" and it opens the form page with no Event Name. My Combo is Unbound.  I want the user to be forced to chose an "Event Name". If I put Control source as EventName then the first event in EventQ is displayed and would display in the form.



Reply from Alex Hedley:

Can you copy your code here please.
Show Just This Thread        Post Reply
Read Button and Combo box by vicki H @ 3/22/2015
That got the message to prompt but when I click on the prompt "OK" it goes straight to the CreateEntriesForm with no info from the EventNameCombo.  Doesn't give the user a chance to select from the combo box.


Reply from Alex Hedley:

Does the Combo not dropdown?
Show Just This Thread        Post Reply
Read Button and Combo box by vicki H @ 3/21/2015
I want to click on a Button (CreateRideEntries button created to open a form)and if a combo box(has list of events EventQ)is null, I want to prompt the user the make a selection from the combo box, then click on the CreateRideEntriesButton again and it take them to the form for That Event to enter data.


Reply from Alex Hedley:

Replace 'EventQCombo' with the name of your Combo

If IsNull(EventQCombo) or EventQCombo = "" Then
  MsgBox "Please select an Event"
  EventQCombo.SetFocus
  EventQCombo.Dropdown
End If
Show Just This Thread        Post Reply
Read Search Combo Boxes by Lee A @ 3/10/2015
With Access 2007, when I add a Combo Box to the Header, I do not get the 3rd option in your 2010 lesson to : "Find a record based on my form based on the value I selected in y combo box."  How do I get to that option?


Reply from Alex Hedley:

See this blog post.
Show Just This Thread        Post Reply
Read Combo Box Auto Fill In by Ramona W @ 3/7/2015
Yes - 2 columns total.


Reply from Alex Hedley:

Can you add some Debug.Print JobTitle Or MsgBox JobTitle to your GetEmployeeInformation and see what values you get.
Show Just This Thread        Post Reply
Read Combo Box Auto Fill In by Ramona Woitas @ 3/5/2015
Sorry. Yes I do


Reply from Alex Hedley:

What's your Combo's first Field is it an ID?
Show Just This Thread        Post Reply
Read Combo Box Auto Fill In by Ramona W @ 3/4/2015
What I am trying to do is automatically populate a few fields from my EmployeeF into my RequestF fields, for an employee.

EmployeeT Fields - TeamID (#) BuildingID (#) JobTitle (Text) FullTimePartTime (Text) SalaryWage (text)

EmployeeF Fields - TeamID (ComboBox from Table) BuildingID (Combobox from table) JobTitle (combobox from table) FullTimePartTime (valuelist) SalaryWage (valuelist)

RequestT - EmployeeID (#) SalaryWage (text) JobTitle (text) Building (Text) Team (text) FullTimePartTime (text)

RequestF - EmployeeID (combobox) JobTitle, Building, Team, FullTimePartTime, SalaryWage (all textboxes)

THIS IS THE CODE I USED FOR REQUEST FORM:

Private Sub EmployeeCombo_AfterUpdate()
    GetEmployeeInformation
End Sub

Public Sub GetEmployeeInformation(Optional Employee As Integer = 0)
    
    JobTitle = DLookup("JobTitle", "Employeet", "employeeid=" & EmployeeCombo)
    FullTimePartTime = DLookup("FullTimePartTime", "Employeet", "employeeid=" & EmployeeCombo)
    SalaryWage = DLookup("SalaryWage", "Employeet", "employeeid=" & EmployeeCombo)
    Building = DLookup("Building", "Employeet", "employeeid=" & EmployeeCombo)
    Team = DLookup("Team", "Employeet", "employeeid=" & EmployeeCombo)
    
End Sub

RESULT:  After selecting an employee name - Job title (5), Building & Team (blank), FullTimePartTime & SalaryWage (are the only fields that worked).

Does this only work then if the fields on the employee form are VALUE LIST FIELDS, as opposed to using a combobox and getting the information from a TABLE instead?

Or is there a way to do it with table/comboboxes too and I just don't know what that is?

If there is a way, please let me know exactly what I need to do.

Thanks



Reply from Alex Hedley:

Have you got an EmployeeID in your EmployeeT? It's not mentioned above.
Show Just This Thread        Post Reply
Read Combo box data changed by Al @ 1/1/2015
Thank you for the response.  But still no joy..  

The intent is when the Table I am using for the combo box changes I need the underlying table to display the new description from the table I am using as the dropdown or when I delete a row from the main table I am using as the combo box..  but when I delete a row from the main table of the combo box...The underlying table still has description ...thought I'm  using the ID field to be store in the underlying table ...IF you get my understanding...


Reply from Alex Hedley:

If you have a Table with your values:
ID1 | Desc
1 | desc1
2 | Desc2
Etc

Then you have another table with
ID | Field | RelatedId
1 | data | 1
If you create a query that joins the second table with the first and join the ID1 with relatedid this will show
If you update Desc the new one will show
If you deleted 1 Desc1 table 2 will still have 1 in record 1, you would need to delete that or change the join not to show up.
Show Just This Thread        Post Reply
Read Combo box data changed by Al @ 12/31/2014
I have deleted field in the combo box but the underlying table still has the original field I inserted from the combo box...How do I get my underlying table to state value no longer in the combo box..


Reply from Alex Hedley:

Just make a query getting all the records with that value stored and delete them.
Show Just This Thread        Post Reply
Read combo box by Hamish @ 11/16/2014
Hi Alex,

I have followed your suggestion and I still cannot seem to populate the value list in the cboCategory combo box. I have also tried by removing the ORDER BY clause and just have the sql "SELECT CatID, CategoryName FROM VendorSetupT WHERE DepartID=" & cboDepart & "". Now when I try to select the cboDepart combo box. I now get the sql statement dipalayed in the cboCategory combo box like this...Category FROM VendorSetupT WHERE DepartID=GM There's still no value list in the drop down of the cboCategory combo box.

I'd sincerely appreciate it, if you're able to let me know what I'm not doing.

Regards

Hamish


Reply from Alex Hedley:

Since you've said "DepartID=GM" it appears that the source of your cboDepart might not contain the ID column.
How have you filled your combo box?
Make sure you have both the ID and the Department name.
Make sure the ID is the first Fields, you can then hide it.

Is the RowSource of the second combo a Table/Query and not a Value List as this will be why it's seeing the sql and not your values.

Without seeing the db it's hard to know exactly what is wrong but these are possibilities.
Show Just This Thread        Post Reply
Read combo box by Hamish @ 11/14/2014
Hi Alex,

I have the following VBA code in the after update of the 1st combo box (cboDepart)

sCategorySource = "SELECT CatID,DepartID, CategoryName FROM VendorSetupT " & _
"WHERE DepartID=" & Me.cboDepart & "" & _"ORDER BY CategoryName"
Me.cboCategory.RowSource = sCategorySource

The problem I'm having, is when I select a field in the 1st combo box (cboDepart) the items is the 2nd combo box (cboCategory) is blank. What could be the reason for this not to appear. Both combo box are value list.

I would appreciate you sound advice with regards to this matter, as I have wasted quite some time trying to figure this out.





Reply from Alex Hedley:

Make sure to add a space before your ORDER BY clause.

"WHERE DepartID=" & Me.cboDepart & " ORDER BY CategoryName"

Otherwise your sql statement will look like
"WHERE DepartID=xORDER BY CategoryName"

A good way to check things like this is to use a DEBUG.PRINT statement.
So you could have
DEBUG.PRINT sCategorySource
And check in your immediate window in the VBA editor (Ctrl+G)
Show Just This Thread        Post Reply
Read combo box by Hamish @ 11/13/2014
Hi Richard,

Thanks for the quick tip tutorial on how to make two dynamic combo boxes work together.

The problem I'm having is that, I get this to work if I use a select query. But how can I make this work by using a value list. Is this possible? Or is it only possible by using a query.

Regards

Hamish


Reply from Alex Hedley:

Hi Hamish,
You could use some VBA to create a Recordset Where the Query is filtered then add each item to the list.
Show Just This Thread        Post Reply
Read Combo box on a form by Carl Thompson @ 7/30/2014
Thanks Richard - I must have been asleep to miss that ! Regards, Carl
Show Just This Thread        Post Reply
Read Combo box on a form by Carl Thompson @ 7/29/2014
When first entering a form with a combo box, I want to set the focus on the combo box. How do I do this please?



Reply from Richard Rost:

Easiest solution: make it the FIRST item in your TAB ORDER.
Show Just This Thread        Post Reply
Read Cascading Combo Boxes by Ray @ 7/15/2014
Which is the best way to create a combo boxes depending on 2 others value.

For example.
Combo 1 (Brand)
HP
Samsung

Combo 2 (Category)
HP - Printer and PC
Samsung - PC and Phone

Combo 3 (Model)
When Combo 1 = HP, Combo 2 = PC
Combo 3 show only HP's PC Model.

My workout is showing both Samsung & HP's PC Model



Show Just This Thread        Post Reply
Read Combo Box by Elvin A @ 7/10/2014
Hi Richard,
I am working along with your videos in Access 2010 Beginner 8, Lesson 6, Search Combo Box.
I open my ClientF and go to Design View. Open the Header and I bring in the Combo Box. The Wizard starts but it only gives me 2 options. The search option is missing.
Where did I go wrong?
Show Just This Thread        Post Reply
Read Relationship Combo Boxes by Ray Chan @ 7/6/2014
I followed the lesson (Advance 307).
Now I managed to create the cascade combo, but the after update seems not working.

I need to Refresh it or Save it in order to have the data changed in second combo box.

I tried the same method to create after update in Advance 303.


Reply from Richard Rost:

Without seeing your database, it's impossible for me to tell you what's wrong. Did you follow the steps EXACTLY as in the video? Check all your spelling.
Show Just This Thread        Post Reply
Read Relationship Combo Boxes by Ray Chan @ 7/4/2014
Let me give an example

Brand:-
HP
Apple

Category:-
HP - PC, Printer
Apple - PC, Phone

When I select HP at Combo Box 1 (Brand)

It's will affect the Data at Combo Box 2 (Category)
Which show only PC & Printer (if HP is selected)



Reply from Richard Rost:

Have you seen my Cascading Combo Boxes tip?
Show Just This Thread        Post Reply
Read Relationship Combo Boxes by Ray Chan @ 7/3/2014
which is the best way to create a product form which has multiple relationship combo boxes.
For Example,
Combo Box 1 - by Brand (ABC / XYZ)
Combo Box 2 - by Category (TV / Phone / Camera)
Combo Box 3 - by Model

I am now in Expert 11



Reply from Richard Rost:

I'm not sure I understand what you want to do. You need to create a product form where the products can be displayed (filtered) by brand, category, or model?
Show Just This Thread        Post Reply
Read Disable based on combo box by Paul Do @ 4/25/2014
Hi Richard,
I am using MS Access 2010.  It doesn't have Oncurrent Event option.  Plus when I click on the Afterupdate,  it doesn't have Enable or locked option.  
Thanks
Paul



Reply from Richard Rost:

Sure it does. OnCurrent has been in every version of Access as far back as I can remember. It's a FORM event. AfterUpdate is another event. Enable and Locked are properties.
Show Just This Thread        Post Reply
Read Disable based on combo box by Paul Do @ 4/23/2014
Hi,
I have a Status combo box.   It contains Cancelled, Completed and Open.
I would like all the fields on the form are completely disable (not allow to edit, read only) if the user pick Completed.
Could someone kindly provide me a code along with instruction for my request?
Thank you.
Paul



Reply from Richard Rost:

In the AfterUpdate event for your combo box, just set the Enabled or Locked properties for the fields you want (or the whole form, but that will also disable the combo box). You'll also need to duplicate this in the OnCurrent event.
Show Just This Thread        Post Reply
Read Select multiple from Combo Box by CEV @ 3/26/2014
I would like to create a combo box in a form and have the option to select multiple items instead of the normal one. Then I would like to create a report that includes and item that has been selected from the combo box, whether it was selected by itself, or along with another item. Is this possible. Is there a lesson that walks me through it?

Thanks You,
Chad


Reply from Richard Rost:

This is possible, but you have to use a LIST box, not a combo box. A combo box can only have ONE item selected. You can use a multi-valued field (which were new in Access 2007 or 2010, and I do NOT recommend them) or you can use manual programming with a recordset to read/write the values. I cover programming multi-select list boxes in Access 321.
Show Just This Thread        Post Reply
Read 2 Combo Boxes by Robert @ 3/4/2014
Hi Tom,

I m not sure if this is going to help but

If you ve followed the standard convention for setting up a parent/child
relationship between ActivityTermsT and ActivityDetailsT


TABLE: ActivityTermsT    TABLE: ActivityDetailsT  
      
       ActivityDetailsTID  AutoNumber
ActivityTermsTID  AutoNumber ----------> ActivityTermsTID  Number
ActivityTermsText  Text   ActivityDetailsText  Text
      
  
And your combo box  cboActivityTermsText  contains two columns  ActivityTermsTID  &
ActivityTermsText  (in that order).


If you change the first field in your query from  ActivityTermsText  to  ActivityTermsTID  
(using the same selection criteria) -> [Forms]![ActivityF]![cboActivityTermsText]
-> this will default to the column 0 value which will be the  ActivityTermsTID .  

Note: this should work but there are many other factors that can throw a wrench in the works.
Show Just This Thread        Post Reply
Read 2 Combo Boxes by Robert @ 3/4/2014
Hi Tom,
I m not sure if this is going to help but
If you ve followed the standard convention for setting up a parent/child relationship between ActivityTermsT and ActivityDetailsT

ActivityTermsT   ActivityDetailsT  
      
ActivityTermsTID   AutoNumber
ActivityDetailsTID   AutoNumber
ActivityTermsText   Text        ActivityTermsTID   Number
    ActivityDetailsText   Text
  
And your combo box  cboActivityTermsText  contains two columns  ActivityTermsTID  &  ActivityTermsText  (in that order).
If you change the first field in your query from  ActivityTermsText  to  ActivityTermsTID  (using the same selection criteria) -> [Forms]![ActivityF]![cboActivityTermsText] -> this will default to the column 0 value which will be the  ActivityTermsTID .   Note: this should work but there are many other factors that can throw a wrench in the works.
Show Just This Thread        Post Reply
Read combo boxes on a search form by nicolas s @ 3/4/2014
Can combo boxes be used on a search form?


Reply from Richard Rost:

Yep.
Show Just This Thread        Post Reply
Read 2 Combo Boxes by Tom Houg @ 3/4/2014
I have a Microsoft Access 2007 database I am working on. I have two tables (ActivityTermsT and ActivityDetailsT). I would like to set up 2 combo boxes, one with all the terms, and then the second combo box dropdown list of details will be dependent on what activity is chosen. I have created a form titled ActivityF. All information for the form is pulled from ActivityDetailsT (because I have the terms and termID's which form relationships with the details). I then create two combo boxes. The names of the first one is cboActivityTermsText and the second one is cboActivityDetailsText. I then have an ActivityF: Query Builder to populate the combo boxes. I am using the ActivityDetailsT to pull the fields for the query.

The first field in the query is ActivityTermsText, with the criteria of [Forms]![ActivityF]![cboActivityTermsText]. The second field is ActivityDetailsText with no criteria. Then I go back to the ActivityF, and for After Update", I type in the code:

Private Sub cboActivityTermsText_AfterUpdate()
cboActivityDetailsText.Requery

So the problem arises when I go back to my form. I can select the ActivityTermsText from the dropdown list in my combo box, but then when I try to select anything from the ActivityDetailsText, the dropdown list is empty.

What could I be doing wrong?

Thanks for your help, in advance.

Tom
Show Just This Thread        Post Reply
Read Search as you type filter on combo box by Lauren Satterly @ 2/6/2014
I worked out why! Was because the field in the code was looking for text and those fields are numbers. For this I created a variable code as they are ID fields they needed to stay as numbers.

L
Show Just This Thread        Post Reply
Read Order entry Combo box by Len Jolly @ 1/19/2014
I have just created my order entry system in Access 2010, but instead of putting a combo box on each line of
the OrderDetail subform I used a ProductCombo box and an add item button in the subform footer(as we did in Access 2003).
My problem is every time I click the add button it adds the selected product to ProductT and to the ProductCombo! I think its because in Access 2003 I did not use ProductT in OrderDetailQ, but I cannot figure how to fix it. Any guidance appreciated.
Regards
Len
Show Just This Thread        Post Reply
Read Search as you type filter on combo box by Lauren Satterly @ 1/2/2014
I have the following code to search as you type into a combo box, but it keeps bugging when I complete a field. E.g. when I search for the TID (ID field) 300184 I can type 30018 but as soon as I hit 4 it bugs. I seem to be coming across this problem because I have added more than one combo box to search in different fields e.g. Contact Email, Company Name etc. because when I have just 1 it seemed to work fine. The code is below is for Combo Box 75 filtering the TID field and Combo Box 77 filtering the Gateway ID field. I only use 1 combo box at a time when searching:

Private Sub Combo75_Change()

' If the combo box is cleared, clear the form filter.
  If Nz(Me.Combo75.Text) = "" Then
    Me.Form.Filter = ""
    Me.FilterOn = False
  
  ' If a combo box item is selected, filter for an exact match.
  ' Use the ListIndex property to check if the value is an item in the list.
  ElseIf Me.Combo75.ListIndex <> -1 Then
    Me.Form.Filter = "[TID] = '" & _
                     Replace(Me.Combo75.Text, "'", "''") & "'"
    Me.FilterOn = True
  
  ' If a partial value is typed, filter for a partial company name match.
  Else
    Me.Form.Filter = "[TID] Like '*" & _
                     Replace(Me.Combo75.Text, "'", "''") & "*'"
    Me.FilterOn = True

  End If

  ' Move the cursor to the end of the combo box.
  Me.Combo75.SetFocus
  Me.Combo75.SelStart = Len(Me.Combo75.Text)

End Sub

Private Sub Combo77_Change()

' If the combo box is cleared, clear the form filter.
  If Nz(Me.Combo77.Text) = "" Then
    Me.Form.Filter = ""
    Me.FilterOn = False
  
  ' If a combo box item is selected, filter for an exact match.
  ' Use the ListIndex property to check if the value is an item in the list.
  ElseIf Me.Combo77.ListIndex <> -1 Then
    Me.Form.Filter = "[Gateway ID] = '" & _
                     Replace(Me.Combo77.Text, "'", "''") & "'"
    Me.FilterOn = True
  
  ' If a partial value is typed, filter for a partial company name match.
  Else
    Me.Form.Filter = "[Gateway ID] Like '*" & _
                     Replace(Me.Combo77.Text, "'", "''") & "*'"
    Me.FilterOn = True

  End If

  ' Move the cursor to the end of the combo box.
  Me.Combo77.SetFocus
  Me.Combo77.SelStart = Len(Me.Combo77.Text)
  
End Sub

Let me know if you have any ideas?

Thanks L
Show Just This Thread        Post Reply
Read transfer date from a list control to a combo box by Uriel Ramirez @ 10/17/2013
i have a control list in a form that display the results of a search, when i click in one of the results and press a button i want to transfer the clicked result to another form's combo box,  i try to simple set the unique id of the control list into the combo as form!combo = result_list, the bound field on the combo box and the result list are the same but when i click the button i build to run the code an error appear saying that the result_list = unique ID, which is the right result but i do not know why it does not set the combo box of the other form to that unique ID value.
Show Just This Thread        Post Reply
Read Combo Box and inactive customers by Jim Pigeon @ 10/13/2013
I'm sorry, the form is based on a sales table and the combo box is query based. The query limits the list to active customers. However, when I navigate to sales where the customer is no longer active the value in the customercombo is blank. The underlying table does still contain the correct customerID, it just isn't displaying in the combobox of the form.


Reply from Richard Rost:

That's a problem you're going to run into if your combo box doesn't include the current customer. You could change the rowsource of the combo box in the OnCurrent event of the form so that it shows either ACTIVE or ALL customers based on the current customer.
Show Just This Thread        Post Reply
Read Combo Box and inactive customers by Jim Pigeon @ 10/4/2013
I have made a combo box (based on a query limiting the list to active customers)on a single form. When I navigate to previous records where a customer is inactive the combo box is blank. What have I done wrong?


Reply from Richard Rost:

I don't understand. The form is based on the customer, and the combo box is also a list of customers? Why?
Show Just This Thread        Post Reply
Read combo box by erika @ 9/21/2013
hey, i would like to know how can i put a combo box to let user to be able to choose  two choices from the combo box?


Reply from Richard Rost:

Sorry, you can't do this with a combo box. LIST BOXES can be set to allow multiple selections, but that gets a little tricky. I talk about Multi-Value Fields in Access Expert 7. To do this PROPERLY involves some programming.
Show Just This Thread        Post Reply
Read How can I store relevant data from combo box by Hero Yang @ 9/18/2013
Hi,
I've created a order forms.
A combo box with multi-columns was created for picking a product I want.
How can I store the price in the TABLE?

Currently my Order Sub Form is based on the ProductQ to generate the price I want. It work well, as long as i don't change the product price.
If I changed my price, all exciting Orders are also changed which I DON"T want this to be happened.

Thanks


Reply from Richard Rost:

Yep... this is the problem that is presented to the user at this time. Changing ANY of the product details in the product table will cause all of your ORDERS to change. I'll be addressing this issue in an upcoming lesson. You need some EVENT programming to store the values in the Product Table. We'll do it with a macro first in the advanced classes, and then in the developer classes I'll show you how to do it with VBA code. If you want to jump ahead and see how it's done now, you can skip to my older Access 302 class where I demonstrate it with VBA in Access 2003 (code all works the same in 2013).
Show Just This Thread        Post Reply
Read Cascading combo boxes by Mark B @ 9/18/2013
Hi Richard, Regarding recording referral info between different sets of people, I have 3 tables, CustomerT, EmployeeT, SupplierT.

In my ReferralF can you have one combo box display a list of all customers, employees & suppliers. So different lists from different tables in one box. Or have a referral type combo change/update the referrer combo box with the relating table list of names.

Reading this back I'm sure I've explained myself correctly, but I hope you get the idea?

Regards


Reply from Richard Rost:

You could merge all 3 lists together using a UNION query. You could then save the ID in a ReferralID field, but you'd need a second field to tell you what kind of value that was (customer, etc.). Possible, but a little more tricky.
Show Just This Thread        Post Reply
Read Remove the scroll button from combo box by Henry @ 8/25/2013
I have a form that allows user to input items from a budget.
In the form's footer, I placed a combo box that pulls information from a query, that shows the sumof all the items in the budget.
The problem is that this box, instead of clearly showing the dollar amount of the budget, it shows a scrollable box, and the user has to scroll to get the total, can you suggest a way to avoid to have to scroll?


Reply from Richard Rost:

Well, a combo box is, by design, a scrollable box. If you just want to display ONE value, use a DLOOKUP or DSUM function.
Show Just This Thread        Post Reply
Read Scroll in Combo Box by Greg Davis @ 8/19/2013
Nice and simple Richard --- thanks a lot.
These old timers will appreciate this, they remember the days of Word Perfect when everything was down with the keyboard.
Thanks again
Greg
Show Just This Thread        Post Reply
Read Scroll in Combo Box by Gregory G D @ 8/18/2013
Richard I've been trying various ways to scroll down in a combo box when you first enter it without using a mouse. Keyboard only --- any ideas


Reply from Richard Rost:

ALT-DOWN ARROW to open up the combo box. ARROW keys to move up and down. ENTER to make your selection.
Show Just This Thread        Post Reply
Read Cascading combo boxes by Henry @ 8/14/2013
Then I create a query where I would get data from the 3 tables,
And under modelID, in Criteria, I would add:[forms]![modelcombo]![colorcombo]![enginecombo]?
The problem I have is that I am missing something and it does not work, none of the dropboxes shows info in them.


Reply from Richard Rost:

Henry, did you watch this tutorial: CASCADING COMBO BOXES? It's the same thing, but instead of one extra combo, you've got two.
Show Just This Thread        Post Reply
Read Cascading combo boxes by Henry @ 8/13/2013
If I was dealing with a car catalog and wanted the user to make a cascade selection of MODEL, MODEL SPECIFIC COLORS, and ENGINE.
Let's assume that there are 5 model, 20 colors (some of them apply to all models some not), and 2 engines, available for 3 of the models.
How do you suggest to go about a form and cascading combo boxes where I need the user to select the info from MODELCOMBO then proceed to select from COLORCOMBO that is sensitive to the model selection and last, select from ENGINECOMBO which is sensitive to the model as well.
I tried with the first combobox and works great, but as soon as I try to do a second combo box, the form will not even find the information for the first combo box,
Please help.


Reply from Richard Rost:

You'll need 3 tables:

ModelT: ModelID, ModelName
ModelColorT: ID, ModelID, ColorName
EngineT: ID, ModelID, EngineName

When the first combo selection is made, requery the other two combo boxes to filter their data based on the ModelID selected.
Show Just This Thread        Post Reply
Read Using cascading combo boxes by Henry @ 7/27/2013
I am using cascading combo boxes, they are now working although, I have 2 problems.
1.- the records are repeated, I get the same list in the drop down repeated twice.
2.- when I make a selection from the list, I get an error message "the value you entered isn't valid for this field"
Could someone please shed some light in this?


Reply from Richard Rost:

1. If you want a unique list of values, you need to use an AGGREGATE QUERY to group them.

2. Sounds like your data type is invalid (putting text into a number field, for example). Without seeing your database, however, it's impossible to tell. Is the first column of your combo box TEXT instead of an ID?
Show Just This Thread        Post Reply
Read Alphabet Group Combo Box by Margaret @ 6/18/2013
Does anyone have an easy solution to this problem?  My client list is getting quite long.  What I want to do is create a combo box on my client information form that shows alphabet groups (a-d,e-k,l-n,etc.) I would like to set up a second combo that will requery and show only the clients whose names are in the group selected in the first combo.  Ideas?
Show Just This Thread        Post Reply
Read dynamic combo box by dave @ 6/11/2013
my combo box are unbound and i can see all the data of the fields. Excpet when i run the query it doesn't show anything after i enter the criteria on the state combo
Show Just This Thread        Post Reply
Read dynamic combo box by Dave @ 6/10/2013
I created a dynamic combo box (state,city) but, i can only choose the first one of the citycombo list,  i cannot pick another city. Is there anything that i forget?


Reply from Richard Rost:

Without seeing what you've built, there's no way I can tell you for sure. Make sure your combo boxes are UNBOUND or at least bound to the right field types.
Show Just This Thread        Post Reply
Read combo box only has the first two bubbles by Mathew T @ 5/22/2013
I am wanting to create a combo box on an employee data sheet. The box would include the names of all employees, and the data would update when you select different employees. When I open the combo box wizard it only has the first two bubbles and will not even display the third option which is what I think I need to use. Help!!!


Reply from Richard Rost:

Matthew, I don't understand what you're trying to do. Can you elaborate, please?
Show Just This Thread        Post Reply
Read cascading combo boxes by Hilda @ 5/17/2013
Yes I did watch that tutorial and set up my query exactly as you did. What I am trying to understand is why a window would pop up after I have selected a campus and if this has anything to do with using a multiple items or datasheet form.


Reply from Richard Rost:

If you're getting an "enter parameter value" window then Access is looking for a criteria OR you have something spelled wrong.
Show Just This Thread        Post Reply
Read Combo box confusion by David K @ 5/10/2013
I'm finishing up Beginner Level 4 and doing pretty well.  Still running my business and I'm stuck on a couple of things that Richard will probably cover down the road, but I could really use them  now, as I learn.
1)  The combo box.  I can set it up for address, and type in the address and it finds it immediately.  But when I try to set up a combo box on record id (not the Access id, but my own assigned numbers in the table,....and it's indexed), I get error messages and it doesn't find anything.  The message is "Select an item from the list, or enter text that matches one of the listed items".  It's a number, so I don't get why it won't do what the address field combo box does.
Anybody?  What am I doing wrong with the numeric combo box?


Reply from Richard Rost:

I really can't tell what this problem is without seeing how your combo box is set up. I cover combo boxes A LOT over the next couple of classes, so I'll bet your question gets answered soon.
Show Just This Thread        Post Reply
Read Cascading combo boxes by Ana @ 4/5/2013
Hi Chris!
I need to create a Combo Box that does this:  when one selects one alternative from the list, a second combo box opens with another list.  The new list gives new alternatives for another selection. Even a further selection list opens up for a third choice.  Is this called Cascading?  
My database is about Orthodontic Treatments (braces). There are many products of many different sizes for each specific tooth.   This means for example:  I select Band from the first combo box  called Procedures. Then, a second combo box opens and I select a 36 (which is a name of a tooth). Finally, a third combo box opens and I select LL17 All this can be summarized in: I banded tooth 36 with a band size LL 17.
I look forward to your help!
Best regards
Ana



Reply from Richard Rost:

Yep. These are called cascading combo boxes.
Show Just This Thread        Post Reply
Read Add items to combo box by John @ 3/27/2013
What if that's not allowed? All 4 tables hold different type of info.  Only 2 fields are the same.
(My tables are not sports of course).

I guess the is not in list event my only option?
Thanks


Reply from Richard Rost:

The boxes themselves don't hold any data when the forms are closed. You need to add the records to the underlying tables to which they're bound. You can do this with an Append Query or an SQL INSERT INTO statement.
Show Just This Thread        Post Reply
Read Add items to combo box by John Hart @ 3/26/2013
Hi Richard,
Much thanks for the reply and explanation

What you're saying makes sense.  I did not know whoever that on close property on works if the form is open.  Makes sense but I never thought about it since I don't use it much.  In any event, that's not a problem.

I have replicated your method and I got it working just fine in my database.  I think we're miscommunicating.  Here's my scenario that I failed to explain.

I have a sports database.  There's a customer table and continuous form.  
I also have 4 other tables (football, basketball, baseball, soccer).  All 4 of these forms have a combobox named "cboCustomer" that is properly linked to my customer table.

I have tested the tip in your video in the football onclose event and it works great.  I click "add customer" and I the customers form pops up, I enter them in, and when I close it, that customer shows up in the customers combo box in the football form.

My question is, can I replicate this trick in the other 3 forms? Yes I realize I can only have them open on at a time and that's perfectly fine.

This is why I attempted to test with for statements.

Again, here's how I did it and it works.  The code below is coded in the customer table in the OnClose Event:
Private Sub Form_Close
Forms!frmFootball!cboCustomer.Requery
End Sub

I was attempting to add these:
Private Sub Form_Close
Forms!frmBasketball!cboCustomer.Requery
Forms!frmBaseball!cboCustomer.Requery
Forms!frmSoccer!cboCustomer.Requery
End Sub

Thanks again for all your help and hopefully I have explained it better this time around.



Reply from Richard Rost:

Use ONE table and ONE form for all four sports. Have a FIELD that indicates which sport it is.
Show Just This Thread        Post Reply
Read List not in combo box by John Hart @ 3/25/2013
Hi. I posted a question regarding the subject line last week but it isn't showing up. Do I need to resubmit or am I simply not seeing it?

Thanks!
John Hart


Reply from Richard Rost:

If you're not seeing it, go ahead and resubmit it. The gremlins may have eaten it. ;)
Show Just This Thread        Post Reply
Read Add items to combo box by John Hart @ 3/25/2013
Hi. I'm using access 2010 and I watched your YouTube video: "Microsoft Access Add item to Combo Box".

First, thanks for an amazing video and tutorial.
I have replicated your procedure and it works great.
However, I have multiple forums that have customers. For example:
I have a customer table and customer form.
There are 4 other tables/forms that I pick a customer from the drop down. So I tried your method on the close event when I point the line of code to that 1 form. I found out it didn't work for me to do the following:

Private Sub Form_Close
     Forms!frmCustomer1!cboCustomer.Requery
     Forms!frmCustomer2!cboCustomer.Requery
     Forms!frmCustomer3!cboCustomer.Requery
     Forms!frmCustomer4!cboCustomer.Requery
End Sub

I also tried 4 different private subs for kicks and giggles but I knew that wouldn't work

Any idea how I can make it work for all 4 forms. Only one form will be opened at a time if that matters.
Or is the not in list event my only choice in this scenario

Thanks again!



Reply from Richard Rost:

Why are you trying to requery the combo box after the form is closed, or on the other closed forms? You don't seem to understand how a combo box works. The combo box does not store data. A combo box is (preferably) bound to a table, which means that it's showing the data that's stored in the table AT THE TIME THE FORM IS LOADED. When you issue a requery, you're saying "go back to the table and get me an updated list of records." In my tip video that you mentioned, one combo box is used as a criteria to FILTER the results in the 2nd combo box, but the boxes themselves don't store any data, nor can you refer to them in code when the forms they're on are closed. I cover combo box basics in Access Beginner 8.
Show Just This Thread        Post Reply
Read cascading combo boxes by Lance @ 3/18/2013
Richard, I have the cascading combo boxes working, thanks. I have an issue on my form where I have provided the users a button to reset the form if they have enter incorrect data. But after selecting the reset button the form resets but my cascading combo box will not reset/requery and is set to the last query selection. S the selection is limited to the last selection. The only way to clear this is to close and reopen the form. What should I do to correct this problem? Here is the code for my reset button:

Private Sub Command224_Click()

On Error GoTo Err_Command224_Click

     DoCmd.RunCommand acCmdUndo

Exit_Command224_Click:

    Exit Sub

Err_Command224_Click:

    MsgBox Err.Description

    Resume Exit_Command224_Click

End Sub

Thanks Lance


Reply from Richard Rost:

How about setting the combo box values to NULL and then issuing a requery?
Show Just This Thread        Post Reply
Read cascading combo boxes by Lance @ 3/10/2013
I have followed the example that you gave in your online video. The City combo box is displaying the city, but when I add the [forms]![formName]![StateCombo] in the criteria row of State field of the City Table in the query, the query no longer displays the city info. Any idea what I may be doing wrong? Is there an example database some where that I could look at that has cascading combo boxes?


Reply from Richard Rost:

I'm assuming you changed "formName" to the actual name of your own form? I don't think any of my free database templates include that. I cover it in a couple of different lessons. Did you build the database that I built in the video EXACTLY as I did - with a BLANK database... before trying to do it in your own database?
Show Just This Thread        Post Reply
Read cascading combo boxes by Hilda @ 2/7/2013
Yes, I did.  I went ahead and ordered the 307 course and I'm now able to do cascading combo boxes in my sleep.  My next obstacle is how to cascade the combo boxes from form to form.  Let's say I have six combo boxes (1, 2, 3 on one form and 4,5, 6 on another).  How do I accomplish the transition from combo box 3 to combo box 4? Both forms are Modal Dialog.  I have added macros to open and close both forms.  Maybe that is blocking my success...  
Show Just This Thread        Post Reply
Read Combo box create update table by Ivan @ 2/7/2013
Hi,

I would like to create a combo box that will create and update my table, example if I choose 5 it will create/append a table rows of 5 in a specified table, if I choose 20 it will create/append 20 rows on the same table. How could I do it in a form?

Thanks!
Show Just This Thread        Post Reply
Read cascading combo boxes by Hilda @ 2/4/2013
Hi Richard, I'm trying to create multiple cascading combo boxes (more than three) that requery as each combo box is updated.  From your course selection, what would be the best course for me? I have currently completed, beginning, intermediate and advanced access training.  I'm have not taken sql nor VBA courses yet. I'm also using access 2010.  Thank you!


Reply from Richard Rost:

Have you seen THIS tutorial?
Show Just This Thread        Post Reply
Read filtered combo boxes by Eleanor @ 1/17/2013
It is not locked.
Show Just This Thread        Post Reply
Read filtered combo boxes by Eleanor @ 1/17/2013
Yes.  It is bound because it is used in a form to complete a table.


Reply from Richard Rost:

Sounds like the field is either locked, or it's in a non-updateable recordset.
Show Just This Thread        Post Reply
Read filtered combo boxes by Eleanor @ 1/17/2013
I have a form with two combo boxes and the second is filtered by the first combo box, cascading.  The second combo box gets filtered but it doesn't allow me to select a record from the drop down list it just sticks on the first record.

Please help.

Regards.


Reply from Richard Rost:

Is your 2nd combo box bound to a field?
Show Just This Thread        Post Reply
Read Parameter querry from a combo box by Neven Miskulin @ 9/12/2012
Hi,

I want to create a search form in which a user can choose from a combobox a criteria by which a querry can be made. I don't want to use parameter query because for example the user doesn't know which criteria he can choose.

Thanks in advance

Neven
Show Just This Thread        Post Reply
Read Combo Box Filter by David S @ 8/20/2012
Please disregard my message of yesterday.  I have resolved the problem, and, once resolved, it is sublimely obvious.  I was not using an ID Field for either my Category or BankAccount tables!  Not surpringly, if it was not there Access could not find it!
All's well now and all's also working well.
Thanks
David


Reply from Richard Rost:

Glad you figured it out. That's what I thought it might be (see my previous comment).
Show Just This Thread        Post Reply
Read Filling a textbox based on combo box selection by Susan Plotner @ 8/2/2012
Thanks! Works great now!
Show Just This Thread        Post Reply
Read Filling a textbox based on combo box selection by Susan Plotner @ 8/1/2012
I have a combo box that I am using to populate a textbox based on the selection. It seems to be working, except that I have multiple items in the subform and it is changing ALL the values in every textbox to what I just chose. I am using this code: Private Sub Asset_AfterUpdate()
Me.CpyNumber.Value = Me.Asset.Column(4)
End Sub . What do I need to do to tie the combo box selection to a specific record so it quits changing everything?
Thanks!


Reply from Richard Rost:

This will happen if the textbox in your subform isn't bound to a field. Check the ControlSource.
Show Just This Thread        Post Reply
Read Pulling a value from multiple combo boxes by Susan Plotner @ 7/31/2012
I have 4 combo boxes for different class types. I created a query that can pull the value from the first combo box. My problem is...I want the query to recognize that if the first box is null, it needs to look in the next box. Any ideas?


Reply from Richard Rost:

How about something like:

=IIF(IsNull(Forms!MyForm!Combo1),Forms!MyForm!Combo2,Forms!MyForm!Combo1)

This basically says "if Combo1 is NULL, use the value from Combo2, otherwise use Combo1."

Show Just This Thread        Post Reply
Read Populate List Box Based on a Combo Box by Brent @ 7/25/2012
I have a combo box based off a table on a unbound form with a listbox the field in the list box is column(3) what is the code I need to Select a name in the combo box and have it display all records thdat pertain to that name. I feel this has been covered before I just dont remember.


Reply from Richard Rost:

There are a couple of ways to do it. You could use an AfterUpdate for the Combo Box to rewrite the RowSource of the List Box:

MyListBox.RowSource = "SELECT * FROM WhateverT WHERE SomeID=" & ComboBoxValue

Or you can base a query criteria on the combo box value (Forms!WhateverT!ComboBoxValue) and then use that query as the RowSource for the List Box, just .Requery it when the combo box is changed.

I prefer the first method. :)

If you want me to figure out which courses I covered this stuff in, let me know... or you can use the SEARCH page, or the ACCESS INDEX.
Show Just This Thread        Post Reply
Read Update field when combo box changed by Hibbert Anderson @ 7/20/2012
How do I update a textbox in a form when I select data in a combobox on the same form?


Reply from Richard Rost:

Use an AfterUpdate event.
Show Just This Thread        Post Reply
Read Problems requerying combo box by Dana @ 6/11/2012
enjoyed your tip - but I tried to utilize the tip for a subform combo.  Tried
Private Sub Form_Close()

    Forms![REGISTRATION]![REGISTRATION Details].Form![StudentCombo].Requery
    
End Sub

Didn't work - Main form is REGISTRATION the Subform is REGISTRATION Details.  Can you advise?



Reply from Richard Rost:

Your code looks fine to me. You might want to try throwing this in before the requery command:

Forms![REGISTRATION]![REGISTRATION Details].Refresh

It's worth a shot.
Show Just This Thread        Post Reply
Read Multiple combo boxes input into one field Access by Villager @ 6/6/2012
Thank you, you have been very helpful. I have watched most of the free lessons, and have realized how great they are.  I will definitely be purchasing the Classes.
Show Just This Thread        Post Reply
Read Multiple combo boxes input into one field Access by Villager @ 6/5/2012
Okay, so now with those tables. How can i make a form to input new data.
So the form is something like

Name: John      PayRate: $20
Location1: Here
Location2: There
Location3: Everywhere

With each section of the form being a combo box and Location 1,2,3 all looking to the same table for the possible work locations.
Then i want the form to fill the datebase like.

Name     Location   PayRate
John    Here         $20  
John    There        $20
John    Everywhere   $20

The part i can't get to work is the 3 Location combo boxs.


Reply from Richard Rost:

Yep. You could pull the data out of the combo box using the .COLUMN() technique, or you could use the DLOOKUP function. Both are covered in Access 302.
Show Just This Thread        Post Reply
Read Multiple combo boxes input into one field Access by Villager @ 6/5/2012
I have a table and query set up for each Employees, Location, and PayRate for the combo boxs to retrive their list. I also have a different table where the data is stored. To answer your question: For my purpose the PayRate will be constant.


Reply from Richard Rost:

If PayRate is constant per employee, then I wouldn't make that a separate table, just a field in the Employee table. Here's how I would set it up:

EmployeeT: ID, FirstName, LastName, PayRate, etc.
LocationT: ID, LocationName, etc.
JobHistoryT (Junction Table): ID, EmployeeID, LocationID, PayRate (for history, copy from EmployeeT), JobDate, etc.
Show Just This Thread        Post Reply
Read Multiple combo boxes input into one field Access by Villager @ 6/5/2012
I have a database that holds employee names, work location, and pay rates. Many of employees work in a different location each day. So i would like to make a form that tkaes the employees name, 4 or 5 work locations and the employees pay rate. then put it in the database.
ex:
Form
Name: John Doe
Location: Here , There, Everywhere
    Rate: $20

DB:
Name  Location    Rate
John    Here       $20
John    There      $20
John    Everywhere $20

I am using combo boxs in the form. Is this possible?


Reply from Richard Rost:

This is going to be a pretty complex setup. You'll need at least 3 tables: Employees, Locations, WorkHistory, and then possibly a junction table for two or three of them. A few questions though: is the PAY RATE always the same for each EMPLOYEE, or does it change based on the job or location? That makes a huge difference.
Show Just This Thread        Post Reply
Read Combo Box Validation by Sam F @ 12/4/2011
Hi Greg
Thanks for the suggestions.
The reason I went down the VBA route for validation was because there is no effective way to use an input mask etc for a UK phone number. (too many variations etc)
Since I already had a VBA procedure that worked and a Not-In-List procedure that worked I thought it might be easy to merge the two.
Of course, I was wrong hence the post.
Show Just This Thread        Post Reply
Read Combo Box Validation by Greg Beben @ 12/3/2011
Sam, this is a bit lame, but if the phone number is being entered with only numbers (no parentheses or -) then it would work. You could use the "Validation Rule" property in the table design. Set the rule to something like "Between 0 and 10000000000". Then use the "Input Mask" property for the combobox on the form to set the formatting for a phone number. There are probably better ways to do this, but this way should work, IF the person enters just numbers.
Show Just This Thread        Post Reply
Read Combo Box Validation by Sam F @ 12/2/2011
I have a combo box which I can add values to using the Not In List event.
I also have some VBA code to "validate" a phone number. (Actually, it only checks to see whether a number is being added, as
opposed to any letters etc but this is all I require)

Seperatley, they both work well. But I'd like to be able to have them work together. So, is it possible to add a number that is Not In List and then have it validated ?

So far i've tried incorporating the validation code into the Not In List event code without success. I've also tried the Afterupdate, On Lost Focus and  On Exit events of the combo box but they just seem to be ignored.

Any Ideas ?

Show Just This Thread        Post Reply
Read Update Form via Combo Box by Sam Finlay @ 11/3/2011
Hi Guys
I have a combo box with a list of suppliers. I thought I had sussed out a good way to add another supplier to the list. Since access 2007 it has, apparently, been possible to specify a "List item edit form" in the combobox Data tab in its property sheet. No code is involved and it works up to a point, however :
a. Before the designated form opens up to add a record, you get the standard "The Text You Entered Isn't an Item on ther List" message box. Is it possible to change this to a cudtomised message box ?
b. The form opens up at the first record. Can this be changed to open up to a new, blank record ready to enter data ?
If any of this is not possible, is there some other way to achieve what i'm looking for ?
Many thanks
Show Just This Thread        Post Reply
Read Fill combo box from table value by Scott Adkins @ 10/21/2011
1. I really only need to move the part value because the rest of it is kind of fluff for reports. It is also stored in the parts table for the relational aspect.

The incomplete table is an imported table from an excel spreadsheet that is emailed to me every day. I dont know if this matters or not, but it is an excel.csv.

The unique things that I need from this are the date, quantity, customer, and part.

date and quantity are easy because they merge right over as numbers. price comes from the parts table, so that is a simple dlookup(except for this little problem.)

On the completed side, the part is a combobox, (customer will be too, and I think the same fix for this will fix that). Otherwise all the other fields will come from Dlookups to the parts table.

The whole problem is this:

incompleteSalesf![part] is a textbox and it needs to move to CompletesalesF![PartName] as a combobox.
Show Just This Thread        Post Reply
Read Fill combo box from table value by Alex Hedley @ 10/21/2011
Hi Scott,

One thing I usually do is write everything down that I want doing and have the values I wish to get on a piece of paper first so I know I'm getting the correct result. Then I check them one step at a time so I know if there is a mistake it is the last thing I have done and I'm not searching through a lot of code.

So you have 5 values you wish to copy from one place to another.
They are from the IncompleteSalesT going into the CompleteSalesT??

part = Number
price = Number/Currency
SaleDate = Date
Customer = Text/Number?
TransactionNumber = Number

What data types are those? Can you amend the above list please.

I think I misread one of your posts and this line is the one that is causing problems.
Forms![salescompletef]![part].Value = Forms![incompleteordersf]![PartName]

If I am correct [part] is a Number but [PartName] is text.
This is why the error is occurring.

Is PartName the combo on the incomplete side?
If you need it to be text this is where the
Forms![incompleteordersf]![PartName].Column(1) is needed instead so you are passing the text name to the other table.
Try just that on it's own to check the value is correct.

[Why aren't you passing the partnumber (PrimaryKey) and using a query to get the name at a later stage?]

Al
Show Just This Thread        Post Reply
Read Fill combo box from table value by Alex Hedley @ 10/20/2011
msgbox DLookup("price", "partst", "ID=" & Forms![salescompletef]![part]) to see what is being returned.

part - That's the combo box?
What columns does it have?
Is it made up of a hidden ID column and a text description?
You might need to use the .Column(#) property of the combo to get the correct value.

DLookup Tip.
Show Just This Thread        Post Reply
Read Fill combo box from table value by scott adkins @ 10/19/2011
It is the third line that is highlighted, and that makes sense. I would expect the error given the text is the field that is displayed, and the primary key is a number (for the combo box).

What I need to figure out is how to get the thing to recognize the text as the record in the parts table.
Show Just This Thread        Post Reply
Read Fill combo box from table value by Alex Hedley @ 10/19/2011
Which line is highlighted when it throws the error.
Try adding breakpoints in your code and check if the values are there.

Run Time Error 2113

Mostly this occurs when you try to store Text in a Numeric field for example, that isn't allowed, and it will throw this error.

What data types are your fields in the table?
Do some of them not match?

You might have to convert the result before you assign it.

Dim LValue As Integer
LValue = CInt(8.5) 'Rounds to 9

Or
CCur( expression ) 'For Currency

Al
Show Just This Thread        Post Reply
Read Fill combo box from table value by scott adkins @ 10/18/2011
ok I made a new one that is pretty sterile but follows the same problem, and I get a new run time error 2113 The value you entered isnt valid for this field.

same principal though, parts table, imported incomplete sales table, and complete sales table with the combo box on the complete sales table based on the parts table.

here is the code:

(and I also took the sql from a find unmatched query to make the delete query that you will see in the code.
Private Sub Merge_Click()

DoCmd.OpenForm "salescompletef"
    DoCmd.GoToRecord acDataForm, "salescompletef", acNewRec
    Forms![salescompletef]![part].Value = Forms![incompleteordersf]![PartName]
    Forms![salescompletef]![price].Value = DLookup("price", "partst", "ID=" & Forms![salescompletef]![part])
    Forms![salescompletef]![SaleDate].Value = Forms![incompleteordersf]![Date]
    Forms![salescompletef]![Customer].Value = Forms![incompleteordersf]![Customer]
    Forms![salescompletef]![TransactionNumber].Value = Forms![incompleteordersf]![TransactionNumber]
    DoCmd.OpenQuery "deletecompletedsalesq"
End Sub

hope this helps

and thanks again
Show Just This Thread        Post Reply
Read Fill combo box from table value by scott adkins @ 10/18/2011
alex, i can email it to you, but i dont want to post it because there is some proprietary stuff on there. if you are ok with that, email me to sadkins@amtci.org. If not I get that.
Show Just This Thread        Post Reply
Read Fill combo box from table value by Scott Adkins @ 10/18/2011
Alex, I havent gotten to the delete part yet. I cannot get the thing to fill the combobox.

Ill post the code in a little bit though
Show Just This Thread        Post Reply
Read Fill combo box from table value by Alex Hedley @ 10/18/2011
Hi Scott,

You mention your steps previously:
Now the complete table has a form with a combobox for the part. I have a command button that when clicked
1. Opens the complete record form
2. Fills all the pertinent fields(one of them is this combobox.
3. Close the incomplete form and delete this record, as it is now in the complete record table.

How are you deleting the record?
Can you put up the code.

Richard mentioned the table being LOCKED. This could be the case that you still have the table open or that you are using it say to build a query for part of a form.
I'd close everything that isn't needed and then delete the record you need afterwards.

Alex
Show Just This Thread        Post Reply
Read Fill combo box from table value by Alex Hedley @ 10/17/2011
I had a look for the error code and found it was

Access 2007
3465 The disk drive you are attempting to access is unreadable.

Are you running a delete query? Or are you using a recordset?

Do all these processes work independently but when joined together produce the error.

If you add "On Error Resume Next" then run the code does the record delete?
Be careful using this, it doesn't fix problems just bypasses them.

Al


Reply from Richard Rost:

Do you have permissions on this table? Is your hard drive full? Is the table LOCKED by another user or process?
Show Just This Thread        Post Reply
Read Fill combo box from table value by Scott Adkins @ 10/16/2011
I think I am confused myself. So here goes: There are 3 tables; incomplete records, complete records, and a parts table.

The parts table has 3 fields; primary key(autonumber), part number, part description.

The excel report feeds into the incomplete records table. It shows the part number as a text string.

Now the complete table has a form with a combobox for the part. I have a command button that when clicked
1. Opens the complete record form
2. Fills all the pertinent fields(one of them is this combobox.
3. Close the incomplete form and delete this record, as it is now in the complete record table.

I keep getting run time error 3465 I think.
Show Just This Thread        Post Reply
Read Fill combo box from table value by Alex Hedley @ 10/16/2011
You just need to create a foreign ID in the Sales table, call it RatingID.

This will create a relationship between the two tables.

Now create a combo box with two columns, a RatingID and Rating (hide the ID column).

Set the control source of the RatingID in the Sales data entry form to the value of the combo box.
Now when you select a rating the value you will be saved to the Sales table.

Al
Show Just This Thread        Post Reply
Read Fill combo box from table value by Scott Adkins @ 10/15/2011
I guess. I put this in here to help sort out the stuff. The "parts" are already there. I need it to recognize the "partnumber" as the "partid" from the "parts" table.

Maybe this be easier. Say you were selling movies. One of the things that comes with the sale of a movie is the viewer rating. The cash register sends you a report that you sold JAWS. In the report it tells you that JAWS is rated R, and it is a horror movie.

Now you want to be able to see how many "R" movies that you sell over the period. So a movie ratings table has RatingID, Rating, and Rating description.

The report tells me that I sold JAWS and it is rated R. I need to be able to move this information to a combo box so it recognizes R as it would in the combobox as RatingID 3 for example.
Show Just This Thread        Post Reply
Read Fill combo box from table value by Alex Hedley @ 10/15/2011
Hi Scott,

Are you wanting a combo box filled with all the part numbers from the "Parts" table on the form you have created for the "Sales" data entry so you can assign a part number to a sale?

Alex
Show Just This Thread        Post Reply
Read Fill combo box from table value by Scott Adkins @ 10/15/2011
Hi all. I am still wrestling with this little problem. I am hoping for some more great ideas from the gallery. So, here is the whole litany:
I get a report that is automatically generated in an excel.csv file. I import that to a table that holds incomplete records-“incompleterecords”. One of the fields, lets say “partnumber” for example, is stored as a text field. It is a 2 digit code that is either a letter and a number or 2 numbers.
I have a table that we will call “parts”, which also has the “partnumber”, primary key “partid” (numbers only) and a part description that is text (for pretty-ness on reports).
Now, on the “sales” table, the “partnumber” field is a combobox. This table also has a data entry form. This is done for moving the record from incomplete to complete, and also to “check the accuracy of the report”.
I want to be able to use a button that will add the value to the combobox automatically, then it can be changed if necessary.
Thanks again in advance for your help.
Show Just This Thread        Post Reply
Read Open Form with a Combo Box by Mr. P @ 10/3/2011
Hi,

Need to know how to open different forms with a combo box?  example: combo box has three fields (1) User Form, (2) data form, (3) Log Out.  

user picks data form and the combo box redirects you to the data form

- Will


Reply from Richard Rost:

AfterUpdate event with a little logic. Something like...

If MyCombo = 1 then
   docmd.openform "UserFormF"
elseif MyCombo = 2 then
   docmd.openform "DataFormF"
else
   docmd.quit
end if

You can put this in the AfterUpdate event of the combo box, OR make a "GO" button for after they make their selection.
Show Just This Thread        Post Reply
Read DOUBLE Cascading Combo Boxes by ellesha @ 5/18/2011
Hi Richard,

For Double cascading combo boxes tutorial, do you show how to do it in a continuous form? I am abit stuck in a continuous form for these function.


Reply from Richard Rost:

I use a single form. I don't think it would be possible to do it with a continuous form because when you change the rowsource of any combo box it will change that object for ALL of the records. "Combo1" is "Combo1" for every instance of the form.
Show Just This Thread        Post Reply
Read DOUBLE Cascading Combo Boxes by Alex Hedley @ 5/16/2011
Now that sounds cool,
Looking forward to it :)
Al
Show Just This Thread        Post Reply
Read DOUBLE Cascading Combo Boxes by Richard @ 5/16/2011
I just recorded a lesson for the SQL SEMINAR PART 2 that includes DOUBLE cascading combo boxes. It's like a DOUBLE RAINBOW, only better. Ha ha.

What does this mean? Well, you're probably familiar with the concept of a cascading combo box where you pick a country, then the next combo box is filtered to only show states from that country. That's easy enough.

Well, this lesson not only shows THREE combo boxes (country > state > city) but it shows you how to go BACKWARDS. In other words, I know the CITY, so filter UP to show the state that city is in, then show the COUNTRY that state is in, then refresh all of the combo boxes to show valid choices.

Real cool stuff. That was lesson 24 in the SQL2 seminar (actually it's a bit long, so I'll have to chop it into 2 lessons).

Finishing the seminar up now and it should hopefully be online tonight!
Show Just This Thread        Post Reply
Read Combo Box to Change Search Field by Greg @ 2/23/2011
Regarding Neil's question about searching and Richard's answer.  Richard, great answer, but here's a further question.  I like to have a search work where it brings you to the specific record you're looking for, but with all of the other records still present, so you can still scroll to other records when you're done with the one you wanted.  Your solution, I believe, would leave you with the form only showing the particular records which met the search criteria.  Then you'd have to have another button to reload all records or something when you were done with that record.  Can you work this so that the code merely brings you to the proper record without changing the underlying record source?


Reply from Richard Rost:

Yes, you certainly could. Just open the form without custom SQL and then you could use a Docmd.FindRecord command to find a specific record using the field name and criteria. I believe it was covered in Access 307.
Show Just This Thread        Post Reply
Read Combo Box to Change Search Field by Neil @ 2/22/2011
I am trying to put a search function on my database. It is a very small db with only one table that lists equipment part numbers, serial numbers and a few other criteria.

I would like to use a dropdown box to select the field that is to be searched and am unsure how to do it. For the actual search, I have used individual queries for each column to be searched and done a parameter value search (which I found on your website thank you).


Richard Answers:

So you want the drop-down box to have a list of FIELD names in it? That's certainly doable. OK, let's say you want to be able to search on FirstName or LastName. Make a static combo box with just one column and put those values in it. Name the combo box "FieldName". Now make a blank text box called MyParameter where the person will actually type in the value they're looking for.

Now, make a command button with the following code in it:

DoCmd.OpenForm "CustomerForm" ' whatever your customer form is called
Forms!CustomerForm.RecordSource = "SELECT * FROM CustomerTable WHERE " & FieldName & "='" & MyParameter & "'"

When Access compiles that SQL Statement, it will replace the variable names with your data and you'll get something like:

SELECT * FROM CustomerTable WHERE FirstName='Joe'

So the code will open the form and then set the RecordSource property of your form to that SQL statement, basically showing any and all records that meet that criteria.

I cover this in more detail in my Access 311 class:

http://www.599cd.com/site/courselist/access/access311/
Show Just This Thread        Post Reply
Read Combo Box Question by Fischer @ 11/27/2010
Hey Rich
I'm one of your students with a question.  I've built a company form and have used several combo boxes in it but for some reason the combo box never asked me what information i want to store for instance i have a combo box for states the fields are stateid and stateabbviation and statename it never gave me the option on picking what i want to store the stateid, the stateaddviation, or the statename and when i run the form in form view everything works but i built a contact information form heres how i did that i made a table with contact information fields then i made a query table using both company information and contact information fields and built the for using the query table and when i use the form it is pulling information just from the id's the autonumber its not puting the information i want i thought would show from the combo example stateid is being put in to the contact form witch is number 13 not the state name which i want and all the combo box fillins are just showing the id autonumber how do i fix this?????


Reply from Richard Rost:

Normally, you only want to store the ID from that combo box in the underlying table. For example, if you have a StateID, that's what you want to store in your customer table. If you want to get additional information out of that combo box, you will need to do it with an AfterUpdate event, utilizing the .Column() property. However, you're better off just storing the ID and then using a linked query to get that information if you need it again in the future.
Show Just This Thread        Post Reply
Read Combo Box that popultes a field by Gary @ 8/22/2010
Ron,
You can approach this in two different ways. However I believe this solution would be the best approach, as you could come back in a future date and update the due amounts if needed.
1st I’m assuming you already have two tables
1. A table containing your Customer information.
2. A table containing your Membership information Tierlevel & TierAmt. If this table contains an ID field please delete it in design view.
On your form based off your Customer table,
1. With the wizard turned on, add a combo box to your form
2. From the wizard choose “I want the combo box to look up the values in a table or query”.
3. Select next, and then pick the table that contains your Membership information.  
4. Select next, then using the >> button; add your two fields Tierlevel & TierAmt to the “Selected Fields” window.  
5. Select next, in this area choose how you want the data in the drop down window to be displayed.  I suggest picking ascending on the Tierlevel field.  
6. Select next and you get a quick view of what the data sort will look like.  
7. Select next, now this is one of the important parts. Here you have to decide what “value” you want stored in your table. IE Tierlevel =Associate or TierAmt= $65.00.  
8. Select next, here we choose the option “Store that value in this field” and from the drop down choose the field in your table where you want to store the data.
Now this last part is optional, on the property sheet of the combo box change the value of the “Column Widths” to 1”;0” . this will hide the 2nd column displayed in the combo box.
I also suggest watching this video in the tips & tricks sections “Microsoft Access Relational Combo Box”
Hope this helps….
Gary
Show Just This Thread        Post Reply
Read Combo Box that popultes a field by Ron @ 8/14/2010
Our local Chamber has database that contains all their member information.

One part is for membership dues that are tier based.  A member may register at an Associate level and pay $65 a year; a Business level pays $100, etc.

There are five total levels a

Tierlevel TierAmt
Associate $65.00
Business $100.00
Entrepreneur $175.00
Premium         $300.00
Executive $650.00
President's $1,200.00

OK, so I would like to have a combo box with the Tierlevels. When selected, they would place the TierAmt in a field either in the same table as the TierLevel or another one.

I am a novice, so hope someone can help me with a sample AfterUpdate or how I could use a Lookup function.

Thanks!!
Show Just This Thread        Post Reply
Read Populate combo box with option group selection on by Tammie @ 7/26/2010
Hi Richard,
I am a beginner Access user and I am using 2007 version. I have an option group on my main form called OptionGrAgeCategory which has 2 choices: Neonatal and Adult. I have a combo box on a subform within this main form and it's called cboTypeofTherapy. I have 2 lookup tables that I'd like to populate the combo box with depending on user selection. So, for example, if the "Neonatal" option is chosen on the main form, then I would like to populate cboTypeofTherapy (on the subform) with data from the table NeonatalTherapy. If "Adult" is selected, then the combo box would be populated with data from the table AdultTherapy.

I am using If/Then/Else/End If to indicate RowSource changes of the combo box in the AfterUpdate of the Option group, but it's not recognizing the subform. I'm not sure how to correct this or if this is the right approach. Any help would be great.


Answer from Richard Rost:

This tutorial should help you. It's not EXACTLY what you're looking for, but it will get you started. Instead of populating the data in a 2nd combo box, you're just going to apply the same technique to the data in a subform or second form.

I get asked this question all the time. You've got two or more combo boxes and you want the first one to filter the second one. I get asked this so much that I recorded a video tutorial explaining how to do it:

http://599cd.com/tips/access/two-combo-boxes?key=BlFAlEx
Show Just This Thread        Post Reply
Read Combo Box Lookup by  Carlos @ 5/6/2010
Hi Richard. I have a database , with a lot of fields in a form . But I am not sure how to select a field from a combo box and bring information from another table by selecting a field from the combo box . Gracias de Mexico.. very helpfull information ..


Reply from Richard Rost:

Carlos, what you need is an AfterUpdate event and something like a DLOOKUP function to pull data from another table. You can find out more about DLOOKUP and AfterUpdate events here.
Show Just This Thread        Post Reply
Read Cascading combo boxes by Astro @ 5/5/2010
I have now copy/pasted around 5 different examples of cascading combo boxes, and no matter how I try, the second box just turns up blank. I have gone through step by step tutorials several times, and nothing works. I noticed when downloading a sample database, that the combo boxes on the forms were unbound. Is this perhaps a requirement?

I am a total beginner with Access. My tables are set up as follows:

tblCategories:
ID                AutoNumber   (PK)
CategoryName      Text

tblSubCategories:
ID                AutoNumber   (PK)
SubCategoryName   Text
Category          Number       (FK)

Both category and subcategory are foreign keys in my tblInventory table, over which I want a form in which the subcategory combo box is filtered by the category combo box. I have tries all manners of VBA to update the rowsource of cboSubcategory on the After_Update event of cboCategory.

Is there some fundamental flaw to my approach, or do you think I have simply misunderstood some crucial step in the process?


Answer from Richard Rost:

Here is how I handle cascading combo boxes:

I get asked this question all the time. You've got two or more combo boxes and you want the first one to filter the second one. I get asked this so much that I recorded a video tutorial explaining how to do it:

http://599cd.com/tips/access/two-combo-boxes?key=BlFAlEx
Show Just This Thread        Post Reply
Read Opening reports using combo box by Kirthika @ 4/12/2010
Hi,

I need to open some reports using combo box. I am new to access. I have created a combo box. In the after update event of the combo box i gave

DoCmd.OpenReport Me.Report, acPreview

Then in the Row source i gave

SELECT Name FROM MSysObjects WHERE Type=-32764 ORDER BY Name;

with Row source type as Table/Query.

My combo box name is Report.  However when i click the report name in the combo box, the corresponding report do not open. I don't understand what is the problem.


Answer from Richard Rost:

First of all, rather than parsing the MSysObjects table, you can make your own Reports table that has a list of all of the reports that you want in your combo box. This allows you to also give them a "description" that you can show in the combo box, and have the actual name of the report as the "bound" column - so instead of seeing "AcctRcvblR" the user can see "Accounts Receivable 30 days" for example. The downside is that you have to populate this table yourself, but you get more control over it.

Now, let's say your table is called ReportT, and you have fields: ReportName, Description. Call your combo box ReportCombo. You would just need the following code in the AfterUpdate event of your combo box:

Docmd.OpenReport ReportCombo, acViewPreview

That's it.

If you still want to parse the MSysObjects, however, I suspect the problem is that your OpenReport command is wrong.

First, it should be Me!Report not Me.Report (assuming Report is the name of your combo box). Second, it should be acViewPreview, not acPreview.

Those two things would make a big difference.
Show Just This Thread        Post Reply
Read Populating a payout based on 2 combo boxes. by Michael @ 3/12/2010
I have a Form with a ItemCombo and ServiceCombo.  I want a textbox to populate with the correct payout based off the combo boxes.  Each item has 6 services available and pricing is different for each one.  I have a ItemsT for the item and each payout associated with that item. I wasnt sure how to use data from multiple combo boxes.  I also want it to store that value in an ItemPay column, for up to 4 items.


Reply from Richard Rost:

Michael, you'd need a little programming to do this. You would need an AfterUpdate event in the second combo box to perform a DLOOKUP based on both of those values, and then set the value of your ItemPay field.
Show Just This Thread        Post Reply
Read Query for a combo box by Richard Rost @ 12/20/2009
Mike, I would have to see the form to tell you exactly what's going on. The only thing I can think of is that the combo box can't find the data because the query hasn't run yet because the form hasn't fully loaded. You're in a catch-22 situation. Instead of a query, how about using that SQL code in the ControlSource for the combo box directly? Give that a try.
Show Just This Thread        Post Reply
Read Query for a combo box by Mike @ 12/17/2009
I am working on a project similar to Access 302..  I have an Items list  and a combo box below to add items. I decided that I would use a query to create the combo box, because i wanted to use criteria that the user could enter on the form. So far so good.  I then created an option box for additional criteria. The query still worked fine but the Combo box could no longer find any data  ??
Notice the time of the email.  thgis is sooo addictive.
Thanks..
mike
Show Just This Thread        Post Reply
Read Combo Box Limit To List by Richard Rost @ 10/9/2009
Paula, I cover the OnNotInList event in Access 324, which I see you've taken. Unfortunately, if your combo box is BOUND to a field with an ID, there isn't any way you can NOT add the item to the related table and still show the value in the combo box.

You can do this if you are storing the value as TEXT in your table, and not a related ID. Create the combo box as normal, but instead of making the first column the ID value as I've always shown you, make the first column the VISIBLE text. Then store this in your field.

For example, let's say you've got a list of favorite movies. We'll call it MovieT. Each movie has an ID and MovieName.

Now in your employee table, EmployeeT, you want to store each employee's favorite  movie, but you don't want to limit it to the list. So, you make a FavoriteMovie field which is a TEXT field.

Now, on your Employee form, make a combo box that's bound to FavoriteMovie, and have it get it's list of values from the MovieT, but you WON'T be pulling the ID into the box. You're storing JUST the text. Now you can turn LimitToList off, and type the value in straight into the box if the value isn't in the list.

The DOWN SIDE to this is that you no longer have a relationship, and you'll be storing extra data (possibly redundant data) in your Employee table.

The PLUS SIDE is that users can pick from the list OR type in whatever they want.

Does this help?
Show Just This Thread        Post Reply
Read Combo Box Limit To List by Paula @ 10/8/2009
Hello Richard,
   I have a combo box that is ‘Limited to List’. I put code in the “On Not in List” Event. When someone enters an item not in the list, a message asks “Do you want to add this to the list”.  If they Click “Yes”, it adds the item to the list and the item they typed in the field is fine. The user can then go on to the next field.  
   Question: I cannot figure out how to code this if the User Clicks “No”, and merely wants to have this item entered in this field this one time, and not add it to the list. It won’t let them, and a message tells them to choose from the list.

Is there a Lesson on how to do this? Thanks.
Paula
Show Just This Thread        Post Reply
Read multi value combo box by Richard Rost @ 8/27/2009
Sally, I have covered multi-select list boxes. Unfortunately, they're not easy to work with. They almost always require some programming to get them to work right. Here's a free tutorial I recorded on them. I cover them in more detail in Access 320-329.
Show Just This Thread        Post Reply
Read multi value combo box by Sally @ 8/24/2009
Hi Richard,
I did a multi value combo box containing contact names of people to be assigned the same project. When I choose the names from the form, the table will show me each name with commas in between. I can't seem to get the ContactIDs for each name though.  It only gives me back the first persons name ID. How do I get it to show all of them?

And, on my project details form, it will show the same project data on 6 records (when moving forward with the arrow)  if I had 6 different people assigned that project. I want one record with just the listing of the people assigned. Have you covered multi value combo boxes in a lesson?
Thank you so much.  
Show Just This Thread        Post Reply
Read Update database when combo box value is changed by Richard Rost @ 1/21/2009
Jan, this is certainly possible, but without knowing exactly how you have everything setup, I can't tell you what to do SPECIFICALLY. You can simply create an AfterUpdate event for your combo box so that after it runs, you update the values in your other fields. See THIS TIP for instructions.
Show Just This Thread        Post Reply
Read Update database when combo box value is changed by Jan @ 1/20/2009
I tried to do this but it doesn't work. Do you have any other suggestions. All I want to do is when I change the value of a drop down menu. I want to dynamically update the query that is related to that value. Like for example I have a drop down of dates and 2 text boxes and 1 other drop down menu of certain values. I want to be able to update all 3 data structures using the 1 drop down that has dates. And as I change the dates I want to change the values. Is this possible???? Please any help would be nice.
Show Just This Thread        Post Reply
Read Update database when combo box value is changed by Richard Rost @ 1/20/2009
Jan, you can use an Update Query (or a RecordSet) in an AfterUpdate event for the combo box to run through your database and make changes. You can find lessons for how to do this under the Access section, or look in the Tips & Tricks area: http://www.599cd.com/tips
Show Just This Thread        Post Reply
Read Update database when combo box value is changed by Jan @ 1/2/2009
Hello,

I am wondering how to update all the text fields, other combo boxes, and memo's when values are change in a combo box in MS Access 2003.

For example, I have a form that when values are change of a combo box that has dates for every quarter. I want the values that I have set for the other quarter to update. I don't know how to set and save for the values either. Any help with be appreciated.

Thanks,
Jan
Show Just This Thread        Post Reply
Read dynamic cascading combo boxes by Richard Rost @ 10/25/2008
Dwayne, yes you can cascade multiple bound combo boxes. I show you how to do this in Access 313:

http://599cd.com/site/courselist/access/access313

The example is a list of Products and Product Categories. When you select a Product Category, you see only Products from that Category. I show you how to do it with dynamic SQL (rewriting the RowSource of the box) but you could just as easily do it with queries.

Also see:

http://599cd.com/tips/access/two-combo-boxes
Show Just This Thread        Post Reply
Read dynamic cascading combo boxes by Dwayne @ 10/23/2008
Richard can you cascade combo boxes that are bound? What I am trying to do is pull data from a separte table and populate another. Now the trick for me is that when I am using a form menu I would like to see the selection that I made earlier, is this possible?

thanks,
Show Just This Thread        Post Reply

Collapse All Topics

 

Post Your Comments or Subscribe
    If you would like to be notified of new posts on this forum,
    just enter your email address below. It will be kept private.
 
If you just want to subscribe to get email updates when this forum is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
 
  Your Name:  Required
  Your Email:  NOT Public
  Subject:
  Comments:

 

Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 4+8:
  
  Notify me when the this forum is updated
  Remember Me for my next comments
  
 
 
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be.
As always, I promise to never give away your personal information to anyone else, ever.

NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard

 

 

You may want to read these articles from the 599CD News:

8/15/2019Last Chance for Access CDs
8/1/2019Customer List Form Template
8/1/2019New Template Access Customer Database
7/30/2019Microsoft Access Developer 13
6/20/2019TIP: Validation Rules in Access
11/20/2018Microsoft Access Developer 12
8/25/2018NEW: Access Dev 9, 10, 11
8/25/2018Microsoft Access Developer 11
8/25/2018Microsoft Access Developer 10
8/25/2018Microsoft Access Developer 9
 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP