Check table to see if record exists, if not -INSERT

[lawsonta]

This is followup to my post on how to insert invoices into Quickbooks.

I have a custom Access database/application that is linked to some Quickbooks tables using the QODBC driver. I need a way to check to see if a record already exists in in Quickbooks table, Customer. If it does not, I need to insert it. I have seen something about a Dlookup but it is usually used with information from a form so I don't know if that will work for me. I also have used recordsets for other things, but can't get it to work in this case. I will post what I have so far, so please don't laugh....

....
Dim rsc As New ADODB.Recordset
rsc.Open "Select * from Customer", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Do Until rsc.EOF
If clname <> rsc("Name") Then
DoCmd.RunSQL "Insert INTO Customer(Name,IsActive,CompanyName,BillAddressAddr1,BillAddressAddr2," & _
"BillAddressAddr3)" & _
"VALUES ('" & clname & "',1,'" & clname & "','" & cladd1 & "','" & cladd2 & "', '" & clctzp & "')"
End If
rsc.MoveNext
Loop
rsc.Close
Set rsc.ActiveConnection = Nothing
....

I know the reason it is not working is that I am comparing against all the records, and in almost every case it will not exist. Is there a way to compare against every record in the recordset?

 

[pbaldy]

I'm about to sit down for dinner, but the key is to use an SQL statement for your recordset that restricts to the customer you're looking for:

rsc.Open "Select * from Customer WHERE Name = '" & clname & "'",...

Then, if the recordset comes back with no records, you know you have to add:

If rsc.EOF Then
'add cust
End If

You could also use DLookup or DCount, with a similar criteria. Now I'm going to have some spaghetti and wine!

 

[lawsonta]

Urgg....I should have gotten that. Bad thing about having access to a help center like this is it is hard to push yourself when there are so many helpful (and more knowledgeable people) out there. Thanks so much! Below is as far as I got with the Dlookup (just in case someone else out there is following our work):

If DLookup("Name", "Customer", "Name = '" & clname & "'") > 0 Then
MsgBox "customer already exists"
Else
DoCmd.RunSQL "Insert INTO Customer(Name,IsActive,CompanyName,BillAddressAddr1,BillAddressAddr2," & _
"BillAddressAddr3)" & _
"VALUES ('" & clname & "',1,'" & clname & "','" & cladd1 & "','" & cladd2 & "', '" & clctzp & "')"
End If

Thanks! I will be going with the recordset I believe since I have become so familiar with them lately. Thanks again for all your help! Tammy (SC, USA)

 

[lawsonta]

P.S. enjoy the wine and speghetti!!!!

 

[boblarson]

If DLookup("Name", "Customer", "Name = '" & clname & "'") > 0 Then

If you use DLookup that way and wind up with a null, then you will get an error. You should either encapsulate your DLookup with an NZ function to handle nulls, or even better, use a DCount instead of DLookup. A DCount will not error out on nulls and will return 0 if it encounters no records.

 

[lawsonta]

Sweet, thanks guys! To finalize the post, below is what I decided to go with:

Dim rsc As New ADODB.Recordset
rsc.Open "Select * from Customer WHERE Name ='" & clname & "'", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If rsc.EOF Then
DoCmd.RunSQL "Insert INTO Customer(Name,IsActive,CompanyName,BillAddressAddr1,BillAddressAddr2," & _
"BillAddressAddr3)" & _
"VALUES ('" & clname & "',1,'" & clname & "','" & cladd1 & "','" & cladd2 & "', '" & clctzp & "')"
End If

rsc.Close
Set rsc.ActiveConnection = Nothing

Thanks to this forum, I was able to build a link between Access and Quickbooks (Customers and Invoices) within a week or so. I haven't looked at Access/VBA in more than 8 years, and Quickbooks was completely new to me. Whew.....

 

[boblarson]

Glad to hear you are on your way with things. We are glad that we were able to help.

 

[jon jomaco]

Ah thanks bob i will use dcount myself to check for no records being located, seems like a good method. Is it possible to do something like this with two criteria for example i have:

If DCount("OrderID", "tblCustomerLink", "CustomerID =" & CustomerID) = 0 Then GoTo NoSelection

but i need it to find out if the order is completed or not and so would need it to count the records where the CustomerID ='s the customerID in the form and also where OrderDone = false

i presume it can be done but im unsure how i would incoporate it into what i allready have or even if putting: If Dcount() = 0 is the right method to use

Any help appreciated,
Jon.

 

[boblarson]

If I understand you correctly, this should work:

If DCount("OrderID", "tblCustomerLink", "CustomerID =" & CustomerID [color=red]& " And [OrderDone]=False"[/color]) = 0 Then GoTo NoSelection

 

[Sean Illingworth]

If you use DLookup that way and wind up with a null, then you will get an error. You should either encapsulate your DLookup with an NZ function to handle nulls, or even better, use a DCount instead of DLookup. A DCount will not error out on nulls and will return 0 if it encounters no records.

When I use DLookup like:
? DLookup("[Processor name]", "querySophia", "[property_id] = 39")
It gives me correct answer:
Dave Irvine

But When I am using a form and want set value automatically:
? DLookup("[Processor name]", "querySophia","[property_id]= " & Me![property_id])

System Error:
"Variable not yet created in this context"

How to deal with this problem? Any help will be appreciated.