After update find record on subform

[Tara]

I have a tabbed form with a subform on one tab. I have a combo on the main
form which is used to choose a specific client. Once the user has chosen the
client, I need the subform to update to that record and then set focus on the
FirstName field on the subform. Here's the code I have so far (without the
Set Focus piece in place) but it's not working:

Private Sub cboSearch_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.sbfDemographics.Form.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Me![cboSearch],0))
Me.Bookmark = rs.Bookmark
End Sub

 

[Jack Leach]

Me.Bookmark = rs.Bookmark

Try

Me.sbfDemographics.Form.Bookmark = rs.Bookmark


You may also want to include a .NoMatch check after FindFirst just in case...


hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Tara]

Thanks for getting back with me Jack. I tried your solution, but now I'm
getting syntax error on this line:

rs.FindFirst "[ContactID] = " & Str(Me![cboSearch],0))

I'm not very good at coding yet, so I'm not sure where the error is. Any
additional help you can offer would be greatly appreciated.

Me.Bookmark = rs.Bookmark

Try

Me.sbfDemographics.Form.Bookmark = rs.Bookmark


You may also want to include a .NoMatch check after FindFirst just in case...


hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

I have a tabbed form with a subform on one tab. I have a combo on the main
form which is used to choose a specific client. Once the user has chosen the
client, I need the subform to update to that record and then set focus on the
FirstName field on the subform. Here's the code I have so far (without the
Set Focus piece in place) but it's not working:

Private Sub cboSearch_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.sbfDemographics.Form.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Me![cboSearch],0))
Me.Bookmark = rs.Bookmark
End Sub

 

[Jack Leach]

rs.FindFirst "[ContactID] = " & Str(Me![cboSearch],0))

It seems that you have an extra closing parenthese added to the end of that.
I think if you remove that the syntax error would go away. But then you
will find an error using the Str() function. The Str() function converts a
Number datatype to a String datatype, and only takes one argument. You have
0 added as a second argument, which will throw an additional error.

Are ContactID and cboSearch Number or Strings? There's a different way you
need to build that criteria line for strings versus numbers. Here's an
example of a string comparison vs a numeric comparison:

for strings:
Somethingstring = "Something"

for numbers:
SomeNumber = 1234

so you can see that strings need quotes to tell VBA that you're working with
a string, but numbers cannot have them. "1234" and 1234 are two different
things. Anyway, plug that into your FindFirst Criteria, and you will have
one or the other based on your datatype:

rs.FindFirst "[ContactID] = " & Me![cboSearch]

or

