New to Forms-Display a blank combobox but have one column in it restricted to a combobox?

Hello all,

I have another pressing user form question.

I don't know if this even possible so I will describe what I want and you can tell me if its wishful thinking.

I want to show the end user a blank combo box (say 1-2 rows and 3 columns wide. The 1st column will be for an invoice primary key and be hidden. The 2nd column will be for an invoice number and the third column will be for an amount). When the end user selects the 2nd column (i.e. 1st visible column), they will receive a dropdown containing the invoice numbers for that client. in otherwords a combobox within a column of a combobox. Is this even possible?

If not, how should I go about achieving a similar result?

Originally Posted by Kyle123

tbh I'd say it's not worth the effort - but going on some of your other posts.....

[Wince] You didn't need to say that... XD

Originally Posted by Kyle123

You might have an easier time using something like a listview or flexgrid as a base http://www.vbforums.com/showthread.p...ide-a-Listview, but I'm not at all sure on the availability of these types of controls on most machines

It looks interesting but I can't figure out how to get the downloaded attachments working in VBA. :S And I don't like the idea of involving external systems for something like this (I would rather change my approach).

Originally Posted by Kyle123

If it were me, I'd look at your approach and see if you can find another way - I can't really suggest anything since I've no idea what you're doing

Well it's a form for an accounts department for matching receipt payments against outstanding invoices.

The first page of the multipage has already collected the receipt date, the client name and the total amount paid. This data is validated and the primary key for the client name is matched and used to connect to a database and collect an array of all the unpaid invoices for this customer.

What I was describing in the OP is what I hoped to have on the 2nd page of the multiform. The end user needs to be able to assign amounts against one or more of the outstanding invoices (ideally the invoices would be chosen from a listbox or combobox). When the total of the amount/s assigned against the invoice/s equal the total paid (from the first page of the multiform), then the end user presses a button to "save" the completed breakup (i.e. writes the amounts paid against each outstanding invoice in the database along with the payment date etc).

Which is why I thought of a dropdown list inside an empty combobox (I was thinking of the VBA equivalent of what can be done in Excel with a table and a dropdown box). It seems I will need an alternative approach.

Originally Posted by Kyle123

I find it abhorrent that multiple invoices would share an "Invoice Primary Key"

So do I. And they're not.
I do have a little bit of database background. I am reluctant to rely merely on an invoice number/code (which is assigned by the end users) as the primary key. So instead each new invoice created silently generates a unique primary key for that invoice. In the combobox I envisioned the end user would only select the invoices by their invoice numbers. But in the background, I am actually linking to the primary key for that invoice, not the invoice number.

You want the user to pick an invoice no from a blank combobox then have all the clients invoices displayed?

Why aren't they picking the client from a non-blank combobox?

As for a combobox within a combobox, I can't see that happening, why not have a separate listbox that lists the invoice numbers based on what the user has selected in the combobox?

Hi Norie

Originally Posted by Norie

You want the user to pick an invoice no from a blank combobox then have all the clients invoices displayed?

Almost. What I had in mind was this: A blank combo box (2 visible columns, say 1 row) would be displayed on the 2nd page of a multiform. If the user clicks in the first visible column in the combobox, a dropdown of current outstanding invoices would appear (similar to a dropdown list in Excel when you click in the entry cell). The user will then select an invoice. User tabs to the 2nd visible column and enters a currency. Upon selecting this cell, the combobox automatically expands to add another row so that the user can then assign a 2nd invoice and amount. And so on. (Yes the last bits weren't described in the OP, I was going to break it down into individual problems).

Originally Posted by Norie

Why aren't they picking the client from a non-blank combobox?

They will be. The code for picking the client is on the first page of the multiform and this will be a combobox with a list of current debtors (code is working for this).

Originally Posted by Norie

As for a combobox within a combobox, I can't see that happening, why not have a separate listbox that lists the invoice numbers based on what the user has selected in the combobox?

Well I could but then how would the end user breakup the total against individual invoices?

Originally Posted by Kyle123

Just use a combobox with a textbox alongside and add as many as you need dynamically underneath - you'll need classes to handle the events, but it should be fairly trivial.

A combobox with a textbox alongside = Check.

Add as many textboxes as I need dynamically underneath = I'm not sure I will be able to work that out. (I am new to forms as per thread title)

Classes to handle events but should be fairly trivial = Help!

I could build a combobox on 2nd page of multiform change. The combobox will display ALL of the outstanding invoices for that client (rather than select by a dropdown list). This will look very messy and, in some cases, could end up going off the edge of the multiform due to the size of the combobox after populated

And it isn't what I was after (which was an auto expanding combobox with a dropdown in the first visible column).

Thanks Kyle. It wasn't quite what I had in mind - however this may work out better

I will play round with it and get back to you in a week or so.

This is great Kyle! +1

I have a few questions if you don't mind.

1. I want the "fancy dropdown" to be visible on the form initialize. And do away with Command Button 1 altogether. Is this possible? (Don't laugh - I've never written a class in my life!)
2. How do I return .value of the txtbox from the last row in the "fancy dropdown"? (I plan to use this to check that the txtbox is greater than 0 before allowing the user to add new row)
3. Last but most important - I need a way of retreiving the data entered by the user. How do I create an array of the values of all the combo boxes & text boxes currently in the "fancy dropdown")

Thanks Kyle

It's coming together nicely. I have uploaded where I'm up to. I want to alter the Comboboxes so they allow for the input of a 2D array but only the 2nd array data visible (width = "0;" ) however I can't work out how to do this in the Class modules.

No problem, can you follow how it works?

You just need to adjust the properties of the combo-box when it is created:

    With o_cb
        .Top = lRowTop
        .Left = L_ROW_LEFT
        .Width = L_CB_WIDTH
        .Height = L_ROW_HEIGHT
        .ColumnCount = 2 '2 Columns
        .ColumnWidths = "0pt" 'Hide the first one
    End With

Your other problem then is that your label has a "$" prefix which is throwing an error since you can't subtract from a string.

You also need to call oListView.Dispose in your submit routine (or anywhere you close the form) to prevent memory leaks

When I changed both, everything worked as expected

Thanks again Kyle.

• No I can't say that I completely follow how the Class modules work. (Note to self - I must learn these soon).
• Combo box properties: (Slaps forehead) Of course. I should have thought of trying that! Thanks!
• $ in Label: I'm only using the $ as a place holder on the form in design mode. (I will be making sure that the final version validates/cleans the curTestAmount before loading the form).
• Point noted re oListView.dispose

I think I can probably take it from here so I will mark this as solved. If I do have any further questions, I will reopen the thread and/or PM you.

Added code to a tool that required it - and it works great!