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]

Field Name  Type
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 :

Zone         Jan_2012  F3  Feb_2012  F5  Mar_2012  F7  F8  Apr_2012  ...
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).

[almaroc]

well the problem with the redesign is that the excel sheet. is what i get from the pricing company. the excel sheet is not my design. I'm just trying to work with it.

[Seth Schrock]

How often does the data change in the excel sheet? Is it once a year?

[NeoPa]

Almaroc:
well the problem with the redesign is that the excel sheet. is what i get from the pricing company. the excel sheet is not my design.

That, and the fact that you have never explained what the design is. Hence my leaving the thread as far as helping directly with the question goes. My earlier response was a clarification on some points Seth raised. I'm happy to do that still, but not so working on a question that is missing the basic information required for it to make sense. Until that changes I'll continue simply to monitor the thread.

[almaroc]

the data changes everyday, and the fields change every month i.e in jan-2012 my fields will be jan-2012, feb-2012,....dec2012. and on February the fields will be feb-2012, mar-2012...jan-2013. it is a twelve month span from the current month. the data is actually energy prices that is changing daily.

[Seth Schrock]

Is the only purpose of this database to make it easier to lookup the price for the area and month specified? If so, it might be easier to just leave it in Excel. Otherwise, you are going to have to manually update your database with the price changes every day and change the design every month. You just simply can't use the data out of the Excel file that you posted in post 14 as a linked table in Access.

[almaroc]

Thanks Seth. I have Realized that so i changed my code to read directly from the file which is replaced every day with the current prices sheet. this is what i am working on now. i don't get any error message but yet i don't get any value in my text box.

[NeoPa]

Almaroc:
the data changes everyday, and the fields change every month i.e in jan-2012 my fields will be jan-2012, feb-2012,....dec2012. and on February the fields will be feb-2012, mar-2012...jan-2013. it is a twelve month span from the current month. the data is actually energy prices that is changing daily.

I can't tell if this is a serious attempt to provide the information as to the layout of the data. If so, as it doesn't even mention a field (or column) called [Zone], we're still in little better a position to answer your question.

However, as it does indicate at least that the data expected in the table is named in such a way as that it covers twelve months from today's date, we at least have a way of knowing what we're referring to. If the requirement is that when Me.CboMonth is selected then the data corresponding to that selected month is shown in the TextBox Me.txt1, then we have all we need (I'd suggest - This assumes that the Zone value is the unique identifier for the record or row required from [Small_6M]).

If, as I assume (I'm afraid we have to make more assumptions than is sensible due to the lack of information available from you), the values found in Me.CboMonth match the names of the fields currently found in [Small_6M] (as they change each month I understand), then the poulation of the TextBox becomes relatively straightforward :

Private Sub CboMonth_AfterUpdate()
    With Me
        .txt1 = DLookup(Expr:="[" & .CboMonth & "]", _
                        Domain:="[Small_6M]", _
                        Criteria:="[Zone] = '" & .CboZone & "'")
    End With
End Sub

There may be shorter ways to this if the data of the relevant record is already available within the dataset of the form, but without that knowledge this is the closest we can suggest I believe.

[Seth Schrock]

NeoPa, here is the Excel file in a 2003 version format if that helps. I'm wondering if a macro could be used in Excel to put the data into a usable format instead of having combined cells.

[NeoPa]

I'll have a look Seth, particularly as you've gone to that trouble on my behalf (which I appreciate). I would expect though, assuming my guesswork as to what the question is all about is reasonably accurate (and I do sometimes miss by a distance), that my previous post (#22) should provide an answer to the requirement originally posted.

[NeoPa]

Having looked at it I guess that even what is included in post #12 isn't accurate. This starts at what is currently next month. I doubt that will make much of a difference to the solution mind you, as long as [CboMonth] accurately reflects the available options.

Having done a test import for myself, I can see that even the empty columns (where columns are displayed as merged for some reason) do not cause any problems, as they just result in a bunch of empty fields in the table which need never be referenced. I would expect post #22 to provide a viable solution for the OP.

PS. Actually, as this info is still missing and many people are likely to want to see it I will add an explanation in post #1 explaining the data properly.

[Seth Schrock]

Wrong excel file in previous thread. A primary Key will need to be added to this because zone plus date won't work. This is where I think that the macro could add one.

[NeoPa]

Seth:
Wrong excel file in previous thread.

I guess you mean post :-(

:-( because such a data format doesn't allow for accurate/reliable processing using Access. There is nothing, except the position of the rows in the file, that indicates which are related to the 6 month data and which to any of the other categories.

It's also a big, big :-( when you think of the fact that none of this was explained in the question - even after heavy prompting. It's a wonder I still have all (most) of my hair (Just a thought - was it Brigitte Bardot in 1 Million Years BC?).

[Seth Schrock]

Post yes, sorry. The only thing that I can think of is to add a column for period (ie 6 month, 12 month, etc) and add a third combo to select which period to search. It still isn't the best design, but I think that it could work. I guess the empty columns isn't a problem, but the empty rows would be, so the macro idea could still be used for this.

[NeoPa]

That is a sound idea that could work, but it would require that work be done to the basic Excel data before it could work. That rather throws out a lot of the convenience of the previous situation (as originally described until it just turned out to be all wrong) though, where the file would be placed into an existing folder overwriting the previous version and would automatically be accessible as [Small_6M].

It means preparatory code would need to be run on the data in the Excel file before this could work. That could be done from within the Access project if required, using Application Automation, or elsewhere (In Excel for instance), but it would be required one way or another.

[almaroc]

thank you neo for your explanation. you really broke it down. thanks a lot. I am only using one text box in this question to see if i can get it to work. it actually going to be a text box for each period. txt_6M, txt_12M, etc.
the purpose is to view the base rate which the data in the excel file. have a text box where the sale agent will enter his markup and calculate the commission. but for now i am trying to find the best way to read the data from the excel file.

[NeoPa]

I think I've already explained how, but here's a quick rerun.

Get the data in the Excel file to be updated to include the period (6M; 12M; etc) on each row. Easy enough using Excel code, but can be done from within Access too.

Have a linked table connected to this updated file.

Include the period in the selection criteria (The example code in post #22 shows only criteria for [Zone] at the moment).

That way all should be doable.

[almaroc]

I tried your code. i still don't get anything. i don't get any error messages but i don't get any data in my text box either. this must be something that i am missing. so i uploaded my database and the file. please take a look at it. i know i have been a pain. please bear with me. thanks.

Private Sub CboMonth_AfterUpdate()
    With Me
        .txt1 = DLookup(Expr:="[" & .CboMonth & "]", _
                        Domain:="[Small_6M]", _
                        Criteria:="[Zone] = '" & .CboZone & "'")
    End With
End Sub

I did not add any field name because for now i am only using the six month values.
my controls are: CboZone containing values like "AEP South", "AEP West", etc. CboMonth containing Values like "Jan_2012", "Feb_2012", etc and one Text Box txt1.

[Seth Schrock]

I happen to know that NeoPa has Access 2003 which can't open your file. If you could post exactly what code you are using (in code tags), the control names on your form, and the name of the field that you added for the period and that should be all we need.

[almaroc]

I have edited my post # 32 to include the code and answers to your questions. thanks for your help.

[almaroc]

Thank you guys so much i finally got it to work . this really stupid it was the security option in access that was stopping the code from running i just had to disable it. thanks Neo & Seth. you guys have been a great help.

[NeoPa]

The code you've posted there matches the layout I thought you had at that time. Since then it has become clear that your data is not as straightforward so I gave some tips on how to approach the newly defined situation. Why not take it from there?