Syntax error in Join operation

[brucerwalker]

It is a copy, it is not split and it is an accdb. file. So far I have not been able to reduce it down to the max 2 MB file size the website will accept.

 

[jdraw]

Did you run compact and repair? That will remove any bloat/used but recoverable space.
Then you need to make a compressed file --I use WinZip.

The zip format will reduce the overall size and is acceptable for the forum.

There are videos if you search youtube windows compressing a file to zip

 

[brucerwalker]

The security on the database will not allow me to use compact and repair. When I do that it kicks me back out to the login screen. So when I zip it, the size is just over 3 MB. I may have an original untouched copy of it

 

[jdraw]

What security do you have??

You could try to create a new database,
then import just the tables you use in the query (that's giving the problem) and the forms involved.

 

[brucerwalker]

I was able to create a new database and have attached it to this post.

 

[brucerwalker]

The form searchrequest is the one to open, and the drop down with part number is where my problem is.

 

[jdraw]

I have looked at your database.
The query in question is the recordsource of form SubFormSearch.

When I take that SQL

SELECT REQUEST.*, Customer.Name, Programs.ProgramName, Report.FilePath
FROM (Programs INNER JOIN (Customer INNER JOIN REQUEST ON Customer.CustomerID = REQUEST.CustomerID) ON Programs.ProgramID = REQUEST.ProgramID) LEFT JOIN Report ON REQUEST.RequestID = Report.RequestID;

, and create a new query Query1, I do not get an error. I get the records shown in the attached jpg.

Note: The records returned are very long, so I have 2 jpgs
query1__leftside and query1__rightside in order to show most of the fields and values.

What you have seems to work much of the time, but I haven't seen any details of the database requirements.

Some general comments:

I recommend
- you have a clear description of the business /business requirements involved
- you create a data model/relationships when creating a database, base on the description
- you identify the Linkfields(Master/Child) when dealing with Form/subform
- give field and controls meaningful names (Command68_Click())
- include error handling in procedures that have conditional logic

NOTE: I have been on skype for over an hour then just submitted my response, just saw your latest post after I submitted mine.

What exactly must the user enter for searching? If they enter more than 1 selection, do you want those to be ANDed or ORed?

This code is where the issue arises. What is the TEST table?

'Field Parts
If (Not IsNull(Me.ComboSearchPart)) Then
    query = query & " INNER JOIN TEST ON ( Request.RequestID = Test.RequestID AND Test.PartNumber = '" & Me.ComboSearchPart & "')"
End If

You allow all selections in the comboboxes. There is no limiting values in say comboB, based on selection in comboA. This is called cascading combos and may be a design issue---depending on your requirements. For example, if user selects Company = Ford, there may be no need to offer selections for Parts that are not related to Ford.


Latest info:

I have modified your database, but did not change the name, so you may want to rename the accdb file so you don't get confused.

I had to make a few guesses/decisions.
I copied your original procedure fo the OnClick and changed the name to include XXOrigXX---that is your original.

I modified my copy of that procedure and left its original name so it gets executed when you click the button.
It now alters the SQL to use a where clause, and not the INNER JOIN. If you look at the code , there are some comments. Also, there are debug.print statements to print the values of variables to the immediate window.

If the changes aren't what you want, you can modify the name of that click event procedure, and rename your own to get your original logic.

Good luck.

 

[brucerwalker]

So I can just replace the existing code with the code you provided? Thank you very much for your help and suggestions. Since I am a beginner at this, I greatly appreciate it!

 

[jdraw]

Yes, you probably can. BUT, test it and make sure it does what you expect.

I asked some questions and made some recommendations in my last post. These may be important to your logic and requirements. If you are responsible for the database, then get some documentation re the design and operation of the database for your own peace of mind.