473,366 Members | 1,085 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,366 software developers and data experts.

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 12415
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

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

Similar topics

1
by: Old Timer | last post by:
I wish to type in a number in my "Code" field, for instance 1060, I then wish the number 1060 to trigger an event that will fill in the next field (township field) For instance, 1060 brings up and...
6
by: Robert C | last post by:
I have 2 forms, an unbound which calls a bound form via a command button. Both forms have a textbox called TDate. The name of the calling form is SelectionFrm. When I set the DefaultValue...
1
by: Norbert Lieckfeldt | last post by:
MS Access 2002 here. I am just trying to set up a simple database for a friend who's an optician. Basically, all clients with address details, date of last eyetest and a drop-down combo box to...
6
by: Larry R Harrison Jr | last post by:
I have a database I'm designing in Access 97. I have a custom field in a query which looks in {Table of Documents} and shows them all. It then needs a "latest revision number," stored in another...
0
by: Ratman | last post by:
Regular Expression validators NOT working, Required Field validators ARE working I have tried everything from moving the aspnet_client folder around from root to web application, making it a...
2
by: jim | last post by:
I have created a Table that has 13 fields and 2 Primary Keys, e.g. 60 1, 60 2, ... 60 28, 61 1, 61 2, ... 61 28, etc... I want to create a Form where I can input the Primary Key values to query...
3
by: Henrootje | last post by:
I have a form based on the query: SELECT tblCreditGeld.CG_Selected, tblCreditGeld.CG_ClientSelected, tblCreditGeld.CG_ComplexSelected, tblCreditGeld.CG_OKGR_ID, tblCreditGeld.CG_OKGR_IDN,...
1
by: Neil | last post by:
A client of mine is using a form (Matters) which has a button which opens another form in dialog mode. The underlying query of the second form references the first form as a paramter value, as...
3
by: sphinney | last post by:
I have a form with a subform. The code of the parent form alters the "Recordsource" property of a subform. Altering this property automatically triggers the subform to requery a table that has lots...
1
by: dohminator | last post by:
Hello Experts, I have two systems written in Access 2003 that I'm in the process of merging. A user will be given a specific screen depending on his user name. I have a form (let's call it...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.