Find record if not add record

[Guest]

This may sound dumb and I should be able to do this, but I have been stuck
for the past couple hours. Anyway I have a pop-up form which has a query for
its record source. On this form I have 4 unbound fields which gets populated
during the Onload event from the calling form. What I am trying to do is
compare the fields against the queries record set. If the record exist then
have it populate the rest of the form if not then go to new record. Here is
what I came up with so far, but it's not working.
varX = DLookup("[A110ID]", "tblA110Comments", "[PracticalIDNumber] =" &
Me.PracticalIDNumber _
& " And [STUDENTID] = " & Me.StudentID & " And [InstGrader] = " &
Me.InstGrader & " And [PractCatID] = " & Me.PractCATID)
Set rst = Me.RecordsetClone
rst.FindFirst "A110ID = " & varX
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
Else
DoCmd.GoToRecord , , acNewRec
End If

The problem I am experiencing is if varX is null, which makes sense but I
need to get past that part to create a new record. Thanks so much for any
input. Should I create a record with the fields populated then bookmark it?
But then I could possibly have 2 records, but only one having data. Any help
would be appreciated.

 

[Michel Walsh]

Hi,


Add an additionnal test ?

If IsNull(varX) then
DoCmd.GoToRecord , , acNewRec
Else
rst.FindFirst "A110ID = " & varX
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
Else
DoCmd.GoToRecord , , acNewRec
End If
End If



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Actually I sort of combined the two already. However, I do appreciate your
response.

If IsNull(varX) = False Then
Set rst = Me.RecordsetClone
rst.FindFirst "A110ID = " & varX
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
End If
Else
DoCmd.GoToRecord , , acNewRec
End If

Hi,


Add an additionnal test ?

If IsNull(varX) then
DoCmd.GoToRecord , , acNewRec
Else
rst.FindFirst "A110ID = " & varX
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
Else
DoCmd.GoToRecord , , acNewRec
End If
End If



Hoping it may help,
Vanderghast, Access MVP

This may sound dumb and I should be able to do this, but I have been stuck
for the past couple hours. Anyway I have a pop-up form which has a query
for
its record source. On this form I have 4 unbound fields which gets
populated
during the Onload event from the calling form. What I am trying to do is
compare the fields against the queries record set. If the record exist
then
have it populate the rest of the form if not then go to new record. Here
is
what I came up with so far, but it's not working.
varX = DLookup("[A110ID]", "tblA110Comments", "[PracticalIDNumber] =" &
Me.PracticalIDNumber _
& " And [STUDENTID] = " & Me.StudentID & " And [InstGrader] = " &
Me.InstGrader & " And [PractCatID] = " & Me.PractCATID)
Set rst = Me.RecordsetClone
rst.FindFirst "A110ID = " & varX
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
Else
DoCmd.GoToRecord , , acNewRec
End If

The problem I am experiencing is if varX is null, which makes sense but I
need to get past that part to create a new record. Thanks so much for any
input. Should I create a record with the fields populated then bookmark
it?
But then I could possibly have 2 records, but only one having data. Any
help
would be appreciated.