Right Join not allowed

[New2VB]

Greetings Gurus

I need a little help with a listbox/lookup problem please.

The listbox contains a list of company names which, when a company name is clicked on, brings up a corresponding datasheet display of orders that company has placed. That datasheet contains a combobox of contacts.

What I am trying to achieve is that the combobox only displays a list of contacts for the particular company selected in the listbox.

My query currently states:
SELECT dbo_Contacts.ID, dbo_Contacts.ContactName, dbo_Contacts.Contactid
FROM (dbo_Company RIGHT JOIN dbo_Contacts ON dbo_Company.ID = dbo_Contacts.CompanyNo) INNER JOIN dbrders ON (dbo_Company.ID = dbrders.CompanyNo) AND (dbo_Contacts.ID = dbrders.User)
ORDER BY dbo_Contacts.ContactName;

but obviously doesn't work.

Can you help please?

 

[JamesMcS]

Is an AND the right thing to put in there? That looks like a WHERE condition to me. Have you tried building the query graphically in the query designer and comparing that to the SQL you've written?

 

[jdraw]

I suggest you look at the video tutorials in this link

http://www.datapigtechnologies.com/flashfiles/combobox1.html

You may want to view a few of the tutorials regarding selected values in related boxes on a form.
Typically this relates to cascading combo boxes, but combobox and listbox can also be an issue.
Good luck.

 

[MSAccessRookie]

Is an AND the right thing to put in there? That looks like a WHERE condition to me. Have you tried building the query graphically in the query designer and comparing that to the SQL you've written?

While an AND may be an unusual case in this context, it is acceptable. I believe that the following might better express what I believe th be the OP's intent:

ON ( (dbo_Company.ID = dbrders.CompanyNo) AND (dbo_Contacts.ID = dbrders.User) )
​

 

[namliam]

You cannot do a Inner join on a Outer (left or right) join, you just cannot, and should not as well as it doesnt make sence.

The right join allows for contacts (I think) without companies.
Then the inner join cancels that by demanding both a Contact and a company...

Once you go outer join you have to "keep going" there is no turning back ! Once right, keep it right, in the Designer the arrow must always point in the same direction
- Inner
> outer joins
< outer joins
Good: Table1 - Table2 > Table3
Good: Table1 > Table2 > Table3
Bad: Table1 > Table2 - Table3 (This would be yours)
Bad: Table1 > Table2 < Table3
Offcourse good: Table1 - Table2 - Table3

This may not be exact, but I hope you understand what I mean and please do not "nit pick" sayinig "I think that one is right/wrong" its a global idea...

 

[New2VB]

Greetings Gurus

Thank you all for your valuable advice but I have yet to find a solution. I have made progress however in that the query will respond with the correct fields but only if the Company.ID is manually entered in the messagebox instead of it being read from the listbox (List76).

Any ideas on how I can get the query to accept the highlighted field on List76 as input?

SELECT dbo_Users.UserName
FROM dbo_Company INNER JOIN dbo_Users ON dbo_Company.ID = dbo_Users.CompanyNo
WHERE (((dbo_Company.ID)=([]![List76])))
GROUP BY dbo_Users.UserName;

Thanks,

 

[New2VB]

Hi again,

I have attached a replica mdb demonstrating what I am trying to achieve.

Bob works for Northwind
Harry works for Southwind
John works for Eastwind
Sue works for Westwind

when I select Northwind etc. I would like only Northwind's employee available in the "Employee" combobox in the Orders subform.

Is this possible? How?

Please could you post the code

 

[namliam]

Your looking for a "cascade combobox" see how far you get .

 

[New2VB]

Hi Mailman,

The problem is not the populating of the combobox, I can't get the combobox to recognise the inout from the listbox.

 

[namliam]

And the input is ... .suposed to limit the output yes?

 

[namliam]

I had a (nother) look at your database, I cannot do but repeat myself

Your looking for a "cascade combobox" see how far you get .

 

[New2VB]

Yeehaa...

Ok, so I got it working through a query, is it possible now to "tranlate" the following into an OnEnter code?

SELECT dbo_UserName FROM dbo_Users INNER JOIN dbo_Company ON dbo_Users.CompanyNo = dbo_Company.ID
WHERE (((dbo_Company.ID) = [Forms]![ConsoleIII]![List76]))
GROUP BY dbo_Users.UserName;

 

