Access database code help 1

[arisythila]

Hello, Im new to the forums. I've read through a few of the posts, and decided to ask a question myself. Im in the middle of doing a database for a friend. He wants there to be a way if there is any changes made to a file, to input who did it, and when. So he can keep tract of the last person that made changes in a file. so far, I got pretty much everything setup, Im just having trouble with this code. Im pretty use to C, and that was back in the day.

I made an update button that updates the current file they are in, and I tried to add some code, that would say who the current user was, and try to write it to a field in the table. But no luck, here are some screenies.

http://img469.imageshack.us/img469/6632/screen6zl.jpg

http://img47.imageshack.us/img47/4301/tables6aw.jpg

Here is the code, I Wrote for the update button.

////--------------////
Private Sub SaveRecord_Click()
On Error GoTo Err_SaveRecord_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_SaveRecord_Click:
Exit Sub

Me.Updatedby = CurrentUser
Me.Updateddate = Date


Err_SaveRecord_Click:
MsgBox Err.Description
Resume Exit_SaveRecord_Click

End Sub
////----------////

Please let me know if you have any suggestions. I could really use the help.

~Michael

 

[fneily]

Get rid of:
Me.Updatedby = CurrentUser
Me.Updateddate = Date

Add to the top of your OnClick event:

Dim DB As DAO.Database
Dim RS As DAO.Recordset

Set DB = CurrentDb()
Set RS = DB.OpenRecordset("HistoryTable", dbOpenDynaset)
RS.AddNew
RS![Region] = "Whatever"
RS![UpdatedBy] = Me![LastName]
RS![CompanyName] = Me![Agency]
RS![UpdatedDate] = DATE()
RS![WorkPhone] = Me![BusinessNumber]
RS.Update
End If
RS.Close
DB.Close
Set RS = Nothing
Set DB = Nothing
REST OF YOUR CODE
End Sub

 

[arisythila]

Awesome this work good, Anyway to get it so if they do update a record without clicking the Update button, it will also post that they updated the file.

Example.

you have the record numbers down at the bottom, If I update a table, and click on another record on the bottom, it update the file without telling me who updated it.

~Mike

 

[fneily]

Goto Design view of your form. Click on the little square in the upper left. You'll see a black square appear - this means the form is selected. Bring up the property sheet (button at top right looking like a hand holding paper). Click on the Event tab. Click in the box next to AfterUpdate. Click on the button with the three dots. Select Code Builder and copy in the code that I gave you. It'll then add a history record when they use the Navigation bar.

 

[arisythila]

I used that code you gave me. Its giving me an error at the "End If" line.

Compile Error:
End If without block If

This is what it looks like.

////---BEGINNING---////

Private Sub Form_AfterUpdate()

Dim DB As DAO.Database
Dim RS As DAO.Recordset

Set DB = CurrentDb()
Set RS = DB.OpenRecordset("HistoryTable", dbOpenDynaset)
RS.AddNew
RS![Region] = "Whatever"
RS![Updatedby] = Me![LastName]
RS![CompanyName] = Me![Agency]
RS![Updateddate] = Date
RS![WorkPhone] = Me![BusinessNumber]
RS.Update
End If <---- Error
RS.Close
DB.Close
Set RS = Nothing
Set DB = Nothing

End Sub

////----END----////

 

[arisythila]

Im an idiot, I got it fixed.. Works like a champ now. I was wondering what you might suggest doing if I wanted to print a certian file, But it comes up as two pages, because its too wide. I'm not exactly sure how to go about printing the information if they needed without wasting paper.

I was thinking of making a new form and just formatting it for 8x11 piece of paper. Maybe trying to get the print button to print that form instead of the one they will use.

any ideas?

~Mike

 

[fneily]

Did you try Landscape? File - Page Setup

 

[arisythila]

With landscape it is not Tall enough, and in portriat it is not wide enough. Go figure.