Select Case/If-Then-Else Help

[Geodon7]

My problem is that I need the contents of a dropdown box to change based on a previous selection in a different dropdown box.

More specifically I have a table with 2 fields, Project and Program. There can be Programs with more than one Project associated and there can be different Programs with the same Project Name. The first dropdown asks for the Program and I want the second dropdown to give the options that are associated only with that Program.

I'm assuming I need to use the AfterUpdate Event and some sort of If-Then or Select case, but I'm not familiar with how to do that using VBA.

Can anyone help?
Thanks,
G

 

[FoxProProgrammer]

Hi G,

You are correct that you need to use the After Update event of the combo box. In the After Update event of the first combo box, you will set the Rowsource of the second combo box with a Select Query similar to below:

Dim strQuery As String

strQuery = "Select fldname From tblname Where field = " & Chr(34) & comboBox1.Value & Chr(34)

comboBox2.RowSource = strQuery

This assumes that comboBox1 contains text data.

If the first combo box contains numeric data, do this:

strQuery = "Select fldname From tblname Where field = " & Trim(Str(ComboBox1.Value)"

comboBox2.RowSource = strQuery

You would include something similar in the After Update event of the second combo box, but would set the RowSource of the third combo box.

Hope this is what you are looking for.


dz
dzaccess@yahoo.com

 

[sko]

See this FAQ submitted by RickSpr

[FAQ702-681]

 

[Geodon7]

dz,

I put that code in using the combobox names and field and table names that I have and I'm getting an error. My entries are numeric, so I used the second list of code that you included and I received an error saying, "Syntax error (operator missing)in query expression 'ProgramID ='

Do you know what I'm doing wrong?
Thanks for your help!
G

 

[FoxProProgrammer]

G,

Please post the entire SQL string so I can look at it. Also, do your combo boxes have more than one column?

Thanks,
dz
dzaccess@yahoo.com

 

[Geodon7]

There's only one column in the combo boxes. Here is what I have in the AfterUpdate Event.

Private Sub ProgramName_AfterUpdate()
    Dim strQuery As String

    strQuery = "Select ProjectName From Projects2 Where ProgramID = " & Trim(Str(ProjectName.Value))
    
    ProjectName.RowSource = strQuery

End Sub

I basically copied it directly from what you had typed and added my table and field names. Thanks for your help!

Geoff

 

[vikin9]

The approach I use is two unbound combo boxes in the form header. In Combo1 AfterUpdate, requery Combo2 and the form itself. The row source of Combo2 uses a parameter that refers to the value in Combo1 (e.g. [Forms]![FormName]![Combo1]). In Combo2 AfterUpdate, requery Combo1 and the form itself. Also, I usually set the value of the other Combo to Null with each AfterUpdate event just to keep things clean. Something like:
Combo1_AfterUpdate()
Me!Combo2.Value = Null
Me!Combo2.Requery
Me.Form.Requery
Combo2_AfterUpdate()
Me!Combo1.Value = Null
Me!Combo1.Requery
Me.Form.Requery
I hope this helps.

 

[FoxProProgrammer]

Hi Geoff,

It looks like you might have mixed up ProgramName with ProjectName in the strQuery assignment.

This line:

strQuery = "Select ProjectName From Projects2 Where ProgramID = " & Trim(Str(ProjectName.Value))

Should be:

strQuery = "Select ProjectName From Projects2 Where ProgramID = " & Trim(Str(ProgramName.Value))

Is ProjectName a number? If it is a string, assign strQuery like this:

strQuery = "Select ProjectName From Projects2 Where ProgramID = " & Chr(34) & ProgramName.Value & Chr(34)

ProgramID sounds like a numeric field but ProgramName sounds like a text field. Is the program data in the table numeric or text? The format in the query has to match what is in your table. You might consider relating your tables instead of using a flat file, but you can work with it like this if it is small.

Let me know how it works out if you get a chance.

Thanks,




dz
dzaccess@yahoo.com

 

[FoxProProgrammer]

vikin9, that's another way to do it...but he would have the same problem getting the query to work. You can either fix the Rowsource and requery the combo box (as you suggested) or change the Rowsource, in which case Access automatically requeries the combo box (as I suggested). Either way works, and I have actually done it both ways. I see no difference in the complexity of either approach.

Best, dz
dzaccess@yahoo.com

 

[Geodon7]

dz,

So I'm almost there, and yes, I'm still trying to figure this darn thing out! But there's one (maybe one and a half) more problems. In the second dropdown box, the options are not visible. I can tell there is more than one option because of the size of the dropdown box, and it also registers in the table the form corresponds to, but you cannot see the options you have to choose from. Any thoughts on why this may be happening? And what I should do to fix it?

The other problem I am having is that I was getting an error saying that there was a mismatch between the data types (lengths). So I determined that for the second combobox the selections are being stored as text (although the only place I can see this is in the table), but I want it to store a numeric value (ProjectID). I'm not sure if I'm making any sense, but here is the code that I have. I hope you can help! Thank you so much for your help.

Here is the code that seems to be working for me:

Private Sub ProgramNameDrop_AfterUpdate()
    Dim strQuery As String
    strQuery = "Select ProjectName From Projects2 Where [Projects2].[ProgramID] = " & Trim(Str(ProgramNameDrop.ItemData(ProgramNameDrop.ListIndex)))
    
    ProjectNameDrop.RowSource = strQuery
    End Sub

Thanks again for all your help!
Geoff

 

[FoxProProgrammer]

Hi Geoff,

I'm a bit confused about a few things.

How do you know that the second combo box contains items if you can't see them? I would guess that the query isn't working and nothing is in the combo box.

Please clarify the following.

1. Data type of Projects2.ProgramID
2. Data type of Projects2.ProjectName
3. Number of columns in ProgramNameDrop and the data type of each column.

This ought to be easy to get working after you clarify the above.

Thanks,
dz
dzaccess@yahoo.com

 

[Geodon7]

I'm pretty sure the second combo box contains items because when I look at the records in the corresponding table, I see a value entered in the project field.

As for the things that need to be clarified:
-Projects2.ProgramID is a Long Integer. It is the Autonumber field of the Programs table, which I have related.
-The data type of Projects2.ProjectName is text.
-The number of columns in the ProgramNameDrop is 1, with the index column hidden. The data type of the 1 visible column is Long Integer as well (it is populated by the ProgramID field).

Thank you so much!! I am really grateful.
Geoff

 

[FoxProProgrammer]

Sorry, Geoff...but you are confusing me more as we go on. In the beginning of this thread you said "I have a table with 2 fields, Project and Program." I interpreted "a table" as "one" table, and even eluded to this in one of my posts where I said "You might consider relating your tables instead of using a flat file, but you can work with it like this if it is small." Now you appear to be saying that you have two tables: Programs and Projects2. How many tables do you have? What are their structures including field names and data types? By which fields are the tables related?

Then you said "The number of columns in the ProgramNameDrop is 1, with the index column hidden." How can the number of columns be 1 if one of them is hidden? Did you mean that there are two columns?

This is really a very simple issue, but you are confusing me with your description. Please try to clarify so I can understand. It would save me some time if you zip up your database and email it to me...if you don't mind.

Thanks,
dz
dzaccess@yahoo.com

 

[Geodon7]

dz,

For some reason, I can't connect to my email right now, so I figured I would post this to you here just to let you know I finally got it to work! The selections are visible and the query runs perfectly! I had to do some tweaking, but I have the gist of it now. Thank you so much. You don't know how helpful you have been!!

Thanks again,
Geoff

 

[FoxProProgrammer]

That's great, Geoff! Thanks for emailing your database to me. Sometimes that works out better. I appreciate your taking the time to let me know that I helped you. Some people never even come back to let you know that they read what you posted no matter how many hours you spend helping them. Please let me know if you need any help in the future.

Best wishes,
dz
dzaccess@yahoo.com