Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old May 1st, 2007, 02:40 PM
Authorized User
 
Join Date: Apr 2007
Location: Fort Bliss, Texas, USA.
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default 'Search Form' based on criteria

I am trying to create a form that has an area that a user enters a key word, clicks 'Find!' and then a query is ran on 'tblTask' for data within the 'Task Description' field. The results of this query are displayed on that same form in some thing that looks like a listbox with columns for the following information:

Task_ID
Task_Description
Date_Originated
Status

When the user finds the one that is correct, they then can double-click on that task and it is opened in 'frmTask' for editing purposes.


Thanks,

Bryan

  #2 (permalink)  
Old May 2nd, 2007, 10:42 AM
Friend of Wrox
 
Join Date: Apr 2006
Location: Ternat, , Belgium.
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default

What exactly is the problem?

What have you done and what do you need help with?

  #3 (permalink)  
Old May 2nd, 2007, 10:51 AM
Authorized User
 
Join Date: Apr 2007
Location: Fort Bliss, Texas, USA.
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Vince,

I thought that I explained it pretty well.

I created a form, 'frmSearchTask', that has an unbound textbox where the user can enter a word to search by. There is a command button next to it that essentially will need to run a query to find the exact text in the 'tblTask'-'Task_Description' (memo field). ALL of the results are displayed in a listbox below where they entered the search criteria. When the user sees the one that they want, they double-click the one that they need and it then opens that task on the 'frmTask' for editing.

  #4 (permalink)  
Old May 3rd, 2007, 04:58 AM
Friend of Wrox
 
Join Date: Apr 2006
Location: Ternat, , Belgium.
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default

So if I get it wright this time, you need help to open the frmtask when you double-click on the item in the list you want to see...

Then first on the double-click event of the list box, find the primary key of the record you want to see. I guess your PK is Task_ID...

Do this with

dim intNumber as integer
dim strSQL as string
dim stDocName as string
dim stLinkCriteria as string

intNumber = me.nameoflistbox.column(0)

strSQL="SELECT tblTask.Task_ID, tblTask.Task_Description, tblTask.Date_Originated, tblTask.Status " & _
            "FROM tblTask " & _
            "WHERE (((tblTask.Task_ID)= " & intNumber & "));"
strTask=strSQL
stdocname="frmTask"
docmd.openform "stdocname", , ,stLinkCriteria

Create a module where you put the line

public strTask as string

and on the on load event of the "frmTask" you give the strTask to the forms record source with

Me.RecordSource = strTask

This way you have selected the record you need and if I didn't forget a thing the only record shown should be the one you selected with the dubbel click...

Hope this helps...

  #5 (permalink)  
Old May 3rd, 2007, 09:49 AM
Authorized User
 
Join Date: Apr 2007
Location: Fort Bliss, Texas, USA.
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I guess it would work if I can figure out how to make the "Find!" command button search the 'Task_Description' field in the 'tblTask', return the results to the listbox below it and then allow the user to highlight the found task, then click the command button "Open" or just double-click on the desired task to open it into the 'frmTask' for editing.

  #6 (permalink)  
Old May 3rd, 2007, 01:12 PM
Friend of Wrox
 
Join Date: Apr 2006
Location: Ternat, , Belgium.
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok, seems like I went to quick if I understand it...

You need to know how to show the records in the list box when you click the find button...

First of all, leave the row source of your list box empty, it will be filled in when you click the action button 'find'

on the on click of the action button, put the next code:

dim strSQL as string
dim strDescription as string

strDescription=me.NameOfUnboundTextBoxWhereUserPutsWordToLookFor

strSQL = "Select tblTask.Task_ID, tblTask.Task_Description, tblTask.Task_Originated, tblTask.Status FROM tblTask WHERE ((tblTask.Task_Description) like *" & strDescription & "*));"

me.NameOfTheListBox.RowSource=strSQL

me.NameOfTheListBox.Requery

If I'm not mistaken, this should show the items that matches in the list box.
If you want multiple colums in the list box, you need to change its properties in the Format tab and than Column count, column head (if you want them) and column widths.

If this works and you add what I already gave you, I believe everything should work. If not, let me know and I'll take a look where it is going wrong...

  #7 (permalink)  
Old May 3rd, 2007, 02:33 PM
Authorized User
 
Join Date: Apr 2007
Location: Fort Bliss, Texas, USA.
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Did that, and but in that 'SELECT'-'WHERE' statement, it returned a compile error with the entire line highlighted grey.

  #8 (permalink)  
Old May 4th, 2007, 09:39 AM
Friend of Wrox
 
Join Date: Apr 2006
Location: Ternat, , Belgium.
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default

try changing the

WHERE ((tblTask.Task_Description) like *" & strDescription & "*));

to this

WHERE (((tblTask.Task_Description) like " & chr (34) & "*" & strDescription & "*" & chr(34) & "));

I think this should solve that problem...



Similar Threads
Thread Thread Starter Forum Replies Last Post
sum group values based on cell criteria darkdestroyer Excel VBA 1 January 9th, 2008 09:13 PM
Multiple Search Criteria in a Form tet Access VBA 5 October 27th, 2007 08:54 AM
attach excel content to emails,based on criteria miracles Excel VBA 0 May 21st, 2007 09:45 PM
select rows based on a criteria and paste ashu_gupta75 Excel VBA 2 July 30th, 2004 01:32 AM
Report Heading Based On Selection Criteria Fo CloudNine Access 5 March 4th, 2004 08:09 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.