Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DYNAMIC SQL SUBFORM RECORDSOURCE RESET/REQUERY PROBLEM

Posted on 2004-04-15
19
Medium Priority
?
975 Views
Last Modified: 2008-02-20
I have this code running in a Access 2000 database:

Forms![frmAdvCoSearch].[frmSearchSub].Form.RecordSource = bsql
Forms![frmAdvCoSearch].Form![frmSearchSub].Requery

But when I use the same code (but change the form names) in a different, brand new Access 2003 database to reset the SQL of two subforms on the same primary form, it doesn't work.  So, I checked some PAQs and found that the syntax they used was slightly different:

Forms![frmSearch]![frmBuyers].Form.RecordSource = bsql
Forms![frmSearch]![frmBuyers].Form.Requery
Forms![frmSearch]![frmSellers].Form.RecordSource = ssql
Forms![frmSearch]![frmSellers].Form.Requery

But that still doesn't work!!  ANY IDEAS?!

THank you in advance,

n
0
Comment
Question by:normenclature
[X]
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
  • 8
  • 6
  • 5
19 Comments
 
LVL 7

Expert Comment

by:ala_frosty
ID: 10838654
By "doesn't work" what exactly do you mean? Are you getting an error? Does the recordset not get set

Try:
Forms("frmSearch").controls("frmBuyers").Form.RecordSource = bsql
Forms("frmSearch").controls("frmBuyers").Form.Requery
debug.print bsql
Forms("frmSearch").controls("frmSellers").Form.RecordSource = ssql
Forms("frmSearch").controls("frmSellers").Form.Requery
debug.print ssql


Make sure you don't have a field name of the name "frmSearch"  "frmBuyers" or "frmSellers" .. access really hates it when you do that.

Maybe insert a query where the debugs are to make sure your bql and ssql are really returning what you think they should be returning.
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10838766
Hi normenclature,


Check the properties of the sub-form container with the main form opened in design view.
The name property is often not what you would expect it to be, Access has a habit of using the name of the table or query that the subform is bound to as the name of the subform control, should be the same as the value in the next property field Source Object.

Alan
0
 
LVL 1

Author Comment

by:normenclature
ID: 10838798
Hi Alan!  

The crazy thing is there is no error-- it simply doesn't pull.  I've tested the queries and both pull the records they should.  It's just not setting or requerying or something.

OK-- frosty, I tried the code you sent and it appears that the resetting code is working because even though when I run the code, the subforms don't update, when I put the main form into design mode and then back to view mode, the records appear in the subforms!!  But when I run the code again, the records disappear--

very strange--

any other ideas?  thanks so much for helping me-- I'm on a deadline.

n
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 7

Expert Comment

by:ala_frosty
ID: 10838831
can you post this on an FTP site I could grab it and take a look? Kinda hard working blind on this.
Have you tried repainting the parent form?
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10838838
Hi normenclature,

Have you set your Child/master links or is the sub-form not bound to the main form.
If so is the child link field in the new SQL resultset as a field?

Alan
0
 
LVL 7

Expert Comment

by:ala_frosty
ID: 10838866
Sounds like maybe another event (on current?) is changing them back with the subforms requeries.
0
 
LVL 7

Expert Comment

by:ala_frosty
ID: 10838873
to that end, try some debugs in your on_current events. Maybe just print the recordsources to see whether they're changing unexpectedly.
0
 
LVL 1

Author Comment

by:normenclature
ID: 10838884
frosty-- if you could take a look, that would be great!  And anyone else if they'd be so kind...

ftp://ftp.jonesfamily.com/httpsdocs/ee/

uname: jonesfamily
pword: FdegrrD

Thank you a ton!!
0
 
LVL 1

Author Comment

by:normenclature
ID: 10838885
the file is called InStock.zip

thanks again!!
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10838954
0
 
LVL 1

Author Comment

by:normenclature
ID: 10839035
first go to the folder itself, then when you see it, download it--

the key is to first log into the /ee directory, then you should be good

thnx!

n
0
 
LVL 1

Author Comment

by:normenclature
ID: 10839044
you can do it in IE-- pull it up and then when you see the file, right click it and select Copy To Folder...

thanks again!
0
 
LVL 7

Expert Comment

by:ala_frosty
ID: 10839095
Got it.
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10839096
LogIn:
User name:
Pwd:


tried anonymous :(
0
 
LVL 7

Expert Comment

by:ala_frosty
ID: 10839164
frmSearch is bound to tblCONTACTS. Is it supposed to be that way? When I remove it, everything works. Why is it bound to the form?

Both FrmBuyers and FrmSellers were linked parent to child with the parent. Was that intentional?

Is there more going on here, or have I nailed it?
0
 
LVL 7

Accepted Solution

by:
ala_frosty earned 2000 total points
ID: 10839188
Note: Even after I deleted the parent/child relationships, the search did NOT work. It wasn't until I deleted the reference to the table tblContacts, that the search started working. i'm not sure exactly why, but if this gets you what you want then okay. If not, we have more work to do.
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10839428
Hi normenclature,

seems like you are in good hands here, I couldnt manage to crack  your security on your FTP site. LOL

Alan
0
 
LVL 1

Author Comment

by:normenclature
ID: 10839712
Frosty-- my man-- (assuming you are a man)- you DID nail it!  Thank YOU!!  I didn't think I had linked it to the table, but I remember I copied the frmContacts in order to save some time having to set the background properties, etc.

I guess I never knew that was a problem to reset properties on a subform of a form that has a table reference.  Weird.  I suppose you learn something every day.  Thanks again for taking the time.  You really went the distance for me and I REALLY appreciate it!!

Take care,

norm
0
 
LVL 7

Expert Comment

by:ala_frosty
ID: 10839741
No worries. Glad to help
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

722 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