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?

[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.

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

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
mbizupCommented:
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

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
Amour22015Author Commented:
Thank you for helping great job!
0
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.