Solved Change quote into an order

slharman1 said:
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!
Click to expand...
Glad you got it working!! (y)
 
Now to figure out how to insert into a new record in my orders table.
 
There is always the method: CurrentDb.Execute "insert into tablename (col1,col2) values('value1','value2')",dbFailOnError

But if your SQL is anything complex and difficult to build up dynamically you can always use the method of saving the complex query as a saved query object, with placeholder text to be replaced at runtime, and used in another query (the empty bucket)'s .sql property

1. save a query just how you want it, with placeholder text instead of the actual values, like: Where ID = 999
2. save a second query with any sql of any kind - anything at all that will simply allow you to Save it with a Name
3. code: Currentdb.Querydefs("query2").SQL = Replace(Currentdb.Querydefs("query1").sql,"999",SomeVariableOrFormControlValue)
4. code: Currentdb.Execute "query2",dbFailonError
 
Isaac said:
There is always the method: CurrentDb.Execute "insert into tablename (col1,col2) values('value1','value2')",dbFailOnError

But if your SQL is anything complex and difficult to build up dynamically you can always use the method of saving the complex query as a saved query object, with placeholder text to be replaced at runtime, and used in another query (the empty bucket)'s .sql property

1. save a query just how you want it, with placeholder text instead of the actual values, like: Where ID = 999
2. save a second query with any sql of any kind - anything at all that will simply allow you to Save it with a Name
3. code: Currentdb.Querydefs("query2").SQL = Replace(Currentdb.Querydefs("query1").sql,"999",SomeVariableOrFormControlValue)
4. code: Currentdb.Execute "query2",dbFailonError
Click to expand...
Damn. It sounds like I didn’t need all of the code we have been discussing preciously. I could’ve just made a query from the quotes recordset and used the Currentdb.Execute method you are referring to here to insert it into the order table. Does that sound correct?
 
Code: 
Dim neworder As Long
neworder = clng(InputBox("Enter Quote number for New Order"))

In passing note that constructs like this need some error handling. If you don't enter a number here, you will get a run time error. Input box can be a tricky thing to get right.
 
slharman1 said:
Damn. It sounds like I didn’t need all of the code we have been discussing preciously. I could’ve just made a query from the quotes recordset and used the Currentdb.Execute method you are referring to here to insert it into the order table. Does that sound correct?
Click to expand...
In this case I'm not sure precisely what you are envisioning so I'm not sure if 'yes' would be my answer.
 
Isaac said:
In this case I'm not sure precisely what you are envisioning so I'm not sure if 'yes' would be my answer.
Click to expand...
I just need to copy some data from one table to another. Then it will get added to and modified in the new table.
There are 3 tables with PK FK that I need to copy from and into They all share the same field types and similar names ie, QuoteName to OrderName, ect.
 
gemma-the-husky said:
Code: 
Dim neworder As Long
neworder = clng(InputBox("Enter Quote number for New Order"))

In passing note that constructs like this need some error handling. If you don't enter a number here, you will get a run time error. Input box can be a tricky thing to get right.
Click to expand...
Any ideas on the error handling?
 
slharman1 said:
I just need to copy some data from one table to another. Then it will get added to and modified in the new table.
There are 3 tables with PK FK that I need to copy from and into They all share the same field types and similar names ie, QuoteName to OrderName, ect.
Click to expand...
You definitely have several options. When I am deciding whether to use "VBA / dynamic SQL to execute" versus "a saved query object", the deciding factor generally centers around how easy or hard the product will be to read, edit, and maintain.

