Textbox saving in 2 tables?

[Joel]

How do I get a Textbox to save to 2 different tables?

I have tried to use VB code, but no luck.

I know you can use Me.[YourField] = [YourControl]

But how do I tell it what table to I want it to save to?

 

[Ofer]

Have you tried running an update query on the two tables


Dim SqlStr as string
SqlStr = "UPDATE MyTable1 SET Field = " & Me.FieldName & " WHERE
MyTable1.Field2 =" & me.Field2
docmd.runsql SqlStr

SqlStr = "UPDATE MyTable2 SET Field = " & Me.FieldName & " WHERE
MyTable2.Field2 =" & me.Field2
docmd.runsql SqlStr


For String value add ' before and after the value
SqlStr = "UPDATE MyTable2 SET Field = '" & Me.FieldName & "' WHERE
MyTable2.Field2 ='" & me.Field2 & "'"

For DAte value add # before and after the value
SqlStr = "UPDATE MyTable2 SET Field = #" & Me.FieldName & "# WHERE
MyTable2.Field2 =#" & me.Field2 & "#"

 

[Joel]

Thnaks for the quick responce.
That code works, but it overwrites everything in the field for all the
records with whats in the textbox.

This is my Code;

Dim SqlStr As String
SqlStr = "UPDATE MainUnitData SET NextinspectionDate = # " &
Me!NextInspectionDate & "# WHERE InspectionHistory.NextInspectionDate = # " &
Me!NextInspectionDate & "#"
DoCmd.RunSQL SqlStr

Have you tried running an update query on the two tables


Dim SqlStr as string
SqlStr = "UPDATE MyTable1 SET Field = " & Me.FieldName & " WHERE
MyTable1.Field2 =" & me.Field2
docmd.runsql SqlStr

SqlStr = "UPDATE MyTable2 SET Field = " & Me.FieldName & " WHERE
MyTable2.Field2 =" & me.Field2
docmd.runsql SqlStr


For String value add ' before and after the value
SqlStr = "UPDATE MyTable2 SET Field = '" & Me.FieldName & "' WHERE
MyTable2.Field2 ='" & me.Field2 & "'"

For DAte value add # before and after the value
SqlStr = "UPDATE MyTable2 SET Field = #" & Me.FieldName & "# WHERE
MyTable2.Field2 =#" & me.Field2 & "#"

How do I get a Textbox to save to 2 different tables?

I have tried to use VB code, but no luck.

I know you can use Me.[YourField] = [YourControl]

But how do I tell it what table to I want it to save to?

 

[Rick B]

You don't!

The whole point of a RELATIONAL database is to store things once and only
once, then point to it. What happens if the value changes down the road?
Will you remember to fix both tables?

If you are doing this, then you did not use NORMALIZED DATABASE DESIGN
PRINCIPLES.

Rethink your data structure and post back if you ened help getting it
normalized.

 

[Joel]

Here is a bit more detail:

In my form i have an unbound textbox that is filled from another form.
I have this texbox save to a field in the forms control source.
I also need this textbox to save into a field in a specific record in a
different table.

Thanks

 

[Joel]

Well its an expression that im saving.
I need it saved into the main table and into a history table, so it is
getting saved twice, but the 1st one will always be changing, the history
will stay the same.

You don't!

The whole point of a RELATIONAL database is to store things once and only
once, then point to it. What happens if the value changes down the road?
Will you remember to fix both tables?

If you are doing this, then you did not use NORMALIZED DATABASE DESIGN
PRINCIPLES.

Rethink your data structure and post back if you ened help getting it
normalized.


--
Rick B

How do I get a Textbox to save to 2 different tables?

I have tried to use VB code, but no luck.

I know you can use Me.[YourField] = [YourControl]

But how do I tell it what table to I want it to save to?

 

[Rick B]

It is an expression? Meaning a combination of multiple fields? That is
ALSO a violation of normalized design. If you have the various fields that
make up the expression, then you ONKY save those fields. To also save the
expression would be redunant. What if one of the fields changes? What
would prompt the expression to change?

You need to step back and think about your design.

--
Rick B

Well its an expression that im saving.
I need it saved into the main table and into a history table, so it is
getting saved twice, but the 1st one will always be changing, the history
will stay the same.

You don't!

The whole point of a RELATIONAL database is to store things once and only
once, then point to it. What happens if the value changes down the road?
Will you remember to fix both tables?

If you are doing this, then you did not use NORMALIZED DATABASE DESIGN
PRINCIPLES.

Rethink your data structure and post back if you ened help getting it
normalized.


--
Rick B

How do I get a Textbox to save to 2 different tables?

I have tried to use VB code, but no luck.

I know you can use Me.[YourField] = [YourControl]

But how do I tell it what table to I want it to save to?

 

[Joel]

Well what do you do when the fields that make up the expression are always
changing and you need to keep a record of what the expression equaled?

It is an expression? Meaning a combination of multiple fields? That is
ALSO a violation of normalized design. If you have the various fields that
make up the expression, then you ONKY save those fields. To also save the
expression would be redunant. What if one of the fields changes? What
would prompt the expression to change?

You need to step back and think about your design.

--
Rick B

Well its an expression that im saving.
I need it saved into the main table and into a history table, so it is
getting saved twice, but the 1st one will always be changing, the history
will stay the same.

