Update fields

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I have a bit of a challenge to overcome.

I have a table that stores the current LOCATION of
equipment. This value is selected via a combo box in a
form.

The challenge is to create a "history" (see below ******
line )of ALL OF THE LOCATION the equipment went through
during its "lifetime".

For instance, this should be the process:

Step 1. Record Creation:
Date = 08-25-03
Current Location = Manufacturer

Step 2. Record Modification
Date = 08-30-03
Current Location = Warehouse

Step 3. Record Modification
Date = 09-02-03
Current Location = Shipped to Customer


*****************

Date & Location History Field (in History table):
=================================================
08-25-03 Manufacturer
08-30-03 Warehouse
09-02-03 Shipped to Customer



Again, the challenge is to update the "History" table
automatically by adding today's date and the current
location once the date/location has changed.

Is this possible or is there potentially a better way of
doing this?

Thanks, Tom
 
The challenge is to create a "history" (see below ******
line )of ALL OF THE LOCATION the equipment went through
during its "lifetime".
Click to expand...

private sub cboLocation_AfterUpdate

strSQL = "INSERT INTO LocationChanges" & _
" (DateChanged, ItemID, NewLoc) " & vbCrLf & _
"VALUES ( " & _
Format$(Now(), "\#yyyy\-mm\-dd hh\:nn\#") & ", " & _
"""" & me!txtItemID.Value & """, " & _
"""" & me!cboLocation.Value & """)"

MsgBox strSQL,,"Check this out!"

CurrentDb().Execute strSQL, dbFailOnError

End sub


HTH


Tim F
 
Tim:

Just wanted to ensure that I get your feedback one more
time...

I did have some difficulties in the original
thread "Update Fields". You I PLEASE ask you to revisit
the thread and provide me some additional answer.
 
PMFBI

It sounds like you are using Acc2K or XP. Currentdb() is a
reference to DAO. A2K & XP use ADO by default.

From the database window (or a form in design view), press
<alt-F11>. Goto Tools, then click on References. Make sure
there is a check next to

Microsoft DAO 3.6 Object Library

If you don't see it, scroll down until you do and put a
check next to it. Click on OK. Click on References again

and move it above any line that has ADO in it.
(or change the code from CurrentDb() to DAO.CurrentDb() )

Click on OK . I always close the datbase and open it to
make sure the references were loaded.


Look again at the code Tim provided you. The syntax for
the SQL 'Insert INTO' is (from Help):

INSERT INTO targetTable [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

It looks like you are missing the field1,field2 part or
the statement. Your targetTable would be 'History', field1
and field2 would be the names of the fields in table
History and the values (in the same order as above) would
be from Date() and the textboxname that you entered the
location in.

HTH

Steve
 
Steve:

Thanks for your feedback... I appreciate it!

I made the checkmark for the Object Library and moved it
to the top.

Modified your version of the function. I doubt I truly
understood it. This is how I modified it... it doesn't
work though...

Do you have any additional pointers?

Thanks,
Tom

&&&&&&&&&&&&&&&&&&&&&&&&&&&

Private Sub Location_AfterUpdate()
On Error GoTo Err_Location_AfterUpdate

strSQL = "INSERT INTO History (Date, Location) VALUES
(Me!DateHistory.Value, Me!Location.Value)"


MsgBox strSQL, , "Location history table will be
updated!"

CurrentDb().Execute strSQL, dbFailOnError

End_Location_AfterUpdate:
Exit Sub

Err_Location_AfterUpdate:
MsgBox Err.Description & " (" & Err.Number & ")", _
vbOKOnly + vbCritical
Resume End_Location_AfterUpdate

End Sub


-----Original Message-----
PMFBI

It sounds like you are using Acc2K or XP. Currentdb() is a
reference to DAO. A2K & XP use ADO by default.

From the database window (or a form in design view), press
<alt-F11>. Goto Tools, then click on References. Make sure
there is a check next to

Microsoft DAO 3.6 Object Library

If you don't see it, scroll down until you do and put a
check next to it. Click on OK. Click on References again

and move it above any line that has ADO in it.
(or change the code from CurrentDb() to DAO.CurrentDb () )

Click on OK . I always close the datbase and open it to
make sure the references were loaded.


Look again at the code Tim provided you. The syntax for
the SQL 'Insert INTO' is (from Help):

INSERT INTO targetTable [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

It looks like you are missing the field1,field2 part or
the statement. Your targetTable would be 'History', field1
and field2 would be the names of the fields in table
History and the values (in the same order as above) would
be from Date() and the textboxname that you entered the
location in.

HTH

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


-----Original Message-----
Tim:

Thanks for you prompt reply:

For testing purposes, I kept the fields very simple...

In both tables (the one updated is called "History"), I
have the DATE & LOCATION field.


However, currently the VBA debugging window pops up and
the line "CurrentDb ... " is highlighted in yellow.

Did I make some of my modifications incorrectly?

Tom





Private Sub Location_AfterUpdate()

strSQL = "INSERT INTO History" & _
"VALUES ( " & _
Format$(Now(), "\#yyyy\-mm\-dd hh\:nn\#")
& ", " & _
"""" & Me!Location.Value & """)"


MsgBox strSQL, , "Check this out!"

CurrentDb().Execute strSQL, dbFailOnError

End Sub

hh\:nn\#")
& ", " & _
.
Click to expand...
.
Click to expand...
 
In both tables (the one updated is called "History"), I
have the DATE & LOCATION field.
Click to expand...

Actually this is more complex than what I suggested. First normal form is
that you keep one item of information in one field. Combining stuff like
this is a Bad Idea and a false economy.
However, currently the VBA debugging window pops up and
the line "CurrentDb ... " is highlighted in yellow.
Click to expand...
I see that you got the DAO references sorted out.

More about the SQL later...

By the way, I do this in my spare time and I have to work during the day,
so I can't guarantee a five-minute response time...


B Wishes


Tim F
 
-----Original Message-----


This is pretty close, but there's an important gotcha when passing dates to
the Jet engine -- they MUST be formatted correctly and that means either
(a) USA type "\#mm\/dd\/yyyy\#" or (b) ISO standard "\#yyyy\-mm\-dd\#".

The upshot is that users in Norther Merica can cross their fingers and copy
the contents of a text box into the SQL, but the rest of the world will
store January 5th instead of May 1st. That is why I used the Format()
function in my original post.

B Wishes


Tim F

.
Click to expand...

Thanks for the info, Tim. I didn't know about using Format
and didn't see it in the help file or the Google search I
did for 'Insert Into'. I slapped together a table and
form/code to sort it out. I'll try to be more 'Global' in
the future <grin - Pun intended>

Steve
 
I didn't know about using Format
and didn't see it in the help file or the Google search I
did for 'Insert Into'.
Click to expand...

It's documented under 'Date literals in SQL' -- try looking in help for
"Date"

All the best


Tim F
 
Back
Top