"the expression is typed incorrectly or it is too complex "

[Design by Sue]

Below is the SQL view of the query which is resulting in this error message. This is one one of 2 subforms on my mainform. The first subform has a similar query based on a different table and created the same as this one and that one does not result in the error. Is this enough information to see the problem??

SELECT [Components Combination TBL].Prepack, [Components Combination TBL].Component, [Components Combination TBL].CQuantity, [Components TBL].Description, [Components TBL].[Inventory Type]
FROM [Components TBL] INNER JOIN [Components Combination TBL] ON [Components TBL].[Components ID] = [Components Combination TBL].Component
WHERE ((([Components Combination TBL].Prepack)=[Forms]![Combinations FRM]![Prepack ID]))
ORDER BY [Components TBL].Description;

Thanks
Sue

 

[Design by Sue]

After some experimentation I have found that the problem seems to be in the Where statement. This links the information to a text field on the mainform that is also used as a source for the other subform. Should using the same control as a source for the information in 2 different subforms cause this error message? and if so how can one either cancel the warning (the subform seem to work correctly) or correct the situation so there is no message.

Sue

 

[boblarson]

)=[Forms]![Combinations FRM].[Form]![Prepack ID]))

 

[Design by Sue]

Here's what I have now based on your response, and I still get the same error message - with either version the form works.



SELECT [Components Combination TBL].Prepack, [Components Combination TBL].Component, [Components Combination TBL].CQuantity, [Components TBL].Description, [Components TBL].[Inventory Type]
FROM [Components TBL] INNER JOIN [Components Combination TBL] ON [Components TBL].[Components ID] = [Components Combination TBL].Component
WHERE ((([Components Combination TBL].Prepack)=[Forms]![Combinations FRM].[Form]![Prepack ID]));

 

[Design by Sue]

Is there any way to trap the error code for this?

Sue

 

[Design by Sue]

I found the error code as 3071 and tried putting a trap for in the oncurrent, onload and onerror events with no results. I have searched for a solution to this and have come up empty - please help if you can.

Thanks
Sue

 

[llkhoutx]

Trap the error in the Class Module generating the error.

Are you query tables joined 1:1 or 1:Many?

Subforms having the same recordsource and criteria are evidence of a design issue. Why are 2 subforms being ultilzed? Why can't you use just one subform?

Are your subforms populating correctly? One subform populates before the other and both before the main form. I vaguely recall that the first subform generated populates first even before the main form.

With subforms populate before the mainform have produced unpredictable results for me on initial open.

 

[MSAccessRookie]

Is there any way to trap the error code for this?

Sue

I am not sure about trapping the code, but I have found that sometimes the error that is reported is not always due to the reason being reported. At the same time, I have had some success in determining alternative reasons by eliminating the WHERE Statement, and changing the Query to add additional Fields to display values in the WHERE Statement. If, as I expect you will, you encounter any errors, keep making changes until you get the results that you are looking for. Eventually you should have the format that you need for your Query.

