function assistance

[JohnE]

Hello. I am having a day in which the mind just isn't working. I need to
put the following code into a function (module) but drawing a blank on how to
do it. Just need to pass the one parameter (slip_number) into the function.

Public Sub FindPackingSlip(ByVal slip_number As Long)
On Error GoTo Err_FindPackingSlip

'Need to remove any filters from form
Me.FilterOn = False

'Requery form in case new records were added immediately preceding this call
Me.Requery

'Find the record
Me.RecordsetClone.FindFirst "PackingSlipID = " & slip_number

If Me.RecordsetClone.NoMatch Then
Beep
MsgBox "Could not find Packing Slip # " & slip_number & ".",
vbInformation, "Packing Slip Not Found"
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

Exit Sub

Err_FindPackingSlip:
Beep
MsgBox err.Description, vbExclamation, "Find Packing Slip Error"
Exit Sub
End Sub

Thanks for any help on this.
John

 

[Stefan Hoffmann]

hi John,

Hello. I am having a day in which the mind just isn't working. I need to
put the following code into a function (module) but drawing a blank on how to
do it. Just need to pass the one parameter (slip_number) into the function.

What kind of module?

Basically, the code is okay. I would use this inside the method:

Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
rs.FindFirst "PackingSlipID = " & slip_number
If rs.NoMatch Then
Beep
MsgBox "Could not find Packing Slip # " & _
slip_number & ".", _
vbInformation, "Packing Slip Not Found"
Else
Me.Bookmark = rs.Bookmark
End If

mfG
--> stefan <--

 

[Daryl S]

John -

It sounds like you just need to know how to get the code into the right place.

Since you are using the Me convention, this code should be in the form, not
a stand-alone module. There are a couple ways to open the code window for
the form. It is probably easiest to go into design mode on the form, then in
the Properties window (Property Sheet in Access 2007) choose the Event tab.
If there are any [Event Procedure] events listed, you can go there by
clicking the ... button on that event line. If not, pick any event and use
the drop-list to pick Event Procedure, and then click on the ... button to go
there. You can delete the shell of the procedure you created this way, and
insert your code there.

Hope that helps...

 

[JohnE]

Thanks for the reply. But, this code came from a form and it will be used in
other forms going forward so I was hoping to convert it over to function
(module) so it can be used in multiple places. The Me.--- is what throws me
off when it comes to making or converting to functions. But, as mentioned,
my mind just is not getting wrapped around this for some reason.
John

John -

It sounds like you just need to know how to get the code into the right place.

Since you are using the Me convention, this code should be in the form, not
a stand-alone module. There are a couple ways to open the code window for
the form. It is probably easiest to go into design mode on the form, then in
the Properties window (Property Sheet in Access 2007) choose the Event tab.
If there are any [Event Procedure] events listed, you can go there by
clicking the ... button on that event line. If not, pick any event and use
the drop-list to pick Event Procedure, and then click on the ... button to go
there. You can delete the shell of the procedure you created this way, and
insert your code there.

Hope that helps...
--
Daryl S

Hello. I am having a day in which the mind just isn't working. I need to
put the following code into a function (module) but drawing a blank on how to
do it. Just need to pass the one parameter (slip_number) into the function.

Public Sub FindPackingSlip(ByVal slip_number As Long)
On Error GoTo Err_FindPackingSlip

'Need to remove any filters from form
Me.FilterOn = False

'Requery form in case new records were added immediately preceding this call
Me.Requery

'Find the record
Me.RecordsetClone.FindFirst "PackingSlipID = " & slip_number

If Me.RecordsetClone.NoMatch Then
Beep
MsgBox "Could not find Packing Slip # " & slip_number & ".",
vbInformation, "Packing Slip Not Found"
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

Exit Sub

Err_FindPackingSlip:
Beep
MsgBox err.Description, vbExclamation, "Find Packing Slip Error"
Exit Sub
End Sub

Thanks for any help on this.
John

 

[Stefan Hoffmann]

hi John,

Thanks for the reply. But, this code came from a form and it will be used in
other forms going forward so I was hoping to convert it over to function
(module) so it can be used in multiple places. The Me.--- is what throws me
off when it comes to making or converting to functions. But, as mentioned,

Then simply use a form parameter:

Public Sub Public Sub FindPackingSlip(ASlipNumber As Long, _
AForm As Access.Form)

' Replace Me with AForm.

End Sub

Call it in a form with:

FindPackingSlip Me

Call it in a form for a subform:

FindPackingSlip ctlSubForm.Form

Use it as event property:

=FindPackingSlip([Form])


mfG
--> stefan <--

 

[JohnE]

No further responses needed. I got it figured out.
Thanks.

Thanks for the reply. But, this code came from a form and it will be used in
other forms going forward so I was hoping to convert it over to function
(module) so it can be used in multiple places. The Me.--- is what throws me
off when it comes to making or converting to functions. But, as mentioned,
my mind just is not getting wrapped around this for some reason.
John

John -

It sounds like you just need to know how to get the code into the right place.

Since you are using the Me convention, this code should be in the form, not
a stand-alone module. There are a couple ways to open the code window for
the form. It is probably easiest to go into design mode on the form, then in
the Properties window (Property Sheet in Access 2007) choose the Event tab.
If there are any [Event Procedure] events listed, you can go there by
clicking the ... button on that event line. If not, pick any event and use
the drop-list to pick Event Procedure, and then click on the ... button to go
there. You can delete the shell of the procedure you created this way, and
insert your code there.

Hope that helps...
--
Daryl S

Hello. I am having a day in which the mind just isn't working. I need to
put the following code into a function (module) but drawing a blank on how to
do it. Just need to pass the one parameter (slip_number) into the function.

Public Sub FindPackingSlip(ByVal slip_number As Long)
On Error GoTo Err_FindPackingSlip

'Need to remove any filters from form
Me.FilterOn = False

'Requery form in case new records were added immediately preceding this call
Me.Requery

'Find the record
Me.RecordsetClone.FindFirst "PackingSlipID = " & slip_number

If Me.RecordsetClone.NoMatch Then
Beep
MsgBox "Could not find Packing Slip # " & slip_number & ".",
vbInformation, "Packing Slip Not Found"
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

Exit Sub

Err_FindPackingSlip:
Beep
MsgBox err.Description, vbExclamation, "Find Packing Slip Error"
Exit Sub
End Sub

Thanks for any help on this.
John