combo box question

[rockyjr]

I have a combo box (lets call it combo3) that is filtered by 2 other combo box (combo1 and combo2). Once it is filtered by combo1 and 2, I only have one entry in the combo3. Is it possible to auto select that enrty so that the user dont have to click and select that combo box 3?

Hope I'm making sences.

Lucas

 

[boblarson]

If it is only going to have one entry then why use a combo box at all. Just assign the value to a text box based on the after update event of the second combo box.

 

[DCrake]

Simple Software Solutions

Once combobox 3 has been requeried do a listcount on the control and if the count = 1 then set the selected(0) = true for the combobox.

If Me.ComboBox3.ListCount = 1 Then
   Me.CombBox3.Selected(0) = True
End If

CodeMaster:

 

[rockyjr]

If it is only going to have one entry then why use a combo box at all. Just assign the value to a text box based on the after update event of the second combo box.

Question -
The list in the combo2 has about 50 different entries....

Are you saying that I would need to write a code saying....

if combo2 = "whatever" then
combo3 = "this and that"

50 times???

 

[rockyjr]

Once combobox 3 has been requeried do a listcount on the control and if the count = 1 then set the selected(0) = true for the combobox.

If Me.ComboBox3.ListCount = 1 Then
   Me.CombBox3.Selected(0) = True
End If

I tried this, but it keeps kicking me back to my first entry...( or ID)
it's weird!!

 

[boblarson]

Question -
The list in the combo2 has about 50 different entries....

Are you saying that I would need to write a code saying....

if combo2 = "whatever" then
combo3 = "this and that"

50 times???

No, if you are ONLY going to have one selection returned from the other combo box, instead of using a combo box, use a text box and in the After Update event of the other combo you can just use the query you were going to use for the combo3 like this:

Dim strSQL As String

strSQL = "SELECT Whatever FROM YourTableName WHERE YourFieldName=" & Me.YourCombo2Name
Me.YourTextBox = DLookup("YourFieldNameHere", strSQL)

 

[rockyjr]

Thank you for all your help..

I'm trying to do what you gave me (boblarson) but I'm getting the error that it cant find the field name.... make sure it exist and try again.....

I know it exist because, it gets populated by the first combo box.

I might be missing a little something and not looking at the right place.

Here's how the table is named :

table name : MAIN CODE

id - autonumber
code id - text (many are the same and named "cbocategory" on the form)... combo1
code - text (discription of the code id and named "cboproblemtype" on the form)... combo2
group - text (part of group... and named "txtgroup" on the form)... was combo3... now txt field

code in the afterupdate:

Dim strSQL As String

strSQL = "SELECT [main code].group FROM Main Code WHERE code=" & Me.cboproblemtype
Me.txtgroup = DLookup("code", strSQL)

Any ideas!!??

 

[rockyjr]

Here's the code I tried, and doesnt work.... any ideas??

Dim strSQL As String

strSQL = "select [MAIN CODE].[Group]FROM [MAIN CODE]WHERE ((([MAIN CODE].

) " = [cboProblemType] 

Me.txtgroup = DLookup("group", strSQL)

here's the error :
[IMG]http://stpierrel.com/error.jpg[/IMG]

 

[DCrake]

Simple Software Solutions

You are confusing yourself, look at what you have asked it to do

Me.txtgroup = DLookup("group", select [MAIN CODE].[Group]FROM [MAIN CODE]WHERE ((([MAIN CODE].

) " = [cboProblemType] )[/QUOTE]

You are attempting to embed as select within a dlookup:eek:


your dlookup should look like


Me.TextGroup = Nz(DLookup([Group],"Main Code","[Code]='" & cboProblemType & "'"),"Unknown")


Because the syntax is wrong in your dlookup access returns False this is why the second part of your VBA results in the error.

CodeMaster::cool:

 

[rockyjr]

You are confusing yourself, look at what you have asked it to do



You are attempting to embed as select within a dlookup


your dlookup should look like


Me.TextGroup = Nz(DLookup([Group],"Main Code","

='" & cboProblemType & "'"),"Unknown")


Because the syntax is wrong in your dlookup access returns False this is why the second part of your VBA results in the error.

CodeMaster::cool:[/QUOTE]


Sorry DCrake, It's the first time that I play with DLookup and havent gotten the logic behind it.  I'm reading about it has we speak...

One question though, What are you refering to when you used "Unknown"?

 

[DCrake]

Simple Software Solutions

Every question should have an answer, evenif the answer is incorrect. So if you do a lookup the object is to return the results. what if it does not return anything, has it worked? has it found anything? dunno? by using the NZ() function wrapped around the dlookup function I have told it to return Unknown if whatever I am looking for is not found.Then at least I know the command has been run.

David

 

[rockyjr]

Every question should have an answer, evenif the answer is incorrect. So if you do a lookup the object is to return the results. what if it does not return anything, has it worked? has it found anything? dunno? by using the NZ() function wrapped around the dlookup function I have told it to return Unknown if whatever I am looking for is not found.Then at least I know the command has been run.

David

AH ok ok... makes more sence!!

but, I'm getting an error again.... might just be a little something....

Again, not too familliar with DLookup.... I've tried to fix it with no success.

Me.txtgroup = Nz(DLookup([Group], "Main Code", "

='" & cboProblemType & "'"), "Unknown")

error: 
[IMG]http://stpierrel.com/error2.jpg[/IMG]

I understand the basic....
Go get Group from Main Code where the code = to cboproblemtype,.... but dont know why it doesnt work.

 

[rockyjr]

Yeah!!! Got it to work!!!

I dont know if it is because I'm using Access 2007, but the syntax was a bit different.

I used :
txtgroup = Nz(DLookup("group", "main code", "code = Forms![call details]!cboProblemType"), "Unknown")

Instead of:
Me.txtgroup = Nz(DLookup([Group],"Main Code","

='" & cboProblemType & "'"),"Unknown")


Thank you boblarson and DCrake for all your patience and ideas.

Lucas