Question Refer a query field in access form ?

[ponneri]

Hello Everyone!

After a long hiatus, I'm into developing an application using my favorite Ms-Access; 2007 version to be exact.

My issue is as follows :

I have a form for data entry. But I need to fill in some fields in this form, from another table which I am able to refer as a query result - based on a combo box in the form; after the form loads.

Using VBA, how do I refer to the fields in the query and have them inserted into some fields on my current form ?

Any help will be appreciated.

(Right now, I'm using a list box to show the results of the query based on what user selects in the combo and then inserting the selected record from the list box to the fields I want on form. This step of using a list box is not required actually.)

 

[bob fitz]

Perhaps you could use the DLookup() function to return data from your query

 

[gemma-the-husky]

Are you just displaying the values, or are you copying from another table into the active table?

 

[ponneri]

Thank you Bob and Dave.

Hope Dlookup() helps.

And to clarify, I would like to first display the fields from the query on my form and later move these values into the underlying table; when I save the form.

 

[gemma-the-husky]

thanks for the clarification

if you are duplicating data - so that records in one table are being repeated in another table, then this is bad practice.

you can always obtain the data in the related table.

by duplicating the data you
a) make the dbs larger
b) much worse, run the risk of inconsistent updates, so that data that ought to be the same in both places, becomes different. This is what normalisation is all about. Avoiding duplication of data.

 

[ponneri]

Thanks again for the suggestion on normalisation, Dave.

However, I'm only populating about 5 fields from the other table; as it is a must during data entry. And my form has a total of 15 more fields that need to be filled/typed in manually too.