Open a new record if a matching one doesn't exist

[Sydcomebak]

Hey gurus!

This works only if a record exists in the Contracts table where the Listing_ID equals the ID of a record in the Listings table:

Private Sub Command44_Click()
  Dim sWHERE As String
  sWHERE = "Contracts.Listing_ID = " &[Listings.ID].Value
  DoCmd.OpenForm "Contract_Info", acNormal, , sWHERE
  DoCmd.Close acForm, "Commissions", acSavePrompt
End Sub

I Googled and saw that somoene on this board knew of a solution, so I tried modifying like this:

Private Sub Command44_Click()
  Dim sWHERE As String
    sWHERE = "Contracts.Listing_ID = " &[Listings.ID].Value
  If Len(Me.[ID] & "") = 0 Then
    DoCmd.OpenForm "Contract_Info", , , , acFormAdd
  Else
    DoCmd.OpenForm "Contract_Info", acNormal, , sWHERE
  End If
    DoCmd.Close acForm, "Commissions", acSavePrompt
End Sub

I've never used the "Len(" before. Where am I going wrong?

Thanks, everyone!

-Dave

 

[Sydcomebak]

[resolved]

Now I just need to get some of these fields to populate onLoad...

 

[Isskint]

sydcomeback,

You have not actually said what problem you are encountering? However here are a few observations...

• I do not think there is anything wrong with the use of the Len function. it is standard check for null data.
• [Listings.ID] is wrongly scripted it should be [Listings].ID
• You could do with enclosing the openform criteria in brackets "(Contracts.Listing_ID = " &[Listings].ID.Value & ")".
• Is Listings.ID Numerical or text? If text then it needs tobe enclosed in quotes - - "(Contracts.Listing_ID = '" &[Listings].ID.Value & "')"

Hope this helps