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!
Amour22015Asked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
Try saving the current record before running the update, and requerying the form after the update:


Private Sub AddSCG_Click()

Me.refresh '<--- Save current record in form

' 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

Me.requery '<-- Requery the form

''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 

Open in new window

0
 
mbizupCommented:
Your update syntax is incorrect.  It should be

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 )?
0
 
Amour22015Author Commented:
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!
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
mbizupCommented:
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 )?
0
 
mbizupCommented:
I think you need something like this

Ssqltemplate = "UPDATE TempTable SET scgNumber =???
WHERE TemplateId='" & tempid &"'"
0
 
mbizupCommented:
Not sure what you are trying to set scgNumber to, though.
0
 
Amour22015Author Commented:
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!
0
 
mbizupCommented:
Try this

Ssqltemplate = "UPDATE OCATempTable SET SCGNumber = '" & PermNum & "' WHERE TemplateId = '" & TempNumber & "'"
0
 
Amour22015Author Commented:
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!

0
 
Amour22015Author Commented:
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!
0
 
mbizupCommented:
<<
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 Sub

Open in new window

0
 
Amour22015Author Commented:
mbizup

Thank You!

Syntax error in update:
sSQLTemplate = "Update SCGNumber from TempTable where TemplateID = '" & TempNumber & "'"

please help and thank you!
0
 
mbizupCommented:
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.

0
 
Amour22015Author Commented:
mbizup

Thank you,

You did not post your corrected version?

Thank you!
0
 
mbizupCommented:
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:


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 

Open in new window

0
 
Amour22015Author Commented:
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!

0
 
mbizupCommented:
What were the results when you tried the code exactly as I posted it in http:#a37338493 ?
0
 
Amour22015Author Commented:
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!
0
 
mbizupCommented:
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 Sub

Open in new window

0
 
Amour22015Author Commented:
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!
0
 
mbizupCommented:
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?
0
 
Amour22015Author Commented:
mbizup

Thank you,

What is your form's recordsource?
OCATempTable

PermNum Control source is:
OCATempTable.SCGNumber

Thank you
0
 
Amour22015Author Commented:
Thank you for helping great job!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.