Solved Change quote into an order (1 Viewer)

[Isaac]

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!

Glad you got it working!!

 

[slharman1]

Glad you got it working!!

I couldn’t have done it without you (not for a few more hours of head scratching )
Thank you so much!
Issac you da man!

 

[slharman1]

Now to figure out how to insert into a new record in my orders table.

 

[Isaac]

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

 

[slharman1]

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

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?

 

[gemma-the-husky]

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.

 

[Isaac]

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?

In this case I'm not sure precisely what you are envisioning so I'm not sure if 'yes' would be my answer.

 

[slharman1]

In this case I'm not sure precisely what you are envisioning so I'm not sure if 'yes' would be my answer.

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.

 

[slharman1]

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.

Any ideas on the error handling?

 

[Isaac]

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.

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:

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

 

[slharman1]

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:

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

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));

 

[Isaac]

You could probably use either method, then. The SQL is fairly short, so it wouldn't be too hard to do something like

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

 

[slharman1]

You could probably use either method, then. The SQL is fairly short, so it wouldn't be too hard to do something like

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

don't i need the insert statement in the loop to put it into the new table?

 

[Isaac]

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

 

[slharman1]

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

I think so, thanks

 

[gemma-the-husky]

Any ideas on the error handling?

store the result in a string, and test for null, and isnumeric before assigning to a number
alternatively store the result in a string, and error trap the assignment.

 

[slharman1]

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 & ")"

 

[slharman1]

What's wrong, access says my syntax is incorrect, OrderNumber is not a PK:

 strSQLQuoteInsert = "INSERT INTO tblOrders(OrderNumber, CustID, CustConID, CustLocID, JobName, ShippingID, TermsID, Notes) VALUES (" & lngQuoteNumber & ", " & lngCustID & ", " & lngCustConID & ", " & lngCustLocID & ", " & strJobName & ", " & lngShippingID & ", " & lngTermsID & ", " & strNotes & ")"

 

[Isaac]

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

 

[slharman1]

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, 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)