CODEWITHSUNDEEP
ms-accessvba
D347HxD
D347HxD

Reputation: 465

Check table if there is fields if not create one if so edit it?

I am trying to make a button that adds data to a table. Though I only want to be working with one field.

I use this atm:

rec.AddNew
rec("WorkOrder") = Me.ProjectAddWorkOrderTxt
rec.Update

With 3 different buttons that change 3 other columns in that field. Every time I push the button to add the information into the table, it creates a new field with the data under the correct column. Why is obvious, because the rec.AddNew. It creates a new field.

Is there a way to check if there is a field in the table yet, and if so it just edits that one field and if not it creates a new one?

There is a main form with 5 buttons on it; which pop up 5 different forms. These forms have 1 text box and 1 button. None of the forms are bound to any record sources.

What I want is for you to be able to enter data into the text box, and it adds that data to the temp form when you push that 1 button. At the same time it closes the form down revealing the main one again.

Then, when I push another button on the main form it grabs all the information currently on the temptable, and puts it into the maintable. Then it deletes all data on the temptable.

--

This is the full code for the button with the answer's below additon

Private Sub ProjectWorkOrderBackBtn_Click()

Dim db As Database
Dim rec As DAO.Recordset

Set db = CurrentDb
Set rec = db.OpenRecordset("Select * from ProjectsTempT")

rec.FindFirst "WorkOrder='" & Me.ProjectAddWorkOrderTxt.Value & "'"

If (rec.NoMatch) Then
  rec.AddNew
Else
  rec.Edit
End If

rec("WorkOrder").Value = Me.ProjectAddWorkOrderTxt.Value
rec.Update

Set rec = Nothing
Set db = Nothing
Me.ProjectAddWorkOrderTxt = Null
DoCmd.Close
End Sub

Upvotes: 1

Views: 153

Answers (2)

Johnny Bones
Johnny Bones

Reputation: 8402

You could also pare the code a little bit, which would also increase your response time.

Private Sub ProjectWorkOrderBackBtn_Click()

Dim db As Database
Dim rec As DAO.Recordset

Set db = CurrentDb
Set rec = db.OpenRecordset("Select * from ProjectsTempT WHERE WorkOrder='" & Me.ProjectAddWorkOrderTxt.Value & "'")

If rec.EOF = "True" Then
  rec.AddNew
Else
  rec.Edit
End If

rec("WorkOrder").Value = Me.ProjectAddWorkOrderTxt.Value
rec.Update

Set rec = Nothing
Set db = Nothing
Me.ProjectAddWorkOrderTxt = Null
DoCmd.Close
End Sub

Keep in mind that Access is Client Side, which means it pulls the entire dataset to your local machine before performing a function. Reducing the size of that dataset will speed up the process, so just bring in the records you need instead of pulling the whole table and doing a FindFirst.

Upvotes: 1

jacouh
jacouh

Reputation: 8741

Please test this if it works ?

Private Sub ProjectWorkOrderBackBtn_Click()

  Dim db As Database
  Dim rec As DAO.Recordset

  Set db = CurrentDb
  Set rec = db.OpenRecordset("ProjectsTempT", dbOpenDynaset, dbSeeChanges)

  'On Error Resume Next

  '
  'rec.FindFirst "WorkOrder='" & Me.ProjectAddWorkOrderTxt.Value & "'"
  '

  If (rec.EOF) Then
    rec.AddNew
  Else
    rec.Edit
  End If

  rec("WorkOrder").Value = Me.ProjectAddWorkOrderTxt.Value
  rec.Update

  Set rec = Nothing
  Set db = Nothing

  'On Error Goto 0

  '
  'Me.ProjectAddWorkOrderTxt = Null
  'DoCmd.Close
  '
End Sub

The table ProjectsTempT must exist.

Upvotes: 1

Related Questions