One Click - Two Events

O

Olu Solaru

Scenario - I have form that contains one combo box, twelve text boxes and
two command buttons.
The combo box upon selection, automatically populates the text boxes with
the data. One command button(cmdSumbit) adds the data in the text boxes to
a table, and the other closes the form (cmdClose).

Want I want to do is to generate a report based on the me clicking the
cmdSubmit button. This way in addition to adding the data in the textboxes
to a table, I would also like to be able to generate a report based on the
contents of those textboxes.

Basically running two events with one click. Below is a code for my
cmdSubmit button. Where within this code, will I place the procedure to run
a report.

Private Sub cmdSubmit_Click()

On Error GoTo Err_cmdSubmit_Click

Dim rstOrder As ADODB.Recordset

Set rstOrder = New ADODB.Recordset

rstOrder.Open "tblLotNumber", CurrentProject.Connection,
adOpenStatic, adLockOptimistic
If rstOrder.Supports(adAddNew) Then
With rstOrder
.AddNew
.Fields("BPRNumber") = cboBpr
.Fields("ProcessDate") = ProcessDate
.Fields("LotNumber") = LotNumber
.Fields("Sop1") = Sop1
.Fields("Sop2") = Sop2
.Fields("Sop3") = Sop3
.Fields("Sop4") = Sop4
.Fields("Sop5") = Sop5
.Fields("Sop6") = Sop6
.Fields("Sop7") = Sop7
.Fields("Sop8") = Sop8
.Fields("Sop9") = Sop9
.Fields("Sop10") = Sop10
.Fields("Sop11") = Sop11
.Fields("Sop12") = Sop12
.Fields("Sop13") = Sop13
.Fields("Sop14") = Sop14
.Fields("Sop15") = Sop15
.Fields("Sop16") = Sop16
.Update
'cmdReset_Click
End With
End If

rstOrder.Close
Set rstOrder = Nothing

' DoCmd.Close

Exit_cmdSubmit_Click:
Exit Sub

Err_cmdSubmit_Click:
MsgBox Err.Description
Resume Exit_cmdSubmit_Click
End Sub
 
D

Douglas J. Steele

Assuming that the report is based on the table that you're adding the data
to, put a DoCmd.OpenReport after you've closed the recordset.
 
O

Ofer Cohen

I don't know which field is the key field in the table that can specify which
record to print, so you'll have to change the key field name to the filter of
the report
Note:
1. I added --- to the code I added
2. Change the fieldname
3. If the KeyField is a text type, then change the filter to
MyFilter = "[KeyFieldInTable] ='" & Me.KeyFieldNameInForm & "'"
4. The report need to be based on the all table, without a filter

Private Sub cmdSubmit_Click()

On Error GoTo Err_cmdSubmit_Click

Dim rstOrder As ADODB.Recordset
'----
Dim MyFilter as String
'----
Set rstOrder = New ADODB.Recordset

rstOrder.Open "tblLotNumber", CurrentProject.Connection,
adOpenStatic, adLockOptimistic
If rstOrder.Supports(adAddNew) Then
With rstOrder
.AddNew
.Fields("BPRNumber") = cboBpr
.Fields("ProcessDate") = ProcessDate
.Fields("LotNumber") = LotNumber
.Fields("Sop1") = Sop1
.Fields("Sop2") = Sop2
.Fields("Sop3") = Sop3
.Fields("Sop4") = Sop4
.Fields("Sop5") = Sop5
.Fields("Sop6") = Sop6
.Fields("Sop7") = Sop7
.Fields("Sop8") = Sop8
.Fields("Sop9") = Sop9
.Fields("Sop10") = Sop10
.Fields("Sop11") = Sop11
.Fields("Sop12") = Sop12
.Fields("Sop13") = Sop13
.Fields("Sop14") = Sop14
.Fields("Sop15") = Sop15
.Fields("Sop16") = Sop16
.Update
'cmdReset_Click
End With
End If

rstOrder.Close
Set rstOrder = Nothing

'-------
MyFilter = "[KeyFieldInTable] =" & Me.KeyFieldNameInForm
Docmd.OpenReport "ReportName" , , , MyFilter
'-------
' DoCmd.Close

Exit_cmdSubmit_Click:
Exit Sub

Err_cmdSubmit_Click:
MsgBox Err.Description
Resume Exit_cmdSubmit_Click
End Sub
 
O

Ofer Cohen

Try

Private Sub cmdSubmit_Click()

On Error GoTo Err_cmdSubmit_Click

Dim rstOrder As ADODB.Recordset
'----
Dim MyFilter as String
'----
Set rstOrder = New ADODB.Recordset

