ADOB RS, want to pass variable from one form to another

[alh1002]

I have one form (Form3)which has the following code


Private Sub ladder_date_DblClick(Cancel As Integer)

DoCmd.OpenForm "NAV_bound", , , "ladder_date = " & [Forms]![FORM3]![ladder_date]


End Sub


where ladder_date is the date on the form. I then want it to open the form NAV_bound. And I want NAV_bound to open on the ladder_date that was double clicked on from the previous form (Form3)

For Form NAV_bound

I load the form as such


Private Sub Form_Load()

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CurrentProject.Path & "\test.mdb;"

'create a new connection instance and open it using the connection string
Set cnNAV1 = New ADODB.Connection
cnNAV1.Open strConnection

'create a new instance of a recordset
Set rsNAV1 = New ADODB.Recordset

'set various properties of the recordset
With rsNAV1
'specify a cursortype and lock type that will allow updates
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
'open the recordset based on tblContacts table using the existing connection
.Open "tblPerformance", cnNAV1


End With

'if the recordset is empty
If rsNAV1.BOF And rsNAV1.EOF Then
Exit Sub
Else

End If
Set Me.Recordset = rsNAV1
Me.dtladder_date.ControlSource = "ladder_date"

'close the database connection and release it from Memory

End Sub


and it always open to first recod in the table, ignoring the date I double click on the previous form. How can I load the form such that I get the desired result.

Note: using
Dim cnNAV1 As ADODB.Connection
Dim rsNAV1 As ADODB.Recordset

 

[rjoubert]

On your first form (Form3), instead of

DoCmd.OpenForm "NAV_bound", , , "ladder_date = " & [Forms]![FORM3]![ladder_date]

use this...

DoCmd.OpenForm "NAV_bound", , , [Forms]![FORM3]![ladder_date]

Then, on your second form (Nav_bound), get your ladder_date value by accessing Me.OpenArgs.

Good Luck!

 

[alh1002]

where does the Me.OpenArgs go?
I get an error when I put it with the set that it is invalid use of property
Set Me.Recordset = rsNAV1
Me.OpenArgs
'then everything else I want to set

 

[rjoubert]

On your second form in the line where you're setting the Me.dtladder_date.ControlSource = "ladder_date", you want the ladder_date value from your first form, correct? If that's the case, you'll need to replace "ladder_date" with Me.OpenArgs.

 

[rjoubert]

Also, your ladder_date_dblclick procedure (on Form3) appears to be missing something. Try this...

DoCmd.OpenForm "NAV_bound", , , , , , Me.ladder_date.Value

I believe you had your ladder_date value passed in as the linkcriteria parameter.

 

[alh1002]

ok, I am debugging this, but I am incorrectly setting the RS for the form NAV_bound

I have

Set Me.Recordset = rsNAV1
'Me.OpenArgs

Dim strDate As String
strDate = Me.OpenArgs
Debug.Print strDate


and the correct date (from Form3) is coming out.

but on the form NAV_bound the field for date has no name,

and all of the other fields have the values of the first value in the RS (which is the first value in the source table)

 

[rjoubert]

I'm assuming your rsNav1 is populated correctly? Have you verified this? If so, try...

me.RecordSource=rsNav1