Calculated listbox in Form not writing data in Table

Question

Hello.

Although I read that it's not good to have calculated fields in a Form, I need to have a couple of them as they need to immediately show results.

I have two IIf expressions in the calculated Fields but I also have a normal Query that should give me a result.

The problem that I have is that although my listbox can only present one result, that result is not automatically selected so it is written to the table. If I click on it, it works, but as I need this to be simple for users, I would like to have automatically this result selected and written.

Could you guide me to what I should different?

Answer 1

I'm afraid I'm finding hard to follow what your setup is and what your trying to accomplish.  Could you provide us with a sample of your database, or screenshots to enable us to better understand.

---

Daniel Pineault, 2010-2017 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 2

Hi, I'm not sure I follow either but to auto select a list box, you could try something like: Me.ListboxName.Selected(0) = True Hope it helps...

Answer 3

So here is my form. My Total Based on Analysis Info is a Listbox with a calculated row source which only has one result.

The problem is, when I submit this in the Table I get no value:

I do get a value but only when I click on the result in the listbox in the form before submitting the form.

I tried Me.ListboxName.Selected(0) = True but this doesn't work.

Many thanks.

Answer 4

I tried Me.ListboxName.Selected(0) = True but this doesn't work.

Many thanks.

How about?

Me.ListboxName = Me.ListboxName.ItemData(0)

Also, did you make sure to use the actual name of your listbox?

Answer 5

Hello again.

Tried Me.ListboxName = Me.ListboxName.ItemData(0) (with the right listbox name) and it still doesn't work.

I can see that already when adding data to form because it doesn't turn black, as it would be selected, whilst the Total (which works but because it's a simple calculated field, not a listbox calculation) is always black.

Is it maybe the wrong Sub to which I am putting this code?

Answer 6

Which "Sub" were you using? Try the first one again (and then the last one) but add the following line as well:

Me.Recalc

Hope it helps...

Answer 7

If you can compute the SQL statement for the list box's RowSource property, rather than using a list box, why not simply establish a recordset on the basis of the SQL statement in code and assign the value from the recordset's one and only row to a bound text box control?

Having said that, the fact that you can compute the value in this way removes the need for a bound control, and the consequent redundancy of storing the computed value in a column in the table.  You can compute the value at runtime either in an unbound control in the form or a computed column in the form's RecordSource query.  The simplest way would be to write a little function into which the values which form the basis of the computation can be passed as arguments, with the result of the computation as the function's return value.  The function can then be called, either in the query or as the ControlSource property of an unbound text box.

---

Ken Sheridan, Stafford, England

Answer 8

I used AfterUpdate for this particular listbox.

Should I use it any of the Events of the Form itself and not bound to this listbox?

Best regards.

Answer 9

I used AfterUpdate for this particular listbox.

Should I use it any of the Events of the Form itself and not bound to this listbox?

Best regards.

Not sure what you're doing but try using the Current event of the form too (or in place of it).

Hope it helps...