Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Aug 2006
    Posts
    559

    Unanswered: Search Form & Query

    Ok, I made a form to search by a User_Name within the database. I tried to use the example that Paul (pkstormy) on here gave me 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.

    **Attached is the DB

    ***Update: I've gotten it to run the query, but it doesn't take the dtaa entered into the actual combobox, it puts up an MS Access Query Input Parameter window and there you have to type in the data. It'll display the results in the list box, then double-clicking will allow you to open the record on the form. But it's not pulling the inputted data from the combobox.
    Attached Files Attached Files
    Last edited by Grafixx01; 05-24-07 at 13:27. Reason: Inserting attachment

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    frmSearchUserName

    Hey Bryan,

    I thought I'd take a quick look since I had a hand in setting this up for you. When I looked at the code, you have this firing on the BeforeUpdate, not the AfterUpdate in the listbox.

    Private Sub ListUser_BeforeUpdate(Cancel As Integer) - I changed this to the AfterUpdate event.

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

    End Sub

    Also, you had the form name spelled wrong in the criteria in the query (see qryUserNameBad).

    Here's what I did...
    Made another text box (called FStatusName) on the form which grabs the column(1) (i.e. not 0) from the FStatus box which is the UserName (notice the ControlSource for FStatusName).
    Based the query off of FStatusName verses FStatus since you were trying to match your criteria against the first field which was the ID field.

    The other alternative would be to switch the columns in the combobox so UserName is first but I liked doing it the above way better.

    Hope this helps.
    Attached Files Attached Files
    Last edited by pkstormy; 05-24-07 at 22:20.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    With Headers

    Here I changed the property of the listbox to the header = yes. (and it has the same code mentioned above)
    Attached Files Attached Files
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Aug 2006
    Posts
    559
    Paul,

    I thank you for taking a look at the db. I downloaded and looked at what you did versus the one that I continued to work on while at work waiting to see if anyone could figure out what I was doing wrong. I managed to create a "Search by Organization" and did everything the same as the "Search by Status" that you helped me out with. That, "Search by Organization", works fine.

    Yet when I look at the "Search by User Name" that you did versus mine, they look exactly the same. I don't want to just continue to use what you did. I want to see where it is that mine is not doing that to make it correct. I set up the query the same way the "Search Status" and "Search Organization" is for the "Search User Name", all of the other items on the form are the same, yet it doesn't work.

    I know you fixed it, but I guess like I said, I just wonder what is wrong with mine if it looks the same as what you did. I even tried to copy your code / criteria for the form and query, it still didn't work.

    ***On a side note, I was trying to figure out how you got the 'column heads' to display, pulled my hair out for like an hour trying to figure it out. Thanks for telling me.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Bryan,

    The 2 things I saw wrong with yours were:
    1. combobox had ID as the first column for FStatus. This caused the criteria in the query to look at the ID verses the UserName (i.e. like Forms!frmSearchUserName!FStatus) which returned nothing since there were no UserNames in the query which would have a 12 or 13, etc..
    2. The criteria statement in the qryUserName had the wrong form name. It was off by just a bit:frmSearchUserNames verses frmSearchUserName (you had an s on the end.)

    The little things are what always catch anyone. It's usually syntax, pointing to wrong column, or mispelled formname/fieldname/queryname which are the biggest culprits. A lot of times I will go to the forms, right-click on the form and select rename, highlight the name, and Ctrl-C (Copy), then go to the criteria in the query, and do a paste Ctrl-V to make sure I have the name right. Be careful you don't actually rename the form when doing this. I do the same type of thing with fieldnames, query names, procedure names, etc.. sometimes.

    Also keep in mind when you use comboboxes, a query with criteria as yours looks at the first column in a combobox (i.e. Forms!frmSearchUserName!FStatus). You can't set the query to look at the second column for criteria (i.e. Forms!frmSearchUserName!FStatus.column(1) - note 1 really equals the 2nd column since you start counting columns at 0 when you're working with comboboxes. It's always bugged me with comboboxes columns starting with 0 but it's something you'll have to work ith as this is true for all of MSAccess. Listboxes are the same way - the first column is: Forms!frmSearchUsers!ListUsers which is the same as Forms!frmSearchUsers!ListUsers.column(0) and the 2nd column is Forms!frmSearchUsers!ListUsers.column(1).

    Look again at the ControSource property for the FStatusName field I added on the frmSearchUserName form. You can use syntax like: Forms!frmSearchUsers!FStatus.column(1) almost anywhere except in criteria for queries. But like I mentioned, the other option would have been to move the UserName to the first column in the combobox on that form and change the width for column 1 from 0" to 2". Then you could have used Like Forms!frmSearchUsers!FStatus & "*" in your criteria for your query.
    Last edited by pkstormy; 05-25-07 at 00:35.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Aug 2006
    Posts
    559
    Paul,

    Ahhh.... I see it now. Another guy I know at my job was trying to help me figure out that same problem. Issue with that is that he hasn't played with MS Access in close to 6 years so he and I are kind of in the same boat.

    I looked at what you did, understand and now I am trying to figure out one thing, which is:

    On my form, frmTask, at the bottom, there is a listbox that is called "Pick-Up Contact". This list box, when double-clicked, opens the "Search Contact Form", frmSearchContact. There the user, well, obviously does what the form does. I have to figure out a way to pass that information, in this order:

    Last Name, First Name, Phone

    Back to the form, frmTask.

    I am trying to do it the way you did it on the "Search User Name". Going to give it a whirl~!!!

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Bryan,

    Just remember that you want to pass the values from the "frmSearchContact" form to the frmTask BEFORE you close it. This is true for any other forms you want to pass values to frmTask. Otherwise you have to write the values to global variables and then retrieve them from the global variables (which is a pain). Or you have to Dim variables, write to the variables, close the form, and then write the variables to the form (which I don't like to do). See Post Below to pass values the other way.

    You might have something like this when they click the Close button on the "frmSearchContact" form:

    Private Sub cmdClose_Click()
    Forms!frmTask!SomeFieldName = me!SomeFieldNameOnfrmSearchContact
    Forms!frmTask!SomeOtherField = me.mylistboxname.column(1)
    Forms!frmTask!AnotherField = me.mylistboxname.column(2)
    Forms!frmTask!FieldXXX = me.mycomboboxname.column(1)
    docmd.close acform, "frmSearchContact"
    End Sub

    or this:
    Private Sub cmdClose_Click()
    Forms!frmTask!SomeFieldName = Forms!frmSearchContact!SomeFieldNameOnfrmSearchCon tact
    Forms!frmTask!SomeOtherField = Forms!frmSearchContact!mylistboxname.column(1)
    Forms!frmTask!AnotherField = Forms!frmSearchContact!mylistboxname.column(2)
    Forms!frmTask!FieldXXX = Forms!frmSearchContact!mycomboboxname.column(1)
    docmd.close acform, "frmSearchContact"
    End Sub

    ******* Make sure also that your listbox or combobox has the correct number of columns and widths for each column (you can set the last number of columns width to 0;0;0 but you DO need to do this to pass the values!) For example, if you have 5 columns in your combobox or listbox rowsource but in the property for the number of columns you only have 3 (instead of 5) or your widths is something like 0";2";1" (instead of: 0";2";1";0";0") - you won't be able to pass the values from columns 4 or 5!) This has caught me so watch out for this when you are passing values from a combobox or listbox!) *******
    Last edited by pkstormy; 05-26-07 at 00:54.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I had to edit the last post (10:07 pm) to correct some syntax. The previous post shows you how to pass values FROM the Search Contact Form "frmSearchContact" TO the frmTask when you click the close button on the frmSearchContact but you can use the same concept to pass values TO the Search Contact form when they click a value in a listbox ON the frmTask:

    On the frmTask for when they click an item in the listbox named: ListBoxName (Note that I use the AfterUpdate verse the OnClick event - this assures that they have ACTUALLY clicked on a value in the listbox!)

    Private Sub ListboxName_AfterUpdate() 'You can also use the OnDoubleClick event here
    docmd.openform "frmSearchContact"
    Forms![frmSearchContact]!SomeFieldName = me!SomeFieldNameOnfrmTask 'Pass some other field value on the frmTask to Search Contact form
    Forms![frmSearchContact]!SomeOtherField = me!listboxname 'Pass first column value in the listbox
    Forms![frmSearchContact]!SomeOtherField = me.listboxname.column(1) 'Pass 2nd column (i.e. column 1) in the listbox
    Forms![frmSearchContact]!AnotherField = me.listboxname.column(2) 'Pass 3rd column (i.e. column 2) in the listbox
    Forms![frmSearchContact]!FieldXXX = me.mycomboboxname.column(1) 'Pass some other combobox column value on the frmTask to Search Contact form
    end Sub

    Note the above example also copies other field values on the frmTask form to the frmSearchContact form.

    Read the note above though (previous post) on listbox columns and widths!!!
    Last edited by pkstormy; 05-26-07 at 00:54.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Sorry again....re-edited both previous posts at 10:30 pm. I had the wrong name for the form.
    Last edited by pkstormy; 05-26-07 at 00:28.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Search form alphabetically

    Bryan,

    You might find this attachment useful.
    Attached Files Attached Files
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by pkstormy
    I had to edit the last post (10:07 pm) to correct some syntax. The previous post shows you how to pass values FROM the Search Contact Form "frmSearchContact" TO the frmTask when you click the close button on the frmSearchContact but you can use the same concept to pass values TO the Search Contact form when they click a value in a listbox ON the frmTask:

    On the frmTask for when they click an item in the listbox named: ListBoxName (Note that I use the AfterUpdate verse the OnClick event - this assures that they have ACTUALLY clicked on a value in the listbox!)

    Private Sub ListboxName_AfterUpdate() 'You can also use the OnDoubleClick event here
    docmd.openform "frmSearchContact"
    Forms![frmSearchContact]!SomeFieldName = me!SomeFieldNameOnfrmTask 'Pass some other field value on the frmTask to Search Contact form
    Forms![frmSearchContact]!SomeOtherField = me!listboxname 'Pass first column value in the listbox
    Forms![frmSearchContact]!SomeOtherField = me.listboxname.column(1) 'Pass 2nd column (i.e. column 1) in the listbox
    Forms![frmSearchContact]!AnotherField = me.listboxname.column(2) 'Pass 3rd column (i.e. column 2) in the listbox
    Forms![frmSearchContact]!FieldXXX = me.mycomboboxname.column(1) 'Pass some other combobox column value on the frmTask to Search Contact form
    end Sub

    Note the above example also copies other field values on the frmTask form to the frmSearchContact form.

    Read the note above though (previous post) on listbox columns and widths!!!
    Paul,

    Both forms have 4 columns,

    frmTask goes : 0";1";1";1"
    frmSearchContact goes : 0";1.5";1.5";1.5" (only to display it better)

    I tried to put in values for what you suggested, got an error but am going to try to figure it out on my own for a while and if I can't, I'll probably repost for help.

    Here's the updated DB though.

    Thanks,

    B
    Attached Files Attached Files

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Calendar Example

    Bryan,

    Here's another snippet which you might find useful. It shows you how to have 1 (and only need 1) popup calendar form that you can utilize from any other form.
    Attached Files Attached Files
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  13. #13
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by pkstormy
    Bryan,

    Here's another snippet which you might find useful. It shows you how to have 1 (and only need 1) popup calendar form that you can utilize from any other form.

    Yeah, I think that's how it is with mine now. There is only one calendar form which is just called for each seperate date field, then populates that date to that particular field. I think it's some what the same. Code for mine follows:

    Code:
    Private Sub DateClosed_DblClick(Cancel As Integer)
        Call PosCalendar(Me, "Calendar0", "DateClosed")
    End Sub
    
    Private Sub DateOriginated_DblClick(Cancel As Integer)
        Call PosCalendar(Me, "Calendar0", "DateOriginated")
    End Sub
    
    Private Sub From_Deputy_to_the_Garrison_Commander_DblClick(Cancel As Integer)
        Call PosCalendar(Me, "Calendar0", "From_Deputy_to_the_Garrison_Commander")
    End Sub
    
    Private Sub From_Deputy_to_the_GC_Assistant_DblClick(Cancel As Integer)
        Call PosCalendar(Me, "Calendar0", "From_Deputy_to_the_GC_Assistant")
    End Sub
    
    Private Sub From_DHR_AG_DblClick(Cancel As Integer)
        Call PosCalendar(Me, "Calendar0", "From_DHR_AG")
    End Sub
    
    Private Sub From_Garrison_Commander_Assistant_DblClick(Cancel As Integer)
        Call PosCalendar(Me, "Calendar0", "From_Garrison_Commander_Assistant")
    End Sub
    
    Private Sub From_Garrison_Commander_DblClick(Cancel As Integer)
        Call PosCalendar(Me, "Calendar0", "From_Garrison_Commander")
    End Sub
    
    Private Sub From_Garrison_CSM_Assistant_DblClick(Cancel As Integer)
        Call PosCalendar(Me, "Calendar0", "From_Garrison_CSM_Assistant")
    End Sub
    
    Private Sub From_Garrison_CSM_DblClick(Cancel As Integer)
        Call PosCalendar(Me, "Calendar0", "From_Garrison_CSM")
    End Sub
    
    Private Sub From_Garrison_XO_DblClick(Cancel As Integer)
        Call PosCalendar(Me, "Calendar0", "From_Garrison_XO")
    End Sub
    
    Private Sub From_S1_NCOIC_OIC_DblClick(Cancel As Integer)
        Call PosCalendar(Me, "Calendar0", "From_S1_NCOIC_OIC")
    End Sub
    
    Private Sub From_SGS_DblClick(Cancel As Integer)
        Call PosCalendar(Me, "Calendar0", "From_SGS")
    End Sub

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yep...just a different way of doing it. What if you wanted to call this from a different form though (or a subform)? Your code in the calendar only writes to frmTask.
    Last edited by pkstormy; 05-26-07 at 19:23.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  15. #15
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by pkstormy
    Yep...just a different way of doing it. What if you wanted to call this from a different form though (or a subform)? Your code in the calendar only writes to frmTask.
    That code is written on the txtbox fields on the frmTask, not on the calendar form. There is nothing listed on the calendar form for events at all. It's all on the frmTask. So I figure it should be alright if another form with date is created, you could do the same thing, right? Or am I wrong?

    Man...This learning this is rough! No wonder why I HAVE to do classes on programming languages actually attending class!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •