Create a Form That updates one table and inserts edits into a new table (1 Viewer)

[AccessWillKill]

Hello, i'm new to the forum and i've been working on this problem for the best part of a week. So far all was going well until i came to this issue.

I've recently started a new job which involves using access 2003 and VBA and was given a few tasks to do, as follows

• find or create code that can get a users login name (done)
• make a form that takes a snapshot of a user selected field for editing (done)
• Make the form save any edits to the selected fields in the form table (done)
• Make the form get all the edits from the selected table and log them in a new table (very stuck)

I have successfully created a loop that cycles through the fields and gets the values inserted whether edited or not. And i have an SQL Statement that inserts fields into the edited field and the username of the person who inserted them (although the loop and the statement are not linked yet, as i have tested them individually not together yet)

However my loop cannot compare the value of the field in the loop with what was originally in the field.

i shall give you the code of the 'save' button i have created to perform this and comment what it does and the variables etc to give you an idea. if you need anymore information just say.

Private Sub save_click()

Dim SQLstr As String
Dim Field As Integer

[COLOR="Green"]' prikey string1 and dateone are global variables Me!field1edit et all are the values when the save button is clicked, whether edited or not [/COLOR]
PriKey = Chr(34) & Me!PrimaryKeyFieldEdit & Chr(34)
String1 = Chr(34) & Me!Field1Edit & Chr(34)
DateOne = Me!Date1Edit

[COLOR="green"]' this is the update string which updates the fields in the table[/COLOR]
SQLstr = "UPDATE TwentyFieldTable SET TwentyFieldTable.field1 = " & [String1] & ", TwentyFieldTable.Date1 = #" & [DateOne] & "# WHERE (((TwentyFieldTable.PrimaryKeyField)= " & [PriKey] & " )); "
Debug.Print SQLstr

[COLOR="green"]'this is the for loop which is incomplete[/COLOR]
  For Field = 1 To Me.Count - 1

[COLOR="green"]'this bit breaks the code[/COLOR]
      If Me(Field).Value <> Me(Field).Value Then
[COLOR="green"]'this bit tells me whats value is in the field, for testing purposes mainly[/COLOR]
         MsgBox ("field contains " & Me(Field).Value)
[COLOR="green"]'this is where IF the field has been altered the SQL Insert Statement (below will be placed)[/COLOR]

   [COLOR="green"]   'elseIf  field is the same then end if and continue the loop[/COLOR]
      End If
   Next Field
   ' Toggle the flag.
   status = Not status

[COLOR="green"]'this is my insert statement with test values except getusername(), which goes to a module to get the users login name[/COLOR]

INSERT INTO project2 ( EditedField, oldvalue, newvalue, username )values( 'field1' , 'test' , 'test2' , getusername()) ;


DoCmd.SetWarnings False
DoCmd.RunSQL SQLstr
'DoCmd.Close , frmMassiveEdit
End Sub

like i said i've really struggled with this part, i can't get my head around it.

 

[Dairy Farmer]

Two Tables with the same fields
Second table can include a date/time/user stamp

Two Forms based on the two tables.
Form1 is the input form (the one the user sees)
Form2 is where the edits are written. It has one (or more) extra fields that date/time stamp the edit.

Private Sub Button_Save_Click()
     DoCmd.OpenForm "Form2", acNormal, "", "", acEdit, acHidden
          Forms("Form2").Field21 = Me.Field11
          Forms("Form2").Field22 = Me.Field12
          Forms("Form2").Field23 = Me.Field13
     DoCmd.RunCommand acCmdSaveRecord
     DoCmd.Close acForm, "Form2"
End Sub

 

[AccessWillKill]

Sorry i didn't make it specific in the code, what i'm being asked to do is when the user clicks the save button the button will update the table (called Twentyfieldtable) with anything thats been edited in the record and what has been edited will go to the edited table (called project2) which holds the following:

Number (autonumber)
FieldEdited
Oldfield Value
Newfield Value
Username

the SQL Insert statement i have can do fills the project 2 table with the username, but not with the values i want yet and the loop loops through the fields but doesn't compare the old value with the new edited value.

this is the way i've been told it needs to be done. Sorry for not saying before hand