chergh
blah
- Local time
- Today, 08:03
- 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.
The stored queries referred to in strSQL1 and strSQL2 are:
qry_items_by_location
and
qry_docs_by_location
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:
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?
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?