Auto-filling form fields by pulling data from another table in response to a field in a form. (1 Viewer)

[MothMan]

I am sure that this is a simple problem to anyone with some experience but it has got me stumped.

I have 2 tables "Animal_Data" and "Species_Data".

Animal_Data has fields: "TxtCommonNameOnAnimalData", TxtLatinName, TxtCITESStatus and TxtAnimalClass.

The information to fill these fields is held in "Species_Data" in fields: TxtCommonNameOnSpecies, TxtScientificName, TxtCITESAppendix and TxtAnimalClass.

All fields are text and are set as short text.

Data is entered by users into table Animal_Data via form FrmNewAnimal

On FrmNewAnimal the user selects the common name by using a ComboBox (Combo88) which looks up the common name from field TxtCommonNameOnSpecies in the Species_Data table and then saves it to TxtCommonNameOnAnimalData in the Animal_Data table.

When a user has selected the common name of an animal then I would like to form to autopopulate the text boxes for latin name, CITES appendix and Animal Class on FrmNewAnimal by looking up the values from table Species_Data. These values would then be saved when the record is saved.

I thought that DLookup would be a good solution to this, using the selected Common Name in Combo88 on FrmNewAnimal as the reference and then matching latin name, class and CITES status from the Species_Data table into different text boxes also on frmNewAnimal.

I cannot get this to work though and have been trying different configurations in the Data.

So my questions would be:

1. Is this the best way to achieve this?
2. If yes, then how do I get it to do it.

At the moment I have a text box which I want to autopopulate with the latin (scientific) name. I have the control source set to:

=DLookUp([Species_Data]![TxtScientificName],[Species_Data],"[Species_Data]![TxtCommonNameOnSpecies]= " & [Forms]![FrmNewAnimal]![Combo88])

but this returns a #Name? error. I have tried various combinations, including shortened versions of referencing expression and domain, and these either return the same name error or just #Error. If I just set the Expression and domain then it finds the first latin name on the list so seems to look in the right place but my criteria is wrong somehow.

I am sure this is just me doing something silly and any help and advice would be really appreciated.

Thanks.

 

[pbaldy]

Does this help?

Autofill

 

[MothMan]

Possibly, once I have digested it. If I understand this then you basically call a combo box but don't display all the data, and then have a text box with a control that gets the relevant hidden field. Is that correct? I don't quite understand the bit about saving the data, which I will need to do, so I will have to get my head around that.

 

[pbaldy]

Yes, that's basically it. Do the values you think you should save change in the Species_Data making you need to save the values current at the time the record was created? As mentioned in the link, normally you only save a key value in related tables. I'd say at least 90% of the times that's what happens. You save a value when it changes over time. For example, I look up a price when I select a product. That price could change over time so I save it with the order I'm creating. I would not save the product description, because it's static.

 

[arnelgp]

since the fields: TxtLatinName, TxtAnimalClass can be found on Species_Data, you can remove them from Animal_Data (normlization).
you only leave TxtCommonNameOnAnimalData, xtCITESStatus to Animal_Data table.

use a Query to show the "other" field/data from Species_Data, and use this query on your form.

SELECT Animal_DAta.TxtCommonNameOnAnimalData, Animal_Data.xtCITESStatus,
Species_Data.TxtLatinName, Species_Data.TxtAnimalClass
FROM Animal_Data LEFT JOIN Species_Data
ON Animal_Data.TxtCommonNameOnAnimalData = Species_Data.TxtCommonNameOnSpecies;

you don't need ANY code to show the other fields for New/Existing record of Animal_Data.

 

[arnelgp]

see this demo, No Code required.

 

[pbaldy]

I'll get out of the way.

 

[MothMan]

Yes, that's basically it. Do the values you think you should save change in the Species_Data making you need to save the values current at the time the record was created? As mentioned in the link, normally you only save a key value in related tables. I'd say at least 90% of the times that's what happens. You save a value when it changes over time. For example, I look up a price when I select a product. That price could change over time so I save it with the order I'm creating. I would not save the product description, because it's static.

OK, I see how that works. Things would change, latin names get modified over time, species change CITES appendix and so on. I'm not sure that matters though as you would probably want to see how things are classified now, not what they were when the animal first arrived. Probably I don't need to save the details other than the common name.

 

[MothMan]

see this demo, No Code required.

Thanks that's really helpful as well. The animal information is only a small part of the data so doing it this way I would need a sub-form to allow me to use the query as the data source? If the information wasn't saved then it would change the way I report I suppose. For example, at the moment to find out how many Annex A species arrived in a time period I simply run a query to find all the entries which are "Annexe A" and then count. With the data not saved then I guess my query would find all the entries and then do the process to determine which were annex a, and then count them? Sorry if these are all simple questions, this is a whole new world to me!

 

[arnelgp]

You can create a subform for the animals, having the species as the main form.
You should make a mockup of the form you need to build.

 

[arnelgp]

If the information wasn't saved

it will be saved to Animal_Data.

 

[MothMan]

Does this help?

OK, I see how that works. Things would change, latin names get modified over time, species change CITES appendix and so on. I'm not sure that matters though as you would probably want to see how things are classified now, not what they were when the animal first arrived. Probably I don't need to save the details other than the common name.

Just trying this out and it works when just displaying the value. If I try and change the form to save the value then I get the error Cannot find the object me. and the value does not display in the text box, presumably as it no longer has the same instructions in the control source.

 

[pbaldy]

Did you realize that what was posted in my link to save the value was VBA code? It goes here:

First VBA

It sounds like you put it in the properties window directly.

 

[MothMan]

Did you realize that what was posted in my link to save the value was VBA code? It goes here:



It sounds like you put it in the properties window directly.

Aah, got it. That did it.

 

[pbaldy]

Glad you got it working.