Checking to see if a record exitsts in a recordset.

[JBurlison]

I want to see if a record exists in a record set and if not then i want to add it. this is what i have so far

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Sainumber As String


Set db = CurrentDb
Set rs = db.OpenRecordset("BuildInfoBySaiNumber", dbOpenDynaset)
Sainumber = Me.Sai_Serial_Number.Value

With rs
    If "(check for saiserialnumber)=False" Then
        .AddNew
        .Fields("Sai Serial Number") = Sainumber
        .Update
    End If
End With

 

[boblarson]

Dim db As DAO.Database
Dim rs As DAO.Recordset


Set db = CurrentDb
Set rs = db.OpenRecordset("BuildInfoBySaiNumber", dbOpenDynaset)


If DCount("[Sai Serial Number]","BuildInfoBySaiNumber","[Sai Serial Number]=" & Me.Sai_Serial_Number) > 0 Then
   Exit Sub
Else

With rs
     .AddNew
     .Fields("Sai Serial Number") = Me.Sai_Serial_Number
     .Update
End With

rs.Close
Set rs = Nothing

But, if Sai_Serial_Number is text and not a number then it would be:

Dim db As DAO.Database
Dim rs As DAO.Recordset


Set db = CurrentDb
Set rs = db.OpenRecordset("BuildInfoBySaiNumber", dbOpenDynaset)


If DCount("[Sai Serial Number]","BuildInfoBySaiNumber","[Sai Serial Number]='" & Me.Sai_Serial_Number & "'") > 0 Then
   Exit Sub
Else

With rs
     .AddNew
     .Fields("Sai Serial Number") = Me.Sai_Serial_Number
     .Update
End With

rs.Close
Set rs = Nothing

 

[JBurlison]

so i want the form to open up into the number that it was on on the previous page how would i apply that filter?

Private Sub Label106_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Sainumber As String

Set db = CurrentDb
Set rs = db.OpenRecordset("BuildInfoBySaiNumber", dbOpenDynaset)
Sainumber = [Sai Serial Number]

If DCount("[Sai Serial Number]", "BuildInfoBySaiNumber", "[Sai Serial Number]='" & Me.Sai_Serial_Number & "'") > 0 Then

    DoCmd.OpenForm "Build", acNormal
    DoCmd.ApplyFilter , [BuildInfoBySaiNumber]![Sai Serial Number] = Sainumber
    Filter = True
   Exit Sub
Else

With rs
     .AddNew
     .Fields("Sai Serial Number") = Me.Sai_Serial_Number
     .Update
End With

DoCmd.OpenForm "Build", acNormal
DoCmd.ApplyFilter , [BuildInfoBySaiNumber]![Sai Serial Number] = Sainumber
Filter = True

End If
rs.Close
Set rs = Nothing

End Sub

 

[boblarson]

If you just want the form filtered then you can use:

DoCmd.OpenForm "Build", acNormal,,"[BuildInfoBySaiNumber]![Sai Serial Number] ='" & Sainumber & "'"
Forms!Build.FilterOn = True

 

[JBurlison]

it comes up empty, the code is adding the record but the filter is not bringing up the number??? any thoughts

 

[boblarson]

Is your serial number actually a number or is it text?

 

[JBurlison]

Its a Number

 

[boblarson]

Ah then we need to revise to:

Dim Sainumber As [color=red]Long[/color]

DoCmd.OpenForm "Build", acNormal,,"[BuildInfoBySaiNumber]![Sai Serial Number] =" & Sainumber
Forms!Build.FilterOn = True

 

[JBurlison]

Ack i see why i have build types linked there also and have not been filled. the filter dose not work because the other link is null. poped a value in and boom got it

 

[JBurlison]

Ok bob got it thanks again! heres the final whammy!

Private Sub Label106_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Sainumber As Integer
Dim Buildtype As String

Set db = CurrentDb
Set rs = db.OpenRecordset("BuildInfoBySaiNumber", dbOpenDynaset)
Sainumber = [Sai Serial Number]


If Me.Builds_Build_Type = "" Or IsNull(Me.Builds_Build_Type) Then
MsgBox "Please Select a build Type!", vbCritical, "Build Type!"
Else
Buildtype = Me.Builds_Build_Type.Value

If DCount("[Sai Serial Number]", "BuildInfoBySaiNumber", "[Sai Serial Number]=" & Me.Sai_Serial_Number) > 0 Then

DoCmd.OpenForm "Build", acNormal, , "[BuildInfoBySaiNumber]![Sai Serial Number] =" & Sainumber
Forms!Build.FilterOn = True
   Exit Sub
Else

With rs
     .AddNew
     .Fields("Build Type") = Buildtype
     .Fields("Sai Serial Number") = Me.Sai_Serial_Number
     .Update
End With

DoCmd.OpenForm "Build", acNormal, , "[BuildInfoBySaiNumber]![Sai Serial Number] =" & Sainumber
Forms!Build.FilterOn = True

End If
End If
rs.Close
Set rs = Nothing

End Sub

 

[boblarson]