Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Recordsource not updating (Access 2003)

    I have created a form to display Student information based on their Student ID number(SIN). The user is given an unbound form where they can select the SIN, current status, etc. The bound form is then opened based on their criteria. Things were working for awhile (I'm still testing), but now they're not. I don't understand why the recordsource is not updating in all cases. If I select only active students (MemClass = 6), then the recordsource is properly updated, but for the SIN, I just get a screen with blanks displayed. I am using the openargs to pass the criteria from the unbound form to the bound form. The openargs should contain the following text: "[SIN]='123456789' AND [MemClass] = 6". If no SIN is selected, the openargs contains only "[MemClass]=6". It works if there's no SIN. If the SIN that is passed does not exist in tblMembers then that means they want to add a new Student. I've pasted the code below that I've inserted into Form Load for the bound form:

    Private Sub Form_Load()
    Dim strSIN, strCriteria, strStuff As String

    strStuff = Mid(Me.OpenArgs, 2, 1)
    If strStuff <> "M" Then ' No SIN passed in this case
    strSIN = Mid(Me.OpenArgs, 8, 9)
    strCriteria = "[sin] = '" & strSIN & "'"
    If DCount("sin", "tblMembers", strCriteria) > 0 Then 'SIN found in members' table
    Me.RecordSource = "SELECT * FROM tblMembers WHERE " & Me.OpenArgs
    Else
    Me.DataEntry = True 'SIN not found - create new record
    Me.SIN = strSIN
    End If
    Else
    Me.RecordSource = "SELECT * FROM tblMembers WHERE " & Me.OpenArgs
    End If
    Me.SIN.BackColor = 8454016
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Recordsource not updating (Access 2003)

    Try setting a breakpoint on the line Private Sub Form_Load(), either by double-clicking the gray margin to the left of the line or by clicking in the line and pressing F9. (You can use the same method later on to remove the breakpoint).
    When you open the student form from the unbound form, code execution will pause at the breakpoint.
    You can execute the code line by line by pressing F8. This allows you to see which choices are made in the If ... Else ... End If statements.
    You can inspect the value of a variable by hovering the mouse pointer over the variable in the code.
    Hopefully this will give you a clue as to what's happening.

  3. #3
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recordsource not updating (Access 2003)

    Okay, I set a breakpoint and walked through the code step-by-step, and I still don't see what's wrong. When the form appears on the screen, all the fields are blank and the navigation bar shows Record 1 of 1 displayed with only the First Record (<|) and Last Record (>|) options available. Am I somehow flipping into "Add Mode"? The code seems to follow the correct process, and the value of me.recordsource is being set to the correct value. Is there any other way of checking what the recordsource is really being set to once the form is on screen? Is there a way to see if I am somehow setting the form to "Add" instead of "Edit"?

    I'm confused. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Recordsource not updating (Access 2003)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recordsource not updating (Access 2003)

    Okay, I will admit it. Sometimes I'm an idiot.

    I stripped the arguments down and added them piece by piece MANUALLY instead of assuming they were being transferred correctly within openargs. Sure enough, the student I was testing was not active, therefore Access was giving me exactly what I asked for: No records matched the criteria.

    Thanks for your help in directing me to use Break mode, Hans. I would never have figured this out otherwise!

Posting Permissions

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