Full Version: Query-By-Form help!
UtterAccess Forums > Microsoft® Access > Access Forms
bekibutton
Hi,
I'm trying to use QBF (ref : MS Knowledge Base Article) to search records. It works, but brings up the results in a separate datasheet view. I would like it to display the results in a list box without the datasheet appearing. Is there any way of doing this?
Thanks
Becki
scuzz
Um... i can think of a long way of doing it. If it displays in datasheet view then does it create a new table each time or store the data in a specific table. If it stores the data in the same table each time, could you not then create a form based on that table, and after the query code, put some code in to open that form to display the results.
bekibutton
I'm not sure it 'stores' it anywhere as such cos it's dynamic. Don't know shrug.gif
scuzz
Well you should see a new table appear in your database window after you run the query. If so then it is storing the data, if not then it's doing what it [censored] well pleases! lol
danishani
Hi Becki,
Take a look at this article to make use of a Listbox. If you are using the code of the article, make sure you will change the QueryDefs according the QueryDefs described in the Microsoft article.
I hope that makes sense...
Daniel
bekibutton
OK well I added an SQL statement to my code to bring up the data in the list box, that appears to work. However I need to be able to run the query without 'opening' it (ie. the datasheet appearing). Is this possible?
Thanks!
Becki
danishani
Well if u skip the command DoCmd.OpenQuery ... I guess thats the command which opens the datasheet...
TH
Daniel
bekibutton
Yep that worked. Thought maybe it needed it to run the query. Guess not! Thanks for your help frown.gif
danishani
Cheers, glad you got it up and running frown.gif
aniel
bekibutton
Ugh, just when I had got it working I've come across another problem - I need to be able to search in both the main table and its subtable. Any ideas? shrug.gif
danishani
I assume that you link have the maintable and subtable linked, then add your subtable to the SQL, linked by an ID field.
TH
Daniel
bekibutton
How? I made a guess and used AND but obviously that didn't work :(
danishani
Using the example of Microsoft Article, adding the subtable:
et QD = db.CreateQueryDef("Dynamic_Query", _
"Select MainTable.*, SubTable.* FROM MainTable INNER JOIN SubTable ON MainTable.YourID = Subtable.YourID " & (" where " + Mid(where, 6) & ";"))
You need to correct the names of the table and the ID's.
HTH
Daniel
bekibutton
ooo.gif That looks like fun! I'll have a go and let you know how it goes
bekibutton
It didn't like that. It's bringing up no records at all:
CODE
MsgBox "Select * from tblQuote " & ("where " + Mid(where, 6) & ";")
Set QD = db.CreateQueryDef("Dynamic_Query", _
"Select tblQuote.*, subPart.* FROM tblQuote INNER JOIN subPart ON tblQuote.id = subPart.id " & (" where " + Mid(where, 6) & ";"))
danishani
U need to adjust the MsgBox lines as well...
aniel
bekibutton
But does the message box actually do anything to the query? It's come up now with a couple of errors that say my SQL is no longer valid:
CODE
Me.List0.RowSource = "Select Dynamic_Query.id,Dynamic_Query.QuoteNumber," & _
"Dynamic_Query.Initials, Dynamic_Query.CustomerName,Dynamic_Query.RevDate," & _
"Dynamic_Query.Type,Dynamic_Query.Category,Dynamic_Query.Sort " & _
"From Dynamic_Query;"
danishani
Well its difficult to tell why the errors occur, because can't see the structure or anything... hmmm Are you able to post your database, zipped and compacted to check it out?
aniel
bekibutton
OK. Ignore the fact that the amend or add order buttons don't really work, think that's to do with lack of data in the tables.
Edited by: bekibutton on Mon Oct 11 9:58:44 EDT 2004.
danishani
Hi there,
fixed your Dynamic Query, hopefully this is what you are after. I tested it, and it seems to work now... frown.gif
See attachment...
Daniel
bekibutton
Wow, that works great! Thanks! And it made me tidy up my tables a bit to remove duplicate columns frown.gif
Thank you!
uarulez2.gif
danishani
U are very welcome, and yea this forum rulez indeed thumbup.gif
Cheers,
Daniel
bekibutton
Hey, one 'last' question - I'm now trying to add the date search bit from the same MS Knowledge Base Article, but it keeps saying I've got a syntax error:
S:
CODE
' Note the number signs (#) surrounding the date field [Order Date].
If Not IsNull(Me![Order End Date]) Then
   where = where & " AND [OrderDate] between #" + _
   Me![Order Start Date] + "# AND #" & Me![Order End Date] & "#"
Else
   where = where & " AND [OrderDate] >= #" + Me![Order Start Date] _
   + " #"
End If

Me:
CODE
If Not IsNull(Me![Date2]) Then
     where = where & "AND [RevDate] between #" + _
     Me![Date1] + "# AND #" & Me![Date2] & "#"
Else
     where = where & " AND [RevDate] >= #" + Me![Date1] + " #"
End If

I've only tried it with a single date but it says the syntax is wrong for the expression ...
danishani
U may try to change the "+" sign into "&" ... hope Access likes it more wink.gif
!--c1-->
CODE
  
' Note the number signs (#) surrounding the date field [Order Date].
If Not IsNull(Me![Order End Date]) Then
   where = where & " AND [OrderDate] between #" & _
   Me![Order Start Date] & "# AND #" & Me![Order End Date] & "#"
Else
   where = where & " AND [OrderDate] >= #" & Me![Order Start Date] _
   & " #"
End If

CODE

If Not IsNull(Me![Date2]) Then
     where = where & "AND [RevDate] between #" & _
     Me![Date1] & "# AND #" & Me![Date2] & "#"
Else
     where = where & " AND [RevDate] >= #" & Me![Date1] & " #"
End If

HTH
Daniel
bekibutton
I usually use the ampersand anyway cos it seems like the plus sign is more arithmetic. But I've tried that and it's working now, so thanks for your help grin.gif
uarulez2.gif
danishani
Glad you got it working now wink.gif
aniel
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.