set form.recordsource with a query string

[Dsperry101 via AccessMonster.com]

Hey y'all

I'm trying to make a form recordsource from a previous form
combo box , I keep getting "you cancelled previous operation"
regardless of which event I put the following code:

Private Sub cmdGo_Click()
MWONumberSelected = cboMwoselect.Column(0)
''' MsgBox MWONumberSelected
Dim sqlrecordselect As String
sqlrecordselect = "SELECT DISTINCT * FROM tblNewMWO WHERE " _
& "tblNewMWO.[Work Order Number] = " _
& MWONumberSelected & ";"
Form_frmMWOFull.RecordSource = sqlrecordselect
MsgBox sqlrecordselect
DoCmd.OpenForm "frmMWOFull"
End Sub

or in the opening form current event

Dim sqlrecordselect As String
sqlrecordselect = "SELECT DISTINCT * FROM tblNewMWO WHERE " _
& "tblNewMWO.[Work Order Number] = " _
& MWONumberSelected & ";"
Form_frmMWOFull.RecordSource = sqlrecordselect
MsgBox sqlrecordselect
End Sub


Thanks

Dan S

 

[Klatuu]

You can't set a property of a form that is not open. I would try passing the
SQL string in the OpenArgs argument of the OpenForm method. And using the
Load event of the form to set the recordsource.

Private Sub cmdGo_Click()
Dim sqlrecordselect As String

MWONumberSelected = cboMwoselect.Column(0)
sqlrecordselect = "SELECT DISTINCT * FROM tblNewMWO WHERE " _
& "tblNewMWO.[Work Order Number] = " _
& MWONumberSelected & ";"
DoCmd.OpenForm "frmMWOFull", , ,sqlrecordselect

End Sub

-------------------
In Load event of frmMWOFull:

Private Sub Form_Load()

If Not IsNull(Me.OpenArgs) Then
Me.RecordSource = Me.OpenArgs
End If

End Sub

 

[Dsperry101 via AccessMonster.com]

kLATUU
PUT IN LOAD AND CURRENT EVENTS FOR THE FORM
If Not IsNull(Me.OpenArgs) Then
Me.RecordSource = Me.OpenArgs
MsgBox Me.OpenArgs
End If
even here
still get error "YOU CANCELLED PREVIOUS OPERATION

You can't set a property of a form that is not open. I would try passing the
SQL string in the OpenArgs argument of the OpenForm method. And using the
Load event of the form to set the recordsource.

Private Sub cmdGo_Click()
Dim sqlrecordselect As String

MWONumberSelected = cboMwoselect.Column(0)
sqlrecordselect = "SELECT DISTINCT * FROM tblNewMWO WHERE " _
& "tblNewMWO.[Work Order Number] = " _
& MWONumberSelected & ";"
DoCmd.OpenForm "frmMWOFull", , ,sqlrecordselect

End Sub

-------------------
In Load event of frmMWOFull:

Private Sub Form_Load()

If Not IsNull(Me.OpenArgs) Then
Me.RecordSource = Me.OpenArgs
End If

End Sub

Hey y'all

[quoted text clipped - 27 lines]

Dan S

 

[Dsperry101 via AccessMonster.com]

also had to add a few comma's

MWONumberSelected = cboMwoselect.Column(0)
Dim sqlrecordselect As String
sqlrecordselect = "SELECT DISTINCT * FROM tblNewMWO WHERE " _
& "tblNewMWO.[Work Order Number] = " _
& MWONumberSelected & ";"
' MsgBox sqlrecordselect
', Form_frmMWOFull.RecordSource = sqlrecordselect
DoCmd.OpenForm "frmMWOFull", , , , , , sqlrecordselect

You can't set a property of a form that is not open. I would try passing the
SQL string in the OpenArgs argument of the OpenForm method. And using the
Load event of the form to set the recordsource.

Private Sub cmdGo_Click()
Dim sqlrecordselect As String

MWONumberSelected = cboMwoselect.Column(0)
sqlrecordselect = "SELECT DISTINCT * FROM tblNewMWO WHERE " _
& "tblNewMWO.[Work Order Number] = " _
& MWONumberSelected & ";"
DoCmd.OpenForm "frmMWOFull", , ,sqlrecordselect

End Sub

-------------------
In Load event of frmMWOFull:

Private Sub Form_Load()

If Not IsNull(Me.OpenArgs) Then
Me.RecordSource = Me.OpenArgs
End If

End Sub

Hey y'all

[quoted text clipped - 27 lines]

Dan S

 

[Klatuu]

YOu don't need it or want it in the Current event. That will cause you to
reset the record source each time you navigate off the current record.
Without testing it, I can't be sure, but it might cause it to always show
only the first record in the table.

also had to add a few comma's

MWONumberSelected = cboMwoselect.Column(0)
Dim sqlrecordselect As String
sqlrecordselect = "SELECT DISTINCT * FROM tblNewMWO WHERE " _
& "tblNewMWO.[Work Order Number] = " _
& MWONumberSelected & ";"
' MsgBox sqlrecordselect
', Form_frmMWOFull.RecordSource = sqlrecordselect
DoCmd.OpenForm "frmMWOFull", , , , , , sqlrecordselect

You can't set a property of a form that is not open. I would try passing the
SQL string in the OpenArgs argument of the OpenForm method. And using the
Load event of the form to set the recordsource.

Private Sub cmdGo_Click()
Dim sqlrecordselect As String

MWONumberSelected = cboMwoselect.Column(0)
sqlrecordselect = "SELECT DISTINCT * FROM tblNewMWO WHERE " _
& "tblNewMWO.[Work Order Number] = " _
& MWONumberSelected & ";"
DoCmd.OpenForm "frmMWOFull", , ,sqlrecordselect

End Sub

-------------------
In Load event of frmMWOFull:

Private Sub Form_Load()

If Not IsNull(Me.OpenArgs) Then
Me.RecordSource = Me.OpenArgs
End If

End Sub

Hey y'all

[quoted text clipped - 27 lines]

Dan S

 

[Dsperry101 via AccessMonster.com]

Klatuu

There is something wrong with my procedural steps. Here's what I'm
trying to do:
Have a small form open (its OK) with a cboBox that shows a dropdown
list of all
the existing work order numbers (tblNewMWO.[Work Order Number])
, user scrolls and select a number (its in text form),
operator then clicks a push button that
opens a new form frmMWOFull in the _click event
with docmd.openform frmMWOFull
now I want to make the new form have the record that
would be selected
from an existing table tblNewMWO by the previous form dropdown box
selection
then fill in the fields in the form frmMWOFull fields with that record

Hope this makes sense

YOu don't need it or want it in the Current event. That will cause you to
reset the record source each time you navigate off the current record.
Without testing it, I can't be sure, but it might cause it to always show
only the first record in the table.

also had to add a few comma's

[quoted text clipped - 38 lines]

 

[Dsperry101 via AccessMonster.com]

Klatuu

There is something wrong with my procedural steps. Here's what I'm
trying to do:
Have a small form open (its OK) with a cboBox that shows a dropdown
list of all
the existing work order numbers (tblNewMWO.[Work Order Number])
, user scrolls and select a number (its in text form),
operator then clicks a push button that
opens a new form frmMWOFull in the _click event
with docmd.openform frmMWOFull
now I want to make the new form have the record that
would be selected
from an existing table tblNewMWO by the previous form dropdown box
selection
then fill in the fields in the form frmMWOFull fields with that record

Hope this makes sense

YOu don't need it or want it in the Current event. That will cause you to
reset the record source each time you navigate off the current record.
Without testing it, I can't be sure, but it might cause it to always show
only the first record in the table.

also had to add a few comma's

[quoted text clipped - 38 lines]

 

[Klatuu]

I don't really see the need for the command button to open the form. I would
do it in the After Update event of the combo, but in either case, pass the
value of the combo box to the form you are opening in the OpenArgs argument
of the OpenForm method. If the record source will always be the same query
or table, but just with a different filter. So:

DoCmd.OpenForm frmMWOFull, , , Me.cboWorkOrder

Now, in the Load event and only the Load event of frmMWOFull:

If Not IsNull(Me.OpenArgs) Then
Me.Filter = "[Work Order Number] = '" & Me.cboWorkOrder & "'"
Me.FilterOn = True
Else
Me.FilterOn = False
End If

You can just set the recordsource for frmMWOFull to tblNewMWO in design mode
and leave it alone. The filter will then present only those records where
the work order number is the same as passed in the OperArgs from the calling
form.

Klatuu

There is something wrong with my procedural steps. Here's what I'm
trying to do:
Have a small form open (its OK) with a cboBox that shows a dropdown
list of all
the existing work order numbers (tblNewMWO.[Work Order Number])
, user scrolls and select a number (its in text form),
operator then clicks a push button that
opens a new form frmMWOFull in the _click event
with docmd.openform frmMWOFull
now I want to make the new form have the record that
would be selected
from an existing table tblNewMWO by the previous form dropdown box
selection
then fill in the fields in the form frmMWOFull fields with that record

Hope this makes sense

YOu don't need it or want it in the Current event. That will cause you to
reset the record source each time you navigate off the current record.
Without testing it, I can't be sure, but it might cause it to always show
only the first record in the table.

also had to add a few comma's

[quoted text clipped - 38 lines]

Dan S