Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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

Reply With Quote
  #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?

Reply With Quote
  #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.

Reply With Quote
  #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...

Reply With Quote
  #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.

Reply With Quote
  #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...

Reply With Quote
  #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.

Reply With Quote
  #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...

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 07:30 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.