Auto populate fields on current form

[Jibbadiah]

I can't look at your db - I only have Access 97 at work.

Ok. so you have the form bound to your table.
1. Create a query that selects all of the elements that you need from the table (obviously you have to keep the table to do this).
2. Go to the properties section of your form and change your datasource from your table to your new query. Check it, it should work in the same way that it did for the table (but instead it is using the query).
3. Go to the properties section of the description control and remove the entry in "control source". This will make the control unbound, i.e. it is no longer populated from the query.
4. Delete your existing role control and insert a new combobox in it's place. Use the wizard to make it work.
5. Insert an unbound text box called txtControl. On the "on Change" event of the combo box insert the following code (changing the names where necessary. This should autopopulate the description of the role

txtControl = DLookup("[Description]", "QueryName", "[Role] = '" & ComboBoxName & "'")
Me.Recalc

 

[dwjz07]

Hi. I linked the tables (by employee id) that I was using as my subforms to employee's table (mainform). Created query and selected fields from all the tables. Ran the query, but did not get any employee information. So, I knew something was wrong there. However, I saved qry as qryEmplAll and changed the datasource from the Employees table to the query. After I entered some of the fields onto the form, I noticed in form view that the new fields I included were not showing. I seem to be making this harder, than I know it really is.

 

[ansentry]

I have had a very quick look at your db.

Most of your table don't have primary keys.

In the table tblHardware you have a primary key (EID) however in the form frmHardware you don't have EID as a control, therefore you can't save the data.

I think you should go back and fix up your tables (re primary keys) also you don't have any relationships.

 

[dwjz07]

FINALLY!! Believe it or not, but I finally was able to make the auto populate work. I joined two tables to create a select query. I created a form from the query and then on the field that I wanted to autopopulate, I pulled in the select query for the row source, just as was suggested. However, when I created a subform from that form and linked it to mainform, The fields do not show in form view. The default value was set to single form, I tried changing to continuous form, nothing. Can anyone tell me what I am doing wrong, now Thanks...

 

[dwjz07]

Hi Ansentry. Yes, my tables were a mess. I did clean that up and make sure what was used for emplid was written the same way for the all the tables. And made sure I had the primary keys set. I did not use emplid for all of my tables because there was only a need to add emplid to main tables used for the subforms. However, I am now stuck once again. I finally was able to autopopulate, however, the forms I created are not working as subforms (can't view fields) when I attached to mainform. Any suggestions...?

 

[ansentry]

If you have "cleaned" up your tables and established relationships then post an updated version of your db and I will try and have a look.

What is the name of the form that you want the "Auto Fill" to work on and is it a subfrom and if so then what is the name of the main form.

It is hard to find the problem if you don't state the name of your forms and controls that are giving you grief.

Read throught all your posts and see if you have posted the name of the forms and subform and controls that are giving you problems.

 

[dwjz07]

If you have "cleaned" up your tables and established relationships then post an updated version of your db and I will try and have a look.

What is the name of the form that you want the "Auto Fill" to work on and is it a subfrom and if so then what is the name of the main form.

It is hard to find the problem if you don't state the name of your forms and controls that are giving you grief.

Read throught all your posts and see if you have posted the name of the forms and subform and controls that are giving you problems.

Ansentry, the main form is called frmEmployees. The first 3 tabs – Hardware, Telecommunication and Security are the subforms in which I am having the issues. At first, all my forms were created from tables. However, I re-created the frmTelecom1 from a select query, qryTelecom and was able to perform the auto populate on the Equipment and Cost fields. However, I am not able to view the fields on the subform Telecommunication tab after I created the subform – sfrmTelecom1 and attached to mainform. The tblHardware was also re-created as form from select query – qryHardware. However, the auto populate is not working for the cmbModel and Cost fields. I have not re-created the form as a select query for the Security tab. When I do, I need the combo Security field to autopopulate the Security Type and Description fields. The Security subform is datasheet view. Thanks for your help…

 

[dwjz07]

Can anyone help me determine why the fields on a subform would not show when the subform is in view mode? I have had this issue for a few days. I actually deleted and recreated subform. Can my database be corrupt?
Thanks for the help...

 

[ansentry]

You have 8 tables with the same Primary key CatID why dont they have their own names?

your tblHardware has category as the primary key, why not HardwareID?

My suggestion is to give all you tables a meaninful primary key with type as autonumber.

For example your table tblEmpInfo_New;

EmpID - Auto Number Primary Key
EmployeeNumber - This can be your Text field to put in the employees number.

Can anyone help me determine why the fields on a subform would not show when the subform is in view mode?

You don't name the form, but i assume you mean FrmTelecom1.

I have got it to work, so that you can view it and the "Equipment" works.

If you don't fix the foundation of your db now you will have problem after problem.

 

[dwjz07]

Hi Ansentry, thank you so much for your assistance and input. I am just getting back with you because I have been playing around with the db and comparing your setup to mine. It seems that I have a learning disorder , as I am not grasping this. I changed my tables to include the personal ids, ie, tblTelecom = TelecomID, tblHardware = HardwareID, etc. However, I noticed from your example db, the TelecomID you entered as primary key, then you added another id field name (fkCatID) data type as number. Why? Anyhow, I updated tblTelecom in same manner and linked the table to tblCommunication. However, when I add the subform to mainform, I am not able to pull in the cost field and sometimes the cost field comes up with zero amount ($0.00). Also, I do not have the option to add a record on my subform. I am only working with two tables at this time to make sure I am doing this correctly. However, the steps I tried to follow from your example does not seem to work for me. I know I am being a pain and would understand if you feel you cannot help me further. However, if you feel you have a little more patience and could possibly help me, I would appreciate it. My deadline is approaching for this database to be completed and I still have not been able to get the auto populate to work on the subform. It does work on the frmTelecom1, so I perplexed. Thanks...

 

[dwjz07]

I finally got the auto populate to work on my subforms!!! It took awhile, but after closely viewing the database example by Arsentry, I was able to figure it out. Hopefully, for this next question, it will not take me as long. Although, now, I believe I have the basics. However, there is one subform in my database for which I think I would need to use coding to perform the auto populate. I am trying to autopopulate from a datasheet drop down list containing three values. Each value is from a separate table and query. Can The subform I am referring to is the Security tab on my example database. I would appreciate if someone could assist me. Thanks...

 

[ansentry]

I am referring to is the Security tab on my example database

You don't have the primary key in the form.

 

[dwjz07]

You don't have the primary key in the form.

Hi. Thanks for the response. Actually, I created SecurityID, autonumber and added primary key. Then, I created another field name, secCatID, as a Number. I linked one of the tables, PSRoles to tblSecurity2 by SecurityID, autonumber to secCatID, Number. Then queried on both. See example db.

 

[ansentry]

No sample attached

 

[dwjz07]

Sorry. Wasn't able to attach for some reason. I updated this example the same way I did my original db, but for some reason, now my autopopulating is not working. I'm trying... Thanks for the patience.

 

[dwjz07]

Hi. Please help!! I've almost gotten a handle on this auto populating. Can someone help me once more in how I can auto populate a combo box with 3 values. Each value will update 2 fields in a datasheet form. So when you select "Apple", from the drop down list, it would autopopulate "Sweet" in one field and "Red" in the other. Or if select "Banana" from the same drop down list, it would autopopulate, "Yellow" in one field and "Peel" in the other. Thank you for input.

 

[ansentry]

Hi. Please help!! I've almost gotten a handle on this auto populating. Can someone help me once more in how I can auto populate a combo box with 3 values. Each value will update 2 fields in a datasheet form. So when you select "Apple", from the drop down list, it would autopopulate "Sweet" in one field and "Red" in the other. Or if select "Banana" from the same drop down list, it would autopopulate, "Yellow" in one field and "Peel" in the other. Thank you for input.

I sent you a "fixed" sample on 27th July and you then post back a version that does not have any of my fixes or suggestions in it. The one I sent you fixed the auto fill for Equipment and yet the one you post at a later date does not.

Here is a sample I post for another user:

Click here

This is my last answer to this thread.

 

[dwjz07]

Ansentry, actually, the file I sent was the wrong one. I had a few that I created as I was testing different ways to make this work. I have actually, been able to make the auto populate work from your examples. The only thing that I was perplexed with was that in your example, the description did not populate on the fields in the table, only the number. I will take a look at your latest example, as it looks like what I need to finalize this piece which has been a complete struggle for me. Again, thank you for all of your suggestions, time and patience. B