rs.FindFirst "[ContactID] = """ & Me![cboSearch] & """"

(this example encloses the contents of cboSearch in quotes to tell vba
you're working with a string)


I'm not exactly sure what you were trying to accomplish using Str(), but
maybe the above will shed a little light.

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

Thanks for getting back with me Jack. I tried your solution, but now I'm
getting syntax error on this line:

rs.FindFirst "[ContactID] = " & Str(Me![cboSearch],0))

I'm not very good at coding yet, so I'm not sure where the error is. Any
additional help you can offer would be greatly appreciated.

Me.Bookmark = rs.Bookmark

Try

Me.sbfDemographics.Form.Bookmark = rs.Bookmark


You may also want to include a .NoMatch check after FindFirst just in case...


hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

I have a tabbed form with a subform on one tab. I have a combo on the main
form which is used to choose a specific client. Once the user has chosen the
client, I need the subform to update to that record and then set focus on the
FirstName field on the subform. Here's the code I have so far (without the
Set Focus piece in place) but it's not working:

Private Sub cboSearch_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.sbfDemographics.Form.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Me![cboSearch],0))
Me.Bookmark = rs.Bookmark
End Sub

 

[Tara]

I see what you're saying. ContactID and cboSearch are both numbers, but
cboSearch displays a string (Client's name) so I thought I'd need the Str for
that. Just wasn't thinking, I guess.

I'll make the changes you suggested...hopefully that will take care of it!

Thanks so much for your help!

rs.FindFirst "[ContactID] = " & Str(Me![cboSearch],0))

It seems that you have an extra closing parenthese added to the end of that.
I think if you remove that the syntax error would go away. But then you
will find an error using the Str() function. The Str() function converts a
Number datatype to a String datatype, and only takes one argument. You have
0 added as a second argument, which will throw an additional error.

Are ContactID and cboSearch Number or Strings? There's a different way you
need to build that criteria line for strings versus numbers. Here's an
example of a string comparison vs a numeric comparison:

for strings:
Somethingstring = "Something"

for numbers:
SomeNumber = 1234

so you can see that strings need quotes to tell VBA that you're working with
a string, but numbers cannot have them. "1234" and 1234 are two different
things. Anyway, plug that into your FindFirst Criteria, and you will have
one or the other based on your datatype:

rs.FindFirst "[ContactID] = " & Me![cboSearch]

or

rs.FindFirst "[ContactID] = """ & Me![cboSearch] & """"

(this example encloses the contents of cboSearch in quotes to tell vba
you're working with a string)


I'm not exactly sure what you were trying to accomplish using Str(), but
maybe the above will shed a little light.

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

Thanks for getting back with me Jack. I tried your solution, but now I'm
getting syntax error on this line:

rs.FindFirst "[ContactID] = " & Str(Me![cboSearch],0))

I'm not very good at coding yet, so I'm not sure where the error is. Any
additional help you can offer would be greatly appreciated.

Me.Bookmark = rs.Bookmark

Try

Me.sbfDemographics.Form.Bookmark = rs.Bookmark


You may also want to include a .NoMatch check after FindFirst just in case...


hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



:

I have a tabbed form with a subform on one tab. I have a combo on the main
form which is used to choose a specific client. Once the user has chosen the
client, I need the subform to update to that record and then set focus on the
FirstName field on the subform. Here's the code I have so far (without the
Set Focus piece in place) but it's not working:

Private Sub cboSearch_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.sbfDemographics.Form.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Me![cboSearch],0))
Me.Bookmark = rs.Bookmark
End Sub

 

[Jack Leach]

That should take care of it. Just make sure that the Bound Column is the
Number index of the dropdown (bound column being the one that is stored in
the table and referred to in code).

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

I see what you're saying. ContactID and cboSearch are both numbers, but
cboSearch displays a string (Client's name) so I thought I'd need the Str for
that. Just wasn't thinking, I guess.

I'll make the changes you suggested...hopefully that will take care of it!

Thanks so much for your help!

rs.FindFirst "[ContactID] = " & Str(Me![cboSearch],0))

It seems that you have an extra closing parenthese added to the end of that.
I think if you remove that the syntax error would go away. But then you
will find an error using the Str() function. The Str() function converts a
Number datatype to a String datatype, and only takes one argument. You have
0 added as a second argument, which will throw an additional error.

Are ContactID and cboSearch Number or Strings? There's a different way you
need to build that criteria line for strings versus numbers. Here's an
example of a string comparison vs a numeric comparison:

for strings:
Somethingstring = "Something"

for numbers:
SomeNumber = 1234

so you can see that strings need quotes to tell VBA that you're working with
a string, but numbers cannot have them. "1234" and 1234 are two different
things. Anyway, plug that into your FindFirst Criteria, and you will have
one or the other based on your datatype:

rs.FindFirst "[ContactID] = " & Me![cboSearch]

or

rs.FindFirst "[ContactID] = """ & Me![cboSearch] & """"

(this example encloses the contents of cboSearch in quotes to tell vba
you're working with a string)


I'm not exactly sure what you were trying to accomplish using Str(), but
maybe the above will shed a little light.

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

Thanks for getting back with me Jack. I tried your solution, but now I'm
getting syntax error on this line:

rs.FindFirst "[ContactID] = " & Str(Me![cboSearch],0))

I'm not very good at coding yet, so I'm not sure where the error is. Any
additional help you can offer would be greatly appreciated.



:

Me.Bookmark = rs.Bookmark

Try

Me.sbfDemographics.Form.Bookmark = rs.Bookmark


You may also want to include a .NoMatch check after FindFirst just in case...


hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



:

I have a tabbed form with a subform on one tab. I have a combo on the main
form which is used to choose a specific client. Once the user has chosen the
client, I need the subform to update to that record and then set focus on the
FirstName field on the subform. Here's the code I have so far (without the
Set Focus piece in place) but it's not working:

Private Sub cboSearch_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.sbfDemographics.Form.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Me![cboSearch],0))
Me.Bookmark = rs.Bookmark
End Sub