Problem with .find in recordset

  • Thread starter Thread starter Billy B
  • Start date Start date
B

Billy B

I have a form which in the edit mode I want to save changes if the user
clicks the next button. Using a recordset, I believe I want to get the
PlantID from the form and use a .find criteria to update that records data. I
get an error msg in the following code at the .find line: (Thanks for the
help)

Private Sub cmdNextRecord_Click()
On Error GoTo Err_cmdNextRecord_Click

Dim conn As ADODB.Connection, rsPlants As ADODB.Recordset

Dim strSQL As String, strLoc As String
Dim strFileName As String
Dim intPlantID As Integer
intPlantID = Me.PlantID

Set conn = Application.CurrentProject.Connection
Set rsPlants = New ADODB.Recordset
strSQL = "Select * from tblPlants"
'strLoc = Me.cboLoc.Value
'gets the forms name for later use in this procedure
strFormName = Me.Name

rsPlants.Open strSQL, conn, adOpenDynamic, adLockPessimistic

On Error Resume Next

If Not rsPlants.EOF Then
.Find "[PlantID] = " & intPlantID
With rsPlants
.Fields("PlantName").Value = Me.Plantname.Value
.Update
.Fields("Alias").Value = Me.Alias.Value
.Update
.Fields("BotonName").Value = Me.BotonName.Value
.Update
.Fields("PlantType").Value = Me.cboPlantType.Value
.Update
.Fields("PlantCat").Value = Me.cboPlantCat.Value
.Update
.Fields("GrowthNote").Value = Me.GrowthNote.Value
.Update
.Fields("Height").Value = Me.txtHeight.Value
.Update
.Fields("Spread").Value = Me.txtSpread.Value
.Update
.Fields("MinHgt").Value = Me.txtMinHgt.Value
.Update
.Fields("MaxHgt").Value = Me.txtMaxHgt.Value
.Update
.Fields("MinSpread").Value = Me.txtMinSpread.Value
.Update
.Fields("MaxSpread").Value = Me.txtMaxSpread.Value
.Update
.Fields("PlantingLoc").Value = Me.cboLoc.Value
.Update
.Fields("Description").Value = Me.Description.Value
.Update
.Fields("Culture").Value = Me.Culture.Value
.Update
.Fields("Moisture").Value = Me.Moisture.Value
.Update
.Fields("HardinessZones") = Me.HardinessZones.Value
.Update
.Fields("Color") = Me.lstEditColor.Value
.Update
.Fields("Features") = Me.Features.Value
.Update
.Fields("Usage") = Me.Usage.Value
.Update
.Fields("PlantWarnings") = Me.PlantWarnings.Value
.Update
.Fields("PicPath").Value = Me.txtPicPath.Value
.Update
.Fields("Purchased").Value =
Me.cboEditListRetailer.Column(0)
.Update
.Fields("Stocked").Value = Me.Stocked.Value
.Update
End With
End If

'clear contents of form
Me.Plantname.Value = ""
Me.Alias.Value = ""
Me.BotonName.Value = ""
'Me.PlantType.Value = ""
'Me.PlantCat.Value = Null
Me.GrowthNote.Value = ""
'Me.PlantingLoc = ""
Me.Description.Value = ""
Me.Culture.Value = ""
Me.Moisture.Value = ""
Me.HardinessZones = ""
Me.Features = ""
Me.Usage = ""
Me.PlantWarnings = ""
Me.PicPath.Value = ""
MecboEditListRetailer.Value = ""
Me.txtMinHgt.Value = ""
Me.txtMaxHgt.Value = ""
Me.txtMinSpread.Value = ""
Me.txtMaxSpread.Value = ""
Me.txtHeight.Value = ""
Me.txtSpread.Value = ""
'Me.AddPic.Picture = ""
Me.Stocked.Value = 0

DoCmd.GoToRecord , , acNext
cmdPrevRecord.Enabled = True

rsPlants.Close
Set conn = Nothing


Exit Sub
 
On Thu, 13 Nov 2008 21:24:10 -0800, Billy B

Take out the "On error resume next" line. That's a really bad idea.

Then note that you're doing a .Find BEFORE the "with rsPlants" line.
Access does not allow this outside of a With block.

-Tom.
Microsoft Access MVP



I have a form which in the edit mode I want to save changes if the user
clicks the next button. Using a recordset, I believe I want to get the
PlantID from the form and use a .find criteria to update that records data. I
get an error msg in the following code at the .find line: (Thanks for the
help)

Private Sub cmdNextRecord_Click()
On Error GoTo Err_cmdNextRecord_Click

Dim conn As ADODB.Connection, rsPlants As ADODB.Recordset

Dim strSQL As String, strLoc As String
Dim strFileName As String
Dim intPlantID As Integer
intPlantID = Me.PlantID

Set conn = Application.CurrentProject.Connection
Set rsPlants = New ADODB.Recordset
strSQL = "Select * from tblPlants"
'strLoc = Me.cboLoc.Value
'gets the forms name for later use in this procedure
strFormName = Me.Name

rsPlants.Open strSQL, conn, adOpenDynamic, adLockPessimistic

On Error Resume Next

If Not rsPlants.EOF Then
.Find "[PlantID] = " & intPlantID
With rsPlants
.Fields("PlantName").Value = Me.Plantname.Value
.Update
Click to expand...
<clip>
 
Back
Top