Updating a single record in a table 1

[bxgti4x4]

I have an update query (QryClearCompetitorDetails) which is used to re-set the data in a number of fields of a table (TblCompetitor)as follows:

 UPDATE TblCompetitor SET TblCompetitor.TxtName = Null, TblCompetitor.TxtInitials = Null, TblCompetitor.TxtRank_Rating = Null, TblCompetitor.TxtUnit = Null, TblCompetitor.fWRN = Null, TblCompetitor.TxtClass = Null, TblCompetitor.fWhiteheadInd = True, TblCompetitor.fRoupelInd = True, TblCompetitor.fFibuaInd = True, TblCompetitor.fWhiteheadTeam = True, TblCompetitor.fRoupelTeam = True, TblCompetitor.fFibuaTeam = True, TblCompetitor.fPetersPrize = Null
WITH OWNERACCESS OPTION;

Data is entered into this table via a form (FrmCompetitorData) which displays the details for a particular competitor.

I want to be able to re-set the data for a single record, using a command button, and have written the following code:

  Private Sub ClearDetails_Click()
On Error GoTo Err_ClearDetails_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "QryClearCompetitorDetails"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    stLinkCriteria = "[LngCompetitorNo]=" & "'" & Me![LngCompetitorNo] & "'"
    
Exit_ClearDetails_Click:
    Exit Sub

Err_ClearDetails_Click:
    MsgBox Err.Description
    Resume Exit_ClearDetails_Click
    
End Sub

The problem is that each time, I receive a message asking if I want to re-set the data for all the records in the table.

Is there any way I should modify the code, or do I need to approach the problem in a totally different way?

Any help would be much appreciated
Best Regards
John

 

[RoyVidar]

[tt]docmd.setwarnings false
' execute the query
docmd.setwarnings true[/tt]

You could probably execute the query trough the DAO database object (not to sure about this one, as I hardly use DAO) or the ADO connection object too.

Roy-Vidar

 

[KenReay]

Hi

Dim strSQL as String

strSQL = "SET TblCompetitor.TxtName = Null, TblCompetitor.TxtInitials = Null, TblCompetitor.TxtRank_Rating = Null, TblCompetitor.TxtUnit = Null, TblCompetitor.fWRN = Null, TblCompetitor.TxtClass = Null, TblCompetitor.fWhiteheadInd = True, TblCompetitor.fRoupelInd = True, TblCompetitor.fFibuaInd = True, TblCompetitor.fWhiteheadTeam = True, TblCompetitor.fRoupelTeam = True, TblCompetitor.fFibuaTeam = True, TblCompetitor.fPetersPrize = Null
WHERE "[LngCompetitorNo]=" & "'" & Me![LngCompetitorNo] & "' WITH OWNERACCESS OPTION;"

DoCmd.RunSQL strSQL

A couple of other points, to suppress the wrning messages, use DoCmd.SetWarnings False, (to put them on again DoCmd.SetWarnings True). Also I am assuming [LngCompetitorNo is a string, if it is a number type then you do not need the ', so WHERE "[LngCompetitorNo]=" & Me![LngCompetitorNo] & " WITH


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[bxgti4x4]

Ken,

Thanks for the revised code. Only one problem, when I try to run the code, I receive an error message which highlights the second [LngCompetitorNo] in the WHERE statement and says "Sub or Function Not Defined".

[LngCompetitorNo] is a numeric field so I tried the second setting as well as the first that you suggested, but I get the same response every time.

Best Regards
John

 

[PHV]

strSQL = "UPDATE TblCompetitor SET TxtName = Null, TxtInitials = Null, TxtRank_Rating = Null, TxtUnit = Null, fWRN = Null, TxtClass = Null, fWhiteheadInd = True, fRoupelInd = True, fFibuaInd = True, fWhiteheadTeam = True, fRoupelTeam = True, fFibuaTeam = True, fPetersPrize = Null" _
& " WHERE LngCompetitorNo ='" & Me![LngCompetitorNo] & "' WITH OWNERACCESS OPTION"
CurrentDB.Execute strSQL

If LngCompetitorNo is defined as numeric in TblCompetitor then get rid of the single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bxgti4x4]

PHV,

Thanks for the help. I used the code as you suggested, with the single quotes included and received a message "Data type mismatch in expression". I removed the single quotes and ran the code again. This time no message but no update either!

Any ideas?

Best Regards
John

 

[PHV]

Is Me![LngCompetitorNo] populated with an existing value ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bxgti4x4]

PHV

Yes the table is populated and the record in my form is displaying a number

 

[PHV]

Try to replace this:
CurrentDB.Execute strSQL
By this (without SetWarnings = False):
DoCmd.RunSQL strSQL
You'll see if you have a locked record issue.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Eupher]

bxgti4x4,

And the name of the control on your form that holds [LngCompetitorNo] is also LngCompetitorNo?

Ken S.

 

[KenReay]

Hi

The easier way to trouble shoot something like this is:

Put a break point after the DoCmd.RunSQL

Run the code, and when it stops at the breakpoint, in the immediate window, print the sql statement (? strSQL), copy the output and paste it into the SQL view of a new query

run the query

observe the error messages(s), which are usually more helpful that when running the code

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[bxgti4x4]

PHV,

Thought I would try your idea first. I now get the message that I am about to update 1 row. So far so good. I then press OK and I get the message box - "Microsoft Access cannot update all the records in the update query"
It then goes on to say that it could not update 1 record due to validation rule violations. If I press yes to ignore the errors and rune the query, nothing happens.

Does this give you a clue as to the problem?

Best Regards
John

 

[PHV]

At least one value in your update query is illegal for TblCompetitor, the most likely is a field not allowing Null.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bxgti4x4]

PHV,

Thanks for the tip. I went carefully through each field and found that the Yes/No fields were set to Null rather than true or false. It works now.

Two last questions:
1 I want the TxtClass field to be set to "O" as opposed to "X" or "T" which can be entered. The SQL view of the original query shows TxtClass = "O" but if I put this into the code it does not like it and comes up with a syntax error warning. If I remove the inverted commas, it displays a message box asking me to enter the parameter value. How do I get it to re-set the value to "O"?
2 When I run the code, the data in the table is updated but the data in the form remains until I move to the next record and back again. I have tried using "RefreshDatabaseWindow" to get the form to clear but this does not work. How should I do this?

Best Regards
John

 

[PHV]

Have a look to the Refresh and/or Requery methods of the Form object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bxgti4x4]

Thanks PHV, the requery code was what I wanted.

Any chance you could help with the first part of my previous question? It would tie up the last loose end.

Best Regards
John

 

[PHV]

What are ALL the properties of the TxtClass field ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bxgti4x4]

PHV,
Properties as follows:
Field Size 50
Format Blank
Input Mask Blank
Caption Class
Default Value "O" (Letter O)
Validation Rule Blank
Validation Text blank
Required Yes
Allow Zero Length No
Indexed No
Unicode Comp. No
IME Mode No Control
IME Sentence Mode None
Smart Tags None

Hope this helps

Best Regards
John

 

[PHV]

You wanted this ?
strSQL = "UPDATE TblCompetitor SET TxtName = Null, TxtInitials = Null, TxtRank_Rating = Null, TxtUnit = Null, fWRN = Null, TxtClass = [highlight]'O'[/highlight], fWhiteheadInd = True, fRoupelInd = True, fFibuaInd = True, fWhiteheadTeam = True, fRoupelTeam = True, fFibuaTeam = True, fPetersPrize = Null" _
& " WHERE LngCompetitorNo ='" & Me![LngCompetitorNo] & "' WITH OWNERACCESS OPTION"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bxgti4x4]

PHV,

That's done it. It just remains for me to say a big thank you for all your patience and help. It is very much appreciated.

Best Regards
John