Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
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
Old May 24th, 2007, 10:22 AM
Authorized User
Join Date: Apr 2007
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default Search by User_Name with combobox

Ok, I made a form to search by a User_Name within the database. I tried to use the same methods as the one that I made with a "Search by Status" however, I just can't figure out why it's not working. The form is pretty much the same visually. One combo box, UserName, one list box, ListUser.

I'm trying to make it so that the User_Name from the database shows in the combo box, got that to work ok. However, I'm trying to return the results from a query, code to follow, into the list box showing the following four "headers" within the listbox: Task_ID, Task_Description, Date_Originated, Status.

The code on the form is:

Option Compare Database
Private Sub Combo12_AfterUpdate()
'This is the combo box for the user to select the name they want to search by for user_name
Me.ListUser.RowSource = "qryUserName"
End Sub

Private Sub ListUser_AfterUpdate()
'this is the double-click event to open the task to be viewed or edited

Dim intTaskID As Integer
intTaskID = Me!ListUser
DoCmd.Close acForm, "frmSearchUserName"
DoCmd.OpenForm "frmTask", , , "[Task_ID] = " & intTaskID & ""

End Sub

Private Sub List9_AfterUpdate()
'this is the double-click event to open the task to be viewed or edited

End Sub

Private Sub ListUser_BeforeUpdate(Cancel As Integer)

End Sub

The query has the following:

Fields: Task_ID, User_Name, Task_Description, Status, Date_Orginated

Criteria under "User_Name" is: Like [Forms]![frmSearchUserName]![UserName] & "*"

Any ideas on why it won't work? I'm working on it but can't figure it out. It all looks the same as the "Search by Status" form, even the query.

Old May 25th, 2007, 06:34 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

I am not sure why you are using code to populate the combo box. You can configure that on the combo box and get greater control.

It looks like the combo box is bound to a UserID, that you name "intTaskID" in your code. This is not a good way to reuse code. You should call it intUserID.

BECAUSE when you open your form called "frmTask" (shouldn't this be frmUser?) you pass it "[Task_ID] = " & intTaskID & ""

If you are sending UserID, why do you pass that to Task_ID? Did you reuse field names in your tables too?

Anyway, that is probably the issue.

Also, put your code into variables before you put it into the runtime commands since that can screw with Access. So instead of:

DoCmd.OpenForm "frmTask", , , "[Task_ID] = " & intTaskID & ""

It shoud be:

Dim sLink As String

sLink = "[Task_ID] = " & intTaskID

DoCmd.OpenForm "frmTask", , , sLink

Also note that your syntax is wrong here:

"[Task_ID] = " & intTaskID & ""

Should be:

"[Task_ID] = " & intTaskID

Did any of that help?

Old May 25th, 2007, 08:27 PM
Authorized User
Join Date: Apr 2007
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts

Well, I figured it out. I made a text box, set the properties to invisible and then inserted code into the 'control source' for that text box which pulled the user name from the combo box. Then the query I set up to get the results, searched using the information in the text box, rather than the combo box. I then put the results of the query into the list box. When they find or see the one they want, it they then click on it and it'll open the task for viewing or editing.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Filling Combobox with value from other Combobox ayazhoda Access VBA 6 June 5th, 2007 04:58 AM
File Search / Indexing Search with .net 2.0 maulik77 ASP.NET 1.0 and 1.1 Basics 2 March 15th, 2007 12:45 AM
Search / Add data in a ComboBox Sudoku Pro VB Databases 1 May 4th, 2006 07:51 PM
Search / Add data in a ComboBox Sudoku Visual Basic 2005 Basics 0 March 30th, 2006 11:21 AM
Search Engine for Full-text Search Kala ASP.NET 1.0 and 1.1 Professional 2 August 29th, 2004 02:16 AM

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