Insert into and default fields

[SueHopson]

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
 

[NeoPa]

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]

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 :

          & "          , [QStatID]%NL" _

For [DateCreated] replace line #12 with :

          & "          , [DateCreated]#%NL" _

This also means that line #19 would no longer be required of course.

[SueHopson]

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]

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

[SueHopson]

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]

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.

[SueHopson]

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.