Find Method on Unbound Form Help

[Joe]

Hi, I need your help adapting this example to using a record that is
selected by an Unbound Combo Box on a form instead of a static value
in the VBA code. Everything works in my example and all I really need
to do is to change the static line below of: rsContacts.Find
"[intContactId] = 2" with the selected record in my Combo Box. Any
help is appreciated.

Thanks!



Sub TestFind()

Dim rsContacts As ADODB.Recordset

'create a new instance of a recordset
Set rsContacts = New ADODB.Recordset
'set various properties of the recordset
With rsContacts
.CursorType = adOpenStatic
'open the recordset based on tblContacts table using the existing
connection
.Open "tblContacts", CurrentProject.Connection
End With

'find a contact with the intContactId value of 2
rsContacts.Find "[intContactId] = 2"

'output a message to the Immediate Window regarding find results
If rsContacts.EOF Then
Debug.Print "Specified record not found"
Else
'record was found - display some info
Debug.Print "Contact Id: " & rsContacts!intContactId & _
" Last Name: " & rsContacts!txtLastName & _
" First Name: " & rsContacts!txtFirstName
End If

'close the recordset
rsContacts.Close

'set the recordset and connection to nothing
Set rsContacts = Nothing


End Sub

 

[ruralguy via AccessMonster.com]

Are you just trying to change:
rsContacts.Find "[intContactId] = 2"
to:
rsContacts.Find "[intContactId] = " & Me.txtBoxName

???

...using your txtBoxName of course.

Hi, I need your help adapting this example to using a record that is
selected by an Unbound Combo Box on a form instead of a static value
in the VBA code. Everything works in my example and all I really need
to do is to change the static line below of: rsContacts.Find
"[intContactId] = 2" with the selected record in my Combo Box. Any
help is appreciated.

Thanks!

Sub TestFind()

Dim rsContacts As ADODB.Recordset

'create a new instance of a recordset
Set rsContacts = New ADODB.Recordset
'set various properties of the recordset
With rsContacts
.CursorType = adOpenStatic
'open the recordset based on tblContacts table using the existing
connection
.Open "tblContacts", CurrentProject.Connection
End With

'find a contact with the intContactId value of 2
rsContacts.Find "[intContactId] = 2"

'output a message to the Immediate Window regarding find results
If rsContacts.EOF Then
Debug.Print "Specified record not found"
Else
'record was found - display some info
Debug.Print "Contact Id: " & rsContacts!intContactId & _
" Last Name: " & rsContacts!txtLastName & _
" First Name: " & rsContacts!txtFirstName
End If

'close the recordset
rsContacts.Close

'set the recordset and connection to nothing
Set rsContacts = Nothing

End Sub

 

[Joe]

Are you just trying to change:
rsContacts.Find "[intContactId] = 2"
to:
rsContacts.Find "[intContactId] = " & Me.txtBoxName

???

..using your txtBoxName of course.

Hi, I need your help adapting this example to using a record that is
selected by an Unbound Combo Box on a form instead of a static value
in the VBA code.  Everything works in my example and all I really need
to do is to change the static line below of: rsContacts.Find
"[intContactId] = 2" with the selected record in my Combo Box.  Any
help is appreciated.

Thanks!

Sub TestFind()

Dim rsContacts As ADODB.Recordset

'create a new instance of a recordset
Set rsContacts = New ADODB.Recordset
'set various properties of the recordset
With rsContacts
   .CursorType = adOpenStatic
   'open the recordset based on tblContacts table using the existing
connection
   .Open "tblContacts", CurrentProject.Connection
End With

'find a contact with the intContactId value of 2
rsContacts.Find "[intContactId] = 2"

'output a message to the Immediate Window regarding find results
If rsContacts.EOF Then
   Debug.Print "Specified record not found"
Else
'record was found - display some info
   Debug.Print "Contact Id: " & rsContacts!intContactId & _
           "  Last Name: " & rsContacts!txtLastName & _
           "  First Name: " & rsContacts!txtFirstName
End If

