New Info to overwrite old; Preview w/print button

[Sizemore5000]

Firstly, I know I'm going to sound like a complete idiot, but here goes.

OK, so I have an employee database that I'm working on. A form opens to view employee details, and I have buttons to perform different actions on the employee, like "change supervisor", which opens another form.
This form collects certain information for the action (stored in fields in the same table), and then users click a button to "Go on", opening another form (this one is always 8.5" x 11", in print preview view).

Here's where I get stuck. I want 2 things from these forms.

1. The form doesn't have to open in preview, but I want the user to see the page as it will be printed, and also to have a button to print page at the same time.

2. On Close, I want the old information to save to other fields, the new information to overwrite the old, then the new info fields to be erased.

I had this in the Event Procedure, but it's doen't work:
(NewSupervisor & EffectiveDate are the info collected in the second form)

Private Sub Form_Close()
Me!OldSupervisor = Me!Supervisor
Me!Supervisor = Me!NewSupervisor
Me!NewSupervisor = " "
Me!SupDate = Me!EffectiveDate
Me!EffectiveDate = " "
End Sub

Any help is very appreciated.

 

[pbaldy]

Most of us don't print forms, we print reports. I feel I have more control over how a report looks than printing a form. So I guess my recommendation for #1 is to design a report that looks like you want, and open it in preview mode.

For #2, what "doesn't work". Do you get an error or does it simply not do what you want? I don't see any code errors. If some of the info is on a different form, you'd have to refer to that form rather than "Me".

 

[Sizemore5000]

Question #1 was not that big a deal... I have the form open in print preview, users can see it as it will print out, I just was hoping I could add a usable "print" button simultaneously with the form in preview. No big deal on that, though.

#2, however: I get a
"Run-time error '2448': You can't assign a value to this object."

The info is in a different place, but that place is a table. When I use this code instead:

Table_Member!OldSupervisor = Me!Supervisor
Table_Member!Supervisor = Me!NewSupervisor
Table_Member!NewSupervisor = " "
Table_Member!SupDate = Me!EffectiveDate
Table_Member!EffectiveDate = " "

I get this error:
"Run-time error '424': Object required"

...I think I see the problem (Table interaction), but I don't know how to fix it...

 

[pbaldy]

The first error may be because of the event. You can try that in the unload event instead of the close event and see if it works. Your second effort will not work, as you can't refer to a table that way. You'd either have to open a recordset on the table or execute SQL (UPDATE TableName...). Let me know if you need help with either of those, if changing the event doesn't work.

 

[Sizemore5000]

I tried to change the event to unload, but no joy.

I'd love to have some help with opening a recordset on the table or the SQL - I don't know much to begin with & definately don't know anything about either of those procedures.

Thank you, by the way. 8)

 

[pbaldy]

Something like this:

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  strSQL = "SELECT ..." 'see below **
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  rs.Edit
  rs!OldSupervisor = Me!Supervisor
  rs!Supervisor = Me!NewSupervisor
  'others here
  rs.Update

  set rs = nothing
  set db = nothing

**this would be a SELECT statement that brought back the specific record you want to modify. You'd have to concatenate in a reference to the form to identify that record. Something like:

strSQL = "SELECT Blah FROM TableName WHERE IDField = " & Me.IDField

 

[Sizemore5000]

After my last post, I figured I would try one more thing:

Private Sub Form_Close()
Form_Form_CROInfo!OldSupervisor = Me!Supervisor
Form_Form_CROInfo!Supervisor = Me!NewSupervisor
Form_Form_CROInfo!NewSupervisor = " "
Form_Form_CROInfo!SupDate = Me!EffectiveDate
Form_Form_CROInfo!EffectiveDate = "1/1/2000"
End Sub

Form_CROInfo (the parent of this printed form) is sourced by Table_Member, and so ended up working.

However, if I were trying to update a different table, your method looks extremely efficient. Should I use the recordset method anyhow (for efficiency)? I'm going to play with it a bit, try to familiarize.

One thing: in the SQL statement, what instead of 'blah'? I've used this type of statement before in combo boxes and such, but this is a little different.

Thank you so much! You have been extremely helpful.

 

[pbaldy]

I wouldn't bother with the recordset if you already have a form open that's bound to the target table. The way you're doing it is fine. For the SQL, it could be as simple as

SELECT * FROM...

but if there's a lot of fields, that would be less efficient, so you'd only call for the fields you want to view or update, so maybe

SELECT OldSupervisor, Supervisor FROM...