Create Duplication of information from a created quote to three tables

**Welshgasman** · 07-08-2021, 04:18 PM

Originally Posted by GregOwen

Attachment 45687

Again, post the Debug.Print output ?

**Blings** · 07-09-2021, 02:00 AM

Debug.Print output to error message on post #27.

Code:

INSERT INTO tblProvisionalQuotes ( QuoteNo, Revision, Project, RaisedBYID, QuoteValue, MonthExpected, StatsuID, SourceID, Specifier, Notes, SalesEngineerId, Delivery, ValidTo, OrderedBy, OrderDate, OrderNo, OrderValue, DateCreated, FollowUpDate, AreaID ) SELECT tblProvisionalQuotes.QuoteNo, "1B", tblProvisionalQuotes.Project, tblProvisionalQuotes.RaisedBYID, tblProvisionalQuotes.QuoteValue, tblProvisionalQuotes.MonthExpected, tblProvisionalQuotes.StatsuID, tblProvisionalQuotes.SourceID, tblProvisionalQuotes.Specifier, tblProvisionalQuotes.Notes, tblProvisionalQuotes.SalesEngineerId, tblProvisionalQuotes.Delivery, tblProvisionalQuotes.ValidTo, tblProvisionalQuotes.OrderedBy, tblProvisionalQuotes.OrderDate, tblProvisionalQuotes.OrderNo, tblProvisionalQuotes.OrderValue, tblProvisionalQuotes.DateCreated, tblProvisionalQuotes.FollowUpDate, tblProvisionalQuotes.AreaID 
FROM tblProvisionalQuotes 
WHERE (((tblProvisionalQuotes.ProvisionalQuoteId)=1120));
INSERT INTO tblQuoteDetails ( ProvisionalQuoteID, TypeID, ElementID, CodeID, Code, Description, Qty, DiscountID, Tax, Delivery, SalesPrice, LineTotal, Ref ) 
SELECT 1154, tblQuoteDetails.TypeID, tblQuoteDetails.ElementID, tblQuoteDetails.CodeID, tblQuoteDetails.Code, tblQuoteDetails.Description, tblQuoteDetails.Qty, tblQuoteDetails.DiscountID, tblQuoteDetails.Tax, tblQuoteDetails.Delivery, tblQuoteDetails.SalesPrice, tblQuoteDetails.LineTotal, tblQuoteDetails.Ref 
FROM tblQuoteDetails 
WHERE (((ProvisionalQuoteID)=1120));

**Welshgasman** · 07-09-2021, 03:21 AM

Just run

Code:

SELECT 1154, tblQuoteDetails.TypeID, tblQuoteDetails.ElementID, tblQuoteDetails.CodeID, tblQuoteDetails.Code, tblQuoteDetails.Description, tblQuoteDetails.Qty, tblQuoteDetails.DiscountID, tblQuoteDetails.Tax, tblQuoteDetails.Delivery, tblQuoteDetails.SalesPrice, tblQuoteDetails.LineTotal, tblQuoteDetails.Ref 
FROM tblQuoteDetails 
WHERE (((ProvisionalQuoteID)=1120));

and see what that produces. Does it provide a value for each field?

I cannot see anything wrong at present, but then I am not an expert.

**Blings** · 07-09-2021, 05:03 AM

What should I comment out in order to run just that as above?

**Welshgasman** · 07-09-2021, 05:58 AM

Just copy what I copied and put it into a new query sql window and run it?

This is part of the benefit of printing the sql. Not only do you get to see if the syntax is correct, you can then run it and see if it produces/updates what you *think* it does?

Parts of it like now, or the whole lot. Also pasting into the sql window highlights syntax errors.

**Blings** · 07-09-2021, 06:09 AM

Okay, we are nearly there! I created a new sql query and ran that which threw an error on the SalesPrice which should have been named "SalePrice". I've now fixed that, however, I need to now configure the letter incrementing from my original quote as when I duplicate a quote no. for the first time it's putting "!" after the quote no. when it should rather be the letter "a" after the quote no.

