Add code to existing code

[OEB]

I am updating some code in a form in a database and I need some direction.

The code that exists looks at the text in a field and clears some other
fields based on a particular value. I would like to take the existing values
and put them in a table before clearing the values. What is the best way to
do this?

 

[Brendan Reynolds]

I am updating some code in a form in a database and I need some direction.

The code that exists looks at the text in a field and clears some other
fields based on a particular value. I would like to take the existing
values
and put them in a table before clearing the values. What is the best way
to
do this?

Probably the most efficient way would be an insert query. Something like
.....

CurrentDb.Execute "INSERT INTO SomeTable (Field1, Field2, Field3) VALUES(" &
Me.Control1.Value & ", " & Me.Control2.Value & ", " & Me.Control3.Value &
")"

Where "SomeTable" is the table you want to insert the record into, "Field1",
"Field2" etc are the fields in the table, and "Control1", "Control2" etc are
the controls (textboxes, comboboxes or whatever) containing the values you
want to store.

The alternative would be to open a recordset on the table and update that,
but that would probably be less efficient.

 

[OEB]

Thanks. I am getting entries into the table, but all they are are date and
time stamps. Can you suggest what I am doing wrong?

 

[Brendan Reynolds]

Thanks. I am getting entries into the table, but all they are are date
and
time stamps. Can you suggest what I am doing wrong?

Not without seeing the code and/or SQL, no.

 

[OEB]

Oh, yeah, I guess that would help. Sorry - here it is.

CurrentDb.Execute "INSERT INTO HistoryTable (RequestedTurnoverDate,
ScheduledTurnoverDate) VALUES(" & Me.RequestedTurnoverDate.value & ", " &
Me.ScheduledTurnoverDate.value & ")"

There are others that need to be captured, but am using these two until I
get it right.

Thanks.

 

[Douglas J. Steele]

In Access, you must delimit dates with # characters. As well, the dates must
either be in mm/dd/yyyy form, or an unambiguous format such as yyyy-mm-dd or
dd mmm yyyy:

CurrentDb.Execute "INSERT INTO HistoryTable (RequestedTurnoverDate,
ScheduledTurnoverDate) VALUES(" & Format(Me.RequestedTurnoverDate.value,
"\#yyyy\-mm\-dd\#") & ", " &
Format( Me.ScheduledTurnoverDate.value, "\#yyyy\-mm\-dd\#") & ")"

Those format statements will ensure that the dates are presented in a
fashion that's recognizable by Access.

 

[OEB]

I guess that goes for number fiels as well? The field is text type, but I am
getting a "syntax error in number in query expression '12.6.0..." error.

 

[Douglas J. Steele]

Text values have to be delimited with quotes. You can either use single
quotes or double quotes in SQL statements.

Your SQL would be something like:

CurrentDb.Execute "INSERT INTO MyTable (IPAddress) " & _
"VALUES ('" & Me.IPAddress & "')"

Exagerated for clarity, that's

CurrentDb.Execute "INSERT INTO MyTable (IPAddress) " & _
"VALUES ( ' " & Me.IPAddress & " ' )"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I guess that goes for number fiels as well? The field is text type, but I
am
getting a "syntax error in number in query expression '12.6.0..." error.