Combo Box - Populating Other Field - Best Way?

  • Thread starter Thread starter Will
  • Start date Start date
W

Will

I have a Combo box which contains a list of order numbers and invoice
numbers. The bound column is the order number but I want to use the invoice
number in another part of the form as a criteria. At the moment I have an
unbound text box with a Dlookup statement which looks up the invoice number,
I know Dlookups can be quite slow, is there a better way of doing it?

Thanks
 
Will,

Your post suggests inv.nos are one-to-one on order nos? If yes, then you
can add the inv.no as an extra cloumn to your combo (you don't have to
show it, you can hide it by setting the corresponding column width
property to 0), and "read" it from there. For instance, if your combo's
rowsource has only one column right now, and you add a second one for
Invoice No. you can read it as:

Me.MyCombo.Column(1)
(the column index is zero based).

HTH,
Nikos
 
Thanks for your help
Invoice numbers and order numbers are indeed one-to-one
I am trying to use the column reference as criteria in a query of another
combo box but when I enter =Forms!frmCustClaim!InvoiceNo.Column(1), it comes
up as "undefined function in expression".
 
Will,

You're right, it doesn't seem to work in the query criterion; I hadn't
tried it in this context. Maybe a syntax thing? Not sure. Anyway, one
workaround is to add another textbox to the form, make it invisible, set
its data property to Me.InvoiceNo.Column(1), and use the invisible
textbox for filtering the other combo. The other way (my preferred
method) is to set the other combo's rowsource in code from the InvoiceNo
combo's On Change event, like:

Private Sub InvoiceNo_Change()
Me.OtherCombo.RowSource = "SELECT FieldX FROM SomeTable" & _
" WHERE OrderNo = " & Me.InvoiceNo.Column(1)
End Sub

assuming OrderNo is numeric, or:

Me.OtherCombo.RowSource = "SELECT FieldX FROM SomeTable" & _
" WHERE OrderNo = '" & Me.InvoiceNo.Column(1) & "'"

if OrderNo is text.

HTH,
Nikos
 
I got round this by using a textbox & setting the control source to the
relevant column of the combo box, many thanks for your help
 
Back
Top