How do I have a combo box only show selected values?

[ducky]

I am working with a form that contains two text boxes and a combo box. I want the combo box to only show you the options that are available to you after you enter two seperate values in the previous text boxes. I have the combo box showing three columns - two are the text box values and the other is a value that was not included in the text boxes. Once I select the one I want, I need to be able to print a report for the values I selected.

[NeoPa]

Perhaps if you post what is in the various fields we can understand what it is you want!?!?

[ducky]

I have a text box with Year
I have a text box with Make
I have a combo box with Model / Make / Year
I want the combo box to only show the vehicles with the year and make that were selected in the text boxes. I will also need to print a report showing the record that was selected.

[NeoPa]

That's much more helpful.
Can you also explain how the data behind it is stored.
Is it a form linked to a single table (or updatable recordset) with records which all have all of those fields?
Or maybe the two TextBoxes must be made up from the data within the whole recordset?

This would be very helpful to understand what you need.

[ducky]

That's much more helpful.
Can you also explain how the data behind it is stored.
Is it a form linked to a single table (or updatable recordset) with records which all have all of those fields?
Or maybe the two TextBoxes must be made up from the data within the whole recordset?

This would be very helpful to understand what you need.

The data related to those text and combo boxes are all stored in one table - tblVehicle. The combo box gets the details from the tblVehicle, and lists them in the combobox.

[NeoPa]

Assuming the textboxes are called txtYear & txtMake and that the combo is cmbModel.
The tblVehical fields are Year, Make & Model.
Lastly, that all fields are strings (text).

cmbModel.RecordSource = "SELECT [Model] " & _
    "FROM [tblVehicle] " & _
    "WHERE (([Year] = '" & txtYear & "') " & _
    "  AND ([Make] = '" & txtMake & "')) " & _
    "ORDER BY [Model]"

Another alternative is to use Combos for all fields where the the selection list is extracted from the same table so :-

cmbYear.RecordSource = "SELECT DISTINCT [Year] " & _
    "FROM [tblVehicle] " & _
    "ORDER BY [Year]"

and

cmbMake.RecordSource = "SELECT DISTINCT [Make] " & _
    "FROM [tblVehicle] " & _
    "ORDER BY [Make]"

Obviously the txtYear & txtMake in the cmbModel.RecordSource code would have to change to cmbYear & cmbMake respectively.

[ducky]

Assuming the textboxes are called txtYear & txtMake and that the combo is cmbModel.
The tblVehical fields are Year, Make & Model.
Lastly, that all fields are strings (text).

cmbModel.RecordSource = "SELECT [Model] " & _
    "FROM [tblVehicle] " & _
    "WHERE (([Year] = '" & txtYear & "') " & _
    "  AND ([Make] = '" & txtMake & "')) " & _
    "ORDER BY [Model]"

Another alternative is to use Combos for all fields where the the selection list is extracted from the same table so :-

cmbYear.RecordSource = "SELECT DISTINCT [Year] " & _
    "FROM [tblVehicle] " & _
    "ORDER BY [Year]"

and

cmbMake.RecordSource = "SELECT DISTINCT [Make] " & _
    "FROM [tblVehicle] " & _
    "ORDER BY [Make]"

Obviously the txtYear & txtMake in the cmbModel.RecordSource code would have to change to cmbYear & cmbMake respectively.

Where do I find the recordsource? I know ther is rowsorce whrn looking at the properties of the combobox but I do not see a recordsouce. Is this what I enter in VB? I am new to this and don't fully understand what needs to be done. I need to use the first example you gave me but I am not following it completely. Thanks again for all the help.

[NeoPa]

You're absolutely right. I'm sorry.
You need to assign it to the 'RowSource' property whenever the value(s) in txtMake or txtYear change.
Put this in the AfterUpdate events of both objects.

[ducky]

So if i understand this correctly, I need to put the follwoing text in the after update section of the two txt boxes? Do I need anything in the Model combo box?
cmbModel.RecordSource = "SELECT [Model] " & _
"FROM [tblVehicle] " & _
"WHERE (([Year] = '" & txtYear & "') " & _
" AND ([Make] = '" & txtMake & "')) " & _
"ORDER BY [Model]"

[NeoPa]

Nothing related to this question.

[cyberdwarf]

