normenclature
asked on
DYNAMIC SQL SUBFORM RECORDSOURCE RESET/REQUERY PROBLEM
I have this code running in a Access 2000 database:
Forms![frmAdvCoSearch].[fr
Forms![frmAdvCoSearch].For
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]![frmBuye
Forms![frmSearch]![frmBuye
Forms![frmSearch]![frmSell
Forms![frmSearch]![frmSell
But that still doesn't work!! ANY IDEAS?!
THank you in advance,
n
Forms![frmAdvCoSearch].[fr
Forms![frmAdvCoSearch].For
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]![frmBuye
Forms![frmSearch]![frmBuye
Forms![frmSearch]![frmSell
Forms![frmSearch]
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
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
ASKER
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
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
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?
Have you tried repainting the parent form?
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
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
Sounds like maybe another event (on current?) is changing them back with the subforms requeries.
to that end, try some debugs in your on_current events. Maybe just print the recordsources to see whether they're changing unexpectedly.
ASKER
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!!
ftp://ftp.jonesfamily.com/httpsdocs/ee/
uname: jonesfamily
pword: FdegrrD
Thank you a ton!!
ASKER
the file is called InStock.zip
thanks again!!
thanks again!!
ftp://ftp.jonesfamily.com/httpsdocs/ee/InStock.zip
FILE NOT FOUND :(
FILE NOT FOUND :(
ASKER
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
the key is to first log into the /ee directory, then you should be good
thnx!
n
ASKER
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!
thanks again!
Got it.
LogIn:
User name:
Pwd:
tried anonymous :(
User name:
Pwd:
tried anonymous :(
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi normenclature,
seems like you are in good hands here, I couldnt manage to crack your security on your FTP site. LOL
Alan
seems like you are in good hands here, I couldnt manage to crack your security on your FTP site. LOL
Alan
ASKER
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
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
No worries. Glad to help
Try:
Forms("frmSearch").control
Forms("frmSearch").control
debug.print bsql
Forms("frmSearch").control
Forms("frmSearch").control
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.