Find and update record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an unbound form that I want users to enter information then hit the
update/append command button, if two particular controls match what is
already in the table I'd like the form to prompt the user to overwrite the
record with the information on the form. If there is no matching record I
want it to append the record to the table without further user intervention.

Searching around I found the following code snippet that seems to be on the
right track but I can't make it work for me:

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Directions")
rs.MoveFirst
Do While Not rs.EOF
rs.Edit 'does not like this why?
rs!Dir = UCase(rs!Dir)
rs.Update
rs.MoveNext
Loop

rs.Close

Is this the right idea or can a query update/append/and search.
 
I don't know why your .Edit is not working, here is an example:

With rstCompare
.FindFirst strFind
If .NoMatch Then
.AddNew
blnAddFields = True
Else
.Edit
blnAddFields = False
End If
For intFldCtr = 0 To 15
If blnAddFields Then
.Fields(intFldCtr) = rstOne.Fields(intFldCtr)
End If
'Change for Month
.Fields(intFldCtr + 32) = rstSQL.Fields(intFldCtr)
Next intFldCtr
If !jan = 0 Then
!jan = rstOne.Fields(16)
'Change for Month
!CompareNotes = !CompareNotes & "Jan Val From Mar"
End If
!feb = rstSQL.Fields(16)
.Update
End With
 
Actually I was hoping there was a way to have a query do the work since all
that code you just wrote is new to me. I will play with it though but wanted
to know for my boss and myself if a query could do the same thing?
 
Back
Top