How write values to table if record does not exist but over write record if it does exist

Dim RS As DAO.Recordset
Set RS = CurrentDb.OpenRecordset("tblAuthorizationFormData")

With rs
    .FindFirst "[ReqForPickupID]=" & Me.txtReqForPickUpID

     if .nomatch = true then
         .AddNew
         !ReqForPickUpID = Me.txtReqForPickUpID
     else
         .Edit
     endif
     !Name = Me.txtName
     .Update
End With
rs.close
set rs = nothing

Am getting an error...

Operation is not supported for this type of object.

Seems to be stuck at...

    .FindFirst "[ReqForPickupID]=" & Me.txtReqForPickUpID

Is the [ReqForPickupID] field numeric or text?  If numeric, that code should work.

That particular error generally implies that a method (in this case "FindFirst") is not available for the object type, but if this is truly a table and not a query, it should work.  Is the table an Access table, or is it linked from some other ODBC datasource?

Do you have code prior to that which checks to make sure that your controls:

Me.txtReqForPickUpID
Me.txtName

both have valid data entered?

Any thoughts?

Sorry I didn't see your reply...

The field is numeric.  It is a table.  A local Access table.

are the field names spelled correctly?

Yes.  I entered this is the code and got results for  txtReqForPickUpID and txtPetName

    MsgBox txtReqForPickUpID
    MsgBox txtPetName
   
    .FindFirst "[ReqForPickupID] =" & Me.txtReqForPickUpID

And I pasted this...

    MsgBox "Got This Far!"

before  .FindFirst "[ReqForPickupID]=" & Me.txtReqForPickUpID

and got the error.

Can you post all of the code for that particular subroutine or function?

Wrap it with code blocks

Select allOpen in new window

tags if you would so that the code is easier to read.

Sorry... how do I wrap it with code blocks or tags?

sorry, that didn't turn out as I expected.  paste your code in the window, then highlight the code and click the word "Code" in the formatting bar, just to the right of the quotes.  this will embed code tags [code ]and[/code ] around your code block.

Note:  I've added spaces at the end of the tag to prevent it from actually displaying the code block as it did above.

Private Sub cmdPrintRFPreport_Click()
On Error GoTo cmdPrintRFPreport_Click_Err

    DoCmd.RunCommand acCmdSaveRecord
    
    'Function to write data to table for Authorization Form
    Dim RS As DAO.Recordset
    Set RS = CurrentDb.OpenRecordset("tblAuthorizationFormData")
    
    With RS
    
    .FindFirst "[ReqForPickupID] =" & Me.txtReqForPickUpID

    If .NoMatch = True Then

            RS.AddNew
            RS!ReqForPickUpID = Me.txtReqForPickUpID
            RS!PetName = Me.txtPetName
            RS!CustID = Me.txtCustID
            RS!CremationPrice = Me.txtCremationPrice
            RS!UrnPrice = Me.txtUrnPrice
            RS!MemKeepPrice = Me.txtMemorialItemsPrice
            RS!OtherPrice = Me.txtOtherPrice
            RS!Tax = Me.txtTaxDue
            RS!TotalPrice = Me.txtTotalPrice
            RS!Species = Me.txtSpecies
            RS!Breed = Me.txtBreed
            RS!Age = Me.txtAge
            RS!Weight = Me.txtWeight
            RS!Gender = Me.txtMaleFemale
            RS!CustFirstName = Me.txtCustFirstName
            RS!CustLastName = Me.txtCustLastName
            RS!Address = Me.txtAddress
            RS!City = Me.txtCity
            RS!State = Me.txtState
            RS!ZipCode = Me.txtZipCode
            RS.Update
            
        Else
        
        .Edit
        
        End If
                
            RS!ReqForPickUpID = Me.txtReqForPickUpID
            RS!PetName = Me.txtPetName
            RS!CustID = Me.txtCustID
            RS!CremationPrice = Me.txtCremationPrice
            RS!UrnPrice = Me.txtUrnPrice
            RS!MemKeepPrice = Me.txtMemorialItemsPrice
            RS!OtherPrice = Me.txtOtherPrice
            RS!Tax = Me.txtTaxDue
            RS!TotalPrice = Me.txtTotalPrice
            RS!Species = Me.txtSpecies
            RS!Breed = Me.txtBreed
            RS!Age = Me.txtAge
            RS!Weight = Me.txtWeight
            RS!Gender = Me.txtMaleFemale
            RS!CustFirstName = Me.txtCustFirstName
            RS!CustLastName = Me.txtCustLastName
            RS!Address = Me.txtAddress
            RS!City = Me.txtCity
            RS!State = Me.txtState
            RS!ZipCode = Me.txtZipCode
            RS.Update
        
    End With
    RS.Close
    Set RS = Nothing
    'End of function to write data to table for Authorization Form

    DoCmd.OpenReport "rptRequestForPickup", acViewPreview, "", "", acNormal

cmdPrintRFPreport_Click_Exit:
    Exit Sub

cmdPrintRFPreport_Click_Err:
    MsgBox Error$
    Resume cmdPrintRFPreport_Click_Exit

End Sub

Select allOpen in new window