If I get to the point where there are numerous lines of VBA/SQL, or paragraphs thereof, I would usually decide that it's complex enough to use a saved Query object instead, and use the method I mentioned where you can save two queries: Query1 never changes, and has all of the complex design you might want, with Placeholder text for anything that varies at runtime. Query2 is just an empty bucket, basically. Query2's .sql property is modified (query2.sql = replace(query1.sql,"placeholder",Something), and then executed.

Or of course, if the whole thing is 100% static, which I don't think is your case, you can simply save it and execute it at any time.

With the VBA method of building up a SQL string, you can get as complicated as you jolly well please, nothing will stop that. But I'm a huge proponent of readability. If you (2 months later), or your co-worker (remember you are never the last leg in the employment journey - someone will always come after you)........Struggle to read that vba/sql quite a bit, then it's usually a better decision to just save a query object and go at it that way.

I hope that made sense?

For error handling, you could test to see if the inputbox result IsNumeric.

Different ways you could code this; one option would be:

Code: 
dim varInput as variant, lngInput as Long
varInput=Inputbox("Please enter something here")
if IsNumeric(varInput)=false then
    msgbox "You messed up"
    exit sub
end if

...code continues here, lngInput=clng(varInput), etc
 
Last edited:
Isaac said:
You definitely have several options. When I am deciding whether to use "VBA / dynamic SQL to execute" versus "a saved query object", the deciding factor generally centers around how easy or hard the product will be to read, edit, and maintain.

If I get to the point where there are numerous lines of VBA/SQL, or paragraphs thereof, I would usually decide that it's complex enough to use a saved Query object instead, and use the method I mentioned where you can save two queries: Query1 never changes, and has all of the complex design you might want, with Placeholder text for anything that varies at runtime. Query2 is just an empty bucket, basically. Query2's .sql property is modified (query2.sql = replace(query1.sql,"placeholder",Something), and then executed.

Or of course, if the whole thing is 100% static, which I don't think is your case, you can simply save it and execute it at any time.

With the VBA method of building up a SQL string, you can get as complicated as you jolly well please, nothing will stop that. But I'm a huge proponent of readability. If you (2 months later), or your co-worker (remember you are never the last leg in the employment journey - someone will always come after you)........Struggle to read that vba/sql quite a bit, then it's usually a better decision to just save a query object and go at it that way.

I hope that made sense?

For error handling, you could test to see if the inputbox result IsNumeric.

Different ways you could code this; one option would be:

Code: 
dim varInput as variant, lngInput as Long
varInput=Inputbox("Please enter something here")
if IsNumeric(varInput)=false then
    msgbox "You messed up"
    exit sub
end if

...code continues here, lngInput=clng(varInput), etc
Click to expand...
Thanks for the help with the Input Box error handling. I need to add these record sets to their respective orders and orderdetails tables.
Not sure how to insert them though. The code is pretty much built from Post #9 you helped me correct.
As far as the query goes, the first one is this simple:
Code:
SELECT tblQuotes.QuoteNumber, tblQuotes.CustID, tblQuotes.CustConID, tblQuotes.CustLocID, tblQuotes.JobName, tblQuotes.ShippingID, tblQuotes.TermsID, tblQuotes.Notes
FROM tblQuotes
WHERE (((tblQuotes.QuoteID)=neworder));


The other two must loop through the recordset (multiple records needs to be copied, similar to the code above you helped me with.
Code:
SELECT tblQuoteDetails.ItemNo, tblQuoteDetails.EstID, tblQuoteDetails.ModelNo, tblQuoteDetails.Description, tblQuoteDetails.Qty, tblQuoteDetails.Price, tblQuoteDetails.QuoteID, tblQuoteDetails.AccessPrice
FROM tblQuoteDetails
WHERE (((tblQuoteDetails.QuoteID)=neworder));
 
You could probably use either method, then. The SQL is fairly short, so it wouldn't be too hard to do something like

Code: 
dim strSQL as string

LOOP BEGIN
    neworder=something that determines this inside the loop
    strSQL="SELECT tblQuoteDetails.ItemNo, tblQuoteDetails.EstID, tblQuoteDetails.ModelNo, tblQuoteDetails.Description, tblQuoteDetails.Qty, tblQuoteDetails.Price, tblQuoteDetails.QuoteID, tblQuoteDetails.AccessPrice " _
        & " FROM tblQuoteDetails WHERE tblQuoteDetails.QuoteID=" & neworder
    Currentdb.Execute strSQL,dbFailOnError
LOOP NEXT
 
Isaac said:
You could probably use either method, then. The SQL is fairly short, so it wouldn't be too hard to do something like

Code: 
dim strSQL as string

LOOP BEGIN
    neworder=something that determines this inside the loop
    strSQL="SELECT tblQuoteDetails.ItemNo, tblQuoteDetails.EstID, tblQuoteDetails.ModelNo, tblQuoteDetails.Description, tblQuoteDetails.Qty, tblQuoteDetails.Price, tblQuoteDetails.QuoteID, tblQuoteDetails.AccessPrice " _
        & " FROM tblQuoteDetails WHERE tblQuoteDetails.QuoteID=" & neworder
    Currentdb.Execute strSQL,dbFailOnError
LOOP NEXT
Click to expand...
don't i need the insert statement in the loop to put it into the new table?
 
Oh sorry, I wasn't looking at your SQL too closely. I imagine then that the strSQL wouldn't be a Select, it would be an Insert Into ........Right?

I was just paying attention to the sequence
 
Isaac said:
Oh sorry, I wasn't looking at your SQL too closely. I imagine then that the strSQL wouldn't be a Select, it would be an Insert Into ........Right?

I was just paying attention to the sequence
Click to expand...
I think so, thanks
 
What's wrong, access says my syntax is incorrect:
strSQLQuoteInsert = "INSERT INTO tblOrders(OrderNumber, CustID, CustConID, CustLocID, JobName, ShippingID, TermsID, Notes) VALUES (" & lngQuoteNumber & ", " & lngCustID & ", " & lngCustConID & ", " & lngCustLocID & ", " & strJobName & ", " & lngShippingID & ", " & lngTermsID & ", " & strNotes & ")"
 
What's wrong, access says my syntax is incorrect, OrderNumber is not a PK:
Code: 
 strSQLQuoteInsert = "INSERT INTO tblOrders(OrderNumber, CustID, CustConID, CustLocID, JobName, ShippingID, TermsID, Notes) VALUES (" & lngQuoteNumber & ", " & lngCustID & ", " & lngCustConID & ", " & lngCustLocID & ", " & strJobName & ", " & lngShippingID & ", " & lngTermsID & ", " & strNotes & ")"
 
Before I spend any time looking at it, let me suggest a troubleshooting method.

Put a Breakpoint on the strSQLQuoteInsert= line of code. Run your code. When the code breaks on that line, press F8 to execute just that one line.
After that one line executes, ask the immediate window this question:

?strSQLQuoteInsert [then press Enter]

Whatever the Immediate window spits out, with the code still in break mode (if you want - you don't have to even stop it, probably, depending on the modality of your current forms situation + status of your Ribbon): COPY the code , and go to Create > Query Design > SQL view and paste that code into it. Leave the query window in View SQL mode.

Once you've pasted in the code, try to Run the query. The error message that it will then provide, along with the bit of raw SQL text that might be highlighted or indicated next to the cursor, will usually help you determine where you went wrong.

Or, of course, you may be able to spot it simply from seeing what the Immediate window first spit out. Feel free to post here precisely what the Immediate window told you.

My guess is that either one of these wasn't surrounded by single quotes: strJobName/strNotes, or possibly you have a single quote contained in the actual text of strNotes that you haven't escaped
 
Isaac said:
Before I spend any time looking at it, let me suggest a troubleshooting method.

Put a Breakpoint on the strSQLQuoteInsert= line of code. Run your code. When the code breaks on that line, press F8 to execute just that one line.
After that one line executes, ask the immediate window this question:

?strSQLQuoteInsert [then press Enter]

Whatever the Immediate window spits out, with the code still in break mode (if you want - you don't have to even stop it, probably, depending on the modality of your current forms situation + status of your Ribbon): COPY the code , and go to Create > Query Design > SQL view and paste that code into it. Leave the query window in View SQL mode.

Once you've pasted in the code, try to Run the query. The error message that it will then provide, along with the bit of raw SQL text that might be highlighted or indicated next to the cursor, will usually help you determine where you went wrong.

Or, of course, you may be able to spot it simply from seeing what the Immediate window first spit out. Feel free to post here precisely what the Immediate window told you.

My guess is that either one of these wasn't surrounded by single quotes: strJobName/strNotes, or possibly you have a single quote contained in the actual text of strNotes that you haven't escaped
Click to expand...
Isaac, thanks again, I am getting what is expected except as you suggested with the two string variables. The query shows the variable name instead of the value. When I went back and put single quotes around the two string variables i can't even compile, I get a syntax error.
Should i set the variables us the Chr(34) & varName & Chr(34)
 
You must log in or register to reply here.

Similar threads

asteropi
Solved Connecting sums from order detail to order list
Replies
18
Views
663
asteropi
asteropi
brucemc777
Sort a continuous form by the "TXDate" column (yes, dates) in ascending order
2
Replies
20
Views
452
Mark_
M
asteropi
Solved Open a filtered query from a Cmd Button
2
Replies
24
Views
439
asteropi
asteropi
J
Pareto Graphic
2
Replies
25
Views
589
JtrilloJ
J
G
Solved Error #size! on calculated control based on subform value
Replies
2
Views
418
GaP42
G

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom