[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


Form no longer loading me.recordsource

Posted on 2004-11-01
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
LVL 10

Assisted Solution

calpurnia earned 400 total points
ID: 12468380
I take it your SQL statement does actually return a record (i.e. gintProjectRequestID is a valid value for fldPRIndexNo)?

Assisted Solution

brianb99999 earned 800 total points
ID: 12469058
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.


Author Comment

ID: 12469059
Yes it does. I double checked the SQL and it looks great...before it gets nulled out.
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.


Assisted Solution

brianb99999 earned 800 total points
ID: 12469084
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

ID: 12470074
> "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!

Author Comment

ID: 12472729
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

harfang earned 800 total points
ID: 12472798
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 :)

Author Comment

ID: 12473061
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!!!

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

656 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