Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2012

    Unanswered: Query not updating variable

    I am using the afterUpdate event on a list box to do a query and set a variable to the value from a table. The first two queries function as expected but the third one does not and yet is the same as the first two. I am looking for an explanation as to why not and a suggestion to fix or another approach. The underlying tables are the same construction differing only in the third table having two yes/no fields, I don't think that is an issue. I suspect it revolves around the recordset and snapshot. Thanks in advance, code follows

    Private Sub lbFilter_AfterUpdate()
    Dim Choice2
    Dim DB As Database
    Dim rst As Recordset
    Dim rst2 As Recordset
    Dim rst33 As Recordset
    Dim THisSQL As String
    Dim THisSQL2 As String
    Dim THisSQL33 As String

    ''' act on the choice of contracts by COR
    ''' run a new query based on lbFilter value and use the ID number to pull all associated records from data values table. Then use the contract number to open the record

    Choice2 = Me.lbFilter.Value
    Label21.Visible = False

    THisSQL = "SELECT [COR].[ID], [COR].[Location] FROM COR"
    Set rst = CurrentDb.OpenRecordset(THisSQL, dbOpenSnapshot)
    COR_ID = rst![ID] ' works - sets value to pass to update event

    THisSQL2 = "SELECT [Contract_Groups].[Number] FROM Contract_Groups"
    Set rst2 = CurrentDb.OpenRecordset(THisSQL2, dbOpenSnapshot)
    Group_Number = rst2![Number] ' works - sets value to pass to update event

    The following three lines are the problem, the recordset is stuck on record one and not changing to the selected value in the listbox (lbFilter)
    THisSQL33 = "SELECT [Station_Lookup].[Number] FROM Station_Lookup"
    Set rst33 = CurrentDb.OpenRecordset(THisSQL33, dbOpenSnapshot)
    StnName = rst33![Number] ' does not work

    Call ListChoice3(Choice2, OneofFourChoice, COR_ID, Group_Number, StnName)

  2. #2
    Join Date
    May 2004
    New York State
    I assume that StnName is a form control. If it's a variable, like you call it, it must be Dimmed.

    I'm sure you realize that in all 3 queries, you are only accessing the first record of the resulting snapshot-type recordsets.

    You cannot use reserved words for variable names. It causes Access to go ape----. 'Number,' in all probability, is a reserved word. Rename it in the table to StnNum, or something similar.


  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    when you say does not work
    what isn't working
    is the recorset not returning a value
    what steps have you taken to prove the SQL is valid and doing what you want
    have you considered putting in some error handling so you are setting appropriate defaults in the event no rows are returned
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 18
    To add another observation to Sam's, you are not using the value of the list box in any of your queries. None of them have a WHERE clause, and so will always return all values from the source tables.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Posting Permissions

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