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.

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?






mmcdonal

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.