SELECT [Components Combination TBL].Prepack, 
    [Components Combination TBL].Component, 
    [Components Combination TBL].CQuantity, 
    [Components TBL].Description, [Components TBL].[Inventory Type], 
    [Components TBL].[Components ID], 
    [B][COLOR=#ff0000][Components TBL].[Components ID],[/COLOR][/B]  
    [COLOR=red][B][Forms]![Combinations FRM]![Prepack ID][/B][/COLOR]
FROM [Components TBL] INNER JOIN [Components Combination TBL]  
    ON [Components TBL].[Components ID] =  
         [Components Combination TBL].Component;

 

[Design by Sue]

llkhoutx: There are 2 subforms because each as a different table as it's control source, one handles additions of items and the other handles components. I am sure the database is designed correctly.

Your comments about the timing of the load of each form is what I was coming up with when I woke at 5 this morning and started reasoning through this (after NOW I couldn't get back to sleep!) It would seem that if queries on the subforms are based on the mainform, the mainform has to load first. (Interestingly though when there is only one subform on the mainform I don't get the message) So my question here is- Is there some way to force the mainform to load first?


Rookie: I am not at my workstation now and can't try your code for til a little later, but just so I understand... it looks like you are adding the same coding to the select statement but removing the where statement. If I do this how can I get the records required (as previously required by the where?) I don't understand what you are doing and would appreciate any clarification.

Sue

 

[RainLover]

For something different.

Using the SQL you have create a New Query in the query grid. Open the main form then see if you can open the query.

This is just a method to track down your problem. When solved you can post the new SQL back into the Form.

Also you can open a Sub form without any Record Source. Once the Main Form is open you can then set the Recordsource for the Sub Form via Code.

Just a couple of ideas to think about. They are not tested.

 

[Design by Sue]

Rain - I did the test as you suggested just to be sure. Yes the query works perfectly - and I know that anyway, because after I click ok to the message everything on the form works 100% correctly. It has something to do with the load timing I am sure. Your suggestion about loading the record source after the main form opens, but the issue here is not the record source, it is with the parent / child linking. Is there any way to open a subform without the parent link and add that through code? If so can someone help me with the coding for this.

A second thought on this - it is the query that a list box is based on that is using criteria based on the mainform that is triggering the message. So is there someway to load (link ? not sure the correct term here) the query for that list box after the mainform has opened?

 

[Design by Sue]

Anyone have any thoughts on this one. Once again, got to get through this so I can get on with this project. As I continue to fight this battle it appears to me that maybe the solution is one of the following, but I need help doing these

1. Is there a way to force the mainform to load before the subforms?

2. Is there a way to trap the error code 2113. Someone in the responses stated to trap it in the class module, but what I tried didn't work - I have never worked with class modules, so any insite on how to accomplish this would be a great help.

3. Is there a way, after the form and subforms are opened to apply a query to the listbox in the subform. That way the forms would load first and then the query could be added and requeried

( :banghead: - yep that's the way I feel)

 

[MSAccessRookie]

llkhoutx: There are 2 subforms because each as a different table as it's control source, one handles additions of items and the other handles components. I am sure the database is designed correctly.

Your comments about the timing of the load of each form is what I was coming up with when I woke at 5 this morning and started reasoning through this (after NOW I couldn't get back to sleep!) It would seem that if queries on the subforms are based on the mainform, the mainform has to load first. (Interestingly though when there is only one subform on the mainform I don't get the message) So my question here is- Is there some way to force the mainform to load first?


Rookie: I am not at my workstation now and can't try your code for til a little later, but just so I understand... it looks like you are adding the same coding to the select statement but removing the where statement. If I do this how can I get the records required (as previously required by the where?) I don't understand what you are doing and would appreciate any clarification.

Sue

Sue,

What I did was to create a SELECT Query that displays the arguments that are failing in your WHERE Clause. I suspect that the Query will not work as you expect, and thet you will probably need to tweak it until it does. Once you make the SELECT Query get the values that you are looking for, you can make the appropriate changes to your original Query and it should do what you want it to.

-- Rookie

 

[Design by Sue]

Rookie - thank you for you code and your response. I just created a test query and your code seemed to work - I will test it in the form shortly and let you know the results. (I sure don't understand what you did but this may get me out of the problem!)

Sue

 

[Design by Sue]

Rookie - OK I tried to link the test query to the list box and it didn't work there. Can you explain how your rewrite links this to the mainform field prepack ID as was in my originally posted code with the where clause?

Sue

 

[Design by Sue]

All of a sudden the message has stopped appearing. Thank God - though I don't know what made it stop. I had backed up before testing Rookie's query, found it didn't work and went back to my backup file, and now all is ok.

This is so weird!

Thanks for your help
Sue

 

[MSAccessRookie]

Would it be possible for you to post both the test SQL code that you believe works, as well as the code that goes into the list box that you believe does not work?

 

[Design by Sue]

here is the SQL that I originally had that didn't work and now does (??)


SELECT [Components Combination TBL].Prepack, [Components Combination TBL].Component, [Components Combination TBL].CQuantity, [Components TBL].Description, [Components TBL].[Inventory Type]
FROM [Components TBL] INNER JOIN [Components Combination TBL] ON [Components TBL].[Components ID] = [Components Combination TBL].Component
WHERE ((([Components Combination TBL].Prepack)=[Forms]![Combinations FRM]![Prepack ID]));

For the test - I used your code and pasted it in to a query in SQL view and saved it as a query.

Sue

 

[MSAccessRookie]

Sorry, but I see nothing wrong here. Guess it is a good thing that it just decided to cooperate

 

[RainLover]

Sue

If you are having problems with the list box then can you post the SQL for that.