Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Nov 2002
    Location
    Oakland, CA
    Posts
    126

    Unanswered: Pull Data - VB question

    Hello,
    imagine form with two fields - store and district. There is also a table that contains both of these (not the one the form is based on). Now on the form I want the district value automatically pop in after I update the store field. I've tried this code but it doesn't work. Any thoughts?

    Private Sub Store_AfterUpdate()

    Dim db As Database
    Dim rst As Recordset
    Dim sSQL As String
    Dim sStore As String
    Dim sDistrict As String

    If IsNull(Me![Store]) Then Exit Sub

    sStore = Me![Store]

    sSQL = "SELECT DISTINCTROW [tblStores].[District] FROM [tblStores] "
    sSQL = sSQL & "WHERE ((([tblStores].[StoreName])='sStore'));"

    Set db = CurrentDb
    Set rst = db.OpenRecordset(sSQL)
    sDistrict = rst![District]

    Me![District] = sDistrict
    Me![District].SetFocus

    rst.Close: Set rst = Nothing
    db.Close: Set db = Nothing

    End Sub

  2. #2
    Join Date
    Jan 2004
    Posts
    26
    Try changing this line:
    sSQL = sSQL & "WHERE ((([tblStores].[StoreName])='sStore'));"

    to

    sSQL = sSQL & "WHERE ((([tblStores].[StoreName])=""" & sStore & """));"

  3. #3
    Join Date
    Nov 2002
    Location
    Oakland, CA
    Posts
    126
    I'm not getting any error anymoroe but nothing gets inputted in the District field.... Help

  4. #4
    Join Date
    Jan 2004
    Posts
    26
    Try adding

    Me![District].Refresh

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...and if you add a couple of lines:
    Set db = CurrentDb
    Set rst = db.OpenRecordset(sSQL)
    rst.movelast
    msgbox "i have " & rst.recordcount & " records"

    sDistrict = rst![District]


    ...what do you see?


    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Nov 2002
    Location
    Oakland, CA
    Posts
    126
    izy, 1 record (as expected). Instead of refresh I used requery, still get blank field. We are close

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    is there a textbox called "District" on your form??? (check properties¦other¦name) ?

    i never tried it your way cos i dont use bound forms, but consider replacing
    Me![District].SetFocus
    with
    Me![anyOtherControlOnTheForm].SetFocus
    ...does that change anything?



    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Nov 2002
    Location
    Oakland, CA
    Posts
    126
    Man, it doesn't. Just to keep you up to date

    Private Sub Store_AfterUpdate()

    Dim db As Database
    Dim rst As Recordset
    Dim sSQL As String
    Dim sStore As String
    Dim sDistrict As String

    If IsNull(Me![Store]) Then Exit Sub

    sStore = Me![Store]

    sSQL = "SELECT DISTINCTROW [tblStores].[District] FROM [tblStores] "
    sSQL = sSQL & "WHERE ((([tblStores].[StoreName])=""" & sStore & """));"

    Set db = CurrentDb
    Set rst = db.OpenRecordset(sSQL)
    ' rst.MoveLast
    ' MsgBox "i have " & rst.RecordCount & " records"
    sDistrict = rst![District]

    ' Me![District].RowSource = sSQL
    Me![District].Requery
    Me![District] = sDistrict
    Me![District].SetFocus
    Me![Plant].SetFocus

    rst.Close: Set rst = Nothing
    db.Close: Set db = Nothing

    End Sub

  9. #9
    Join Date
    Nov 2002
    Location
    Oakland, CA
    Posts
    126
    Yes, there is a textbox called District (and also Store)

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    does the text show up on the textbox after the
    Me![District] = sDistrict

    ...and i dont think
    ' Me![District].RowSource = sSQL
    Me![District].Requery
    Me![District].SetFocus
    is doing anything useful

    Me![Plant].SetFocus
    is also (you tell me) useless

    your SQL is fine, your rst is fine
    your AnyExistingControlOnTheForm = rst!anyField
    is correct

    if the text is showing onscreen, maybe you are missing the magical save step: since i dont use bound forms, i cant help.

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    desperate measures!

    try another debugging line:
    Set db = CurrentDb
    Set rst = db.OpenRecordset(sSQL)
    sDistrict = rst![District]
    msgbox sDistrict

    ...is it what you expect

    izy
    currently using SS 2008R2

  12. #12
    Join Date
    Nov 2002
    Location
    Oakland, CA
    Posts
    126
    Safe button doesn't do it BUT that msgbox is displaying exactly what should be put in District text box. I'm gonna try to rename the textox and try again. Will keep you posted. All awesome help

  13. #13
    Join Date
    Nov 2002
    Location
    Oakland, CA
    Posts
    126
    I don't know. This is frustrating. Obviously the value is there, I just can't get it in the Default value for the now "txtDistrict" textbox. Anybody any thoughts?

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    AnyExistingControlOnTheForm = rst!anyField
    is correct!
    you are doing it two-step via sDistrict, but it is still correct.

    another desperate measure:

    Private Sub Store_AfterUpdate()

    Dim db As Database
    Dim rst As Recordset
    Dim sSQL As String
    Dim sStore As String
    Dim sDistrict As String

    If IsNull(Me![Store]) Then Exit Sub

    sStore = Me![Store]

    sSQL = "SELECT DISTINCTROW [tblStores].[District] FROM [tblStores] "
    sSQL = sSQL & "WHERE ((([tblStores].[StoreName])=""" & sStore & """));"

    Set db = CurrentDb
    Set rst = db.OpenRecordset(sSQL)
    sDistrict = rst![District]
    txtDistrict = sDistrict 'could equally be txtDistrict = rst![District] and forget sDistrict
    msgbox txtDistrict

    ...and what do you see?

    izy
    currently using SS 2008R2

  15. #15
    Join Date
    Nov 2002
    Location
    Oakland, CA
    Posts
    126
    Literally see nothing. It's an empty message box, at least before I could see the proper district

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •