Update One Record Field In Table

[Kevsim]

I have a Junction Table with 2 fields FixID and DocsID. The FixID key does not have duplicates but the DocsID key has.

The below statement works to Update all records for DocsID in the Table Junction (T_Junction), it changes all DocsID keys to the same number, Text 29 selects the new DocsID key number.

I only require to change one DocsID key number, I know I need a WHERE clause but can not make it work, I was trying to use a reference to the FixID key for that joining record.
There is a Text box on the form (Text188) which displays the FixID record number.

Dim strSQL As String
strSQL = "UPDATE T_Junction Set[DocsID]= " & Me.[Text29] & ";"
End Sub

I have had a look on the web but can not find the Where clause referring to a text box.
I would appreciate some assistance.
kevsim

 

[PHV]

Something like this ?
strSQL = "UPDATE T_Junction Set DocsID=" & Me!Text29 & " WHERE FixID=" & Me!Text188

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

First, kick your development up a notch and change Text29 to txtNewDocID or something similar. Then, I can only guess this might work. Again, the name of the control with FixID should have a descriptive name.

  Dim strSQL As String
  strSQL = "UPDATE T_Junction Set DocsID= " & _
     Me.txtNewDocID & " WHERE FixID = " & Me.txtFixID
  Currentdb.Execute strSQL, dbFailOnError

Duane
Hook'D on Access
MS Access MVP

 

[Kevsim]

PHV,
Thanks for the eply.
I had tried that but received error "Sub or Function not defined", the cursor stopped on FixID
kevsim

 

[PHV]

What is YOUR actual code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Kevsim]

PHV,
I was using

Dim strSQL As String
strSQL = "UPDATE T_Junction Set[DocsID]= " & Me.[Text29] & ""
WHERE FixID = " & Me.Text188;"
End Sub
kevsim

 

[Kevsim]

PHV,
Code should read

Dim strSQL As String
strSQL = "UPDATE T_Junction Set[DocsID]= " & Me.[Text29] & ""
WHERE FixID = " & Me.Text188;"
DoCmd.RunSQL strSQL
End Sub

kevsim

 

[PHV]

So, simply use mine.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886