should I use a query or a form?

[raylward]

I have two tables with many column's of data. I successfuly created a query that inner joins the two and asks me the name of the customer that I would like to see packed items for. I can enter part of the customers name and it will search for all of the items that were shipped to that customer and display them on the screen with all of the data from both tables. My problem is that I want to fancy this process up. I want to make a combo box that displays available customer names in a drop down box. This combo box should populate it self from customer names in the database. The same combo box should also have the ability to autocomplete when the user starts typing. After the customer name has been selected, I want a second combo box to display available pack dates for that particular customer. The user can choose a pack date in the combo box and then press enter (or a button) to trigger the query report. So far; I've been able to make a form with an autocomplete combo box that lists each of the customer names in it. I had to use the distinct feature becuase some of the customers have 100's of entries (100's of items). I dont know where to go from here. Any idea's. I was starting to wonder if this should be created all in a form or all in a query ......maybe both. I am new to this.

 

[dhookom]

Always use forms for user interaction. Don't you have a table of unique customers that could be used as the Row Source for the combo box?

There is a small sample database at Rogers Access Library that shows how to create cascading combo boxes.

Duane
Hook'D on Access
MS Access MVP

 

[raylward]

Yes. Customer name is in 1 of the tables. Generating the customer combo box with autocomplete was not an issue for me. I don't know how to make the selected customer trigger a query report based on the selection. And of course there was the little bit about the pack date also.

 

[PHV]

I suggest you read this:

http://r937.com/relational.html

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Raylward,
Did you even search the link I provided to Roger's site? Did the link work? Did you find the sample? Did you understand the sample?

Duane
Hook'D on Access
MS Access MVP

 

[raylward]

I think if you had actually given a full read; you'd see that I already know how to get what I want with a query. I also already know how to use "joins" as I mentioned that I had successfully done this. Most of my question revolves around making an already successfully cutomer populated combo box in a form return the same query report based on the users input from that combo box. Thats where I'm getting lost.

 

[dhookom]

If you have a combo box (cboCustomer) bound to a numeric field and want to open a report where the CustomerID field = the combo box value, use the combo box wizard to create a button to open the report. Then modify the code:

Dim strWhere as String
strWhere = "1=1 "
If not IsNull(Me.cboCustomer) Then
   strWhere = strWhere & " AND CustomerID = " & Me.cboCustomer
End If
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere

Duane
Hook'D on Access
MS Access MVP

 

[raylward]

Not sure what you mean by "bound" my working combo box is called "comboO" and displays unbound in the design view. When I run it, it shows all of the customer names from a-z. (not numerical) as suggested by you. I created a button and am guessing that I would navigate to the "on enter" under the properties of that button and enter similar code as you mentioned. Is that correct?

 

[PHV]

Usually the Click event procedure.

 

[dhookom]

If your combo box has a bound column that is text, try:

CODE
Dim strWhere as String
strWhere = "1=1 "
If not IsNull(Me.cboCustomer) Then
   strWhere = strWhere & " AND CustomerID = """ & Me.cboCustomer & """ "
End If
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere

Duane
Hook'D on Access
MS Access MVP