Rowsource Woes (1 Viewer)

chergh

blah
Local time
Today, 09:02
Joined
Jun 15, 2004
Messages
1,414
I've created a form with three list boxes and a combo box.

The combo gives a list of locations.

One list box gives the details of items kept at that location.

The other two list boxes are intended to show a list of items and documents that are currently not kept at that location.

There are buttons which allow items to be added or removed from the location. If an item is added to that location it should be removed from whichever listbox it was added from.

The issue I am having is in the query which provides the rowsource for the two listboxes that show items not at the location.

I have the following code.

Code:
Private Sub cbo_loc_Change()

Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim rs As Recordset

strSQL = "SELECT tbl_doc.doc_title, tbl_main.doc_version " & _
         "FROM tbl_main LEFT JOIN tbl_doc ON tbl_main.doc_fk = tbl_doc.doc_tbl_pk " & _
         "WHERE (((tbl_doc.doc_title) Is Not Null) AND ((tbl_main.loc_tbl_fk)=" & [Forms]![frm_location_contents]![cbo_loc] & ")) " & _
         "UNION ALL " & _
         "SELECT tbl_item.item_name, tbl_main.doc_version " & _
         "FROM tbl_main INNER JOIN tbl_item ON tbl_main.item_fk = tbl_item.itm_tbl_pk " & _
         "WHERE (((tbl_item.item_name) Is Not Null) AND ((tbl_main.status_tbl_fk) <> 2) AND ((tbl_main.loc_tbl_fk)=" & [Forms]![frm_location_contents]![cbo_loc] & "));"

strSQL1 = "SELECT tbl_doc.doc_title " & _
          "FROM tbl_doc " & _
          "LEFT OUTER JOIN qry_docs_by_location ON tbl_doc.doc_tbl_pk = qry_docs_by_location.doc_fk " & _
          "WHERE qry_docs_by_location.doc_fk IS NULL"
          
strSQL2 = "SELECT tbl_item.item_name " & _
          "FROM tbl_item " & _
          "LEFT OUTER JOIN qry_items_by_location ON tbl_item.itm_tbl_pk = qry_items_by_location.item_fk " & _
          "WHERE qry_items_by_location.item_fk Is Null"


Me!lst_contents.RowSource = strSQL
Me!lst_contents.ColumnCount = CurrentDb.OpenRecordset(strSQL).Fields.Count
Me!lst_contents.ColumnHeads = True
Me!lst_docs.RowSource = strSQL1
Me!lst_items.RowSource = strSQL2

'this bit is only for debugging
Set rs = CurrentDb.OpenRecordset(strSQL2)

rs.MoveFirst
Do Until rs.EOF = True
Debug.Print rs!item_name
rs.MoveNext
Loop
'end of debugging part


Me!lst_docs.Requery
Me!lst_items.Requery
Me!lst_contents.Requery

End Sub

The stored queries referred to in strSQL1 and strSQL2 are:

qry_items_by_location
Code:
SELECT tbl_main.item_fk, tbl_main.loc_tbl_fk
FROM tbl_main
WHERE (((tbl_main.item_fk)<>0) AND ((tbl_main.loc_tbl_fk)=[forms]![frm_location_contents]![cbo_loc]));

and

qry_docs_by_location
Code:
SELECT tbl_main.doc_fk, tbl_main.loc_tbl_fk
FROM tbl_main
WHERE (((tbl_main.doc_fk)<>0) AND ((tbl_main.loc_tbl_fk)=[forms]![frm_location_contents]![cbo_loc]));

When the even triggers, without the debugging code, no items appear at all in the two lists which show items not at those locations. When I add in the recordset debugging code I get the error:

Run-time error '3061': Too few parameters. Expected 1.

If I place strSQL1 and strSQL2 in stored queries they run properly and return the expected results so anyone got any idea as to what I am doing wrong?
 

ajetrumpet

Banned
Local time
Today, 04:02
Joined
Jun 22, 2007
Messages
5,638
Chergh,

the SQL looks OK to me, but there's a lot of it. Is it possible that all of the criteria in one of them is not being met? I wonder if there's a possible problem with your control referencing...??

Did you see that new FAQ I put up on the subject?
 

chergh

blah
Local time
Today, 09:02
Joined
Jun 15, 2004
Messages
1,414
The SQL is technically correct and works when strSQL1 and strSQL2 are stored queries, it's only when strSQL1 and strSQL2 are embedded within the VBA that the error occurs.
 

Rabbie

Super Moderator
Local time
Today, 09:02
Joined
Jul 10, 2007
Messages
5,906
The SQL is technically correct and works when strSQL1 and strSQL2 are stored queries, it's only when strSQL1 and strSQL2 are embedded within the VBA that the error occurs.
Have you tried a diagnostic print from your VBA to check that the SQL is in fact identical with the stored queries.
 

chergh

blah
Local time
Today, 09:02
Joined
Jun 15, 2004
Messages
1,414
I'll double check it in the morning, but i'm fairly sure they are identical as I copied and pasted from the VBE to the SQL view and deleted the quotes etc.
 

chergh

blah
Local time
Today, 09:02
Joined
Jun 15, 2004
Messages
1,414
Well I've got round the problem by getting rid of the stored queries and using a "NOT IN" statement which I found after a bit of searching the net.

Code:
strSQL1 = "SELECT tbl_doc.doc_title " & _
          "FROM tbl_doc " & _
          "WHERE tbl_doc.doc_tbl_pk NOT IN " & _
          "(SELECT tbl_main.doc_fk " & _
          "FROM tbl_main " & _
          "WHERE (((tbl_main.doc_fk)<>0) " & _
          "AND ((tbl_main.loc_tbl_fk)=" & Forms!frm_location_contents!cbo_loc & ")))"
          
strSQL2 = "SELECT tbl_item.item_name " & _
          "FROM tbl_item " & _
          "WHERE tbl_item.itm_tbl_pk NOT IN " & _
          "(SELECT tbl_main.item_fk " & _
          "FROM tbl_main " & _
          "WHERE (((tbl_main.item_fk) <>0) " & _
          "AND ((tbl_main.loc_tbl_fk) =" & Forms!frm_location_contents!cbo_loc & ")))"
 

Users who are viewing this thread

Top Bottom