rstOrder.Open "tblLotNumber", CurrentProject.Connection,
adOpenStatic, adLockOptimistic
If rstOrder.Supports(adAddNew) Then
With rstOrder
.AddNew
.Fields("BPRNumber") = cboBpr
.Fields("ProcessDate") = ProcessDate
.Fields("LotNumber") = LotNumber
.Fields("Sop1") = Sop1
.Fields("Sop2") = Sop2
.Fields("Sop3") = Sop3
.Fields("Sop4") = Sop4
.Fields("Sop5") = Sop5
.Fields("Sop6") = Sop6
.Fields("Sop7") = Sop7
.Fields("Sop8") = Sop8
.Fields("Sop9") = Sop9
.Fields("Sop10") = Sop10
.Fields("Sop11") = Sop11
.Fields("Sop12") = Sop12
.Fields("Sop13") = Sop13
.Fields("Sop14") = Sop14
.Fields("Sop15") = Sop15
.Fields("Sop16") = Sop16
.Update
'cmdReset_Click
End With
End If

rstOrder.Close
Set rstOrder = Nothing

'-------
MyFilter = "[BPRNumber] =" & Me.cboBpr
Docmd.OpenReport "ReportName" , , , MyFilter
'-------
' DoCmd.Close

Exit_cmdSubmit_Click:
Exit Sub

Err_cmdSubmit_Click:
MsgBox Err.Description
Resume Exit_cmdSubmit_Click
End Sub

--
Good Luck
BS"D


Olu Solaru said:
The Key Field is the BPR Number field.

Ofer Cohen said:
I don't know which field is the key field in the table that can specify which
record to print, so you'll have to change the key field name to the filter of
the report
Note:
1. I added --- to the code I added
2. Change the fieldname
3. If the KeyField is a text type, then change the filter to
MyFilter = "[KeyFieldInTable] ='" & Me.KeyFieldNameInForm & "'"
4. The report need to be based on the all table, without a filter

Private Sub cmdSubmit_Click()

On Error GoTo Err_cmdSubmit_Click

Dim rstOrder As ADODB.Recordset
'----
Dim MyFilter as String
'----
Set rstOrder = New ADODB.Recordset

rstOrder.Open "tblLotNumber", CurrentProject.Connection,
adOpenStatic, adLockOptimistic
If rstOrder.Supports(adAddNew) Then
With rstOrder
.AddNew
.Fields("BPRNumber") = cboBpr
.Fields("ProcessDate") = ProcessDate
.Fields("LotNumber") = LotNumber
.Fields("Sop1") = Sop1
.Fields("Sop2") = Sop2
.Fields("Sop3") = Sop3
.Fields("Sop4") = Sop4
.Fields("Sop5") = Sop5
.Fields("Sop6") = Sop6
.Fields("Sop7") = Sop7
.Fields("Sop8") = Sop8
.Fields("Sop9") = Sop9
.Fields("Sop10") = Sop10
.Fields("Sop11") = Sop11
.Fields("Sop12") = Sop12
.Fields("Sop13") = Sop13
.Fields("Sop14") = Sop14
.Fields("Sop15") = Sop15
.Fields("Sop16") = Sop16
.Update
'cmdReset_Click
End With
End If

rstOrder.Close
Set rstOrder = Nothing

'-------
MyFilter = "[KeyFieldInTable] =" & Me.KeyFieldNameInForm
Docmd.OpenReport "ReportName" , , , MyFilter
'-------
' DoCmd.Close

Exit_cmdSubmit_Click:
Exit Sub

Err_cmdSubmit_Click:
MsgBox Err.Description
Resume Exit_cmdSubmit_Click
End Sub

--
Good Luck
BS"D


Olu Solaru said:
Scenario - I have form that contains one combo box, twelve text boxes and
two command buttons.
The combo box upon selection, automatically populates the text boxes with
the data. One command button(cmdSumbit) adds the data in the text boxes to
a table, and the other closes the form (cmdClose).

Want I want to do is to generate a report based on the me clicking the
cmdSubmit button. This way in addition to adding the data in the textboxes
to a table, I would also like to be able to generate a report based on the
contents of those textboxes.

Basically running two events with one click. Below is a code for my
cmdSubmit button. Where within this code, will I place the procedure to run
a report.

Private Sub cmdSubmit_Click()

On Error GoTo Err_cmdSubmit_Click

Dim rstOrder As ADODB.Recordset

Set rstOrder = New ADODB.Recordset

rstOrder.Open "tblLotNumber", CurrentProject.Connection,
adOpenStatic, adLockOptimistic
If rstOrder.Supports(adAddNew) Then
With rstOrder
.AddNew
.Fields("BPRNumber") = cboBpr
.Fields("ProcessDate") = ProcessDate
.Fields("LotNumber") = LotNumber
.Fields("Sop1") = Sop1
.Fields("Sop2") = Sop2
.Fields("Sop3") = Sop3
.Fields("Sop4") = Sop4
.Fields("Sop5") = Sop5
.Fields("Sop6") = Sop6
.Fields("Sop7") = Sop7
.Fields("Sop8") = Sop8
.Fields("Sop9") = Sop9
.Fields("Sop10") = Sop10
.Fields("Sop11") = Sop11
.Fields("Sop12") = Sop12
.Fields("Sop13") = Sop13
.Fields("Sop14") = Sop14
.Fields("Sop15") = Sop15
.Fields("Sop16") = Sop16
.Update
'cmdReset_Click
End With
End If