E.g. quote 500 needs to be revised, create revision button is clicked which duplicates quote 500 as quote 500a.

What can I tell you in order to fix this?

**kd2017** · 07-09-2021, 07:23 AM

Can you show us the code for the IncrementLetter2 function?

**Blings** · 07-09-2021, 07:42 AM

Module: "mdlLetter"

Code:

Public Function IncrementLetter2(ByVal Alphanum As Variant) As String    Dim strLetter As String
    Dim strNumericPart As String
    
    If IsNumeric(Alphanum) Then
        strNumericPart = Alphanum
        IncrementLetter2 = Alphanum & "A"
    Else
        strNumericPart = Left$(Alphanum, Len(Alphanum) - 1)
        strLetter = Right$(Alphanum, 1)
        IncrementLetter2 = strNumericPart & Chr(Asc(strLetter) + 1)
    End If
End Function

Module: "Module1"

Code:

Public Function StrNulls(vntField As Variant) As String 
    If vntField = " " Then
        StrNulls = "1"
    Else
        StrNulls = Trim$(CStr(vntField))
    End If
 
End Function

This is the code from my original database which does the duplication excluding the tblQuoteDetails which is what we are trying to do which I think we have achieved but now need to incorporate the letter increment correctly for a quote created for the first time:

Code:

Private Sub cmdDup_Click()

Dim strSQL As String


Dim strProject As String
Dim dteFollowUpDate As Date
Dim lngQuoteNo As Long
Dim strRevision As String
Dim lngRaisedByID As Long
Dim strQuoteValue As String
Dim dteDateCreated As Date
Dim dteMonthExpected As Date
Dim lngStatusID As Long
Dim lngSourceID As Long
Dim lngSalesEngineerID As Long
Dim strDelivery As String
Dim dteValidTo As String


strProject = Me.Project
dteFollowUpDate = Me.FollowUpDate
lngQuoteNo = Me.QuoteNo
strRevision = StrNulls(Me.Revision)
lngRaisedByID = Me.RaisedBYID
strQuoteValue = Me.QuoteValue
dteDateCreated = Date
dteMonthExpected = Me.MonthExpected
lngStatusID = Me.StatsuID
lngSourceID = Me.SourceID
lngSalesEngineerID = Me.SalesEngineerID
strDelivery = Me.Delivery
dteValidTo = Me.ValidTo


strSQL = "INSERT INTO tblProvisionalQuotes ( Project, FollowUpDate, QuoteNo, Revision, RaisedbyID, QuoteValue, DateCreated, MonthExpected, StatsuID, SourceID, SalesEngineerID, Delivery, ValidTo) " _
& "Values ('" & strProject & "', " & Format(dteFollowUpDate, "\#dd\-mmm\-yyyy\#") & ", " & lngQuoteNo & ",'" & IncrementLetter2(strRevision) & "', " & lngRaisedByID & ", '" & strQuoteValue & _
"', " & Format(dteDateCreated, "\#dd\-mmm\-yyyy\#") & ", " & Format(dteMonthExpected, "\#dd\-mmm\-yyyy\#") & ", " & lngStatusID & ", " & lngSourceID & ", " & lngSalesEngineerID & ", '" & strDelivery & "', " & Format(dteValidTo, "\#dd\-mmm\-yyyy\#") & ");"
    Debug.Print strSQL
    CurrentDb.Execute strSQL, dbFailOnError


MsgBox "Quote Duplicated", vbInformation






End Sub

**kd2017** · 07-09-2021, 08:13 AM

hmm... The easiest way to get it to returning to the way it was working before would be to change the following line in the code that I gave you:

Code:

Revision = IncrementLetter2(Nz(Me.Revision, ""))

To this:

Code:

