Query result to populate textbox (1 Viewer)

[JamesN]

Hi,

I currently have a form which is used to return almost 100 columns from a table for a user to review. By selecting a reference from a combo box, it then returns the relevant column entries from the table below. At the moment I am using a dlookup to return these, however, it is too slow at the moment.

I did try storing the columns in the main combo box eg.... Me.flm.Value = Me.reference.Column(1). However, the issue I was having was the limit of columns which could be stored in the reference combo box.

Is there any way I can store the result of the query for the specific column in the text boxes or a better way to speed this up?

Any suggestions appreciated

 

[Minty]

It sounds as if you should be changing the complete record source for the form based on the selection.
You could do this easily by using a subform set to a datasheet and using a stored query as the recordsource. Simply store the query name as a column in your combo box, then refer to it in the after update code to set the record source.

 

[JamesN]

Thanks for the suggestion, I will give that a go. Would it be possible to reference each value returned in sub form via VBA as I need these individual values to be added to another table and checked agains the values entered on the main form by the user?

 

[Minty]

You can - It sounds like you could use a query to possibly do that for you.

 

[JamesN]

I have created another form and added the sub form in here, this form is called LookupForm and the sub form is called VarianceLookupForm.

How would I reference this from another form called VarianceScorecard, I basically need to confirm if the values entered in the VarianceScorecard form match the ones held in the subreport. Having a bit of bother referencing it correctly

 

[Minty]

I would bookmark this page http://access.mvps.org/access/forms/frm0031.htm - I still get confused referencing other forms...