Hi Everyone
It's been a while since I've been on here, or have been doing any coding for that matter, so I apologize up front if I forget to post something correctly. I have "inherited" a database and despite all my research I am not sure how to proceed, what I am doing wrong/missing logically, or if what I am trying to do can even be done...
I have a form that is set to copy data from and into multiple tables and uses a lot of INSERT INTO coding.
The frmCopyJob has the following fields:
cmb_cust Unbound
cmb_Company Unbound
OldQuote_Nbr Bound[QuoteNbr]
CopyBtn Button (OnClick)
The first part of the code for the copy button, looks like this: - mysql = " INSERT INTO tbQuote ( CustID, Description, Company)" & _
-
" SELECT " & Me.cmb_cust & ",Description," & Me.cmb_Company & " FROM tbQuote" & _
-
" WHERE tbQuote.QuoteNbr=" & Me.OldQuote_Nbr
-
DoCmd.RunSQL mysql
This code is clear to me except for the logic behind where the " marks are placed in both the SELECT and WHERE lines. I understand the basic concept and syntax for simple inserts, but this is beyond my level of knowledge. And this leads to my question. I have 2 other fields that are part of the table tbQuote that I would like to have copied or updated at the time the record is copied as well (as neither default value is set on the INSERTED record currently).
QStatID (integer) Default value = 1
DateCreated (Date/Time Extended) Default value =Date()
Is there a way to insert or update these fields - either using an unbound field on the form, or in the code directly? I did try a lot of different lines using examples found in my searches, but almost all returned expression errors so it became very obvious to me that I didn't understand the logical order of the code and where the " go, and why. If you have the patience to try and help me understand, as always your guidance would be greatly appreciated.
I have included the full source code for the onclick below as a reference, but the section above directly affects the tbQuote portion I am trying to update. - Private Sub Command7_Click()
-
If IsNull(Me.cmb_cust) = False And IsNull(Me.cmb_Company) = False Then
-
-
' insert quote into tblQuote
-
mysql = " INSERT INTO tbQuote ( CustID, Description, Company)" & _
-
" SELECT " & Me.cmb_cust & ",Description," & Me.cmb_Company & " FROM tbQuote" & _
-
" WHERE tbQuote.QuoteNbr=" & Me.OldQuote_Nbr
-
DoCmd.RunSQL mysql
-
-
Dim NewQuote_Nbr As Integer
-
NewQuote_Nbr = DMax("QuoteNbr", "tbQuote")
-
-
'insert quotedetails
-
mysql2 = "INSERT INTO tbQuoteItems ( QuoteNbr, ItemNbr, Description, Qty, GPM )" & _
-
"SELECT " & NewQuote_Nbr & ",ItemNbr, Description, Qty, GPM FROM tbQuoteItems" & _
-
" WHERE tbQuoteItems.QuoteNbr=" & Me.OldQuote_Nbr
-
DoCmd.RunSQL mysql2
-
-
'insert material details
-
mysql2 = "INSERT INTO tbMaterialDetail ( QuoteNbr, ItemNbr, Description, Material, Type, [Size], FtRequired, LbPerFt, PricePerFt, [Mark-up] )" & _
-
"SELECT " & NewQuote_Nbr & ",ItemNbr,Description,Material,Type,Size,FtRequired,LbPerFt,PricePerFt,[Mark-up] FROM tbMaterialDetail" & _
-
" WHERE tbMaterialDetail.QuoteNbr=" & Me.OldQuote_Nbr
-
DoCmd.RunSQL mysql2
-
-
'insert labour details
-
mysql = "INSERT INTO tbLabourDetail ( QuoteNbr, ItemNbr, ProcessType, RunTime, [Set-UpCharge], HourlyRate )" & _
-
"SELECT " & NewQuote_Nbr & ",ItemNbr, ProcessType, RunTime, [Set-UpCharge], HourlyRate FROM tbLabourDetail" & _
-
" WHERE tbLabourDetail.QuoteNbr=" & Me.OldQuote_Nbr
-
DoCmd.RunSQL mysql
-
-
'insert outsource
-
mysql2 = "INSERT INTO tbOutSourceDetail ( QuoteNbr, ItemNbr, Description, Cost, QTY, [Mark-up], Notes )" & _
-
"SELECT " & NewQuote_Nbr & ",ItemNbr,Description,Cost,QTY,[Mark-up],Notes FROM tbOutSourceDetail" & _
-
" WHERE tbOutSourceDetail.QuoteNbr=" & Me.OldQuote_Nbr
-
DoCmd.RunSQL mysql2
-
-
msg = MsgBox("Quote has been Copied, please view quote #" & NewQuote_Nbr & " under the new customers account.", vbInformation)
-
Forms![frmMain].lstQuote.Requery
-
DoCmd.Close acForm, "frmQuoteMain"
-
DoCmd.Close acForm, "frm_quote_copy"
-
Else
-
msg = MsgBox("You must select who to copy the quote to and/or which company.", vbCritical)
-
Me.cmb_cust.SetFocus
-
End If
-
-
End Sub
-
Hi Sue.
Welcome back :-)
The first step I would advise, if you feel up to it, is to learn about debugging in VBA ( Debugging in VBA), especially where it comes to strings ( How to Debug SQL String).
But in the meantime let's have a look at the statement that creates your SQL code before running it. What it seems to be doing is, after the operator selects an existing quote from the table, copying the [Description] for the existing quote, along with both Cust & Company numbers as entered (selected) on the form, into a new record where, I expect, a new Quote Number is assigned automatically.
To update this code, and maybe tidy it a little on the way you could use the following code, but you should understand how the percent (%) characters are used with codes to allow inserting values into your result string. : - With Me
-
MySQL = "INSERT INTO [tbQuote]%NL" _
-
& " ( [CustID]%NL" _
-
& " , [Description]%NL" _
-
& " , [Company]%NL" _
-
& " , [QStatID]%NL" _
-
& " , [DateCreated] )%NL" _
-
& "SELECT %CU%NL" _
-
& " , [Description]%NL" _
-
& " , %CO%NL" _
-
& " , 1%NL" _
-
& " , #%DT#%NL" _
-
& "FROM [tbQuote]%NL" _
-
& "WHERE ([QuoteNbr]=%QN)"
-
MySQL = Replace(MySQL, "%NL", vbNewLine)
-
MySQL = Replace(MySQL, "%QN", .OldQuote_Nbr)
-
MySQL = Replace(MySQL, "%CU", .cmb_Cust)
-
MySQL = Replace(MySQL, "%CO", .cmb_Company)
-
MySQL = Replace(MySQL, "%DT", Format(Date(), "yyyy\-m\-d"))
-
End With
-
Call DoCmd.RunSQL(MySQL)
%NL == New Line.
%QN == Quote Number (Me.OldQuote_Nbr).
%CU == Customer Number (cmb_Cust).
%CO == Company Number (cmb_Company).
%DT == Date - formatted correctly for SQL.
Also, though not necessary for the SQL to work, I've formatted the string such that if you display it anywhere it will show up. as clearly as I can make it, for what it's doing. This means it takes far more lines than otherwise, but separates things and makes reading much easier - even when still in the code stage before displaying.
7 6210 NeoPa 32,556
Expert Mod 16PB
Hi Sue.
Welcome back :-)
The first step I would advise, if you feel up to it, is to learn about debugging in VBA ( Debugging in VBA), especially where it comes to strings ( How to Debug SQL String).
But in the meantime let's have a look at the statement that creates your SQL code before running it. What it seems to be doing is, after the operator selects an existing quote from the table, copying the [Description] for the existing quote, along with both Cust & Company numbers as entered (selected) on the form, into a new record where, I expect, a new Quote Number is assigned automatically.
To update this code, and maybe tidy it a little on the way you could use the following code, but you should understand how the percent (%) characters are used with codes to allow inserting values into your result string. : - With Me
-
MySQL = "INSERT INTO [tbQuote]%NL" _
-
& " ( [CustID]%NL" _
-
& " , [Description]%NL" _
-
& " , [Company]%NL" _
-
& " , [QStatID]%NL" _
-
& " , [DateCreated] )%NL" _
-
& "SELECT %CU%NL" _
-
& " , [Description]%NL" _
-
& " , %CO%NL" _
-
& " , 1%NL" _
-
& " , #%DT#%NL" _
-
& "FROM [tbQuote]%NL" _
-
& "WHERE ([QuoteNbr]=%QN)"
-
MySQL = Replace(MySQL, "%NL", vbNewLine)
-
MySQL = Replace(MySQL, "%QN", .OldQuote_Nbr)
-
MySQL = Replace(MySQL, "%CU", .cmb_Cust)
-
MySQL = Replace(MySQL, "%CO", .cmb_Company)
-
MySQL = Replace(MySQL, "%DT", Format(Date(), "yyyy\-m\-d"))
-
End With
-
Call DoCmd.RunSQL(MySQL)
%NL == New Line.
%QN == Quote Number (Me.OldQuote_Nbr).
%CU == Customer Number (cmb_Cust).
%CO == Company Number (cmb_Company).
%DT == Date - formatted correctly for SQL.
Also, though not necessary for the SQL to work, I've formatted the string such that if you display it anywhere it will show up. as clearly as I can make it, for what it's doing. This means it takes far more lines than otherwise, but separates things and makes reading much easier - even when still in the code stage before displaying.
NeoPa 32,556
Expert Mod 16PB
Hi again Sue.
I've just read through your post again, and although it makes little sense, it seems you're asking for the existing values from the [tbQuote] record to be copied across rather than set to [QStatID]=1 & [DateCreated]=Today. Why [DateCreated] could ever be set to something other than today for a newly-created quote is beyond me so I'll give individual line replacements for each and you can use any that you feel appropriate.
For [QStatID] replace line #11 with :
For [DateCreated] replace line #12 with : - & " , [DateCreated]#%NL" _
This also means that line #19 would no longer be required of course.
Hi Neo,
Nice to be chatting with you again :) Hope you have been well! I've just read through your post again, and although it makes little sense, it seems you're asking for the existing values from the [tbQuote] record to be copied across rather than set to [QStatID]=1 & [DateCreated]=Today. Why [DateCreated] could ever be set to something other than today for a newly-created quote is beyond me so I'll give individual line replacements for each and you can use any that you feel appropriate.
I agree 100%, it makes little sense the way I had worded it... Perhaps this will be better...
When I create a NEW record on the main frmQuote, [QStatID] defaults to a value of 1 and [DateCreated]=Today.
HOWEVER when the existing insert code is run, both fields on the newly created record are null. Even though the copied record existed in the tbQuote it was not being displayed on the frmQuote since [QStatID] cannot be null in the form query.
So the first code is perfect.
I don't want the existing values for those 2 fields to be copied over, I just need the new record created with those 2 fields populated.
Everything you have shown me makes perfect sense and I really appreciate the resources. I'll read up on the debugging links that you have forwarded me and test the initial code tomorrow when I am back at work. Right now it's time to go play with my snow blower in my laneway.⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀
NeoPa 32,556
Expert Mod 16PB SueHopson:
Nice to be chatting with you again :) Hope you have been well!
Yes indeed - very well thank you. Right back at you :-)
Snow blowers sound like a fun thing to do. I hope you aren't suffering too much from the weather though. Very clement here in South London :-)
The code you provided worked perfectly and the layout is much clearer to me as I continue to learn. I was even able to go back into the code and add a field I had previously overlooked. The resources were great and gave me much better insight. It was also new to learn how I can better review my errors by running the code in steps. Very helpful, Thank you!
PS LOL, you are more than welcome to come and use my snowblower here in Canada :) - Art by Joan Stark
-
_...Q._
-
.' '.
-
/ \
-
;.-""""--.._ |
-
/'-._____..-'\|
-
.' ; o o |`;
-
/ /| () ; \
-
_.-, '-' ; '.__.-' \ \
-
.-"`, | \_ / `'`
-
'._`.; ._ / `'--.,_=-;_
-
\ \| `\ .\_ /` \ `._
-
\ \ `/ ``---| \ (~
-
\ \. | o , \ (~ (~ ______________
-
\ \`_\ _..-' \ (\(~ |.------------.|
-
\/ `` / \(~/ || FREE SNOW ||
-
\__ __..-' - '. || """" """" ||
-
\ \``` \ || shovel all ||
-
;\ \o ; || you want! ||
-
| \ \ | ||____________||
-
; \ \ ; '------..------'
-
\ \ \ _.-'\ / ||
-
'. \-' \ .' ||
-
_.-" ' \-' .-||-.
-
jgs \ ' ' ' \ '..---.- '
-
\ ' ' _.'
-
\' ' _.-'
-
\ _.-'
-
`
NeoPa 32,556
Expert Mod 16PB
Very cool Sue.
Another Bytes.com contributer (More of an alumnus now tbf.) lives up in Toronto. Same line of latitude as London - but being a small island (Most are relative to CNA.) the weather here's a lot more clement ;-)
Sub-zero here today (We use Centigrade/Celcius here more than Fahrenheit so less chilly than you may have thought at first glance.), but we haven't had much snow this Winter yet.
I'm about 2 hours east of Toronto, on the same lake - Ontario- so, being Canadian, Celcius definitely works for me.
It was -14 here yesterday, and +1 today, but the weather here is weird...
The lakefront effect in Toronto is very different from where I work along Lake Ontario. But then again, where I work and home (North of the major highway, and where the elevation changes) is about a 20 min drive and I might go from freezing rain in town to white-out snow conditions on my way home. The world is kind of neat that way.
Regrettably, the only part of the UK I have seen so far is Heathrow Airport, for about an hour, on my way home from Norway - which coincidentally had winters very much like my winters at home :) Hoping to travel again someday soon.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: shea martin |
last post by:
I would like to use maps as a container type, but I don't like how the
operator returns a default value. Is there a way to make return
NULL if it can't find the value?
I know I can do this:
...
|
by: CFW |
last post by:
I use the following flawlessly to insert a single field:
strSQL = "Insert into (Casket) Values " _
& "(" & conQuote & NewCasket & conQuote & ")"
Set db = CurrentDb
If MsgBox(NewCasket & " is...
|
by: David |
last post by:
I am trying to insert an employee number into the EmpNbr field in my
main table from a form where I add a new employee to my employee table.
I was hoping this command would work, but it isn't.
...
|
by: Carl |
last post by:
Hi,
I hope someone can share some of their professional advice and help me out
with my embarissing problem concerning an Access INSERT query. I have never
attempted to create a table with...
|
by: Bruce Momjian |
last post by:
I have events in the next few weeks in New York City, Copenhagen, Paris,
and Atlanta. Check the News section on the web site for more
information. I will also be in Amsterdam February 2-3, though...
|
by: Paul Aspinall |
last post by:
Hi
I'm binding a DataSet, using the ObjectDataSource control, to a GridView
I want the GridView to default to the columns returned in the DataSet,
without having to specifically name them.
...
|
by: 4partee |
last post by:
I'm trying to import a csv file with a PHP procedure. However, some of the
lines in the csv file have missing values.
When this command is given to mysql:
insert tablex values...
|
by: bonneylake |
last post by:
Hey Everyone,
Well recently i been inserting multiple fields for a section in my form called "serial". Well now i am trying to insert multiple fields for the not only the serial section but also...
|
by: omakhileshchand |
last post by:
Sir,
I have a text file that contain some fields,the fields are given below:-...
|
by: jithb4u |
last post by:
In an existing Form I want to show every field and data..
For that I have to go Form Design View and Insert Existing Field and manually drag and drop every field to the current form....
I wonder...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |