writing to multiple fields

[johnlatona123]

hi!

so i have a form for data entry and i am trying to reduce the duplication of user actions by having 1 field in my form write the same exact value to another "hidden" field on the form

example:

text 1 with control source A - is my visible text field where a user will input a value

text 2 with control source B - is my hidden text field

i need the value of text 1 to be written to text 2 when text 1 loses focus.

i thought this would have been a simple "= [field]" but i guess im wrong

in my mind i am thinking i am essentially creating 2 records at a time as the 2 fields have 2 different source tables.

can anyone help me out?

thanks in advance!

 

[jzwp22]

One key rule of database normalization is to not have the same data in more than 1 table. So why are you duplicating information?

 

[johnlatona123]

im essentially merging 2 databases together as one and rather than a massive re-desgn project, i just need to do as i stated to effectively link the 2 together as one.

i would have to redisgn 3-5 external databases as well as the secondary one to avoid duplication, and even though there will be a small amount of duplication it will save greatly on file pathing when i move this one database from one server to another. these files reside on a large network with numerous drives and multiple server locations.

 

[jzwp22]

in my mind i am thinking i am essentially creating 2 records at a time as the 2 fields have 2 different source tables.

Is the form with the 2 controls bound or unbound?

From your comment above, it implies that you are using a query that joins two tables and that you are basing the form on that query and thus a bound form. In that case, you already have a logical join between the two tables and there would still be no need to put the same data in both tables (they already have a common joining field). You would only need a query to bring the information together.

 

[johnlatona123]

well currently text 1 with control source A is bound, text 2 is unbound but i would like it to be bound to control source B. the data dehind the form does support a right join but that does not write to the second table.

think of this more as a data entry form rather than something to view records.

i need to get the data stored in control source A to write to control source B or a seperate function of the database will not run properly.

i could be wrong but i do not feel that a query as you describe will do what i need it to do.

i hope that helped, your thoughts?

 

[jzwp22]

A form can be bound to only 1 table or query (of course a query can have more than 1 table). Having a query with a right join may yield an un-updateable query which of course will not help you out. The only thing that I can think of is to abandon the unbound control and run an append query (to the second table) in the after update event of the first control or after the record is added to the bound table (after insert event of the form).

 

[johnlatona123]

well the append idea sounds doable, can you help me write that?

 

[jzwp22]

Since you will have to supply the value in the textbox to the append query, I would just use code in whatever event you choose. The code would go something like this:

dim mySQL as string

mySQL= "INSERT INTO destinationtablename (destinationfieldname)"
mySQL = "VALUES ('" & me.textboxname & "')"

currentdb.execute mySQL, dbfailonerror

You will have to supply the table and field name of the destination table.

I assume that the value you were appending is text so the value supplied by the textbox control will be bound by single quotes. If the value is going into a date field then replace the single quotes with # signs.

mySQL = "VALUES (#" & me.textboxname & "#)"


If the value is numeric, remove the single quotes.