Combo Box After Update Code not working

[almaroc]

I am using Access 2007. I have a form with two Combo Boxes and a Text box. the first combo box CboZone have values like zone1, zone2,.. the 2nd Combo box CboMonth has values like Month1, Month2.. I need to populate the text box with a value from a table Small_6M depending on the values selected in the combo boxes. the controls are text and the value collected is a number. the MsgBox does return anything. i do not know why.
I really need to get this working asap i have been trying for while with no luck.thanks
Here is the Code:

Private Sub CboMonth_AfterUpdate()
    Dim strSql As String    
    MsgBox "[" & Me.CboMonth & "]"
    strSql = "Select Small_6M.Dec_2011 From  Small_6M      Where Small_6M.Zone = '" & Me!CboZone & "'" 

    Me.txt1 = "'" & strSql & "'"

End Sub

** Mod Edit **
The layout of the data in [Small_6M] varies from month to month, but follows the following general layout :

Table = [Small_6M]

[U][B]Field Name  Type[/B][/U]
Zone        String
mmm_yyyy    Numeric Double
...
mmm_yyyy    Numeric Double

There are twelve monthly columns starting with the month following the current one. [mmm_yyyy] for January of 2012, for instance, would be [Jan_2012] and the last field would be [Dec_2012].

An example of some of the data might be :

[B][U]Zone         Jan_2012  F3  Feb_2012  F5  Mar_2012  F7  F8  Apr_2012[/U]  ...[/B]
AEP South     0.05309       0.05832       0.06381           0.06494  ...
AEP West      0.04528       0.052         0.05918           0.06177  ...
CenterPoint   0.05433       0.0594        0.06465           0.06585  ...
Oncor         0.05258       0.05772       0.06311           0.06467  ...
TNMP          0.05258       0.05772       0.06311           0.06467  ...

[TheSmileyCoder]

Hi and welcome to BYTES

I think you may have a typo in your post. Do you mean to say that the msgbox in your code returns "[]"?

What is the rowsource for cboMonth?

[NeoPa]

You don't say how the data is held in [Small_6M] and your code gives no clue. We don't have the basic info required to help you I'm afraid.

[Seth Schrock]

Like NeoPa and Smiley said, your question lacks some information, but give this a try. Setup a query that contains the information that you are looking for in the textbox and have your two combo boxes. Then in the after update event of your combo box, do a dlookup() to get the value from the query and then set textbox to that value. If the query returns more than one value, you will have to set some criteria in the dlookup () function to get only one value. I would put this dlookup() function in both combo boxes within an IF THEN ELSE statement to test if the other combo box was populated. See following example:

For Combo Box 1 After Update event:

If IsNull(Me.ComboBox2) Then
Me.ComboBox2.SetFocus
ELSE
Me.Textbox = DLookup("{field name}", "{query name}", {Optional critiera})
End If

For Combo Box 2 after update event:

If Not IsNull(Me.ComboBox1) Then
Me.Textbox = DLookup("{field name}", "{query name}", {Optional critiera})
ELSE
Me.ComboBox1.SetFocus
End If

I don't know if this is the best method, but it is something to look at.

PS.
The reason for using the after update event in both combo boxes is that it makes it so that you can populate the combo boxes in any order. While it might not make sense to you and me to do it in backwards order, a user who doesn't know can usually find ways to break the system without trying and it is better to prepare as much as possible to prevent user error.

[almaroc]

Actually I do not get anything. this is what's boggling me. i have attached a copy of the database

[almaroc]

thanks for your reply but i am not really sure how the dlookup works.

[Seth Schrock]

There is lots of information about DLookup() on the Internet, but I'll try to briefly explain the syntax. So you have the DLookup(The next thing you put in is the field name that you are looking for in double quotes followed by a comma. Next, you have name of the table or query that the field is in followed by a comma if there is critieria to follow. For the criteria, I would have to know more about your database before I can help you with that syntax as there are several options. I will look at your database after work.

[NeoPa]

NeoPa:
You don't say how the data is held in [Small_6M] and your code gives no clue. We don't have the basic info required to help you I'm afraid.

Just to be clear, posting a database attachment is not a substitute for providing a proper question with the requisite details as requested.

You may choose to go blindly towards suggestions offered prior to the full question being clear, but in the absence of such information I must warn you that any such suggestion could as easily be leading you away from an appropriate answer as it it could towards it. There are many ways to achieve a result, but many are only appropriate in limited circumstances. The use of DLookup() definitely falls into that category and should be used carefully if you want a reliable and well-designed project.

I'll leave that thought with you (as only you can make your actual choices), but if things go badly, you'll know I warned you.

[Seth Schrock]