You don't!

The whole point of a RELATIONAL database is to store things once and only
once, then point to it. What happens if the value changes down the road?
Will you remember to fix both tables?

If you are doing this, then you did not use NORMALIZED DATABASE DESIGN
PRINCIPLES.

Rethink your data structure and post back if you ened help getting it
normalized.


--
Rick B



How do I get a Textbox to save to 2 different tables?

I have tried to use VB code, but no luck.

I know you can use Me.[YourField] = [YourControl]

But how do I tell it what table to I want it to save to?

 

[Rick B]

You store the two values. Period. For example, the price paid for a widget
might be different each time my customer buys one. The quantity might also
vary. So, I store the "price paid" and the "quantity". When I need to look
at the extended price somewhere down the road, I simply multiply those two
figures in my query, form, or report. But I don't need to store that
extended figure. That would waste disk space and slow down my database. A
math calculation is almost always quicker than a disk read.


--
Rick B

Well what do you do when the fields that make up the expression are always
changing and you need to keep a record of what the expression equaled?

It is an expression? Meaning a combination of multiple fields? That is
ALSO a violation of normalized design. If you have the various fields that
make up the expression, then you ONKY save those fields. To also save the
expression would be redunant. What if one of the fields changes? What
would prompt the expression to change?

You need to step back and think about your design.

--
Rick B

Well its an expression that im saving.
I need it saved into the main table and into a history table, so it is
getting saved twice, but the 1st one will always be changing, the history
will stay the same.

:

You don't!

The whole point of a RELATIONAL database is to store things once and only
once, then point to it. What happens if the value changes down the road?
Will you remember to fix both tables?

If you are doing this, then you did not use NORMALIZED DATABASE DESIGN
PRINCIPLES.

Rethink your data structure and post back if you ened help getting it
normalized.


--
Rick B



How do I get a Textbox to save to 2 different tables?

I have tried to use VB code, but no luck.

I know you can use Me.[YourField] = [YourControl]

But how do I tell it what table to I want it to save to?

 

[Joel]

Ok Ill try to think this over tonight.
So after I get back to work tomarrow, ill probly have more questions abou
this if your willing to help.

Thank you for you advice

You store the two values. Period. For example, the price paid for a widget
might be different each time my customer buys one. The quantity might also
vary. So, I store the "price paid" and the "quantity". When I need to look
at the extended price somewhere down the road, I simply multiply those two
figures in my query, form, or report. But I don't need to store that
extended figure. That would waste disk space and slow down my database. A
math calculation is almost always quicker than a disk read.


--
Rick B

Well what do you do when the fields that make up the expression are always
changing and you need to keep a record of what the expression equaled?

It is an expression? Meaning a combination of multiple fields? That is
ALSO a violation of normalized design. If you have the various fields that
make up the expression, then you ONKY save those fields. To also save the
expression would be redunant. What if one of the fields changes? What
would prompt the expression to change?

You need to step back and think about your design.

--
Rick B



Well its an expression that im saving.
I need it saved into the main table and into a history table, so it is
getting saved twice, but the 1st one will always be changing, the history
will stay the same.

:

You don't!

The whole point of a RELATIONAL database is to store things once and
only
once, then point to it. What happens if the value changes down the
road?
Will you remember to fix both tables?

If you are doing this, then you did not use NORMALIZED DATABASE DESIGN
PRINCIPLES.

Rethink your data structure and post back if you ened help getting it
normalized.


--
Rick B



How do I get a Textbox to save to 2 different tables?

I have tried to use VB code, but no luck.

I know you can use Me.[YourField] = [YourControl]

But how do I tell it what table to I want it to save to?

 

[Rick B]

I'll leave this thread flagged.

--
Rick B

Ok Ill try to think this over tonight.
So after I get back to work tomarrow, ill probly have more questions abou
this if your willing to help.

Thank you for you advice

You store the two values. Period. For example, the price paid for a widget
might be different each time my customer buys one. The quantity might also
vary. So, I store the "price paid" and the "quantity". When I need to look
at the extended price somewhere down the road, I simply multiply those two
figures in my query, form, or report. But I don't need to store that
extended figure. That would waste disk space and slow down my database. A
math calculation is almost always quicker than a disk read.


--
Rick B

Well what do you do when the fields that make up the expression are always
changing and you need to keep a record of what the expression equaled?

:

It is an expression? Meaning a combination of multiple fields? That is
ALSO a violation of normalized design. If you have the various

fields
that

make up the expression, then you ONKY save those fields. To also

save
the

expression would be redunant. What if one of the fields changes? What
would prompt the expression to change?

You need to step back and think about your design.

--
Rick B



Well its an expression that im saving.
I need it saved into the main table and into a history table, so it is
getting saved twice, but the 1st one will always be changing, the history
will stay the same.

:

You don't!

The whole point of a RELATIONAL database is to store things once and
only
once, then point to it. What happens if the value changes down the
road?
Will you remember to fix both tables?

If you are doing this, then you did not use NORMALIZED DATABASE DESIGN
PRINCIPLES.

Rethink your data structure and post back if you ened help

getting
it

normalized.


--
Rick B



How do I get a Textbox to save to 2 different tables?

I have tried to use VB code, but no luck.

I know you can use Me.[YourField] = [YourControl]

But how do I tell it what table to I want it to save to?