SQL INSERT INTO combined with an IF THEN 1

[karassik]

I am using Access 2000.
I believe that I have the right code for the "Insert into and where" Portion:
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO [tblJobs]([QuoteNumber],
[CustomerID],[JobDescription],[BidHours],[JobCosts],[JobRevenue])

Values(tblQuotes.QuoteNumber, tblQuotes.CustomerID, tblQuotes.ProductName, tblQuotes.LaborHours, tblQuotes.MaterialTotal, tblQuotes.GrandTotal)"

WHERE (((tblJobs.QuoteNumber) <> [Forms]![frmQuotes]![QuoteNumber]))
DoCmd.SetWarnings True
But I want to check the table (tblJobs.QuoteNumber) that I'm inserting into for the QuoteNumber in the current form so the I can messagebox the user that there already is a job in tblJobs with that Quotenumber. How would I do this?

One way I tried to do this was to do a make-table query of tblJobs (that I want to insert into) for the QuoteNumber of the current form, which works fine, but I don't know how to reference the table and field from VBA.

Thanks,
Niko

 

[KarMac21]

Try this Niko -

If IsNull(DLookUp(&quot;QuoteNumber&quot;,&quot;tblJobs&quot;,&quot;QuoteNumber = &quot; & Forms![frmQuotes]![QuoteNumber])) then

DoCmd.RunSQL &quot;INSERT INTO [tblJobs]([QuoteNumber],
[CustomerID],[JobDescription],[BidHours],[JobCosts],[JobRevenue])

Values(tblQuotes.QuoteNumber, tblQuotes.CustomerID, tblQuotes.ProductName, tblQuotes.LaborHours, tblQuotes.MaterialTotal, tblQuotes.GrandTotal)&quot; _

& &quot; WHERE (((tblQuotes.QuoteNumber) = [Forms]![frmQuotes]![QuoteNumber]))&quot;

End If

Let me know if you have any questions - Shane

 

[scking]

I believe the question mentioned prompting the user. This is more along those lines.

Steve King

Dim strQuoteNumber As String
strQuoteNumber = DLookUp(&quot;QuoteNumber&quot;,&quot;tblJobs&quot;,&quot;QuoteNumber = &quot; & Forms![frmQuotes]![QuoteNumber])
If strQuoteNumber <> Forms![frmQuotes]![QuoteNumber] Then
If MsgBox(&quot;The value of QuoteNumber is currently '&quot; & strQuoteNumber & &quot;'. Do you want to replace it?&quot;, vbInformation, vbYesNo) = vbYes Then
DoTheUpdate
Else
DoWhateverElseYouWant
End If
Else
DoTheUpdate
End If

Growth follows a healthy professional curiosity

 

[karassik]

Thanks,

I can see now that a there are many ways to check a field from tables or queries. I appreicate the help both of you have provided.

Karassik