Create record if missing

[Guest]

I have a database that tracks information on a number of people. The main
table has a combined primary key using the EmployeeID and date.
I have created a form to allow users to add data to the table with two combo
boxes, the first offering a list of Employees and the second offering a list
of recent dates. This allows the user to select the employee they wish to add
data for and the date it applies to.
However, I need to automatically generate a new record for that particular
employee and date if it does not already exist.

Can anyone point me in the right direction for doing this?

Thanks.

 

[Guest]

Ok, I managed to figure this out myself, any comments are welcome.

Private Sub UpdateForm()
' Find the record that matches Combo box selections
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[UserID] = " & Str(Me![cmbUserName]) & "AND [WeekBegin] =
#" & Format(Me![cmbWeekBegin], "mm/dd/yyyy") & "#"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

' Create new record if it does not exist
If rs.NoMatch Then
With rs
.AddNew
!UserID = Me![cmbUserName]
!WeekBegin = Me![cmbWeekBegin]
.Update
End With
UpdateForm
End If

End Sub

 

[Stefan Hoffmann]

hi,

Ok, I managed to figure this out myself, any comments are welcome.

Private Sub UpdateForm()
' Find the record that matches Combo box selections
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[UserID] = " & Str(Me![cmbUserName]) & "AND [WeekBegin] =
#" & Format(Me![cmbWeekBegin], "mm/dd/yyyy") & "#"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

' Create new record if it does not exist
If rs.NoMatch Then
With rs
.AddNew
!UserID = Me![cmbUserName]
!WeekBegin = Me![cmbWeekBegin]
.Update
End With
UpdateForm
End If

End Sub

You can also use

CurrentDb.Execute "INSERT INTO Table (UserID, WeekBegin) " & _
"VALUES (" & cmdUserName.Value & ", " & _
cmbWeekBegin.Value & ")"

Using it without dbFailOnError as second parameter, it fails silently,
if violating any table constraint. So it will do, if you have an unique
combined index on UserID and WeekBegin.


mfG
--> stefan <--