469,338 Members | 8,284 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,338 developers. It's quick & easy.

reference to field on form via a query - not working

reginaldmerritt
201 100+
I have a form which links to a subform via a PK. One of the fields used on this form is 'EmployerID' (not the PK) the form is named 'FRMSchemeOfWork'. On the subform there is a combobox which should list only the EmployerID on the main form and one other set employer.

In the query used for the combobox on the subform i have used the following criteria for EmployerID
Expand|Select|Wrap|Line Numbers
  1. [Forms]![FRMSchemeOfWork].[EmployerID] or 12

I keep getting enter parameter value for [Forms]![FRMSchemeOfWork].[EmployerID]

I have checked the spelling a thousand times and i'm pretty certain the syntax is correct. Is there a type of lock that can be put on the form to stop the query from seeing [Forms]![FRMSchemeOfWork].[EmployerID]?

I'm a bit stumped any suggestions would be most appreciated.
Sep 25 '11 #1

✓ answered by Stewart Ross

I'm glad your problem is resolved. It sounds like you had a corrupt form (especially in view of the OLE error message). As this can recur it would be worth ensuring you have a clean back-up copy of the DB ready just in case something else goes wrong. You may wish to import all tables, queries, forms, modules etc into a new clean database and work from that copy rather than your current one (in case the unknown form of DB corruption spreads to other objects without warning).

-Stewart

7 11504
Stewart Ross
2,545 Expert Mod 2GB
Assuming that FRMSchemeOfWork is the subform, you are trying to refer to it directly as if it was open on its own, but the subform in this instance is open as part of the main form and cannot be referred to without naming the main form involved. The subform has no independent existence when it is opened from a main form, and because it is not part of the Forms collection you are being asked to supply a parameter value in place of the invalid reference to Forms!FrmSchemeOfWork.EmployerID.

Assuming as mentioned that the subform's name is FRMSchemeOfWork, and assuming also that you have not named the subform object differently within the main form, you can refer to the employee ID as follows. You'll need to substitute for the name of the main form in the expression below, as you haven't told us what it is:

Expand|Select|Wrap|Line Numbers
  1. Forms![Your MainForm Name]!FRMSchemeOfWork.Form!EmployerID

NeoPa wrote an Insights article on how to refer to items on a subform, which is linked here for further info.

-Stewart
Sep 25 '11 #2
reginaldmerritt
201 100+
thanks for your reply steward. the form in question links to a subform, it is not a subform its self. the combo box using the query is on a subform but I don't think that makes any difference. I think the syntax would be the same if referring to FRMSchemeOfWork from another main form.
Sep 25 '11 #3
Stewart Ross
2,545 Expert Mod 2GB
I can't say I understand yet how you have set this up, but anyway there are a couple of things to try. When you have your forms loaded, open the VBA editor (by opening any code module, say, or by pressing Alt-F11) and in the Immediate window type:

Expand|Select|Wrap|Line Numbers
  1. ? [Forms]![FRMSchemeOfWork].[EmployerID]
(? is simply shorthand for the old Basic 'print' command).

If you get an error message saying the DB can't find the form this will tell you right away that the syntax for referring to the form itself is not correct. If you get an error message telling you that the field cannot be found then the form is correct but the name of the control is not.

If there is no error and the Immediate window shows you an employer ID in response then you know that the control reference itself is OK - in which case it could be timing issue about forms not being open when you load the subform concerned.

You can also try copying the SQL for the combo-box row source to a query and seeing if it will run manually after you have loaded the forms. I am presuming it is a SELECT query that is involved in filling the combo. Cross-tabs have known issues when referring to form controls. Running the query manually will mimic the effect of running the combo box query that is causing the current problem. If you still get a failure then the reference to the control concerned is not correct, although you clearly believe in this case that it is.

-Stewart
Sep 25 '11 #4
reginaldmerritt
201 100+
Thanks Stewart. Those are all very good points.

I have already tested [Forms]![FRMSchemeOfWork].[EmployerID] in the Immediate and receive the expected results.
The combobox that uses the query is on a subform to the main form FRMSchemeOfWork, so its definitely open.

I'm enquiring if there are any settings on an object or a form that could stop the query from working.
Sep 26 '11 #5
reginaldmerritt
201 100+
I was creating some OnUpdate events for some objects with no code (just REM statements) on the sub-form when I received and error message stating missing OLE something or other. I removed the OnUpdate events and still had the same error message.

I copied the actual form used for the sub-form and renamed the original so i could start removing each object to see where the error could be. But after deleting all the objects on the form i still got the same error message. Strangeness??

So i opened the the copied version of the form used as a sub-form (now named as the original form) and i received no error messages. I opened the FRMSchemeOfWork where the form is used as a sub-form and still no errors. Even Stranger??

Ok so some randomness from ms access, nothing new. I then tested the error I had with the combobox and now it seems to work. Excellent!!

So all I had to do was to copy the form being used as the sub-form (which has a combobox using a query that referes to an object on a main fom), then delete the original form and rename the copied form.

I'm guessing that there must have been some setting as I suspected on the form stopping the query from working that got reset when I copied the form. Or maybe just a bit of ms access randomness again.
Sep 26 '11 #6
Stewart Ross
2,545 Expert Mod 2GB
I'm glad your problem is resolved. It sounds like you had a corrupt form (especially in view of the OLE error message). As this can recur it would be worth ensuring you have a clean back-up copy of the DB ready just in case something else goes wrong. You may wish to import all tables, queries, forms, modules etc into a new clean database and work from that copy rather than your current one (in case the unknown form of DB corruption spreads to other objects without warning).

-Stewart
Sep 26 '11 #7
reginaldmerritt
201 100+
Thanks Stewart. I tried to open the database over a very slow VPN connection and it wouldn't open (probably due to the slow internet connection rather than a DB problem). After manually closing Access though task manager and reopening in the office the database is now corrupt. I don't know if that is to do with losing connection with the database or the corrupt form. I have lost around 6hrs of work. A harsh lesson to learn. "back up after every major design change!".

Thanks for the help Stewart. I'll have to start from scratch again now but i might do as you suggest, just encase there is a chance of corruption spreading.
Sep 26 '11 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Old Timer | last post: by
6 posts views Thread by Larry R Harrison Jr | last post: by
2 posts views Thread by jim | last post: by
3 posts views Thread by Henrootje | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Marylou17 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.