Best choice for combobox record filter?

[sparky961]

Greetings all...

I've been given the task of creating an Access database form - probably something that's seen a lot on this forum. I have considerable programming experience with other languages - C, Pascal, Java, etc, etc... but little with VB and Access.

What I'm trying to accomplish is to have a dropdown/combobox at the top of a form where a user can select a customer. Then, there will be a list of records in a list box or similarly-appearing controls that shows a list of current orders for that customer as well as the line items and details for each order.

To complicate things further, I'll eventually need a button beside each row and possibly a checkbox. The button will print labels related to that line, and possibly do other things through the use of a menu. The checkbox would simply reflect the state of a field in the record.

The orders and line detail are in two separate tables, and the customers are in yet another table. Each table is related through IDs, as usual. I have a query that will bring up all the information that I need, I just need to know how to populate and filter the list using a combobox.

Thank you all in advance for your time!
-Sparky

 

[DevastatioN]

If you want to go with the listbox idea, you can use code that looks like the following in the AfterUpdate procedure of the Combobox:

Dim strSQL as string

strSQL = "SELECT blah blah blah WHERE PrimaryKey = " & cboSearch
me.lstBox.RecordSource = strSQL
me.lstBox.Requery

The strSQL needs to be the exact same as the SQL statement used when creating the listbox, except with the WHERE attached to the end.

For example, if the listbox is usually:

"Select table.Field1, table.Field2 FROM table1"

Then strSQL should be:

"Select table.Field1, table.Field2 FROM table1 WHERE table.Field1 =" & cboSearch

Now for the buttons and the checkbox beside each row, this is not really doable for the listbox as far as I know except with a bunch of coding. I would make the listbox double click event open another form with that information only which can be edited.

You can have a button for printing the line of what is selected, so a single button, but it always looks up which line is currently selected in the listbox through me.lstBox.

 

[sparky961]

Dev,

From one Canuck to another, thanks for the help!

You've gotten me about 75% of the way, and I'm hoping you or someone else can give me just a little bit more of a push here.

The query is a bit more complicated than I'm used to working with. The list box update is happening after it changes, but I'm having trouble getting it to filter correctly.

Here's what I was trying:

Private Sub cboCustomer_AfterUpdate()
    Dim strSQL As String
    
    strSQL = "SELECT SalesOrder.PONumber, SalesOrder.DueDate, SalesOrderLineDetail.CustomField6, SalesOrderLineDetail.Desc, SalesOrderLineDetail.Quantity FROM SalesOrderLineDetail INNER JOIN (SalesOrder INNER JOIN Customer ON SalesOrder.CustomerRef_ListID = Customer.ListID) ON SalesOrderLineDetail.IDKEY = SalesOrder.TxnID WHERE (((SalesOrder.IsManuallyClosed)=0) AND ((SalesOrder.IsFullyInvoiced)=0)) AND SalesOrder.CustomerRef_ListID = " & cboCustomer
    Me.lstOrders.RowSource = strSQL
    Me.lstOrders.Requery
    
End Sub

The result is an empty list box after changing the combo. Likely due to the query being incorrect.

This is what the list box uses to initialize, and it returns all of the records - just not filtered on the customerID...

SELECT SalesOrder.PONumber, SalesOrder.DueDate, SalesOrderLineDetail.CustomField6, SalesOrderLineDetail.Desc, SalesOrderLineDetail.Quantity
FROM SalesOrderLineDetail INNER JOIN (SalesOrder INNER JOIN Customer ON SalesOrder.CustomerRef_ListID = Customer.ListID) ON SalesOrderLineDetail.IDKEY = SalesOrder.TxnID
WHERE (((SalesOrder.IsManuallyClosed)=0) AND ((SalesOrder.IsFullyInvoiced)=0));

Thanks for any additional help you can give!

-Sparky

 

[DevastatioN]

No problem for the help, glad I got you 75% there... let's see if I can get you 100% there.

The combobox should have the primary key field for the Customer hidden as the main bound column of the combobox. I believe it's Customer.ListID, meaning your combobox should be:

SELECT Customer.ListID, Customer.CustomerName FROM Customer

