Solved DLookup

[Momma]

Hi there
I have a continuous form, frmBuyerDetails with a list of Contacts and a subform called frmInvoice. The forms are linked as follows:
Link Master Fields: CurrentOwnerID (tblContacts)
Link Child Fields: ContactID (tblInvoices)

In the After Update Event of the combobox on the main form where I select the Buyer from 'tblContacts', I have an If statement to check if a record in tblInvoices exist where the ContactID and DogID are the same than the current record on frmBuyerDetails.
If 0 then an Invoice record should be created for that Buyer. If record exist then don't create another invoice. The reason why I include the DogID is that I might sell another puppy to this Buyer in the future in which case it should then create a new Invoice.
I do get an Run-Time error 3201 "You cannot add or change a record because a related record is required in table 'tblContacts'"

I tried the If statement checking only the ContactID and it worked perfectly.

Hope I made the scenario clear enough

If DCount("ContactID", "tblInvoices", "ContactID= " & CurrentOwnerID & " And DogID = " & DogID) = 0 Then

            strSQL = "INSERT INTO tblInvoices (ContactID) " & vbCrLf _
                  & "Values (" & Me.CurrentOwnerID & ");"
            strSQL = "INSERT INTO tblInvoices (DogID) " & vbCrLf _
                  & "Values (" & Me.DogID & ");"
                  
              Debug.Print strSQL
              CurrentDb.Execute strSQL, dbFailOnError
End If

 

[plog]

I barely understand what you are trying to do, but I totally understand what your code is doing. Here's what i see.

1. strSQL is set twice but only used once. You do nothing with your first INSERT statement for ContactID. Sure, you load it into strSQL but then, nothing. You immediately replace that statement with the INSERt for DogID. You need to execute it after you put it in there.

2. You are inserting a NULL value into ContactID for tblInvoices. Sounds like you have referential integrity set to not allow that, or ContactID is required, or a primary key, or whatever. But the one INSERT statement you actually are executing has no value for ContactID. You are simply putting the DogID into a new record and that's it.

Again, I don't understand what it is you are trying to do, but my gut tells me this is over engineered. Why not just allow inputs into that subform?

 

[June7]

Agree with plog. These are BOUND forms so why would you need INSERT action? And if you did need INSERT, should be one SQL statement populating both fields.

Do data validation in form BeforeUpdate event. If user input is not valid then cancel, if input is good then do nothing, not run INSERT.

One way to prevent duplicate buyer/dog pair selection is to not list already sold puppies in combobox.

 

[Momma]

Thank you for your advice, June & Plog. I managed to sort it out.