select accno.tbaccession,classno.tbaccession,bookno.tbaccession,title.tbaccession,author1.tbaccession,status.tb_issue from tbaccession cross join tb_issue where title.tbaccession = '" & Trim(ListBox1.Text) & "';

this code does not work, pls help

For a start if the table name is tbaccession then the columns should be referenced as tbaccession.accno, etc not accno.tbaccession.
You could also post up the actual error you are getting just to help us help you

Your code is horrible.
Columns names are qualified by table name not the reverse as mentioned by you in the SQL.