Insert/change information in a field

[Anthony]

For all you expert VB programmers this is probably a no-brainer. But, How can
I change information in a particular field in a particular record in a table
programmatically. Of course you can do it in a form - just scroll to that
record and change any field in the record - but how can you do it
programmatically through a VB macro. Another complication - I need the
ability to change what gets inserted into the record at runtime (it can't be
hard coded into the VB macro). Thanks

 

[ITperson]

For all you expert VB programmers this is probably a no-brainer. But, How can
I change information in a particular field in a particular record in a table
programmatically. Of course you can do it in a form - just scroll to that
record and change any field in the record - but how can you do it
programmatically through a VB macro. Another complication - I need the
ability to change what gets inserted into the record at runtime (it can't be
hard coded into the VB macro). Thanks

Anthony
You may want to check out assigning SQL to a variable, then running the sql
statement.

It would require a SQL Update table

Ex:
my_sql_text="Update mytable Set myfield=somevalue"

HTH

Terry

 

[Anthony]

Thank you for your quick response. However, your response seems to have me
"hard-code" what I insert into the field with the "Set myfield=somevalue". I
need to be able to determine, at runtime, what I insert into the field.
Maybe I can create a form, insert the data I want into a table through the
form, THEN run a macro that reads that table and inserts the data into my
target table. However, how do I insert it into a particular record (not
append to the last record) of the target table programmatically. The key is
I need to be able to do the insert through the macro, not through a form.

 

[ITperson]

Hi Anthony

This can be determined at run time.

If you can store the value at runtime into a variable, you can then place
that variable into the sql update statement.

Ex:

mysql_text="Update mytable Set myfield = " & "'" & myvariable & "'" & "
Where [mytable].[myfield] = " & "'" & myothervariable & "';"

Docmd.runsql mysql_text

HTH
Terry

 

[Anthony]

Thanks for your response. How do I store the value at runtime into a
variable? Do I use a form? Somehow I need to either get user input before
the macro is run (maybe through a form??) or pause the macro for user input
when the macro runs. Also, wouldn't your VB script (below) just append the
value into a new record in the target table? (E.G if my target table has
1000 records, I need to insert it into field1 of record 300). Fortunately,
the field will always be the same but the record number I need to insert it
into may change each time the macro is run.
If this is too esoteric to program via VB, maybe I just need to change the
value via a form. Just scroll down to the record I need to change and then
change the particular field I need. I just thought it could be done
programmatically.

Hi Anthony

This can be determined at run time.

If you can store the value at runtime into a variable, you can then place
that variable into the sql update statement.

Ex:

mysql_text="Update mytable Set myfield = " & "'" & myvariable & "'" & "
Where [mytable].[myfield] = " & "'" & myothervariable & "';"

Docmd.runsql mysql_text

HTH
Terry

Thank you for your quick response. However, your response seems to have me
"hard-code" what I insert into the field with the "Set myfield=somevalue". I
need to be able to determine, at runtime, what I insert into the field.
Maybe I can create a form, insert the data I want into a table through the
form, THEN run a macro that reads that table and inserts the data into my
target table. However, how do I insert it into a particular record (not
append to the last record) of the target table programmatically. The key is
I need to be able to do the insert through the macro, not through a form.

 

[ITperson]

Thanks for your response. How do I store the value at runtime into a
variable? Do I use a form? Somehow I need to either get user input before
the macro is run (maybe through a form??) or pause the macro for user input
when the macro runs. Also, wouldn't your VB script (below) just append the
value into a new record in the target table? (E.G if my target table has
1000 records, I need to insert it into field1 of record 300). Fortunately,
the field will always be the same but the record number I need to insert it
into may change each time the macro is run.
If this is too esoteric to program via VB, maybe I just need to change the
value via a form. Just scroll down to the record I need to change and then
change the particular field I need. I just thought it could be done
programmatically.

Hi Anthony

This can be determined at run time.

If you can store the value at runtime into a variable, you can then place
that variable into the sql update statement.

Ex:

mysql_text="Update mytable Set myfield = " & "'" & myvariable & "'" & "
Where [mytable].[myfield] = " & "'" & myothervariable & "';"

Docmd.runsql mysql_text

HTH
Terry

Thank you for your quick response. However, your response seems to have me
"hard-code" what I insert into the field with the "Set myfield=somevalue". I
need to be able to determine, at runtime, what I insert into the field.
Maybe I can create a form, insert the data I want into a table through the
form, THEN run a macro that reads that table and inserts the data into my
target table. However, how do I insert it into a particular record (not
append to the last record) of the target table programmatically. The key is
I need to be able to do the insert through the macro, not through a form.

:



:

For all you expert VB programmers this is probably a no-brainer. But, How can
I change information in a particular field in a particular record in a table
programmatically. Of course you can do it in a form - just scroll to that
record and change any field in the record - but how can you do it
programmatically through a VB macro. Another complication - I need the
ability to change what gets inserted into the record at runtime (it can't be
hard coded into the VB macro). Thanks


Anthony
You may want to check out assigning SQL to a variable, then running the sql
statement.

It would require a SQL Update table

Ex:
my_sql_text="Update mytable Set myfield=somevalue"

HTH

Terry

Hi Anthony

This can be done via vba.
All that is needed is a way to find what record number to stop at to update
the field. If you are using a form and a value on the form will allow you to
point to the record that needs to be updated. You can assign the value of
the form field to a variable (myvariable=forms!formname.textboxname). This
will put the value in the textbox into the variable where you can pass that
value to the sql statement.

ex: Lets say you have a testbox on the form or a combobox where someone
selects a value. Either way, put the value into a variable called textboxvar.

mysql_txt="Update mytable Set Field1 = "'" somevalue"'" Where " & "'" &
textboxvar"'" & " = [mytable].[Field1] ;"

I have not tested this syntax, but I think you should be able to get an idea
of what I am talking about.

Terry