Solved: Pushing a value into a combo box?

[andrew93]

Hi All

I'm struggling with a set of forms in Access and am pulling my hair out because I'm not sure what I'm doing wrong (plus I'm relatively new to VBA). A quick search of this forum didn't yield too much, hence the question (apologies if it has been asked before).

What is the VBA syntax for populating a combo box on an Access form? I have a value stored in a tmp string variable and I've tried various methods, without success. I've tried things like :

Forms![frmInvoices]![Entity_ID_Link] = tmp

where the Entity_ID_Link is the name of the combo box and tmp has the value I want to push into the combo box, but it doesn't work (even though I have used similar methods with other forms that weren't using combo boxes)

What am I doing wrong? Or, what is the correct syntax?

TIA, Andrew

P.S. Late edit - I forgot to mention there are 3 'events' attached to the combo box in question - these are the after update (Dlookup and set the invoice number in another field), on not in list (close the form and open frmMaintainEntities) and on got focus (do a requery of the combo box).

[xCav8r]

Hi, Andrew, :hi:

On a form's class module in any procedure, if you get the values from a table...


Me.ComboBoxName.RowSourceType = "Table/Query"
Me.ComboBoxName.RowSource = "tblMyRowSource"
if you get the values from an exsiting query...

Me.ComboBoxName.RowSourceType = "Table/Query"
Me.ComboBoxName.RowSource = "qryMyRowSource"
or...

Me.ComboBoxName.RowSourceType = "Table/Query"
Me.ComboBoxName.RowSource = "SELECT * FROM tblMyRowSource"
if you get the values from a value list...

Me.ComboBoxName.RowSourceType = "Table/Query"
Me.ComboBoxName.RowSource = "value1;value2;value3;value4;etc"

For more information, search for RowSource in VBA.

HTH! :whistle:

[andrew93]

Thanks for the speedy reply but I am still none the wiser even after reading the help file. This is the code I have so far, so how do I get the value stored in tmp to appear in the combo box when I open the form frmInvoices? The value tmp is already in the list that the combo box is based on.

This is my code so far :
Private Sub Command36_Click()

On Error GoTo Err_Command36_Click

Dim tmp As String

tmp = Me.Entity_Name 'this is the value from frmMaintainEntities
'that I want to push into the next form

DoCmd.Close 'closes frmMaintainEntities

DoCmd.OpenForm "frmInvoices", acNormal

MsgBox tmp 'used purely for debugging - works ok up to here

Forms![frmInvoices]![Entity_ID_Link].RowSourceType = "Value List"
Forms![frmInvoices]![Entity_ID_Link].RowSource = tmp

'Old code that didn't work : Forms![frmInvoices]![Entity_ID_Link] = tmp

Err_Command36_Click:
MsgBox "failed" 'used purely for debugging
Exit Sub

End Sub


What change do I need to make?

TIA, Andrew

P.S. Late edit : when I say 'appear in the combo box' I mean that it I want it showing as selected in the combo box. The new item is already in the combo box list but it is not highlighted as selected.

[xCav8r]

[Original Post deleted after getting caught up ;)]

[andrew93]

Hi

I think my late edit crossed with your post. The data is already stored in a table and it appears in the combo box if I click on the arrow, but what I am trying to do is have the item appear as selected or highlighted in the combo box when the form opens.

Specifically : If the user has set up a new customer in the form frmMaintainEntities and clicks on "create a new invoice for this customer", I would like that customer to appear in the combo box on the invoice form called frmInvoices.

Let me know if I haven't described this well enough.

Andrew

P.S. Very late edit - I am such a dunce. It would help if I had picked up the correct key from the first form and then applied that to the combo box. My original code works if I use the correct field. D'Oh!

[xCav8r]

Now that I understand better what you want to do, I will recommend the method that I would favor for doing this. Pass the value to select to the form where the combo box resides via the OpenArgs argument of the OpenForm method. I'd use the OnOpen (or OnLoad) event of the opened form to set the value in the combo box. I prefer this way because it makes the form housing the combo box more versatile in that it could accept values to select from any object, rather than just one form.

Anyway, I'm glad that you got it working! :thumb

PS. Don't forget to mark your thread solved by using the thread tools link at the top of this thread. :)

[andrew93]

Thanks for pointing me in the right direction. By using the correct key from the first form (and not the name field) I could get the combo box to display the value using this code: Private Sub Command36_Click()

On Error GoTo Err_Command36_Click

Dim tmp As String

tmp = Me.Entity_ID
DoCmd.Close
DoCmd.OpenForm "frmInvoices", acNormal
Forms![frmInvoices]![Entity_ID_Link] = tmp

Err_Command36_Click:
Exit Sub

End Sub

I will look into the OpenArgs - but this isn't the only method by which this form is opened.

Cheers, Andrew