Solved Change quote into an order (1 Viewer)

[slharman1]

Rather than trying to revive a thread from 2012 and since my situation is different, i am asking for help with this scenario:

I have a quote with all the related tables and now my custom has sent me a PO, I need to change the quote to an order. I was thinking about just creating a new form with the additional fields (i.e. PO Field, completion date, ect). The problem lies in that some of the quotedetails records (line items) will not be purchased by the customer and my customers have a tendency of adding new items to an order after the order is created by way of revising their PO. And just to complicate things, I have multiple customers requesting a quote for the same projects (they bid against each other for work) So I need to keep the original quote in tact.
Should I create an order table with the related tables for the details?
Thanks

 

[Ranman256]

It would be easy, if you only had to change the OrderType field from QUOTE to ORDER.
same table, 1 field change.

 

[arnelgp]

quotation is different from po, so better create new table (separate them).
as you have mentioned not all the items in the quotation are in the po (more or less).

 

[Uncle Gizmo]

I was thinking about just creating a new form with the additional fields

I answered a somewhat similar question a while ago. I provided and answer in 6 videos which are on my website here:

Move Info from One Table to Another - Nifty Access

Move Info from One Table to Another A brief summary summary of the "OP's" (Original Posters) question HERE. I've done a set of YouTube videos to answer the question. The first answer demonstrates the INSERT INTO method of adding records to another table. This method is where you update the...

www.niftyaccess.com

I mention it because of the Technique I show in video number 6, where the data is transferred into the empty form, into the default properties of the text boxes. The information appears on the Form, but is not saved. You can update the information as you like.

 

[slharman1]

I answered a somewhat similar question a while ago. I provided and answer in 6 videos which are on my website here:

Move Info from One Table to Another - Nifty Access

Move Info from One Table to Another A brief summary summary of the "OP's" (Original Posters) question HERE. I've done a set of YouTube videos to answer the question. The first answer demonstrates the INSERT INTO method of adding records to another table. This method is where you update the...

www.niftyaccess.com

I mention it because of the Technique I show in video number 6, where the data is transferred into the empty form, into the default properties of the text boxes. The information appears on the Form, but is not saved. You can update the information as you like.

This looks like something I could do but I have one caveat. I need to do this for the main table and the related table; quote details and details accessories. Will this method work?

 

[Uncle Gizmo]

This looks like something I could do but I have one caveat. I need to do this for the main table and the related table; quote details and details accessories. Will this method work?

I can't really speak for your particular case. From memory, and you should check this yourself by following the instructions provided, I think you need to provide it with an SQL Statement representing the items that you want to select from. You must add a checkbox field to one of your tables to provide the selection. That's the first query to build. If you can build that one, then you're halfway there. Then you need to know if you can build the insert query, again do a test. This is what all programmers do, test their assumptions before weighing in and making major changes.

 

[slharman1]

I can't really speak for your particular case. From memory, and you should check this yourself by following the instructions provided, I think you need to provide it with an SQL Statement representing the items that you want to select from. You must add a checkbox field to one of your tables to provide the selection. That's the first query to build. If you can build that one, then you're halfway there. Then you need to know if you can build the insert query, again do a test. This is what all programmers do, test their assumptions before weighing in and making major changes.

Will do. Thank you

 

[gemma-the-husky]

It depends how you designed your database. If you designed it so that a quote became an order just by setting an "order" flag, then that's the easiest way. That's what I would have done, anyway.

 

[slharman1]

I can't really speak for your particular case. From memory, and you should check this yourself by following the instructions provided, I think you need to provide it with an SQL Statement representing the items that you want to select from. You must add a checkbox field to one of your tables to provide the selection. That's the first query to build. If you can build that one, then you're halfway there. Then you need to know if you can build the insert query, again do a test. This is what all programmers do, test their assumptions before weighing in and making major changes.

Uncle Gizmo, I can't seem to figure out why I keep getting a "Object variable or With block variable not set" with the line of code below in my function.

Access can't find object "Select......statement", make sure the object exists and you spell the name and path correctly.
Here is my function - seems like access can't find my query statement or table:

