Combo box & clustered key blues

[serviceman via AccessMonster.com]

Hi All,
I have a combo box that looks up records on a table with a clustered key.
Here is the After_update code:

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.Find "[EVENT_ID] = " & Str(Nz(Me![Combo14], 0)) & " AND [EVENT_DAT] =
" & Me.Combo14.Column(1) & " AND [LOCATION_ID] = " & Str(Nz(Me!Combo14.Column
(2), 0))

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

Problem is that I keep getting and error 3001: Type mismatch with this code.
I think it is in the EVENT_DAT portion, but I can't seem to get it. I've
tried using Str(Nz... and still no luck. Am I on the right track or is this
wrong all together?
Andy

 

[tina]

if the [EVENT_DAT] field is a Date/Time data type, then you need to change
the syntax to

[EVENT_DAT] = #" & Me.Combo14.Column(1) & "# AND...

also, if the [EVENT_ID] AND [LOCATION_ID] fields are Number data type, then
you don't need to use the Str() function. just reference the controls, as

"[EVENT_ID] = " & Nz(Me![Combo14], 0) & " AND...

hth

 

[serviceman via AccessMonster.com]

Hi Tina!
Yup, the # was it; I had them in there a hundred different ways and just
didn't get it right. I knew about numbers not needing Str(), but after a
looooooong day of trying to get it working I was clutching at straws. Thanks
so much for the brain boost!
Andy

if the [EVENT_DAT] field is a Date/Time data type, then you need to change
the syntax to

[EVENT_DAT] = #" & Me.Combo14.Column(1) & "# AND...

also, if the [EVENT_ID] AND [LOCATION_ID] fields are Number data type, then
you don't need to use the Str() function. just reference the controls, as

"[EVENT_ID] = " & Nz(Me![Combo14], 0) & " AND...

hth

Hi All,
I have a combo box that looks up records on a table with a clustered key.

[quoted text clipped - 14 lines]

wrong all together?
Andy

 

[tina]

you're welcome

Hi Tina!
Yup, the # was it; I had them in there a hundred different ways and just
didn't get it right. I knew about numbers not needing Str(), but after a
looooooong day of trying to get it working I was clutching at straws. Thanks
so much for the brain boost!
Andy

if the [EVENT_DAT] field is a Date/Time data type, then you need to change
the syntax to

[EVENT_DAT] = #" & Me.Combo14.Column(1) & "# AND...

also, if the [EVENT_ID] AND [LOCATION_ID] fields are Number data type, then
you don't need to use the Str() function. just reference the controls, as

"[EVENT_ID] = " & Nz(Me![Combo14], 0) & " AND...

hth

Hi All,
I have a combo box that looks up records on a table with a clustered

key.
[quoted text clipped - 14 lines]

wrong all together?
Andy

--
A $300 dollar picture tube will protect a 10 cent fuse by blowing first-
Murphy

Message posted via AccessMonster.com