SteveL13
asked on
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("t blAuthoriz ationFormD ata")
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?
Dim RS As DAO.Recordset
Set RS = CurrentDb.OpenRecordset("t
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?
ASKER
Am getting an error...
Operation is not supported for this type of object.
Operation is not supported for this type of object.
ASKER
Seems to be stuck at...
.FindFirst "[ReqForPickupID]=" & Me.txtReqForPickUpID
.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?
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?
ASKER
Any thoughts?
ASKER
Sorry I didn't see your reply...
The field is numeric. It is a table. A local Access table.
The field is numeric. It is a table. A local Access table.
are the field names spelled correctly?
ASKER
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.
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
Wrap it with code blocks
tags if you would so that the code is easier to read.
ASKER
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.
Note: I've added spaces at the end of the tag to prevent it from actually displaying the code block as it did above.
ASKER
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
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:
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
ASKER
I'm still getting an error...
Operation is not supported for this type of object.
Operation is not supported for this type of object.
ASKER
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
If DCount("*", "tblAuthorizationFormData"
ASKER
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
If DCount("*", "tblAuthorizationFormData"
ASKER
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.
If DCount("*", "tblAuthorizationFormData"
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.
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.
ASKER
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
??
and the field txtReqForPickupID does exist on the form.
I did correct the code to read...
If DCount("*", "tblAuthorizationFormData"
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
OM Gang
ASKER
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!!
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!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The default recordset open cursor is forwardonly. Add a parameter to your OpenRecordset() invocation that specifies a dynaset type of cursor.
Set RS = CurrentDb.OpenRecordset("t
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