Function AddNewOrder()
   
    Dim curDB As dao.Database
    Dim rsOrderList As dao.Recordset
    Set curDB = CurrentDb
   
    Dim neworder As Long
    neworder = InputBox("Enter Quote number for New Order")
   
    Dim strSQL_RSL As String
    strSQL_RSL = "SELECT QuoteNumber, CustID, CustConID, CustLocID, JobName, ShippingID, TermsID, Notes FROM tblQuotes WHERE QuoteNumber = neworder;"
   
    '"SELECT QuoteNumber, CustID, CustConID, CustLocID, JobName, ShippingID, TermsID, Notes," _
    'FROM tblQuotes _
   ' WHERE (QuoteNumber = neworder)"
   
    Dim txtQuoteNumber As Long
    Dim txtCustID As Long
    Dim txtCustConID As Long
    Dim txtCustLocID As Long
    Dim txtJobName As String
    Dim txtShippingID As Long
    Dim txtTermsID As Long
    Dim txtNotes As String
   

       
        Set rsOrderList = curDB.OpenRecordset(strSQL_RSL, 1)
       
            Do Until rsOrderList.EOF
                txtQuoteNumber = rsOrderList!QuoteNumber
                txtCustID = rsOrderList!CustID
                txtCustConID = rsOrderList!CustConID
                txtCustLocID = rsOrderList!CustLocID
                txtJobName = rsOrderList!JobName
                txtShippingID = rsOrderList!ShippingID
                txtTermsID = rsOrderList!TermsID
                txtNotes = rsOrderList!Notes
               
                MsgBox ">>>" & txtQuoteNumber
           
                rsOrderList.MoveNext
            Loop
   

End Function

 

[Isaac]

First of all, shouldn't this:

strSQL_RSL = "SELECT QuoteNumber, CustID, CustConID, CustLocID, JobName, ShippingID,
TermsID, Notes FROM tblQuotes WHERE QuoteNumber = neworder;"

be

strSQL_RSL = "SELECT QuoteNumber, CustID, CustConID, CustLocID, JobName, ShippingID,
TermsID, Notes FROM tblQuotes WHERE QuoteNumber = " & neworder

Next, this:

Dim neworder As Long
    neworder = InputBox("Enter Quote number for New Order")

Should probably be:

Dim neworder As Long
    neworder = clng(InputBox("Enter Quote number for New Order"))

.......since the the InputBox function officially returns a string.

 

[slharman1]

First of all, shouldn't this:

strSQL_RSL = "SELECT QuoteNumber, CustID, CustConID, CustLocID, JobName, ShippingID,
TermsID, Notes FROM tblQuotes WHERE QuoteNumber = neworder;"

be

strSQL_RSL = "SELECT QuoteNumber, CustID, CustConID, CustLocID, JobName, ShippingID,
TermsID, Notes FROM tblQuotes WHERE QuoteNumber = " & neworder

Next, this:

Dim neworder As Long
    neworder = InputBox("Enter Quote number for New Order")

Should probably be:

Dim neworder As Long
    neworder = clng(InputBox("Enter Quote number for New Order"))

.......since the the InputBox function officially returns a string.

Issac thanks. I’ll give it a try as soon as I get back in front of a screen.

 

[slharman1]

First of all, shouldn't this:

strSQL_RSL = "SELECT QuoteNumber, CustID, CustConID, CustLocID, JobName, ShippingID,
TermsID, Notes FROM tblQuotes WHERE QuoteNumber = neworder;"

be

strSQL_RSL = "SELECT QuoteNumber, CustID, CustConID, CustLocID, JobName, ShippingID,
TermsID, Notes FROM tblQuotes WHERE QuoteNumber = " & neworder

Next, this:

Dim neworder As Long
    neworder = InputBox("Enter Quote number for New Order")

Should probably be:

Dim neworder As Long
    neworder = clng(InputBox("Enter Quote number for New Order"))

.......since the the InputBox function officially returns a string.

One more thing: what does clng do? Does that make InputBox return a number?

 

[Isaac]

