Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    123

    Unanswered: Getting only one record

    Trying to return a recordset based on parametered SQL, but only getting one record (out of 6 expected).

    Code:

    a = frm!cboTicker
    b = "#" & frm!txtFrom & "#"
    c = "#" & frm!txtTo & "#"
    Set dbs = CurrentDb
    Set rcd = dbs.OpenRecordset("SELECT FOX_PRC_HIST.I_PRC_ID, FOX_PRC_HIST.D_EFF_DT, FOX_PRC_HIST.A_PRC_CLS, " _
    & "FOX_PRC_HIST.A_VOL_TRD, FOX_PRC_HIST.A_OPN_INT, FOX_PRC_HIST.A_CHG, FOX_PRC_HIST.P_CHG FROM FOX_PRC_HIST " _
    & "WHERE FOX_PRC_HIST.I_TICKER = '" & a & "' AND ((FOX_PRC_HIST.D_EFF_DT) Between " & b & " And " & c & ");")

    txtDate = rcd!D_EFF_DT
    txtClose = rcd!A_PRC_CLS
    txtVolume = rcd!A_VOL_TRD
    txtInterest = rcd!A_OPN_INT
    txtChg = rcd!A_CHG
    txtPct = rcd!P_CHG

    Is the problem in assigning record values to the text controls? The display form is set up for "continuous" in the detail section, running the SQL separately does return more than one record. But only one displays on the form.

  2. #2
    Join Date
    Dec 2002
    Posts
    60

    Re: Getting only one record

    Originally posted by jrn0074
    Trying to return a recordset based on parametered SQL, but only getting one record (out of 6 expected).

    Code:

    a = frm!cboTicker
    b = "#" & frm!txtFrom & "#"
    c = "#" & frm!txtTo & "#"
    Set dbs = CurrentDb
    Set rcd = dbs.OpenRecordset("SELECT FOX_PRC_HIST.I_PRC_ID, FOX_PRC_HIST.D_EFF_DT, FOX_PRC_HIST.A_PRC_CLS, " _
    & "FOX_PRC_HIST.A_VOL_TRD, FOX_PRC_HIST.A_OPN_INT, FOX_PRC_HIST.A_CHG, FOX_PRC_HIST.P_CHG FROM FOX_PRC_HIST " _
    & "WHERE FOX_PRC_HIST.I_TICKER = '" & a & "' AND ((FOX_PRC_HIST.D_EFF_DT) Between " & b & " And " & c & ");")

    txtDate = rcd!D_EFF_DT
    txtClose = rcd!A_PRC_CLS
    txtVolume = rcd!A_VOL_TRD
    txtInterest = rcd!A_OPN_INT
    txtChg = rcd!A_CHG
    txtPct = rcd!P_CHG

    Is the problem in assigning record values to the text controls? The display form is set up for "continuous" in the detail section, running the SQL separately does return more than one record. But only one displays on the form.
    I'm guessing you are not stepping through all the records that are returned:
    After Set rcd=......... add:
    rcd.movefirst 'This places first returned record as current record
    Then assign values to your variables (txtDate, txtClose...)

    to move to the next record:
    rcd.MoveNext 'Repeat this to step through all returned records

    If you try to rcd.MoveNext when you are at the end of the records, you will get an error. If rcd.EOF = True then you are at the last record.

  3. #3
    Join Date
    Aug 2003
    Posts
    123

    Re: Getting only one record

    Originally posted by bc301
    I'm guessing you are not stepping through all the records that are returned:
    After Set rcd=......... add:
    rcd.movefirst 'This places first returned record as current record
    Then assign values to your variables (txtDate, txtClose...)

    to move to the next record:
    rcd.MoveNext 'Repeat this to step through all returned records

    If you try to rcd.MoveNext when you are at the end of the records, you will get an error. If rcd.EOF = True then you are at the last record.
    I tried this and still only get one record. I also tried putting it into a Do Until...Loop to stop at rcd.EOF, but I think it's getting stuck in the loop then.

  4. #4
    Join Date
    Dec 2002
    Posts
    60
    Now I think I understand what you are trying to do: you want to display all returned records on a form. When you try this only one record is displayed.

    If so, try this:

    Instead of opening the recordset and assigning to objects on the form, set the form's recordsource property (Me.RecordSource="SELECT FOX_PRC_HIST.I_PRC_ID, FOX_PRC......)

Posting Permissions

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