Update to Recordset Not Finding Match

[Fattire]

I have a form with an unbound text box and a cmd button. I want the user to be able to type in data where it matches the data in the txt box to a record in a table and update some fields tied to the record it matches to - but what I have written do not seem to find a match.
Stepping through, I see it just goes straight to the "End If" from the "If Not rcd1.NoMatch Then"

    Set db1 = CurrentDb()
    Set rcd1 = db1.OpenRecordset("tblMainTable", dbOpenDynaset, dbAppendOnly)

    rcd1.FindFirst "[CHECK_NUMBER] = Me.txtChkNum"
    If Not rcd1.NoMatch Then
    rcd1.Edit
    rcd1![SENT] = -1
    rcd1![SENT_TO_DATE] = Date + Time()
    rcd1.Update
    End If

 

[RoyVidar]

You need to include the value from the reference, not the reference itself,

For numeric field
[tt]rcd1.FindFirst "[CHECK_NUMBER] = " & Me.txtChkNum[/tt]

For text field
[tt]rcd1.FindFirst "[CHECK_NUMBER] = '" & Me.txtChkNum & "'"[/tt]

Date+time would probably be the same as Now

Roy-Vidar

 

[Fattire]

Thanks for the response - I tried that one and it didn't work, it should but it's not.

I know that the two values are equal because I'm copying the value from the table itself into the text box.

Any other ideas on why they might not be matching?

 

[TheAceMan1]

How are ya Fattire . . .

. . . and this:

[blue]   Dim db1 As DAO.Database, [purple][b]rcd1 As DAO.Recordset[/b][/purple]
 
   Set db1 = CurrentDb()
   Set rcd1 = db1.OpenRecordset("tblMainTable", dbOpenDynaset)
   rcd1.FindFirst [purple][b]"[CHECK_NUMBER] = " & Me.txtChkNum[/b][/purple]
   
   If Not rcd1.NoMatch Then
      rcd1.Edit
      rcd1![SENT] = -1
      rcd1![SENT_TO_DATE] = [purple][b]Now()[/b][/purple]
      rcd1.Update
   End If
   
   Set rcd1 = Nothing
   Set db1 = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[RoyVidar]

Yes, I think opening with the dbAppendOnly opens a recordset without any records, so it probably won't find anything.

Drop the dbAppendOnly, and then, in stead of using a .findfirst, use a where clause.

[tt]dim strSql as string
strSql = "SELECT * FROM tblMainTable " & _
"WHERE "[CHECK_NUMBER] = " & Me.txtChkNum

Set rcd1 = db1.OpenRecordset(strSql, dbOpenDynaset)
if not rcd1.eof then
' do the edit[/tt]


Roy-Vidar

 

[Fattire]

RoyVidar = yeah, I figured that out right after I posted, looked at help file again and saw that, thank you for your help. I've done acemans1 suggestion as well as yours and they both work, thank you both - can't believe I missed that one...thanks again

 

[TheAceMan1]

Fattire . . .

BTW: [blue]Welcome to Tek-Tips![/blue] To get great answers and know whats expected of you in the forums be sure to have a look at FAQ219-2884

See Ya! . . . . . .