Revision = IncrementLetter2(StrNulls(Me.Revision))

... seems like some funny business going on here though that I don't understand

**Welshgasman** · 07-09-2021, 08:18 AM

Walk through the code line by line with F8, or set breakpoints and examine what is passed to that function.

**Blings** · 07-09-2021, 08:34 AM

Originally Posted by kd2017

hmm... The easiest way to get it to returning to the way it was working before would be to change the following line in the code that I gave you:

Code:

Revision = IncrementLetter2(Nz(Me.Revision, ""))

To this:

Code:

Revision = IncrementLetter2(StrNulls(Me.Revision))

... seems like some funny business going on here though that I don't understand

Yes! It works! You fixed it! Thank you very much!

**Blings** · 07-14-2021, 06:55 AM

One last question on this thread. I would like to exclude the copying of the 'DateCreated' value from the previous version and insert the current date into the new revision instead of carrying over the previous date. Should the select statement "tblProvisionalQuotes.DateCreated" be tweaked to grab todays date?

Code:

sql = sql & "INSERT INTO tblProvisionalQuotes ( QuoteNo, Revision, Project, RaisedBYID, QuoteValue, MonthExpected, StatsuID, SourceID, Specifier, Notes, SalesEngineerId, Delivery, ValidTo, OrderedBy, OrderDate, OrderNo, OrderValue, DateCreated, FollowUpDate, AreaID ) " & vbCrLf

Code:

sql = sql & "SELECT tblProvisionalQuotes.QuoteNo, """ & Revision & """, tblProvisionalQuotes.Project, tblProvisionalQuotes.RaisedBYID, tblProvisionalQuotes.QuoteValue, tblProvisionalQuotes.MonthExpected, tblProvisionalQuotes.StatsuID, tblProvisionalQuotes.SourceID, tblProvisionalQuotes.Specifier, tblProvisionalQuotes.Notes, tblProvisionalQuotes.SalesEngineerId, tblProvisionalQuotes.Delivery, tblProvisionalQuotes.ValidTo, tblProvisionalQuotes.OrderedBy, tblProvisionalQuotes.OrderDate, tblProvisionalQuotes.OrderNo, tblProvisionalQuotes.OrderValue, tblProvisionalQuotes.DateCreated, tblProvisionalQuotes.FollowUpDate, tblProvisionalQuotes.AreaID " & vbCrLf

**Minty** · 07-14-2021, 07:34 AM

Simply Replace the tblProvisionalQuotes.DateCreated, with Date(),

**Blings** · 07-14-2021, 08:13 AM

Originally Posted by Minty

Simply Replace the tblProvisionalQuotes.DateCreated, with Date(),

Wonderful, thank you.

Code:

sql = sql & "SELECT tblProvisionalQuotes.QuoteNo, """ & Revision & """, tblProvisionalQuotes.Project, tblProvisionalQuotes.RaisedBYID, tblProvisionalQuotes.QuoteValue, tblProvisionalQuotes.MonthExpected, tblProvisionalQuotes.StatsuID, tblProvisionalQuotes.SourceID, tblProvisionalQuotes.Specifier, tblProvisionalQuotes.Notes, tblProvisionalQuotes.SalesEngineerId, tblProvisionalQuotes.Delivery, tblProvisionalQuotes.ValidTo, tblProvisionalQuotes.OrderedBy, tblProvisionalQuotes.OrderDate, tblProvisionalQuotes.OrderNo, tblProvisionalQuotes.OrderValue, Date(), tblProvisionalQuotes.FollowUpDate, tblProvisionalQuotes.AreaID " & vbCrLf

Create Duplication of information from a created quote to three tables

Thread Tools

Similar Threads

Automatically create the same information in 2 different tables

Show Identical Information from multiple tables and create more identical tables

How do you create a form that autofills information from multiple tables

Table design and information duplication

Create a variable from a query created in VBA

Posting Permissions