Set two column combo box value using bound column value from openargs (1 Viewer)

[Bettany]

Private Sub Form_Load()

Dim strOpenArgs As String
strOpenArgs = Me.OpenArgs

Dim argVars() As String
     
argVars = Split(Me.OpenArgs, ",")
     
Me.CboPerson.Value = CInt(argVars(0))

End Sub

Hi, the code above simply does nothing. No errors, no change in the desired form. I've confirmed the openarg value is correct. I guess this is more of a general question about how to change the value displayed in a combo box using VBA when you have a desired bound column (primary key) value. Thanks.

 

[GaP42]

Shouldn't you be setting or using the openarg value to the PersonID query/fieldvalue (as a filter when opening the form) - not to the combo box value? The combo box, if bound to PersonID will then display the person name or other attribute as needed?

 

[Bettany]

Shouldn't you be setting or using the openarg value to the PersonID query/fieldvalue (as a filter when opening the form) - not to the combo box value? The combo box, if bound to PersonID will then display the person name or other attribute as needed?

OpenArgs is just a way to send a value from one form to another. It doesn't have any explicit use case. In my case, I'm using OpenArgs to send multiple values.

 

[GaP42]

So what values are you sending and why if it is not to meet some requirement? Your code will do not do anything if you do not utilize the values sent. The combo is bound - it has an underlying query that allows the combo to display user readable values - it is based on 2 columns (the Id and a descriptor - values from a record/query - it is not set by assigning a value from a parsing of an openarg string

 

[Uncle Gizmo]

Is the value you want to set the combo box to, actually in the combo box? Check by passing a string directly...

Do you need to refresh the form?

 

[theDBguy]

Hi. Can you show us the Row Source for your combobox and the string you're sending via OpenArgs? This will help us see the relationship between the two.

 

[Bettany]

So what values are you sending and why if it is not to meet some requirement? Your code will do not do anything if you do not utilize the values sent. The combo is bound - it has an underlying query that allows the combo to display user readable values - it is based on 2 columns (the Id and a descriptor - values from a record/query - it is not set by assigning a value from a parsing of an openarg string

So how do I programmatically control what I want the combo box to display if not setting its value property?

Even doing writing this code as a test has no effect on the combo box. I am trying to troubleshoot by removing OpenArgs from the equation entirely. What code could I write to hardcode the bound column to a value of 1? Thanks.

Me.CboCompany.Value = 1
Me.CboCompany.Requery
Me.Refresh

Should the code above change the displayed value of the text box portion of the combo box?

 

[Bettany]

Is the value you want to set the combo box to, actually in the combo box? Check by passing a string directly...

Do you need to refresh the form?

Yes, the value is there.

I want to look up one of the primary ids assigned from the row source and hard code that as the combo box value, removing the openargs aspect from the troubleshooting process completely. Would that merely be:

Me.CboCompany.Value = 1
Me.CboCompany.Requery

 

[Gasman]

No requery should be needed?
Try a simple new form with just that code.

This is from another member on another site. I just hardcoded any value in the form load of the persons form.

 

[Bettany]

Let's say there are 10 items in the combo box's row source. And one item has a bound column value of 1. What code would I use to set the combo box value to the bound column value of 1, resulting in the value of column 2 being displayed in the combo box?

 

[Uncle Gizmo]

I think you have said that you are getting a problem when you use open args? Otherwise it's working okay correct?

So I'm wondering if open args is turning the 1 into a string whereas the combo box requires a number 1, as an integer or some sort of number value not a string?

 

[Bettany]

I think you have said that you are getting a problem when you use open args? Otherwise it's working okay correct?

So I'm wondering if open args is turning the 1 into a string whereas the combo box requires a number 1, as an integer or some sort of number value not a string?

I suspect that my problem results from not knowing how to manipulate, via VBA, what is seen in the text box portion of the combo box. The openargs aspect is really more of a distraction at this point, as hardcording the values and ignoring the openargs code entirely still has the same effect.

 

[Bettany]

No requery should be needed?
Try a simple new form with just that code.

This is from another member on another site. I just hardcoded any value in the form load of the persons form.

You are kind for taking the time to include this, but company policy prevents me from opening such things.

 

[Gasman]

So just create a new form.
I thought the code you have should work as is?
Possibly your form is corrupt, but it would not take much effort to just create a form with just a combo?

 

[Uncle Gizmo]

It should be possible to create a working example and post it in the forum for you to see. I'll do it later when I'm at my PC. Someone else might do it before I get there!

 

[theDBguy]

Let's say there are 10 items in the combo box's row source. And one item has a bound column value of 1. What code would I use to set the combo box value to the bound column value of 1, resulting in the value of column 2 being displayed in the combo box?

Not sure your level of experience is, but that description is a little vague. The Value property stores the value from the Bound Column of the Row Source. What's displayed is dictated by the first non-hidden column controlled by the values in the Column Widths property. So, if the bound column is set where the value 1 shows up, then you assign 1 to the Value property to assign it to the combo. And, if the value 2 is in the first visible column where the bound column has the value 1, then the value 2 should show up in the combo after you assign the value 1 to the Value property.

Again, perhaps posting the Row Source for your combo might help clarify some things for us.

 

[Gasman]

You are kind for taking the time to include this, but company policy prevents me from opening such things.

OK, here is the code from the form

Private Sub Form_Load()
Me.Combo90 = 5
End Sub

and the combo source

SELECT DISTINCT tblDataPersons.Persons_PK, tblDataPersons.HholdID_FK, tblDataPersons.FullName
FROM tblDataPersons
ORDER BY tblDataPersons.FullName;

Properties

 

[Pat Hartman]

So how do I programmatically control what I want the combo box to display if not setting its value property?

1. NEVER set a bound control to a value in the form's load event or any other event for that matter except on Dirty or BeforeInsert. You do not want to dirty a record before the user does.
2. I think you are asking how to filter the RowSource of the combo. If so,
a. copy the value from the openargs to a hidden text control on the form.
b. Change the combo's RowSource query to include a where clause that references the hidden control
c. Requery the combo - Me.mycombo.Requery

 

[GaP42]

Bettany - the code you provided for loading the form receives an openarg string which includes as the first part of the string (up to the ",") the PK value for a person (we don't know what other strings are in the openarg). As it is used in the loading of the form, are you opening the form to show the records related to that person (ie you want to filter the records displayed, but for whatever reason are not using the WHERE condition available in DoCmd Openform), or if opening to a new record are you wanting to set a FK for Person in this record?)
As Pat indicates, don't set the combo bound control to a value. A bound control is there to show an existing value in a record or that you wish to select/assign to the record, through the use of the rowsource.
If the general intent is to display the records related to that person FK, displaying the name of the person in the combo and allow users the ability to select a different person then:
You need to apply a filter in the load event such as (untested):

Dim strOpenArgs As String
Dim StrWhere as String
Dim argVars() As String


strOpenArgs = Me.OpenArgs
argVars = Split(Me.OpenArgs, ",")
strWhere = "Person_FK = " & CInt(argVars(0))


Me.Filter = StrWhere
Me.FilterOn = True

You can then apply the other values passed in the strOpenArgs to the record as needed. The combo will show the persons name because that is the person_FK in the record that is the rowsource for the combo.
Note however that this does not handle the situation where the form is opened to a new record, but this can be handled by testing if the stropenargs is null.

 

[The_Doc_Man]

You earlier made it clear that the form itself was bound to a record for which there exists a prime key. (Which is usually a good thing!) The question I have is how you set up the combo. There are three common combo-box setups.

1. The combo is bound, not necessarily directly based on the table driving the main form, but something that it references is also used on your form as a key field. I.e. there is at least potentially a relationship between the .RowSource of the combo and the .RecordSource of the main form. The combo uses some (Wizard-generated) code to select a record on that form and navigate to it. This would probably include some combo-box click event that diddles around with Me.RecordsetClone and some recordset operations.

2. The combo is bound, not necessarily having anything to do with any of the fields on the main form. This kind of combo merely makes the values chosen from the combo available to code in that form. I.e. the code behind the form will refer to Me.ComboX(Column 1) (or column 0) and will perform logic or math on it.

3. The combo is bound and the defining option stores the combo selection from the combo box DIRECTLY into a field on the form. That is, at least one column of the combo is directly bound to the underlying record regardless of the combo's .RowSource value.

If choice #3 was used, then whatever you do in the Form_Load event will make no difference because the Form_Current event will re-make the selection for you when the current record synchronizes with the form. Choices #1 and #2 don't have this behavior. For case #3, the prior contents of the combo have NO effect on what it displays after a _Current event. Unless, of course, you have code in the _Current event to make a change.

If you want to force the issue AND the form is only used ONCE (for a given record) and then closed, it would make sense to have code in the Form_Open routine to look at your OpenArgs property for the form in order to set a filter before you allow the code to step to the _Current event. Note also that if you are looking at a number, OpenArgs IS passed as a string. Therefore you might need (as suggested above by GaP42 above) to use either CInt(arglist(0)) or NUM( arglist(0) ) because you aren't passing a number.