How to Fill A ComboBox from a String Variable.

[Ed S.]

I have a form that is used to maintain 13 periods of data for a given year. Because data is often the same for each period, it is efficient to have a command button that allows the end-user to click and populate all 13 periods with the value in a single data element.

For example, if I place the string "XMe" in Period 1 data field, and click the "FILL DOWN" Command, all 13 periods are updated with the "XMe" string.

The difficulty is all 13 periods are combo boxes with a list. Each period's combo box is Limit to List = NO. Bound column =1.

The code crashes when the following is executed:

Private Sub cmdFillDown_Click()
Dim sFillValue As String

Me![Per01].SetFocus
sFillValue = Me![Per01].Text

Me![Per02].SetFocus
Me![Per02].Text = sFillValue
.
.
.
Me![Per13].SetFocus
Me![Per13].Text = sFillValue


To recap: What must I do to allow a ComboBox to be filled with sFillValue?

MS Access 97 version.

 

[SydneyGeek]

I don't have Access in fornt of me but the procedure goes something like this:

Define a recordset (the one on which the Combo is based)
Open the recordset
Edit the recordset
Add a new record
Update the recordset
Close the recordset
Requery the combo

You'll have to put this inside a loop to run through all the combos...

Places to get the code include The Access Web (Dev Ashish) or The Access Archon (Helen Feddema). Google search should do the trick.

EDIT:
Thinking about it, do the combos share a data source (ie from same table?)
If so, you only have to open one recordset. At the Edit, Add, Update stage you can loop through creating the 13 records, then requery the combos.

HTH
Denis

 

[bat17]

What line of code does it crash on and with what message?
If all of the field are in the form and bound to seperate fields then you should be able to just put in the values.
What you have to watch with combo boxes though is that the field that they store is often not what you see, but the joining field, and this is what you need to set the value to. You should be able to set a fields value with out setting the focus to it first.

HTH

Peter

 

[Ed S.]

Thanks you for your replys.

The error is run-time 438. Object doesn't support this property or method.

Me![FPHC01].SetFocus is line that crashes.

Probably more to this than meets the eye. The row source type is qry/table. It is bound as mentioned earlier.

I am reasoning that if a combo box value is not limited to the list, the value can be maintained/updated.

Please use code examples, I am somewhat new to MS Acess, so general concepts have less value. In the mean time, I am researching web sites for an applicable code example.

All help still greatly appreciated.


In meantime, I am doing internet research

 

[Dugantrain]

As far as the line that is crashing, try:

Me.Per01.SetFocus

assuming that Per01 is the actual name of your combo box and not just the field which it is bound to.
Also, in VBA, you may omit the '.text' as in 'Me.Per01.text' (Me.Per01 will suffice). Other than that, you will need to write some code which will connect to the Back-End, Insert the new data into the back-end, and then Refresh the form so that this new record shows up in all of the combo boxes.

 

[Ed S.]

The problem was the .text property - needed .value property.

Here is the lines of code that worked.... much simpler than expected:


Private Sub cmdFillDown_Click()
Dim sFillValue As String

sFillValue = Me![Per01].Value

Me![Per02].Value = sFillValue
.
.
.
Me![Per13].Value = sFillValue

End Sub