Amour22015
asked on
Access 2007 Updating record
Thank you all for helping me!
I have this code but am getting errors:
Private Sub AddSCG_Click()
' Check if number is in the SCGTable
Set rsSCGNumber = New ADODB.Recordset
sSQLSCGNumber = "Select * from SCGTable where SCGNumber = '" & PermNum & "'"
rsSCGNumber.Open sSQLSCGNumber, CurrentProject.Connection
'No Match in SCGTable so go for it
If rsSCGNumber.EOF Then
Set rsTemplate = New ADODB.Recordset
sSQLTemplate = "Update SCGNumber from TempTable where TemplateID = '" & TempNumber & "'"
rsTemplate.Open sSQLTemplate, CurrentProject.Connection
If Not rs.EOF Then
CurrentDb.Execute (sSQLTemplate)
End If
OCADate = DDate
' There is a match so this is an error
Else
MsgBox "Number already taken please try again", vbOKOnly, "Required Data"
Me.PermNum.SetFocus
End If
End Sub
A user selects from a dropdown a ticket number, once selected.
the user in another text field inputs another value.
User then clicks on button to update selected record with that value.
I want the code to look at the SCGTable to see if already exist, if so then
Error message to the user letting them know.
If no match in SCGTable I want the update to OCATable
Error message I am receiving:
Run-time error '-2147217900 (80040e14)': Syntax error in UPDATE statement
(I have bold on statement above)
Thank you and Merry Christmas!
I have this code but am getting errors:
Private Sub AddSCG_Click()
' Check if number is in the SCGTable
Set rsSCGNumber = New ADODB.Recordset
sSQLSCGNumber = "Select * from SCGTable where SCGNumber = '" & PermNum & "'"
rsSCGNumber.Open sSQLSCGNumber, CurrentProject.Connection
'No Match in SCGTable so go for it
If rsSCGNumber.EOF Then
Set rsTemplate = New ADODB.Recordset
sSQLTemplate = "Update SCGNumber from TempTable where TemplateID = '" & TempNumber & "'"
rsTemplate.Open sSQLTemplate, CurrentProject.Connection
If Not rs.EOF Then
CurrentDb.Execute (sSQLTemplate)
End If
OCADate = DDate
' There is a match so this is an error
Else
MsgBox "Number already taken please try again", vbOKOnly, "Required Data"
Me.PermNum.SetFocus
End If
End Sub
A user selects from a dropdown a ticket number, once selected.
the user in another text field inputs another value.
User then clicks on button to update selected record with that value.
I want the code to look at the SCGTable to see if already exist, if so then
Error message to the user letting them know.
If no match in SCGTable I want the update to OCATable
Error message I am receiving:
Run-time error '-2147217900 (80040e14)': Syntax error in UPDATE statement
(I have bold on statement above)
Thank you and Merry Christmas!
ASKER
mbizup
Thank you,
I want to edit (UPDATE) an existing record(TempTable.SCGNumber
The record gets processed at a later time by another user.
I want the code to look at the SCGTable to see if already exist, if so then
Error message to the user letting them know.
otherwise:
Update The TempTable.SCGNumber
Sorry this comment:
If no match in SCGTable I want the update to OCATable
Should have been:
If no match in SCGTable I want the update to TempTable
Thank you for helping me!
Thank you,
I want to edit (UPDATE) an existing record(TempTable.SCGNumber
The record gets processed at a later time by another user.
I want the code to look at the SCGTable to see if already exist, if so then
Error message to the user letting them know.
otherwise:
Update The TempTable.SCGNumber
Sorry this comment:
If no match in SCGTable I want the update to OCATable
Should have been:
If no match in SCGTable I want the update to TempTable
Thank you for helping me!
Okay ... so to get the syntax right, what field in TempTable needs to be updated, and what criteria are used to identify which records get updated (or do all records get updated )?
I think you need something like this
Ssqltemplate = "UPDATE TempTable SET scgNumber =???
WHERE TemplateId='" & tempid &"'"
Ssqltemplate = "UPDATE TempTable SET scgNumber =???
WHERE TemplateId='" & tempid &"'"
Not sure what you are trying to set scgNumber to, though.
ASKER
mbizup
Thank you for helping me!
I Have:
sSQLTemplate = "UPDATE OCATempTable SET SCGNumber = & PermNum WHERE TemplateId='" & TempNumber & "'"
TempNumber is: a textbox (or Combox), Me.TempNumber
And:
PermNum is a textbox, Me.PermNum
But I am still getting a syntax
Not sure what you are trying to set scgNumber to, though
SCGNumber = PermNum what user types in the textbox on the Form
Please help
Thank You!
Thank you for helping me!
I Have:
sSQLTemplate = "UPDATE OCATempTable SET SCGNumber = & PermNum WHERE TemplateId='" & TempNumber & "'"
TempNumber is: a textbox (or Combox), Me.TempNumber
And:
PermNum is a textbox, Me.PermNum
But I am still getting a syntax
Not sure what you are trying to set scgNumber to, though
SCGNumber = PermNum what user types in the textbox on the Form
Please help
Thank You!
Try this
Ssqltemplate = "UPDATE OCATempTable SET SCGNumber = '" & PermNum & "' WHERE TemplateId = '" & TempNumber & "'"
Ssqltemplate = "UPDATE OCATempTable SET SCGNumber = '" & PermNum & "' WHERE TemplateId = '" & TempNumber & "'"
ASKER
mbizup
Thank you!
Now I am getting error:
Run-Time error '3704': Operaton is not allowed when the object is closed:
If Not rsTemplate.EOF Then
please help and thank you!
Thank you!
Now I am getting error:
Run-Time error '3704': Operaton is not allowed when the object is closed:
If Not rsTemplate.EOF Then
please help and thank you!
ASKER
mbizup
Thank you,
Okay ... so to get the syntax right, what field in TempTable needs to be updated, and what criteria are used to identify which records get updated (or do all records get updated )?
The only record that gets updated is the one that matches the selection from the dropdown
Field SCGNumber gets updated in TempTable
Me.PermNum has control Source from Table: TempTable
Me.TempNumber has no control source and is a dropdown that the user selects. The dropdown is linked to the TempTable.
Thank You!
Thank you,
Okay ... so to get the syntax right, what field in TempTable needs to be updated, and what criteria are used to identify which records get updated (or do all records get updated )?
The only record that gets updated is the one that matches the selection from the dropdown
Field SCGNumber gets updated in TempTable
Me.PermNum has control Source from Table: TempTable
Me.TempNumber has no control source and is a dropdown that the user selects. The dropdown is linked to the TempTable.
Thank You!
<<
Now I am getting error:
Run-Time error '3704': Operaton is not allowed when the object is closed:
If Not rsTemplate.EOF Then
>>
That line of code is not in the code you originally posted. Have you changed things, or is the code posted in your question incomplete?
There are a couple of other mistakes in the original post. Try this:
Now I am getting error:
Run-Time error '3704': Operaton is not allowed when the object is closed:
If Not rsTemplate.EOF Then
>>
That line of code is not in the code you originally posted. Have you changed things, or is the code posted in your question incomplete?
There are a couple of other mistakes in the original post. Try this:
Private Sub AddSCG_Click()
' Check if number is in the SCGTable
Set rsSCGNumber = New ADODB.Recordset
sSQLSCGNumber = "Select * from SCGTable where SCGNumber = '" & PermNum & "'"
rsSCGNumber.Open sSQLSCGNumber, CurrentProject.Connection
'No Match in SCGTable so go for it
If rsSCGNumber.EOF Then
'' Set rsTemplate = New ADODB.Recordset
sSQLTemplate = "Update SCGNumber from TempTable where TemplateID = '" & TempNumber & "'"
''rsTemplate.Open sSQLTemplate, CurrentProject.Connection
''If Not rs.EOF Then
CurrentDb.Execute sSQLTemplate, dbFailOnError
''End If
OCADate = DDate
' There is a match so this is an error
Else
MsgBox "Number already taken please try again", vbOKOnly, "Required Data"
Me.PermNum.SetFocus
End If
End SubASKER
mbizup
Thank You!
Syntax error in update:
sSQLTemplate = "Update SCGNumber from TempTable where TemplateID = '" & TempNumber & "'"
please help and thank you!
Thank You!
Syntax error in update:
sSQLTemplate = "Update SCGNumber from TempTable where TemplateID = '" & TempNumber & "'"
please help and thank you!
Sorry. That was a copy /paste mistake and is what you had posted in your original post.
Please use my corrected version of the update statement instead.
Please use my corrected version of the update statement instead.
ASKER
mbizup
Thank you,
You did not post your corrected version?
Thank you!
Thank you,
You did not post your corrected version?
Thank you!
No - I had mistakenly not included that when I corrected other parts of your code.
I was posting from a cellphone so posting the full code last night was not easy.
I was hoping that you would yourself replace the "sSQLTemplate = " line with the one from here where the syntax error was corrected - http:#a37336628 (that's what I was trying to ask in my last post).
Anyhow, I have done that here, so try this:
I was posting from a cellphone so posting the full code last night was not easy.
I was hoping that you would yourself replace the "sSQLTemplate = " line with the one from here where the syntax error was corrected - http:#a37336628 (that's what I was trying to ask in my last post).
Anyhow, I have done that here, so try this:
Private Sub AddSCG_Click()
' Check if number is in the SCGTable
Set rsSCGNumber = New ADODB.Recordset
Ssqltemplate = "UPDATE OCATempTable SET SCGNumber = '" & PermNum & "' WHERE TemplateId = '" & TempNumber & "'"
rsSCGNumber.Open sSQLSCGNumber, CurrentProject.Connection
'No Match in SCGTable so go for it
If rsSCGNumber.EOF Then
'' Set rsTemplate = New ADODB.Recordset
sSQLTemplate = "Update SCGNumber from TempTable where TemplateID = '" & TempNumber & "'"
''rsTemplate.Open sSQLTemplate, CurrentProject.Connection
''If Not rs.EOF Then
CurrentDb.Execute sSQLTemplate, dbFailOnError
''End If
OCADate = DDate
' There is a match so this is an error
Else
MsgBox "Number already taken please try again", vbOKOnly, "Required Data"
Me.PermNum.SetFocus
End If
End Sub ASKER
mbizup
Thank you!
So this is what I have (cleaned up a little):
Private Sub AddSCG_Click()
' Check if number is in the SCGTable
Set rsTemplate = New ADODB.Recordset
sSQLTemplate = "UPDATE OCATempTable SET SCGNumber = '" & PermNum & "' WHERE TemplateId = '" & TempNumber & "'"
rsTemplate.Open sSQLTemplate, CurrentProject.Connection
'No Match in SCGTable so go for it
If rsTemplate.EOF Then
CurrentDb.Execute sSQLTemplate, dbFailOnError
'Get the current date and input date to OCATempTable.
OCADate = DDate
' There is a match so this is an error
Else
MsgBox "Number already taken please try again", vbOKOnly, "Required Data"
Me.PermNum.SetFocus
End If
End Sub
Private Sub Form_Load()
Dim sSQLTemplate As String
Dim rsTemplate As New ADODB.Recordset
End Sub
But I am getting an error on:
If rsTemplate.EOF Then
The error is:
Run-time error '3704': Operation is not allowed when the object is closed.
Thank you for helping me!
Thank you!
So this is what I have (cleaned up a little):
Private Sub AddSCG_Click()
' Check if number is in the SCGTable
Set rsTemplate = New ADODB.Recordset
sSQLTemplate = "UPDATE OCATempTable SET SCGNumber = '" & PermNum & "' WHERE TemplateId = '" & TempNumber & "'"
rsTemplate.Open sSQLTemplate, CurrentProject.Connection
'No Match in SCGTable so go for it
If rsTemplate.EOF Then
CurrentDb.Execute sSQLTemplate, dbFailOnError
'Get the current date and input date to OCATempTable.
OCADate = DDate
' There is a match so this is an error
Else
MsgBox "Number already taken please try again", vbOKOnly, "Required Data"
Me.PermNum.SetFocus
End If
End Sub
Private Sub Form_Load()
Dim sSQLTemplate As String
Dim rsTemplate As New ADODB.Recordset
End Sub
But I am getting an error on:
If rsTemplate.EOF Then
The error is:
Run-time error '3704': Operation is not allowed when the object is closed.
Thank you for helping me!
What were the results when you tried the code exactly as I posted it in http:#a37338493 ?
ASKER
mbizup
Thank you!
It was the same error, so that is when I started cleaning up a little to make it more readable.
Did you mean to leave this:
sSQLTemplate = "Update SCGNumber from TempTable where TemplateID = '" & TempNumber & "'"
in your code?
In this:
rsSCGNumber.Open sSQLSCGNumber, CurrentProject.Connection
where is:
sSQLSCGNumber
Thank you!
Thank you!
It was the same error, so that is when I started cleaning up a little to make it more readable.
Did you mean to leave this:
sSQLTemplate = "Update SCGNumber from TempTable where TemplateID = '" & TempNumber & "'"
in your code?
In this:
rsSCGNumber.Open sSQLSCGNumber, CurrentProject.Connection
where is:
sSQLSCGNumber
Thank you!
Please run this as-is, without cleaning it up and let me know how it works:
Private Sub AddSCG_Click()
' Check if number is in the SCGTable
Set rsSCGNumber = New ADODB.Recordset
sSQLSCGNumber = "Select * from SCGTable where SCGNumber = '" & PermNum & "'"
rsSCGNumber.Open sSQLSCGNumber, CurrentProject.Connection
'No Match in SCGTable so go for it
If rsSCGNumber.EOF Then
'' Set rsTemplate = New ADODB.Recordset
sSQLTemplate = "UPDATE OCATempTable SET SCGNumber = '" & PermNum & "' WHERE TemplateId = '" & TempNumber & "'"
''rsTemplate.Open sSQLTemplate, CurrentProject.Connection
''If Not rs.EOF Then
CurrentDb.Execute sSQLTemplate, dbFailOnError
''End If
OCADate = DDate
' There is a match so this is an error
Else
MsgBox "Number already taken please try again", vbOKOnly, "Required Data"
Me.PermNum.SetFocus
End If
End SubASKER
mbizup
Thank you for helping!
When I click on the button nothing happens
But when I click on the exit button (just closes current form and opens another form) I get an error:
Write Conflict
This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made.
Copying the chages to the clipboard will let you look at the values the other user entered, and then paste your chages back in if you decide to ma changes.
Save Recore Copy to clipboard Drop Changes
Please help and thank you!
Thank you for helping!
When I click on the button nothing happens
But when I click on the exit button (just closes current form and opens another form) I get an error:
Write Conflict
This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made.
Copying the chages to the clipboard will let you look at the values the other user entered, and then paste your chages back in if you decide to ma changes.
Save Recore Copy to clipboard Drop Changes
Please help and thank you!
Well - something is happening (it sounds like the record was updated - probably causing the write conflict error when you try to close the form).
What is your form's recordsource?
What is your form's recordsource?
ASKER
mbizup
Thank you,
What is your form's recordsource?
OCATempTable
PermNum Control source is:
OCATempTable.SCGNumber
Thank you
Thank you,
What is your form's recordsource?
OCATempTable
PermNum Control source is:
OCATempTable.SCGNumber
Thank you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for helping great job!
UPDATE YourTable
SET yourField =something
WHERE (Some optional criteria )
That said, are you trying to edit an existing record (UPDATE) or add a new record (INSERT )?