One more thing: what does clng do? Does that make InputBox return a number?

Type conversion functions (VBA) | Microsoft Docs

Clng converts something to a Long. In your case, the idea is, your variable is Long, but Inputbox returns a string.
It is not proper to take a String value and assign it as the value of a Long variable.

I just tested it, and VBA seems to be forgiving in this aspect, (I haven't taken the time to figure out why - implicit conversion, I guess), but it's still probably a good awareness as a general rule of thumb - although in this case, my Clng suggestion may be unnecessary

 

[slharman1]

Type conversion functions (VBA) | Microsoft Docs

Clng converts something to a Long. In your case, the idea is, your variable is Long, but Inputbox returns a string.
It is not proper to take a String value and assign it as the value of a Long variable.

I just tested it, and VBA seems to be forgiving in this aspect, (I haven't taken the time to figure out why - implicit conversion, I guess), but it's still probably a good awareness as a general rule of thumb - although in this case, my Clng suggestion may be unnecessary

Thanks for the knowledge. I think I will use it and maybe I can get my brain to remember that I’m the future. Thanks

 

[slharman1]

First of all, shouldn't this:

strSQL_RSL = "SELECT QuoteNumber, CustID, CustConID, CustLocID, JobName, ShippingID,
TermsID, Notes FROM tblQuotes WHERE QuoteNumber = neworder;"

be

strSQL_RSL = "SELECT QuoteNumber, CustID, CustConID, CustLocID, JobName, ShippingID,
TermsID, Notes FROM tblQuotes WHERE QuoteNumber = " & neworder

Next, this:

Dim neworder As Long
    neworder = InputBox("Enter Quote number for New Order")

Should probably be:

Dim neworder As Long
    neworder = clng(InputBox("Enter Quote number for New Order"))

.......since the the InputBox function officially returns a string.

no go on the SQL statement, I can't figurer out what's wrong with it!
I get "expected end of statement"

 

[slharman1]

no go on the SQL statement, I can't figurer out what's wrong with it!
I get "expected end of statement"

do I need to have the table open?

 

[Isaac]

Can you post your current code you're using?

 

[slharman1]

Now I have tried with the quotes around the items in the where statement as shown in Uncle Gizmo's examples. Now I get "too few parameters, expected 1."

' you forgot the code tags again
Function AddNewOrder()
    
    Dim curDB As dao.Database
    Dim rsOrderList As dao.Recordset
    Set curDB = CurrentDb
    
    Dim neworder As Long
    neworder = CLng(InputBox("Enter Quote number for New Order"))
    
    Dim strSQL_RSL As String
    strSQL_RSL = "SELECT QuoteNumber,CustID,CustConID,CustLocID,JobName,ShippingID,TermsID,Notes FROM tblQuotes WHERE (((QuoteNumber)=neworder));"
    
  
    
    Dim txtQuoteNumber As Long
    Dim txtCustID As Long
    Dim txtCustConID As Long
    Dim txtCustLocID As Long
    Dim txtJobName As String
    Dim txtShippingID As Long
    Dim txtTermsID As Long
    Dim txtNotes As String
    

        
        Set rsOrderList = curDB.OpenRecordset(strSQL_RSL, 8)
        
            Do Until rsOrderList.EOF
                txtQuoteNumber = rsOrderList!QuoteNumber
                txtCustID = rsOrderList!CustID
                txtCustConID = rsOrderList!CustConID
                txtCustLocID = rsOrderList!CustLocID
                txtJobName = rsOrderList!JobName
                txtShippingID = rsOrderList!ShippingID
                txtTermsID = rsOrderList!TermsID
                txtNotes = rsOrderList!Notes
                
                MsgBox ">>>" & txtQuoteNumber
            
                rsOrderList.MoveNext
            Loop
    

End Function

 

[Isaac]

You didn't implement the suggestion in the first portion of post 10

 

[slharman1]

You didn't implement the suggestion in the first portion of post 10

I did but it didn't work, Just tried it again, I had the trailing quote in the wrong place.
Damn you were exactly correct!
Thank you!