using openargs to set the recordset of form?

[Guest]

I have in one unbound form some fields that gather data that I use to create
an sql statement that I want to open another form with. I'm trying to call
the form with the sql statement as the openargs value. On the load event of
the form I'm opening I'm assigning openargs to recordset see below

From the unbound form click even of a command button

'after I've created the sql statement stored in sql
DoCmd.OpenForm "frmEquipmentFound", acFormDS, , , , , strsql

From the load evet of frmEquipmentFound
Private Sub Form_Load()
If IsNull(Me.OpenArgs) Then
MsgBox "This Form Can Only Be Called From The Equipment Find Form"
DoCmd.Close acForm, "frmEquipmentFound"
Else
Me.Recordset = Me.OpenArgs
End If
End Sub

I've tried surrounding me.openargs in quote marks "'" & me.openargs & "'"
also but either way I get the error "Object variable or With block variable
not set"


The sql statement is not the problem because I put a break in the code after
it was formed and pasted it into the immediate window ?strsql then copied the
results into the query builder sql window and it found records

besides that I'm checking the sql for records found using by setting a
dao.recordset var to the sql statement set rs =
currentdb.openrecordset(strsql) then checking rs.eof if it is at eof I don't
open form I give no records found message instead.

BOTTOM LINE: How can I create an sql statement in one form to use as the
record set of another form?

 

[fredg]

I have in one unbound form some fields that gather data that I use to create
an sql statement that I want to open another form with. I'm trying to call
the form with the sql statement as the openargs value. On the load event of
the form I'm opening I'm assigning openargs to recordset see below

From the unbound form click even of a command button

'after I've created the sql statement stored in sql
DoCmd.OpenForm "frmEquipmentFound", acFormDS, , , , , strsql

From the load evet of frmEquipmentFound
Private Sub Form_Load()
If IsNull(Me.OpenArgs) Then
MsgBox "This Form Can Only Be Called From The Equipment Find Form"
DoCmd.Close acForm, "frmEquipmentFound"
Else
Me.Recordset = Me.OpenArgs
End If
End Sub

I've tried surrounding me.openargs in quote marks "'" & me.openargs & "'"
also but either way I get the error "Object variable or With block variable
not set"

The sql statement is not the problem because I put a break in the code after
it was formed and pasted it into the immediate window ?strsql then copied the
results into the query builder sql window and it found records

besides that I'm checking the sql for records found using by setting a
dao.recordset var to the sql statement set rs =
currentdb.openrecordset(strsql) then checking rs.eof if it is at eof I don't
open form I give no records found message instead.

BOTTOM LINE: How can I create an sql statement in one form to use as the
record set of another form?

Wrong Form property and wrong event.

Use the Form's Open event to set the RECORDSOURCE property (not
Recordset).

Code the Form's Open Event:
Private Sub Form_Open(Cancel As Integer)
If IsNull(Me.OpenArgs) Then
MsgBox "This Form Can Only Be Called From The Equipment Find Form"
Cancel = True
Else
Me.RecordSource = Me.OpenArgs
End If
End Sub

Notice use of Cancel = True to stop the opening of the form if
OpenArgs is not there.

 

[Guest]

brain fart... sorry

Thanks for the help though.

Wrong Form property and wrong event.

Use the Form's Open event to set the RECORDSOURCE property (not
Recordset).

Code the Form's Open Event:
Private Sub Form_Open(Cancel As Integer)
If IsNull(Me.OpenArgs) Then
MsgBox "This Form Can Only Be Called From The Equipment Find Form"
Cancel = True
Else
Me.RecordSource = Me.OpenArgs
End If
End Sub

Notice use of Cancel = True to stop the opening of the form if
OpenArgs is not there.