Edit Table

[RufussMcGee]

Okay I built is database to keep track of quotes so when the user clicks a button it gets the next quote number. Had my form bound to the table but when the user hit cancel of course the data was put onto the table with they picked. Thus I unbound the form and use expression like so.

rec.new
rec(11).Value = txtQuoteNumber.Value

Instead of new I want to update the line where the data is stored. I tried rec.update but how do you tell it what ID line you are looking for?

 

[acent]

I'm not sure I understand the question exactly, but here goes.

You had a bound form that brings up a new quote when the user clicks a button. Then the user clicks the cancel button and the record is saved. <- sounds like the cancel button was not coded correctly.

So you unbound the form, and if the user gets to editing a quote, it still adds a new one. <- sounds like you need to test if editing a quote or not

What I would probably do, is open the form and set DataEntry to true if they are entering a new one, and false if not. Then you can test for it

If Me.DataEntry Then 
  rec.New
Else
  rec.Update
End If

Hope this points you in the right direction


"If it's stupid but works, it isn't stupid."
-Murphy's Military Laws

 

[kjv1611]

I'm almost thinking that you'll be best running a SQL statement from within VBA or else create a query and create that...

Something like this perhaps:

If the table's Value field is numeric:

[tt]
Private Sub cmdUpdateThatQuote_Click()
  Dim strSQL As String
  strSQL = "UPDATE tblMyTable " & _
           "SET tblMyTable.Value = " & txtQuoteNumber.Value
  DoCmd.SetWarnings False
  DoCmd.RunSQL strSQL
  DoCmd.SetWarnings True

  strSQL = vbNullString
End Sub
[/tt]

If the table's Value field is NOT numeric:

[tt]
Private Sub cmdUpdateThatQuote_Click()
  Dim strSQL As String
  strSQL = "UPDATE tblMyTable " & _
           "SET tblMyTable.Value = '" & txtQuoteNumber.Value & "'"
  DoCmd.SetWarnings False
  DoCmd.RunSQL strSQL
  DoCmd.SetWarnings True

  strSQL = vbNullString
End Sub
[/tt]

Post back to let us know what/if anything worked, and if you got any error mesages, etc...


--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

OOPS! - I left out one of the most important parts... I'll repost the code to include the WHERE clause:

If the table's Value field (and ID field) are numeric:

Private Sub cmdUpdateThatQuote_Click()
  Dim strSQL As String
  strSQL = "UPDATE tblMyTable " & _
           "SET tblMyTable.Value = " & txtQuoteNumber.Value & " " & _
           "WHERE tblMyTable.ID = " & txtQuoteID_OnForm.Value
  DoCmd.SetWarnings False
  DoCmd.RunSQL strSQL
  DoCmd.SetWarnings True

  strSQL = vbNullString
End Sub

If the table's Value field (and ID field) are NOT numeric:

Private Sub cmdUpdateThatQuote_Click()
  Dim strSQL As String
  strSQL = "UPDATE tblMyTable " & _
           "SET tblMyTable.Value = '" & txtQuoteNumber.Value & "'"
           "WHERE tblMyTable.ID = '" & txtQuoteID_OnForm.Value & "'"
  DoCmd.SetWarnings False
  DoCmd.RunSQL strSQL
  DoCmd.SetWarnings True

  strSQL = vbNullString
End Sub

I'm assuming that it's either both numeric or neither numeric for the examples. If it's one numeric, one not, then you'll need to edit the code accordingly.

Let us know if any of it helped...

--

"If to err is human, then I must be some kind of human!" -Me