Bound Column 1, Number of Columns 2, Column Widths 0";1" etc.

This is so we can directly link the number from the combobox to the query (SQL), not the names.

Now because the CustomerID is actually in your SalesOrder table as SalesOrder.CustomerRef_ListID, and you don't need any further information from the Customers table I'm assuming, you should be able to use the following SQL:

strSQL = "SELECT SalesOrder.PONumber, SalesOrder.DueDate, SalesOrder.CustomerRef_ListID, SalesOrderLineDetail.CustomField6, SalesOrderLineDetail.Desc, SalesOrderLineDetail.Quantity FROM SalesOrderLineDetail INNER JOIN SalesOrder ON SalesOrderLineDetail.IDKEY = SalesOrder.TxnID WHERE (((SalesOrder.IsManuallyClosed)=0) AND ((SalesOrder.IsFullyInvoiced)=0)) AND SalesOrder.CustomerRef_ListID = " & cboCustomer

Notice how I added the SalesOrder.CustomerRef_ListID as well to the SQL, I think this may be needed but am unsure. Also I am assuming that CustomerRef_ListID is a number, if it's a string then you will need:

AND SalesOrder.CustomerRef_ListID = '" & cboCustomer & "'"

Hopefully this helps a bit, let me know!

 

[Simon_MT]

Another way of doing this is to put a Clients Search Dialogue Box before the Clients Form and then filtering the Clients on the information within the Search Form.

Simon

 

[DevastatioN]

To do it Simon's way, it'd prolly be best to use a bound form as he suggests and a filter. (I was assuming unbound form, and listbox as in the initial criteria).

Also with Simon's way, instead of a lot of code you could even just use a subform to "list" all of the data, and the continuous subform would be able to have your checkbox and the button attached to each row.

 

[sparky961]

Well, I have it working the way that was suggested by Dev. Again, much thanks. It's been a long time since I've done any VB programming, and it's the little things that are tripping me up.

I've been given a screenshot of another person's Access database, and asked to replicate the functionality.... nice, eh?

In order to make everything work as required, I might need to do it as Simon has suggested so that I can add buttons and other controls to each row. However, I personally like the idea of having a context menu for each row.

I'm sure I'll have a few more questions during this project. Thanks for all your help so far.

-Sparky

 

[DevastatioN]

Np, feel free to post any questions on these forums, they're an excellent source of knowledge.

Sorry I couldn't get it working for you with the listbox. If I get a chance tomorrow I can try and do up an example file for you to download.

 

[Simon_MT]

Here's a screen shot of the Clients Search:

http://www.designedlogic.com/Clients.html

Simon

 

[sparky961]

Both methods seem to work well if you don't consider the checkbox and button for each record. With that taken into consideration, I've tried Simon's method and it works quite well.

The only thing I'm stuck on yet is how to have the button click event know which record to print.

Any suggestions here?

 

[DevastatioN]

If you use a continuous form for the subform. And place a button on that form that refers to me.PrimaryKey, it will retrieve the value of the primary key of that current record, for example:

Continuous Form:

Record 1 PrimaryKey="1" Button (Returns 1)

Record 2 PrimaryKey="2" Button (Returns 2)

etc...


So if you have the report already made, you can use a WHERE condition on the button from the continuous subform, such as:

stDocName = "rptYearlyTimeReport"
DoCmd.OpenReport stDocName, acPreview,, "[PrimarKey] = " & me.primarykey

Hope this helps.

 

[sparky961]

Indeed, that confirms that I've been seeing.

I'm 99% sure that the screenshot I have here did it with a continuous form. Not being "one" with Access, it didn't occur to me that this was possible. As it turns out, it's actually quite simple.

I now have the application to a point where it can filter sales order lines based on two combo boxes, and there's a print button as well as a checkbox on each of those lines. Pressing the print button opens up another form prefilled with the information from that sales order line (but with text boxes that allow you to modify the information), as it will print out on a label, then pressing the "print" button on that form finally sends it off to be printed at the label printer (Brother QL-550 using bPAC SDK).

It's quite slick. Even has a text input box for the number of labels to print.

A big "THANKS!" to everyone who's helped me to get this project off the ground!

-Sparky