Auto Insert of Data

[Clem]

I have a form and wish to automatically update one control after updating another. i.e. I enter data in control 'A' (which saves date in Table x) and want another control (Control B) to look at that data, convert the value by looking at a look up table and then inserting the new data into control 'B' (Saving it in table y). I believe this is done in 'Visual Basic'. I did it some years ago in a different job but have forgotten how.

 

[Mr. B]

You would not necessarily need what you refer to as control "B" to do what you want to do.

You can put VBA code in the AfterUpdate event of your control "A".

This code would read the value in Control "A" and then use that value in a "DLookup" function to return a value from you look-up table then you can just open a recordset from the table where the value needs to be saved and write a new record that that table or if needed, just update and existing record.

You could use the control "B" as an unbound control on you form that would be used to only display the value used to update table "y".

Hope this gets you pointed in the right direction.

 

[Clem]

Mr B, thanks for your help which has given me some direction as you hoped, however I am now struggling with the VBA code. Any guidance/help you can provide would be gratefully received.

 

[Mr. B]

It is very difficult to provide much help to you when I do not have any idea just how much VBA you know or just were you are in the development of the process you are trying to create.

I would suggest that you take the project in very small steps and see how much you can do. Then when you come up on something where you get stuck, post back here, providing as much detail about the issue as possible. I know that someone will be able to assist you in what ever you want to do.

Start by do searches here on the forum and on the internet for what you are looking to do. You will be surprised just how much code is out there and free for the taking.

Ask any questions here that you need to ask. If you start there you will find that you can reach your goals pretty quickly.

 

[Clem]

Mr B Hi. My knowledge of VBA is pretty much nil. I have reached a point in the process where I am stuck. Thank for responding.

I have a form (Yr3 Update) which draws data by query from two related tables. Some column are blank when the form opens. I have a look up table (Lvls Pts equiv) which holds values in two columns, one column is an alpha-numeric value such as 2A, this has an equivalent numeric value in the second column, such as 21.

What I need to do is add data, i.e 2a, to a control (Control A), in Yr3 Update, which will be saved in table Assessment Data. I want the form Y3 Update to add the equivalent numeric data, i.e 21, from Lvls pts equiv, to another control (Control B) as Control A loses focus or on key stroke. The numeric value is saved by the form in a related table (Points Data).

 

[Mr. B]

I have a form (Yr3 Update) which draws data by query from two related tables.

Your statement above is the first issue that you need to look at. To be able to actually edit or update records in either of these tables when linked together in the query and then have the data from the query bound to a form will depend on what the relationship is between these two tables. If these two tables have a one-to-one relationship then it might be possible that you could be able to have the results of that query bound to a form and still be able to edit the data. The key word here is "possible".

However, if these tables have a one-to-many relationship, it is highly unlikely that you will be able to update records in either table in the query or in the form bound to the query. It is one thing to display the results of a query that has multiple table joined together and quite another to be able to edit the data in either of the underlying tables.

You can test this by opening your query in datasheet view and see if you can edit the data in the query in datasheet view. If you can edit it then you do not have a problem. If you cannot edit the data then you will need to take a different approach to updating your data.

As a note: I supposed that it would be possible to do what you are wanting to do with what you currently have but it would require the use of VBA code.

One possibility, which would also be a "no VBA" approach, would be to use a form/sub-form approach. This is where you have your main form bound to the first table and the second form bound to table #2. Then you insert form #2 into form #1 making form #2 a sub-form to form #1. Then you use the "Link Master Fields" and "Link Child Fields" to link the sub-form (form #2) to the master form (form #1)

Using this approach you will be able to edit the data in both tables. You then could use the Dlookup function in the "Control Source" of a control on the sub-form to populate one or more fields in in table #2 which is actually bound to table #2.

I hope this is not to confusing but take a look at and search for more information about the user of Master and Child forms or Main and sub forms.

Hope this helps.