Form no longer loading me.recordsource

Posted on 2004-11-01
Last Modified: 2012-05-05
I've had this form working for weeks loading the correct record in the form using me.recordsource=sql.  Today I think I did something stupid, but I have yet to make a connection on how my changes would effect the form from loading correctly. instead of loading the form with the correct record, it just loads a blank record. I have the following code on the form's On Open:

sql = "select tblProjReqSummary.* from tblProjReqSummary where fldPRIndexNo = " & gintProjectRequestID
Me.RecordSource = sql
' added the following lines for troubleshooting
Debug.Print sql
MsgBox sql

if I put a break on the first line, I can see that the SQL varable is getting setup correctly. When I hit F8, the focus drops on the me.recourdsource=sql line.  I hit F8 again and it blows past the debug and msgbox and goes straight to my On Current event.  At this point I type in ?sql and the SQL varable is now blank. So that makes since, as the form loads doesn't load anything, as the SQL varable is blank.

I suspect I did something stupid, but I wanted to run this my you experts just in case you have ever come across this before.
Question by:Thirt
    LVL 10

    Assisted Solution

    I take it your SQL statement does actually return a record (i.e. gintProjectRequestID is a valid value for fldPRIndexNo)?
    LVL 5

    Assisted Solution

    When I hit F8, the focus drops on the me.recourdsource=sql line.  I hit F8 again and it blows past the debug and msgbox and goes straight to my On Current event.
    That is the way it should work - as soon as the recordsource is reset or a firstrecord, lastrecord, nextrecord or previous record occurs on the form it will go into the on current event.

    point I type in ?sql and the SQL varable is now blank.
    This is because your sql variable was defined locally in the calling function (not in the form_current event).  This is functioning correctly.

    So that makes since, as the form loads doesn't load anything, as the SQL varable is blank.
    The SQL variable is not blank in the calling function.  When your function performs the .recordsource = ... it passes the information from your SQL variable into the form's recordset object.

    Most likely the issue is that your recordset is not returning any values:
    1. You need to run in debug mode, when you get to this line:
    sql = "select tblProjReqSummary.* from tblProjReqSummary where fldPRIndexNo = " & gintProjectRequestID
    2. Hover the mouse over gintProjectRequestID variable and take note of the number.
    3. Stop the project and then open the table tblProjReqSummary
    4. Go to the field gintProjectRequestID and search for the number you found in step 2.  It is likely that that number does not exist - if so that is the problem.  If it does exist, make sure the fields are not blank and that none of the fields for that record are NULL.

    LVL 4

    Author Comment

    Yes it does. I double checked the SQL and it looks great...before it gets nulled out.
    LVL 5

    Assisted Solution

    Also - you can try creating a {shudder} Access Query to make sure you have a record returned.  Go to Queries
    Create new query, in view change to SQL view
    Type the following (where 1 is the index from step 2 in my previous post.
    SELECT * from tblProjReqSummary where fldPRIndexNo = 1

    Also change tblProjReqSummary.* to just *
    sql = "SELECT * from tblProjReqSummary WHERE fldPRIndexNo = " & gintProjectRequestID

    LVL 58

    Expert Comment

    > "At this point I type in ?sql and the SQL varable is now blank"
    As Brian explained, the "sql" variable is out of context at that point. You can however debug what is happening by typeing "? Me.RecordSource", as it should, and certainly is, already set to a copy of your "sql" variable.

    Maybe you will get a better feel of what is happening if you do not manipulate the .RecordSource:
    * Set the Recordsource to "tblProjReqSummary" in design view
    * Replace the .Recordsource manipulation by:
        Me.Filter = "fldPRIndexNo = " & gintProjectRequestID
        Me.FilterOn = True
    Once the form is open, you can look at the filter (Records / advanced filter)

    While I'm at it, this can be done from the calling form:
        DoCmd.OpenForm "frmProjReqSummary", WhereCondition = "fldPRIndexNo = " & gintProjectRequestID

    No need for VB, DoCmd will set the .Filter and .FilterOn properties for you.

    This doesn't explain why it stopped working, as the coding is sound. Run with Brian's suggestions: you probably have a problem with gintProjectRequestID...

    Hope this helps!
    LVL 4

    Author Comment

    Thanks all for the feedback and help.  I came back in this morning and now, the debug.print SQL and MSGbox sql lines are working. So I made Brian's recommended change (Select *) to tidy it up.  But the SQL statement looks good. In the immediate window, I can see the SQL is getting setup correctly. I cut and paste the line:

    select * from tblProjReqSummary where fldPRIndexNo = 55

    into a new query and it returns the correct record set.

    I went ahead and took a few steps back and removed the oN Open event, and bound the form directly to the table tblProjReqSummary. Enabled the Navagation control so that I can cycle through all the records. But when I load the form, I get the same blank form staring back at me. Well now I'm really confused and convinced even more that I have done something stupid and just unable to make the connection. This form does have a few subforms, but I haven't touch those in days.  (yeah,  I can hear going Suuuuuuuure). But really, I completed the GUI work and have been working on some functions to send email and unrelated stuff.

    Man I'm confused.  Any other troubleshooting recommendations at this point? As this no longer appears to be a me.recordsource issue.

    LVL 58

    Accepted Solution

    Then perhaps a Me.Filter or Me.DataEntry issue?

    Create a "stop" button, with only one line in the _Click event: Stop. Click it and debug... I see no other solution.
    Do you launch the form from the database window or code, or macro? If so, post the lauch code.

    Good Luck :)
    LVL 4

    Author Comment

    Remember I thought I did something stupid? Well it appears I fat fingered, or misclicked the data entry option to yes. I didn't see this until I imported the form into a new database and just tore out all the sub forms, code and was just left with a basic shell form. I still didn't see it until I create a new form using the same table and compared the two.  So it looks like Harfang called it.  But let me split this across the board, as you guys gave some good advice. Thanks again!!!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    933 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now