I have recently encountered some problems using Currentdb.  For those reasons, I generally declare a db object, and set it to the Currentdb

When you are inside the With RS / End With construct, you do not need to refer to the recordset (RS) before the . or !.

The point of putting the .AddNew and .Edit commands in the IF Then construct was to avoid having to duplicate all of the code for the fields you want to add/update.  

I'm also not sure why you have the following line at the beginning of this code block:

    DoCmd.RunCommand acCmdSaveRecord

That line will save the current record.  So is the rest of the code writing identical data to a different table?  If not, what is the purpose of that line.  I've left it in the code below, but am not certain it should be there.  On the other hand, that may be the only line you need if you are using a bound form, and it is based on table:  tblAuthorizationFormData

Try the following:

Private Sub cmdPrintRFPreport_Click()
On Error GoTo cmdPrintRFPreport_Click_Err

    DoCmd.RunCommand acCmdSaveRecord
    
    'Function to write data to table for Authorization Form
    Dim db as DAO.Database
    Dim RS As DAO.Recordset

    Set db = Currentdb
    Set RS = db.OpenRecordset("tblAuthorizationFormData")
    
    With RS
    
        .FindFirst "[ReqForPickupID] =" & Me.txtReqForPickUpID

        If .NoMatch = True Then

            .AddNew
            !ReqForPickUpID = Me.txtReqForPickUpID
            
        Else
        
            .Edit
        
        End If
                
        !ReqForPickUpID = Me.txtReqForPickUpID
        !PetName = Me.txtPetName
        !CustID = Me.txtCustID
        !CremationPrice = Me.txtCremationPrice
        !UrnPrice = Me.txtUrnPrice
        !MemKeepPrice = Me.txtMemorialItemsPrice
        !OtherPrice = Me.txtOtherPrice
        !Tax = Me.txtTaxDue
        !TotalPrice = Me.txtTotalPrice
        !Species = Me.txtSpecies
        !Breed = Me.txtBreed
        !Age = Me.txtAge
        !Weight = Me.txtWeight
        !Gender = Me.txtMaleFemale
        !CustFirstName = Me.txtCustFirstName
        !CustLastName = Me.txtCustLastName
        !Address = Me.txtAddress
        !City = Me.txtCity
        !State = Me.txtState
        !ZipCode = Me.txtZipCode
        .Update
        
    End With
    RS.Close
    Set RS = Nothing
    'End of function to write data to table for Authorization Form

    DoCmd.OpenReport "rptRequestForPickup", acViewPreview, "", "", acNormal

cmdPrintRFPreport_Click_Exit:
    Exit Sub

cmdPrintRFPreport_Click_Err:
    MsgBox Error$
    Resume cmdPrintRFPreport_Click_Exit

End Sub

Select allOpen in new window

I'm still getting an error...

Operation is not supported for this type of object.

I have it writing the record if it doesn't exist.  But if it does exist what is wrong with this lne of code?

If DCount("*", "tblAuthorizationFormData", "ReqForPickUpID = " & Chr(34) & Me.ReqForPickUpID & Chr(34)) > 0 Then

I'm getting a type mismatch error

Since I expect that your [ReqForPickupID] field is probably numeric, Try:

If DCount("*", "tblAuthorizationFormData", "ReqForPickUpID = " & Me.ReqForPickUpID) > 0 Then

I'm getting a syntax error missing operator with this...

If DCount("*", "tblAuthorizationFormData", "ReqForPickUpID = " & Me.ReqForPickUpID) > 0 Then

And yes, the [ReqForPickupID] field is numeric.

Steve,

Are you certain that there is a control named ReqForPickupID (maybe txtReqForPickupID) or a field in the forms recordsource with that name?

You seem to have named your controls with the txt prefix.

Generally, if you are going to refer to a field, instead of a control, you would use me.[FieldName], although this is not required.

I have double checked.  There is a field named ReqForPickupID which is a numeric field in tblAuthorizationFormData

and the field txtReqForPickupID  does exist on the form.

I did correct the code to read...

If DCount("*", "tblAuthorizationFormData", "ReqForPickUpID = " & Me.txtReqForPickUpID) > 0 Then

but I still get a syntax error missing operator

??

What is the value of me.txtReqForPickUpID when you run that?  Could it be NULL or blank?  Do you have any code prior to this line which makes sure that data is entered in that textbox?

SteveL13, how large is your Access db?  Is it possible for you to upload it, or a copy with the necessary objects?  Remove any/all data except for what's needed as an example.
OM Gang

To fyed:

Regarding "What is the value of me.txtReqForPickUpID when you run that?  Could it be NULL or blank?  Do you have any code prior to this line which makes sure that data is entered in that textbox? "...

Now I feel really stupid.  There is no value in txtReqForPickUpID  because it's a new record so it hasn't ben written to the table yet.  

That is why I had...  DoCmd.RunCommand acCmdSaveRecord before the code.  So to make it work I now have...

DoCmd.RunCommand acCmdSaveRecord
me.Refresh

before the code and it works!!

The default recordset open cursor is forwardonly.  Add a parameter to your OpenRecordset() invocation that specifies a dynaset type of cursor.