Form no longer loading me.recordsource

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.
Who is Participating?
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 :)
I take it your SQL statement does actually return a record (i.e. gintProjectRequestID is a valid value for fldPRIndexNo)?
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.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ThirtAuthor Commented:
Yes it does. I double checked the SQL and it looks great...before it gets nulled out.
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

> "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!
ThirtAuthor Commented:
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.

ThirtAuthor Commented:
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!!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.