'close the recordset
rsContacts.Close

'set the recordset and connection to nothing
Set rsContacts = Nothing

End Sub

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200802/1- Hide quoted text -

- Show quoted text -

Thanks for the quick reply. I'll give you a little more detail in
hopes it may help.

I have an unbound form using an ADO recordset. I am trying to
implement the ability to search for a specific record in my contacts
table from a combo box. I created a simple query tied to the unbound
combo box so when the user either starts typing or selects the down
arrow the record displays in the combo box. That all works fine. The
last step that I need to do is when the user selects the desired
record it will display in the form. I have successfully created all
of the obvious record navigation VBA code to handle the movefirst,
movelast, next, previous, add, delete, etc...within the unbound form.
I am simply hung up on providing the ability to search for a specific
record and then display it accordingly. I am working with examples
from the Beginning Access 2007 VBA book to try and get my skills
improved. I thought I would tie the VBA code using the Find Method in
the ADO recordset to the combo box After Update Event. Not sure if
this is the right way to go about this or not?


Thanks

 

[ruralguy via AccessMonster.com]

If your form was bound then the cbo wizard could create the find cbo for you.

Are you just trying to change:
rsContacts.Find "[intContactId] = 2"

[quoted text clipped - 56 lines]

- Show quoted text -

Thanks for the quick reply. I'll give you a little more detail in
hopes it may help.

I have an unbound form using an ADO recordset. I am trying to
implement the ability to search for a specific record in my contacts
table from a combo box. I created a simple query tied to the unbound
combo box so when the user either starts typing or selects the down
arrow the record displays in the combo box. That all works fine. The
last step that I need to do is when the user selects the desired
record it will display in the form. I have successfully created all
of the obvious record navigation VBA code to handle the movefirst,
movelast, next, previous, add, delete, etc...within the unbound form.
I am simply hung up on providing the ability to search for a specific
record and then display it accordingly. I am working with examples
from the Beginning Access 2007 VBA book to try and get my skills
improved. I thought I would tie the VBA code using the Find Method in
the ADO recordset to the combo box After Update Event. Not sure if
this is the right way to go about this or not?

Thanks

 

[Joe]

If your form was bound then the cbo wizard could create the find cbo for you.

Are you just trying to change:
rsContacts.Find "[intContactId] = 2"

[quoted text clipped - 56 lines]

- Show quoted text -

Thanks for the quick reply.  I'll give you a little more detail in
hopes it may help.

I have an unbound form using an ADO recordset.  I am trying to
implement the ability to search for a specific record in my contacts
table from a combo box.  I created a simple query tied to the unbound
combo box so when the user either starts typing or selects the down
arrow the record displays in the combo box.  That all works fine.  The
last step that I need to do is when the user selects the desired
record it will display in the form.  I have successfully created all
of the obvious record navigation VBA code to handle the movefirst,
movelast, next, previous, add, delete, etc...within the unbound form.
I am simply hung up on providing the ability to search for a specific
record and then display it accordingly.  I am working with examples
from the Beginning Access 2007 VBA book to try and get my skills
improved.  I thought I would tie the VBA code using the Find Method in
the ADO recordset to the combo box After Update Event.  Not sure if
this is the right way to go about this or not?

Thanks

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200802/1- Hide quoted text -

- Show quoted text -

I am purposely working through the knowledge of how to work with
unbound forms and disconnected recordsets. I am trying to push myself
to learn the underpinnings and not rely on the wizards so much... I
would like to develop the skills at some point to work with Visual
Studio and I wont likely get there by using wizards all the time.
Even though the newer versions of Visual Studio is starting to have
some of the same type of wizards for rapid and quick development, I
still would like to know how to accomplish this through programming...
Needless to say I have a long way to go and working through examples
seems to be a good way to learn.

If anyone knows how to approach searching and returning the result
using an unbound form and ADO recordsets I would appreciate your
thoughts. I have already figured out all of the record navigation
stuff, just stuck on searching via a combo box as described earlier in
the thread.

Thank you.