edit/add row to table in form load

[OTWarrior]

I am probably missing something obvious but here goes...

I have a form which loads in edit mode that is sourced to a table.
if there is a record availble, it pulls the details and dispalys them for editing.

However, the form also needs to generate a new row if the record is not there (based on a unique id)
I do not want duplicate records.

if I change the opencmd to add, it will always add.

how could I get the form to check for a record on load, and add a new one if it cannot find one? if it does find the record to go into edit mode?

would dlookup work?

[mattj]

What about just grabbing the recordset count on the open event? If less than 1, change the edit/add property, then goto new record.

HTH
Matt

[OTWarrior]

I have just looked at the help for recordset, and I cannot see anything about a count, how would I go about this?

[mattj]

Dim rst As Object
Set rst = Me.RecordsetClone
'Do a move last, as the RecordCount method can return the wrong number of records if you dont
rst.MoveLast
If rst.RecordCount > 0 'Then you have records
'do stuff, like set the add property
Else 'You have no records
'Set the allow additions property
Me.AllowAdditions = True
'Create new record
DoCmd.GoToRecord , , acNewRec
End If

'clean up the recordset
rst.close
Set rst = Nothing

[OTWarrior]

How does that code check the value of a field? from what I can understand it will always go to the if criteria, as there will be records in the table.

[mattj]

It doesn't. In that case, replace the recordcount with a FindFirst (which allows you to use criteria), and then the If statement would be "If rst.NoMatch Then ...

Something like (all air code, prob needs to be tweaked):

Dim rst As Object
Set rst = Me.RecordsetClone
'Do a move last, as the RecordCount method can return the wrong number of records if you dont
rst.MoveLast
Dim strCriteria as string
strCriteria = "TableField = value"
rst.FindFirst (strCriteria)
If rst.NoMatch 'Then you don't have records
'Set the allow additions property
Me.AllowAdditions = True
'Create new record
DoCmd.GoToRecord , , acNewRec
Else 'You have records
'do other stuff

End If

'clean up the recordset
rst.close
Set rst = Nothing