Calling a Sub Function

[Guest]

Can someone sort out this code for me. I am trying to call a function to
requery a listBox and a subform after a button is clicked but I just can't
seem to get the correct syntax. Thanks!

Private Sub CmdRemoveTube_Click()
Dim RemoveID As Long
Dim ListBoxName As String
Dim SubFormName As String

RemoveID =
[Forms]![Frm_EditBooking]![Sub_SelectedTube]![lng_SelectedEquipmentID]
ListBoxName = "cbo_TubeSelect"
SubFormName = "Sub_SelectedTube"
Call CBF_RemoveEquipment(RemoveID, SubFormName, ListBoxName)

End Sub

Private Sub CBF_RemoveEquipment(RemoveID As Variant, SubFormName As SubForm,
ListBoxName As ListBox)

On Error GoTo CBF_RemoveEquipment_ERR

Dim strsql As String

If IsNull(RemoveID) Then
Exit Sub
End If
DoCmd.SetWarnings False
strsql = "Delete * FROM Tbl_Wrk_SelectedEquip " _
& "WHERE lng_SelectedEquipmentID = " & RemoveID

DoCmd.RunSQL strsql
DoCmd.SetWarnings True
Me!SubFormName.Form.Requery
Me!ListBoxName.Requery
fDataChanged = True
Exit Sub
CBF_RemoveEquipment_ERR:
Call Error_Handler(False)
Exit Sub
End Sub

 

[Marshall Barton]

Can someone sort out this code for me. I am trying to call a function to
requery a listBox and a subform after a button is clicked but I just can't
seem to get the correct syntax. Thanks!

Private Sub CmdRemoveTube_Click()
Dim RemoveID As Long
Dim ListBoxName As String
Dim SubFormName As String

RemoveID =
[Forms]![Frm_EditBooking]![Sub_SelectedTube]![lng_SelectedEquipmentID]
ListBoxName = "cbo_TubeSelect"
SubFormName = "Sub_SelectedTube"
Call CBF_RemoveEquipment(RemoveID, SubFormName, ListBoxName)

End Sub

Private Sub CBF_RemoveEquipment(RemoveID As Variant, SubFormName As SubForm,
ListBoxName As ListBox)

On Error GoTo CBF_RemoveEquipment_ERR

Dim strsql As String

If IsNull(RemoveID) Then
Exit Sub
End If
DoCmd.SetWarnings False
strsql = "Delete * FROM Tbl_Wrk_SelectedEquip " _
& "WHERE lng_SelectedEquipmentID = " & RemoveID

DoCmd.RunSQL strsql
DoCmd.SetWarnings True
Me!SubFormName.Form.Requery
Me!ListBoxName.Requery
fDataChanged = True
Exit Sub
CBF_RemoveEquipment_ERR:
Call Error_Handler(False)
Exit Sub
End Sub

The really big problem is that you have declared the
arguments as listbox and subform objects and then pass them
as the names of the objects. The way you have declared the
procedure requires that you call it using

Private Sub CmdRemoveTube_Click()
Dim RemoveID As Long

RemoveID =
[Forms]![Frm_EditBooking]![Sub_SelectedTube]![lng_SelectedEquipmentID]
Call CBF_RemoveEquipment(RemoveID, _
Sub_SelectedTube, cbo_TubeSelect)
End Sub

Note that this passes the object references instead of their
names in string arguments.

The next thing is that the procedure is using the arguments
incorrectly. The requery lines need to be:

SubFormName.Form.Requery
ListBoxName.Requery

Since they are objects, not names, you can not use Me.
Note that you should also change the names of the arguments
as they are very misleading.

I also spotted an undeclared variable where you refer to
fDataChanged. I have no idea what that's supposed to
accomplish. Maybe it's some kind of module level or global
variable?