Okay... tabl Small_6M is an excel file that I don't have so I still don't know what kind of data is in that (perhaps a screen shot would be enough to tell us what we need to know). The first combo box as the options hard-coded into the row source and the second combo box is linked to the Small_6M table. Other than that, I really don't know much more than I did before. Guessing by the design of what I see here, you are in for a lot of extra work to do what you need. Included is the 2003 version minus the Small_6M table link which didn't work anyway.

@NeoPa, I'll remember your words from now on. After experiencing this, I'll certainly look before I leap.

[NeoPa]

I expect I've fallen into that trap many more times than you have Seth ;-) Although I see a certain understanding still to be gleaned, I can't feel critical of your efforts as they're clearly from the best of motives :-)

PS. You're doing good work and I really like your avatar :-D

[almaroc]

sorry about that here is the file. thanks !

[NeoPa]

NeoPa:

NeoPa:
You don't say how the data is held in [Small_6M] and your code gives no clue. We don't have the basic info required to help you I'm afraid.

Just to be clear, posting a database attachment is not a substitute for providing a proper question with the requisite details as requested.

At this stage I will walk away as there is clearly little notice being paid to my posts by the OP. I'll continue to monitor as a moderator of course, but working in this way is just not sensible.

[Seth Schrock]

Wow, Poor design for the excel file if you plan on having it linked to access. Also, I'm still lost on what you are wanting. For me, if I select Jul_2012 then I get a message box saying [Jul-2012]. I have no idea why you would want that as it just duplicates what is already showing in the combo box. As far as the textbox goes, because of the design of your database, you are going to have a ton of work coding the solution that will have to be changed as time goes on because you are going to have the years change on your months. The code that you need with your current design will be a Case() function for each month of every year as well as a query for each month. There is a way to run the sql code for a query in vba, but I'm not sure how to do that. The only option that I know of is to make a query and the use the dlookup() function to retrieve the value found in the query. This is not the best solution however. I'm not going to take the time to give you the code as it would be a waste of my time on a poor design. Do a google search for database normalization to help with the redesign. My guess is that it would be easier to start from scratch. Once you do the redesign, then you can come back and ask your questions. Until you do that it is pointless to continue this tread. I'm not trying to be mean. If you continue designing databases, you will see why proper database design will help you and save you lots of headaches.

[almaroc]

thanks for your input. this is my original design just reading directly from the spreadsheet but i could never get it to work. I am a newbie to access so i have a lot to here is my original code. and database.

Private Sub CboMonth_AfterUpdate()
     
   With worksheets = ("Y:\A2zmediaws\daily pricing\Small Pricing.xls")
 If CboMonth.ListIndex <> -1 Then
        'With Myws
            Select Case Me.CboMonth.ListIndex
                Case 0
                Select Case Me.CboZone.ListIndex
                    Case 0
                        Me.Txt_6M = worksheets.range("D15").Value
                    Case 1
                        Me.Txt_6M = myws.range("D16").Value
                    Case 2
                        Me.Txt_6M = myws.range("D17").Value
                    Case 3
                        Me.Txt_6M = myws.range("D18").Value
                    Case 4
                        Me.Txt_6M = myws.range("D19").Value
                End Select
                Case 1
                Select Case Me.CboZone.ListIndex
                    Case 0
                        Me.Txt_6M = myws.range("F15").Value
                    Case 1
                        Me.Txt_6M = myws.range("F16").Value
                    Case 2
                        Me.Txt_6M = myws.range("F17").Value
                    Case 3
                        Me.Txt_6M = myws.range("F18").Value
                    Case 4
                        Me.Txt_6M = myws.range("F19").Value
                 End Select
                 Case 2
                 Select Case Me.CboZone.ListIndex
                    Case 0
                        Me.Txt_6M = myws.range("H15").Value
                    Case 1
                        Me.Txt_6M = myws.range("H16").Value
                    Case 2
                        Me.Txt_6M = myws.range("H17").Value
                    Case 3
                        Me.Txt_6M = myws.range("H18").Value
                    Case 4
                        Me.Txt_6M = myws.range("H19").Value
                 End Select
                 Case 3
                 Select Case Me.CboZone.ListIndex
                    Case 0
                        Me.Txt_6M = myws.range("K15").Value
                    Case 1
                        Me.Txt_6M = myws.range("K16").Value
                    Case 2
                        Me.Txt_6M = myws.range("K17").Value
                    Case 3
                        Me.Txt_6M = myws.range("k18").Value
                    Case 4
                        Me.Txt_6M = myws.range("K19").Value
                 End Select
            End Select

[NeoPa]

Seth Shrock:
There is a way to run the sql code for a query in vba, but I'm not sure how to do that.

All QueryDefs have a .SQL property which exposes the SQL they run. Executing a SQL string can be done in various ways, some of which are :

1. {DatabaseObject}.Execute SQLString

2. DoCmd.RunSQL SQLString

Seth:
Do a google search for database normalization to help with the redesign.

We have a decent article on Bytes that will help with that (Database Normalisation and Table structures).