Combo Box Polulate Text Box

[Mattress58]

Below is the example. I Have a combo Box and when I select it, I need a text box to populate an answer based on a lookup in the access table. Here is what I have.



Two things here I need help with:
A. This code below works the first time only. After I select a MARKET it populates TxtRes, but if I select a different MARKET, the answer doesnt change.
B. Here is have it looking at TxtMO textbox to get the value for MONTH (which is a number and works great)

 " & "MONTH = " & Str(Me.TxtMO.Value) & "

How can I change

MARKET = 'Alberta'

to be in this same format. I need the ' ' around it but cant seem to get it to work.


I think if I can get question B above to work it may fix question A and B.

Private Sub COStation_AfterUpdate()

    Dim dbTemp As Database
    Dim rsTemp As DAO.Recordset
    Set dbTemp = CurrentDb()


    Set rsTemp = dbTemp.OpenRecordset("SELECT Net FROM His WHERE (MARKET = 'Alberta' And Segment = 'Combined' AND " & "MONTH = " & Str(Me.TxtMO.Value) & " And " & "YEAR = " & Str(Me.TxtYR.Value) & ")")




    If rsTemp.EOF = False Then Me.TxtRes.Value = rsTemp("Net")
    If rsTemp.EOF = True Then Me.TxtRes.Value = ""


   rsTemp.Close
   Set rsTemp = Nothing


End Sub

When I select Alberta, TxtRes should populate with -20. I am trying to get the Net value for the combo box selected with TxtMO, TxtYR, and "Combined" filtered down to get that answer


Thank you for any help. Ive been trying to get this to work for a couple days now.

[ItsMe]

This
" & "MONTH = " & Str(Me.TxtMO.Value) & "

Would get changed to this in order to manage literal text.
" & "MARKET = '" & Me.ControlName.Value & "' And

I use the Immediate window to see if my variables are getting concatenated correctly. Debug.Print before you execute the SQL. You can open the Immediate Window within the VBA editor and view the results.

Dim strSQL as string

strSQL = "My text string" & Me.txt & "more text"
debug.print = strSQL

[Mattress58]

For some reason I keep getting Run Time error 13 type mismatched. When I debug and hold the cursor over that line:

" & "MARKET = '" & Str(Me.COStation.Value) & "'

It shows the current market that I have selected like "Alberta". I assume everything else is correct because it works when I have it as

MARKET = 'Alberta'
Any thoughts? Thanks again for the help

[ItsMe]

Oh, sorry. Not sure why I included the Str function. There is not usually a need for casting within Access and my example is a misuse of the function.


" & "MARKET = '" & Me.COStation.Value & "' And

[Mattress58]

AWWWWWW. That worked. Thank you so much.

[ItsMe]

You bet ...