Other Form Opens with Current Form Data upon Btn Press

[KrenzyRyan]

Hi guys, I am very much a novice to VBA code and trying to learn so any help would be appreciated!

I am changing to a new employee review form for 2011 but would still like to have the ease of access between the new form and the old form.

The new form is listed below as Frm_ReviewInput9Block and the code to follow corresponds to the "Get Review" button.

Right now the button will close the 9Block form and open the old form if 2010 is selected from "Year."

Here is where I am stuck. I would like the Associate name, interval, and year to populate in the 2010 form and further gather the data required (Call ClearData, Call PopulateData, Call UpdateTotal) when the form opens.

It is a bit difficult to describe what I am trying to create so I have provided the pictures as follows.

Any help would be very much appreciated!









Private Sub btnGetReview_Click()
Me.RadEnableUpdate = False
Me.btnSubmitNotes.Enabled = False


Dim selected As String
selected = CmboBxYr
DoCmd.Close
Select Case selected
Case "2010"


DoCmd.OpenForm ("Frm_ReviewInput")
'if it equals other value
Case "Other Value"
DoCmd.OpenForm ("Frm_ReviewInput9Block")
End Select

Call ClearData
Call PopulateData
Call UpdateTotal

End Sub

[kennejd]

It could be something like this:

dim frm as form
frm = forms![Frm_ReviewInput]
frm![Associate] = me.Associate
frm![Interval] = me.Interval

and so on

[KrenzyRyan]

I receive compile error as it highlights "frm ="

[kennejd]

Sorry...it should be:

set frm = forms![Frm_ReviewInput]

[KrenzyRyan]

Kenne, that worked great! The fields are now populated on the form!

One last thing, how run the btn "get review" after the form opens and populates the fields (associate, interval, etc)?

[kennejd]

You'll want to add some code to open the form/report that displays the review. I'm guessing the code already exists. Do you need help writing the filter or where part?

[kennejd]

I just re-read your original question. You could add
Call ClearData
Call PopulateData
Call UpdateTotal
to the on open event of the old form.

[KrenzyRyan]

Gotcha...I tried this and it doesn't seem to "see" that the combo boxes are filled (and they are), does my code look okay?:

Private Sub Form_Open(Cancel As Integer)
Me.RadEnableUpdate = False
Me.btnSubmitNotes.Enabled = False
Me.cmboBxAssociate.SetFocus
Call ClearData
Call PopulateData
Call UpdateTotal
End Sub


Edit: Actually I think it's a timing thing...it opens the form...runs OpenEvent (calls cleardata, etc)...then tries setting the combo boxes...

Not sure how to fix this.

[KrenzyRyan]

Here is how I have it set up. It is opening Frm_ReviewInput (my old form), then trying to run "call populate data" but fails because one thing is happening before the next...

New Form:

        

DoCmd.OpenForm ("Frm_ReviewInput")
        
        Dim frm As Form
        Set frm = Forms![Frm_ReviewInput]
        frm![cmboBxAssociate] = Me.cmboBxAssociate
        frm![cmboBxInterval] = Me.cmboBxInterval
        frm![CmboBxYr] = Me.CmboBxYr

Old Form open:

Private Sub Form_Open(Cancel As Integer)

Me.RadEnableUpdate = False
Me.btnSubmitNotes.Enabled = False
Me.cmboBxAssociate.SetFocus

Call ClearData
Call PopulateData
Call UpdateTotal


End Sub

This error message pops up each time I try getting the old review form with the setup listed above:

'ENSURE THAT ASSOCIATE NAME, YEAR, & INTERVAL ARE SELECTED.
If Not IsNull(Me.CmboBxYr) Then
    intYr = Me.CmboBxYr
Else
    MsgBox "Please select a review year from the drop down and try again.", vbCritical, "Error"
    Exit Sub
End If

[KrenzyRyan]

If I use "On Timer" event, it works perfectly. I need to find another option though, as it keeps refreshing over and over.