Populating a list box with field from query

[attman]

I have a Pupils database where I have a form with Pupil name and ID.

I have a query with a calculated field and the ID and this works great.

I can display the associated calc on the form using a sub-form (I link the ID in the calc to the ID on the form) but I dont know how to get this value to display in a listbox.

Can someone point me on how to do this?

Thanks guys...

 

[BlueIshDan]

Depends on your top form's design tbh.

Does it use a record navigator or is it a static form that manages its data passing manually with code?

 

[vbaInet]

Create a query, perform the same calculation in the query and build the listbox based on the query (or change the Row Source property of the listbox to the query). This assuming that you mean a calculated value and not a MS Access Calculated Field.

 

[attman]

Depends on your top form's design tbh.

Does it use a record navigator or is it a static form that manages its data passing manually with code?

Hi - Thanks for your help. There are buttons on the form to move to the next record if thats what you mean?

 

[attman]

Create a query, perform the same calculation in the query and build the listbox based on the query (or change the Row Source property of the listbox to the query). This assuming that you mean a calculated value and not a MS Access Calculated Field.

If I put the query in the record source I get all calculated fields rather than just the one for that record...

 

[vbaInet]

Ok, it can be synchronised. But why do you need a listbox if you only want to return one value?

 

[attman]

Ok, it can be synchronised. But why do you need a listbox if you only want to return one value?

No reason for the Listbox but the previous posts Ive read seem to suggest its easier doing it with a listbox rather than a textbox.

 

[vbaInet]

Makes sense. The reason why it's mentioned is because you can set the Control Source of a Listbox and it will be in sync with the form.

So you will need to set up your listbox's Row Source to have two fields, one being the ID and the other the Calculated Value. Then set the Control Source of the listbox to the ID on the form. Make the listbox as small in height as your textbox.

There other ways to do this but this seems to be the simplest.

 

[attman]

Makes sense. The reason why it's mentioned is because you can set the Control Source of a Listbox and it will be in sync with the form.

So you will need to set up your listbox's Row Source to have two fields, one being the ID and the other the Calculated Value. Then set the Control Source of the listbox to the ID on the form. Make the listbox as small in height as your textbox.

There other ways to do this but this seems to be the simplest.

I guess Im doing something wrong as in the row source I put the name of the query (which has the 2 fields) and in the control source I can put literally anything and all I get is a list of what the query throws up...

 

[vbaInet]

In the Control Source you select the ID from the dropdown list. Also ensure that the first field in the Row Source is the ID field too.

 

[attman]

Fantastic - Nearly there...

2 things :

1) The entry in my listbox is highlighted (black which I dont want)

and

2) The list box doesnt "clear" when theres not an entry in the query for the ID

 

[vbaInet]

Those are the problems you'll encounter using the listbox method.

I've just made up a db showing you one of many ways... no code required. See attached the file aptly named attman.

Open the form frmRecords and notice the use of a subform.

 

[attman]

Just wanted to let everyone know that in fact I couldnt get text/list box to do what I wanted so I gave up and used subforms.

Thanks anyway for your help.

 

[vbaInet]

... so I gave up and used subforms.

Thanks anyway for your help.

That's the approach in the attachment I uploaded

 

[attman]

That's the approach in the attachment I uploaded

I really appreciate the help thanks. I've learnt a such a lot here, it's just that I already had it working with subforms. Thanks again!

 

[vbaInet]

You're welcome