rstOrder.Close
Set rstOrder = Nothing

' DoCmd.Close

Exit_cmdSubmit_Click:
Exit Sub

Err_cmdSubmit_Click:
MsgBox Err.Description
Resume Exit_cmdSubmit_Click
End Sub
Click to expand...
Click to expand...
Click to expand...
 
O

Olu Solaru

The Key Field is the BPR Number field.

Ofer Cohen said:
I don't know which field is the key field in the table that can specify which
record to print, so you'll have to change the key field name to the filter of
the report
Note:
1. I added --- to the code I added
2. Change the fieldname
3. If the KeyField is a text type, then change the filter to
MyFilter = "[KeyFieldInTable] ='" & Me.KeyFieldNameInForm & "'"
4. The report need to be based on the all table, without a filter

Private Sub cmdSubmit_Click()

On Error GoTo Err_cmdSubmit_Click

Dim rstOrder As ADODB.Recordset
'----
Dim MyFilter as String
'----
Set rstOrder = New ADODB.Recordset

rstOrder.Open "tblLotNumber", CurrentProject.Connection,
adOpenStatic, adLockOptimistic
If rstOrder.Supports(adAddNew) Then
With rstOrder
.AddNew
.Fields("BPRNumber") = cboBpr
.Fields("ProcessDate") = ProcessDate
.Fields("LotNumber") = LotNumber
.Fields("Sop1") = Sop1
.Fields("Sop2") = Sop2
.Fields("Sop3") = Sop3
.Fields("Sop4") = Sop4
.Fields("Sop5") = Sop5
.Fields("Sop6") = Sop6
.Fields("Sop7") = Sop7
.Fields("Sop8") = Sop8
.Fields("Sop9") = Sop9
.Fields("Sop10") = Sop10
.Fields("Sop11") = Sop11
.Fields("Sop12") = Sop12
.Fields("Sop13") = Sop13
.Fields("Sop14") = Sop14
.Fields("Sop15") = Sop15
.Fields("Sop16") = Sop16
.Update
'cmdReset_Click
End With
End If

rstOrder.Close
Set rstOrder = Nothing

'-------
MyFilter = "[KeyFieldInTable] =" & Me.KeyFieldNameInForm
Docmd.OpenReport "ReportName" , , , MyFilter
'-------
' DoCmd.Close

Exit_cmdSubmit_Click:
Exit Sub

Err_cmdSubmit_Click:
MsgBox Err.Description
Resume Exit_cmdSubmit_Click
End Sub

--
Good Luck
BS"D


Olu Solaru said:
Scenario - I have form that contains one combo box, twelve text boxes and
two command buttons.
The combo box upon selection, automatically populates the text boxes with
the data. One command button(cmdSumbit) adds the data in the text boxes to
a table, and the other closes the form (cmdClose).

Want I want to do is to generate a report based on the me clicking the
cmdSubmit button. This way in addition to adding the data in the textboxes
to a table, I would also like to be able to generate a report based on the
contents of those textboxes.

Basically running two events with one click. Below is a code for my
cmdSubmit button. Where within this code, will I place the procedure to run
a report.

Private Sub cmdSubmit_Click()

On Error GoTo Err_cmdSubmit_Click

Dim rstOrder As ADODB.Recordset

Set rstOrder = New ADODB.Recordset

rstOrder.Open "tblLotNumber", CurrentProject.Connection,
adOpenStatic, adLockOptimistic
If rstOrder.Supports(adAddNew) Then
With rstOrder
.AddNew
.Fields("BPRNumber") = cboBpr
.Fields("ProcessDate") = ProcessDate
.Fields("LotNumber") = LotNumber
.Fields("Sop1") = Sop1
.Fields("Sop2") = Sop2
.Fields("Sop3") = Sop3
.Fields("Sop4") = Sop4
.Fields("Sop5") = Sop5
.Fields("Sop6") = Sop6
.Fields("Sop7") = Sop7
.Fields("Sop8") = Sop8
.Fields("Sop9") = Sop9
.Fields("Sop10") = Sop10
.Fields("Sop11") = Sop11
.Fields("Sop12") = Sop12
.Fields("Sop13") = Sop13
.Fields("Sop14") = Sop14
.Fields("Sop15") = Sop15
.Fields("Sop16") = Sop16
.Update
'cmdReset_Click
End With
End If

rstOrder.Close
Set rstOrder = Nothing

' DoCmd.Close

Exit_cmdSubmit_Click:
Exit Sub

Err_cmdSubmit_Click:
MsgBox Err.Description
Resume Exit_cmdSubmit_Click
End Sub
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Compile Error: Label Not Defined 2
Auto Populate Text Fields via Combo-Box Selection issue 5

Top