[namliam]

Your looking for a "cascade combobox" see how far you get .

Have you ?? had a ?? search??

Yes, this is kindoff the way, except it is 'good practice' to trigger this "on update" of your listbox.

Then update the recordsource of your combobox with this query substituting in the value(s) it is supposed to filter on.

SELECT dbo_UserName 
FROM dbo_Users 
INNER JOIN dbo_Company ON dbo_Users.CompanyNo = dbo_Company.ID
WHERE (((dbo_Company.ID) = [Forms]![ConsoleIII]![List76]))
GROUP BY dbo_Users.UserName;

Instead of that why not simply do:

SELECT dbo_UserName 
FROM dbo_Users 
WHERE dbo_Users.CompanyNo  = [Forms]![ConsoleIII]![List76]

This should return the same thing.

Except if your triggering this "on update" of your listbox AND substitute in the value(s) to search, it will look more like:

strSQL = ""
strSQL = strSQL & " SELECT dbo_UserName "
strSQL = strSQL & " FROM dbo_Users "
strSQL = strSQL & " WHERE dbo_Users.CompanyNo  = " & me.YourListbox
YourCombobox.Recordsuorce = strSQL

 

[New2VB]

Hi Mailman,

thank you for your patience with my noobish questions, as I explained I an new to vb/Msaccess and am trying to learn on-the-fly with very limited time availability.

I had a reasonably good look at cascading comboboxes but could not get that method of using a combobox as input to work using a listbox. The problem, as I mentioned, was getting the subform's combobox to accept the listbox as input. It turns out that I was pointing the rowsource at the wrong place and that the initial query was basically correct to begin with.

Regarding turning queries into VBcode, yes I did do a limited search but when you run into compile error after compile error it gets a bit frustrating after a while especially when multi-tasking beyond your capabilities.(Apologies for my little rant).

Re your post:
1. I am assuming by "on update" your mean "after update". If so I already have code there:-

Private Sub List76_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![List76], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub​

does your code follow this, or should I rather use an OnClick event?

2. I have tried the "strSQL" code you kindly posted but it doesn't work. I can still get the correct username/s to display in the combobox but if I select a new entry in the listbox the previous username still displays. I have tried many other methods of getting the combobox's rowsource to update over the last 4 hours but must admit I am struggling.

If anyone has any suggestions that do not include Refreshes, Requeries, creating DoCmd.OpenQuery or macros, I am willing to try them.

 

[namliam]

1. You shouldnt need any recordset, your me![List76] should already be the ID.. .But yeah its the basic idea.

2.
I went and found a perfect explanation for you:
http://www.access-programmers.co.uk/forums/showthread.php?t=137236&highlight=cascade+combo

It goes into much detail and even has a working sample (didnt test it myself, but trust Aje 100%)

 

[New2VB]

Hi Mailman,

Thank you, that explanation and the examples refer to a single table situation.

My problem is that I am selecting an entry from table1, populating a field in table 2 with an entry from table 3 which is dependant on entries from table 1 and 3 being equal.

Given that table1 (Company Names) has a few dozen entries and table 3 (Users) has a few hundred entries I do not want to use a SELECT CASE statement (for obvious reasons) to enter usernames in table 2 (Calls).

All I am trying to achieve is to click on List1 (CompanyNames) click on Combo2 (Usernames at the company selected in List1).

This I can do, however...

If I go back and select a different CompanyName form List 1, when I click on Combo2, I get the same list of usernames as I did the first time.

 

[namliam]

Hi Mailman,

Thank you, that explanation and the examples refer to a single table situation.

One table 10 tables, no difference, the basic of a combobox cascade stays the same.

This I can do, however...

If I go back and select a different CompanyName form List 1, when I click on Combo2, I get the same list of usernames as I did the first time.

You may have to include a requery for your combobox
Yourcombo.requery
After giving it a new recordsource.

 

[New2VB]

Hi Mailman,

I tried requerying the combobox, the subform and the main form's control. No good.

It's just this combobox that is causing the problem, all other fields display the correct information depending on which company is selected in the listbox. I just can't understand why the combobox gets stuck on the preceeding field.