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

I have this code in an onclick event of a command button which I want to happen if the ReqForPickUpID does NOT already exist. ...

    Dim RS As DAO.Recordset
    Set RS = CurrentDb.OpenRecordset("tblAuthorizationFormData")
            RS.AddNew
            RS!ReqForPickUpID = Me.txtReqForPickUpID
            RS!Name = Me.txtName
            RS.Update

But if the ReqForPickUpID does already exist I want the values to be overwritten.

Can this be done?
SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
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
SteveL13Author Commented:
Am getting an error...

Operation is not supported for this type of object.
SteveL13Author Commented:
Seems to be stuck at...

    .FindFirst "[ReqForPickupID]=" & Me.txtReqForPickUpID
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Dale FyeOwner, Developing Solutions LLCCommented:
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?
SteveL13Author Commented:
Any thoughts?
SteveL13Author Commented:
Sorry I didn't see your reply...

The field is numeric.  It is a table.  A local Access table.
Dale FyeOwner, Developing Solutions LLCCommented:
are the field names spelled correctly?
SteveL13Author Commented:
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.
Dale FyeOwner, Developing Solutions LLCCommented:
Can you post all of the code for that particular subroutine or function?

Wrap it with code blocks
  

Open in new window

tags if you would so that the code is easier to read.
SteveL13Author Commented:
Sorry... how do I wrap it with code blocks or tags?
Dale FyeOwner, Developing Solutions LLCCommented:
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.
SteveL13Author Commented:
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

Open in new window

Dale FyeOwner, Developing Solutions LLCCommented:
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

Open in new window

SteveL13Author Commented:
I'm still getting an error...

Operation is not supported for this type of object.
SteveL13Author Commented:
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
SteveL13Author Commented:
I'm getting a type mismatch error
Dale FyeOwner, Developing Solutions LLCCommented:
Since I expect that your [ReqForPickupID] field is probably numeric, Try:

If DCount("*", "tblAuthorizationFormData", "ReqForPickUpID = " & Me.ReqForPickUpID) > 0 Then
SteveL13Author Commented:
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.
Dale FyeOwner, Developing Solutions LLCCommented:
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.
SteveL13Author Commented:
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

??
Dale FyeOwner, Developing Solutions LLCCommented:
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?
omgangIT ManagerCommented:
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
SteveL13Author Commented:
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!!
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Two comments:

1. On this:

Set RS = CurrentDb.OpenRecordset("tblAuthorizationFormData")

 Always specify a recordset type.   A good rule of thumb is to always be as explicit as possible with anything you do.  In other words if you can provide an argument, provide it.

 If you leave it up to the tool to decide, you may or may not get the results you expect.

 I had this re-enforced just the other day<g>.

 I got lazy and did OpenRecordset("mytable",, dbSeeChanges) when I was working with a SQL table.

 My code kept blowing up with the message "dbSeeChanges must be used when working with a SQL table with an identity column".

 But darn it, it was right there!

 I must have burned 30 minutes looking at it and trying to figure out what was wrong. Turns out, it wasn't happy until I did:

OpenRecordset("mytable",dbOpenDynaset, dbSeeChanges)

 Once I added that, it was happy.  Weird part is, a dynaset is supposed to be the default if you don't specify a recordset type.

2. On CurrentDB(), which Dale brought up -

  One thing that's not obvious is that CurrentDB() always returns a new database object reference, so you can have some un-expected results.

 For example:

 Dim doc as DAO.Document

 Set doc = CurrentDB().Containers!Tables.Documents(0)

 Debug.Print doc.name

will fail with an error on the Debug.Print because CurrentDB() went out of scope as soon as the Set line finished executing.

This works though:

dim db as DAO.Database
Dim doc as DAO.Document

Set db = CurrentDB()
Set doc = db.Containers!Tables.Documents(0)

Debug.Print doc.name

actually a good idea is to use what I outline in the article here:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2072-CurrentDB-vs-dbEngine-Workspaces-0-Databases-0-and-an-alternative.html

 Then your OK app wide.

 It's one of those things about Access resulting from the fact that "Access" is really made up of three seperate things; "Access", JET the database engine, and VBA.

HTH
Jim.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aikimarkCommented:
The default recordset open cursor is forwardonly.  Add a parameter to your OpenRecordset() invocation that specifies a dynaset type of cursor.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.