Solved

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

Posted on 2014-01-15
25
575 Views
Last Modified: 2014-01-23
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?
0
Comment
Question by:SteveL13
25 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
0
 

Author Comment

by:SteveL13
Comment Utility
Am getting an error...

Operation is not supported for this type of object.
0
 

Author Comment

by:SteveL13
Comment Utility
Seems to be stuck at...

    .FindFirst "[ReqForPickupID]=" & Me.txtReqForPickUpID
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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?
0
 

Author Comment

by:SteveL13
Comment Utility
Any thoughts?
0
 

Author Comment

by:SteveL13
Comment Utility
Sorry I didn't see your reply...

The field is numeric.  It is a table.  A local Access table.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
are the field names spelled correctly?
0
 

Author Comment

by:SteveL13
Comment Utility
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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.
0
 

Author Comment

by:SteveL13
Comment Utility
Sorry... how do I wrap it with code blocks or tags?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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.
0
 

Author Comment

by:SteveL13
Comment Utility
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

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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

0
 

Author Comment

by:SteveL13
Comment Utility
I'm still getting an error...

Operation is not supported for this type of object.
0
 

Author Comment

by:SteveL13
Comment Utility
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
0
 

Author Comment

by:SteveL13
Comment Utility
I'm getting a type mismatch error
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Since I expect that your [ReqForPickupID] field is probably numeric, Try:

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

Author Comment

by:SteveL13
Comment Utility
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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.
0
 

Author Comment

by:SteveL13
Comment Utility
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

??
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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?
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
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
0
 

Author Comment

by:SteveL13
Comment Utility
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!!
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
Comment Utility
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.
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
The default recordset open cursor is forwardonly.  Add a parameter to your OpenRecordset() invocation that specifies a dynaset type of cursor.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now