refactoring code

[Redbeard]

I have several comboboxes that essentially do the same thing, but on
different fields. You select an item, and jump to the record that
matches the data in that field. So they all have the same code as
shown below, just with different field and object names.
Private Sub combo1_AfterUpdate()
If combo1 <> "" Then
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Field1] = '" & Me![combo1] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If
End Sub

I'd like to refactor this into something like the following...

Private Sub combo1_AfterUpdate()
Call General_AfterUpdate(combo1, Field1)
End Sub

Private Sub General_AfterUpdate(combo As ???, Field As ???)
If combo <> "" Then
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Field] = '" & Me![combo] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If
End Sub

I'm more familiar with Java than VBA and I'm not sure of the proper
syntax or what all the keywords are, but it seems to me that what I
have should be close. If someone could tell me where to find a
glossary like QBasic used to have or online documentation like Java
has, it would also be helpful.
Thanks in advance.

 

[Brendan Reynolds]

That would be something like ...

Private Sub combo1_AfterUpdate()
Call General_AfterUpdate(combo1, "Field1")
End Sub

Private Sub General_AfterUpdate(ComboControl As ComboBox, FieldName As
String)
If combo.Value <> "" Then
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst FieldName = '" & ComboControl.Value & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If
End Sub

You might want to also test the combo box for Null values, though ...

If combo.Value <> "" And Not IsNull(combo.Value) Then ...

In Access 2003 you can get to the documentation easily enough by opening the
VBA editor and from the Help menu choose 'Microsoft Visual Basic Help'. What
can be tricky is figuring out which object library is likely to contain the
objects and methods you want (and therefore which help 'book' to expand in
the table of contents). For example, you'll need to look in 'Microsoft
Access Visual Basic Reference' for the documentation on the ComboBox object,
but in 'Microsoft Visual Basic Documentation' for the documentation on the
IsNull function.

Details may differ somewhat in other versions.

 

[Brendan Reynolds]

Oops - I changed the name of the first argument in an effort to be more
explicit about the type of object that was being passed, but forgot to
change one of the references to that argument inside the function ....

Private Sub General_AfterUpdate(ComboControl As ComboBox, FieldName As
String)
If ComboControl.Value <> "" Then

--
Brendan Reynolds (MVP)

That would be something like ...

Private Sub combo1_AfterUpdate()
Call General_AfterUpdate(combo1, "Field1")
End Sub

Private Sub General_AfterUpdate(ComboControl As ComboBox, FieldName As
String)
If combo.Value <> "" Then
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst FieldName = '" & ComboControl.Value & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If
End Sub

You might want to also test the combo box for Null values, though ...

If combo.Value <> "" And Not IsNull(combo.Value) Then ...

In Access 2003 you can get to the documentation easily enough by opening
the VBA editor and from the Help menu choose 'Microsoft Visual Basic
Help'. What can be tricky is figuring out which object library is likely
to contain the objects and methods you want (and therefore which help
'book' to expand in the table of contents). For example, you'll need to
look in 'Microsoft Access Visual Basic Reference' for the documentation on
the ComboBox object, but in 'Microsoft Visual Basic Documentation' for the
documentation on the IsNull function.

Details may differ somewhat in other versions.

--
Brendan Reynolds (MVP)

I have several comboboxes that essentially do the same thing, but on
different fields. You select an item, and jump to the record that
matches the data in that field. So they all have the same code as
shown below, just with different field and object names.
Private Sub combo1_AfterUpdate()
If combo1 <> "" Then
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Field1] = '" & Me![combo1] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If
End Sub

I'd like to refactor this into something like the following...

Private Sub combo1_AfterUpdate()
Call General_AfterUpdate(combo1, Field1)
End Sub

Private Sub General_AfterUpdate(combo As ???, Field As ???)
If combo <> "" Then
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Field] = '" & Me![combo] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If
End Sub

I'm more familiar with Java than VBA and I'm not sure of the proper
syntax or what all the keywords are, but it seems to me that what I
have should be close. If someone could tell me where to find a
glossary like QBasic used to have or online documentation like Java
has, it would also be helpful.
Thanks in advance.

 

[Dirk Goldgar]

That would be something like ...

Private Sub combo1_AfterUpdate()
Call General_AfterUpdate(combo1, "Field1")
End Sub

Private Sub General_AfterUpdate(ComboControl As ComboBox, FieldName As
String)
If combo.Value <> "" Then
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst FieldName = '" & ComboControl.Value & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If
End Sub

I'd like to point out that, though this is modeled on the code generated
by the Access combo box wizard, that code is *wrong*. If the recordset
clone is a DAO recordset -- which it will normally be in an .mdb file --
then the recordset's EOF property won't be set if the FindFirst target
is not found. Instead, the NoMatch property will be set. Therefore,
this line:

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

should be

If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

 

[Albert D.Kallal]

Ah..and we do need to point out that in a "general" routine, the use of the
"me" keyword cannot be resolved...

I have to assume that you "geneal" code routine is to placed in a standard
module.

So, you need also to pass the forms ref...

Call General_AfterUpdate(me ,conbo1,"field1)

And, our standard code in a module would thus be:

Public Sub General_AfterUpdate(f as form, ComboControl As ComboBox,
FieldName As String)

If comboContorl.Value <> "" Then

Dim rs As dao.RecordSet
Set rs = Me.Recordset.Clone
rs.FindFirst FieldName = '" & ComboControl.Value & "'"
If Not rs.EOF Then f.Bookmark = rs.Bookmark
End If
End Sub


Also note that I changed the "object" to a dao.recordset, since if you don't
explicitly define what type reocrdset a form returns, it is kind of up in
the air (you assign to a dao reocrset, you get a dao record set, you assign
to a ado reocrdset, you get a ado reocrdset).


Do note that you can pass the values as a string, and resovle them also:


Call General_AfterUpdate("frmCustomers","mycombbox","field1)

And, our standard code in a module would thus be:

Public Sub General_AfterUpdate(strForm as string, strComboControl As string,
strFieldName As String)


forms(strForm) gives the form ref

forms(strForm)(strComboContorl) gives the combo ref...

 

[Dirk Goldgar]

Ah..and we do need to point out that in a "general" routine, the use
of the "me" keyword cannot be resolved...

I have to assume that you "geneal" code routine is to placed in a
standard module.

So, you need also to pass the forms ref...

Call General_AfterUpdate(me ,conbo1,"field1)

And, our standard code in a module would thus be:

Public Sub General_AfterUpdate(f as form, ComboControl As ComboBox,
FieldName As String)

If comboContorl.Value <> "" Then

Dim rs As dao.RecordSet
Set rs = Me.Recordset.Clone

Oops. After all that work, you forgot to change that to

Set rs = f.Recordset.Clone