you might try something like this on the AfterUpdate event for the text boxes:-

me.cmbMake.filter = "Year = " & txtYear & " AND Make = " & txtMake
me.cmbMake.requery

HTH

Steve

[ducky]

Nothing related to this question.

I am getting a compile error when I enter the lines you have. Where in these lines are there single or double quotes? Would this nake a difference? What else could I be doing wrong?

[ducky]

you might try something like this on the AfterUpdate event for the text boxes:-

me.cmbMake.filter = "Year = " & txtYear & " AND Make = " & txtMake
me.cmbMake.requery

HTH

Steve

Thanks for the help as well steve but this one is not working for me either.
i get a compile error:
Method or data member not found.
And it brings up my txtYear.
Any more suggestions?

[cyberdwarf]

mebbe try:-

me.cmbMake.filteron = true
me.cmbMake.filter = "Year = '" & txtYear & "' AND Make = '" & txtMake & "'"
me.cmbMake.requery

Addition of single quotes around text variables in SQL code!!!

Steve

[ducky]

mebbe try:-

me.cmbMake.filteron = true
me.cmbMake.filter = "Year = '" & txtYear & "' AND Make = '" & txtMake & "'"
me.cmbMake.requery

Addition of single quotes around text variables in SQL code!!!

Steve

I still get the same error. I don't know what I am doing wrong!

[cyberdwarf]

I still get the same error. I don't know what I am doing wrong!

Sorry, got the wrong combo name! Try:-

me.cmbModel.filteron = true
me.cmbModel.filter = "Year = '" & txtYear & "' AND Make = '" & txtMake & "'"
me.cmbModel.requery

Also

• If txtYear and txtMake have a data source, you should use the data source name in your code
• Try using "Me." to prefix your field names

Steve

[NeoPa]

you might try something like this on the AfterUpdate event for the text boxes:-

me.cmbMake.filter = "Year = " & txtYear & " AND Make = " & txtMake
me.cmbMake.requery

HTH

Steve

It's true you could put the WHERE part in the Filter instead (would be better in fact), and also the requery is good.
Beware this code though, It should be on the cmbModel object and the strings should be enclosed in quotes.
In short, take the ideas - don't copy/paste the code.

PS I should have refreshed again - I missed the conversation in between.

[NeoPa]

I am getting a compile error when I enter the lines you have. Where in these lines are there single or double quotes? Would this make a difference? What else could I be doing wrong?

Ducky,

Copy and paste the text into a text editor to find where the quotes are. That's not something you should be asking someone to tell you. But in general, the double-quotes are around text in the program that must be treated as literal string values. The single-quotes are where, in the eventual data, a string needs to be passed to the SQL parser - in other words, in the SQL code.
The quotes will certainly make a difference - unlike prefixing the object names with Me. which is entirely superfluous in this situation.

I can help you with the code, but I need you to tell me where the compiler complains in your code.

[cyberdwarf]

This definitely works!!!

I just built a dummy form, with a simple data table ("Models": contains fields Make/Year/Model)

Use this code in the appropriate events:-

• OnEnter event for cmbModel:
  Private Sub cmbModel_Enter()
  Me.cmbModel.RowSource = "SELECT Ident, Make, Year, Model FROM Models where year='" & Me.txtYear & "' and make='" & Me.txtMake & "'"
  Me.cmbModel.Requery
  End Sub
  
• OnLoad event of Form:
  Private Sub Form_Load()
  Me.cmbModel.RowSource = "SELECT Ident, Make, Year, Model FROM Models where year='" & Me.txtYear & "' and make='" & Me.txtMake & "'"
  End Sub

You may not need to use the form's OnLoad event

Steve

[cyberdwarf]

PS

combo boxes don't have a filter property!!! (Doh!)



Steve

[NeoPa]

That's good work CyberDwarf, but don't forget why the code was originally to be put in the AfterUpdate events of the two text boxes - we don't want a requery every time the user enters the Model field. That ensures the query is only rerun whan a change is made to what the query depends on.

Anyway, have fun and the solution is there for you Ducky.

[cyberdwarf]

we don't want a requery every time the user enters the Model field. That ensures the query is only rerun whan a change is made to what the query depends on.

Then you need to add the code to to the AfterUpdate events for both txtMake and txtYear - that's two lots of requerying??

Steve