running sql insert into in VBA (1 Viewer)

C

Cubsm22p

Registered User.
Local time
Today, 12:26
Joined
Feb 14, 2018
Messages
37
Hello guys i have a duplicate button with some SQL code embedded into it and i cannot get the sql code to execute here is a copy of the code
Code: 
Private Sub Duplicate__Record_Click()
Dim fullpath As String
Dim dirs(0 To 2) As String

Dim s As Variant
Dim onn As String
Dim strSQL As String


onn = [order number].Value
onn = Format(onn + 100000000, String(12, "0"))

MsgBox onn


strSQL = " Insert Into [Order Master] ([Order Master].[Order Number],[Order Master].[Customer],[Order Master].[Received],[Order Master].[Need By],[Order Master].[Sales Rep], [Order Master].[Parts]," & _
    "[Order Master].[Scheduled Ship],[Order Master].[Width],[Order Master].[Length],[Order Master].[Quantity],[Order Master].[Delivery Method],[Order Master].[Midax],[Order Master].[WCSS]," & _
    "[Order Master].[Job Type],[Order Master].[Why],[Order Master].[Service Rep], [Order Master].[Why Reop], [Order Master].[Plate Date],[Order Master].[Press Date], [Order Master].[Press Sequence]," & _
    "[Order Master].[Collator Date],[Order Master].[Offline Date], [Order Master].[Paper Due], [Order Master].[Carbon Due],[Order Master].[Die Due],[Order Master].[Ink Due]," & _
    "[Order Master].[Repeat Order Numbers],[Order Master].[Misc Due],[Order Master].[Companion Order], [Order Master].[Companion Order Number], [Order Master].[Press],[Order Master].[No Parts]," & _
    "[Order Master].[No Wide],[Order Master].[Calc Length],[Order Master].[Speed],[Order Master].[Calc Hrs], [Order Master].[Completed],[Order Master].[Remaining],[Order Master].[Additional Hrs]," & _
    "[Order Master].[No of Inks Face],[Order Master].[No of Inks Back] ,[Order Master].[Die Number(s) Face], [Order Master].[Die Number(s) Liner], [Order Master].[Collator No:]," & _
    "[Order Master].[Calc Collator Hours] , [Order Master].[CompletedC],[Order Master].[RemainingC],[Order Master].[Collator Delivery],[Order Master].[Offline Machine No]," & _
    "[Order Master].[Calc roto Hrs],[Order Master].[Feet Per Core roto], [Order Master].[Order Complete],[Order Master].[Collator Speed],[Order Master].[Offline Speed]," & _
    "[Order Master].[No Wide Thru Collator],[Order Master].[Calc Length Thru Collator],[Order Master].[No Wide Thru Roto],[Order Master].[Calc Length Thru roto]," & _
    "[Order Master].[Completed roto Hrs],[Order Master].[Remaining roto Hrs],[Order Master].[Offline Delivery roto],[Order Master].[Exact Repeat],[Order Master].[Cores Due],[Order Master].[Cartons Due], [Order Master].[Ribbons Due], [Order Master].[Laminate Due],[Order Master].[Cleaning Cards Due],[Order Master].[Chip Due],[Order Master].[Tamarack]," & _
    "[Order Master].[Backer Die Due] , [Order Master].[Quadrel], [Order Master].[Quadrel No], [Order Master].[Quadrel Date],[Order Master].[completed quad hrs],[Order Master].[Remaining Hrs- Quadrel], [Order Master].[Calc Length Thru quardel], [Order Master].[Tamarack Machine No], [Order Master].[Tamarack date]," & _
    "[Order Master].[Remaining Hr-Tamarack],[Order Master].[Calc Length Thru tamarack],[Order Master].[completed tam hrs], [Order Master].[Roto Machine No],[Order Master].[Quad speed],[Order Master].[tam speed], [Order Master].[roto speed],[Order Master].[Feet Per Core tam],[Order Master].[Feet Per Core quad], [Order Master].[Offline Delivery tam],[Order Master].[Offline Delivery quad]," & _
    "[Order Master].[No Wide Thru quad],[Order Master].[No Wide Thru Tam],[Order Master].[Special Pallets Due],[Order Master].[Security Tape Due],[Order Master].[Content Number],[Order Master].[Plate Length],[Order Master].[Number Wide Plates]) Values " & _
    "(''" & onn & "'',[Customer].value,[Received].value,[Need By].value,[Sales Rep].value,[Parts].value,[Scheduled Ship].value,[Width].value,[Length].value,[Quantity].value,[Delivery Method].value,[Midax].value,[WCSS].value," & _
    "[Job Type].value,[Why].value,[Service Rep].value,[Why Reop].value,[Plate Date].value,[Press Date].value,[Press Sequence].value,[Collator Date].value,[Offline Date].value,[Paper Due].value,[Carbon Due].value,[Die Due].value,[Ink Due].value," & _
    "[Repeat Order Numbers].value,[Misc Due].value,[Companion Order].value,[Companion Order Number].value,[Press].value,[No Parts].value,[No Wide].value,[Calc Length].value,[Speed].value,[Calc Hrs].value,[Completed].value,[Remaining].value,[Additional Hrs].value," & _
    "[No of Inks Face].value,[No of Inks Back].value,[Die Number(s) Face].value,[Die Number(s) Liner].value,[Collator No:].value,[Calc Collator Hours].value,[CompletedC].value,[RemainingC].value,[Collator Delivery].value,[Offline Machine No].value," & _
    "[Calc roto Hrs].value,[Feet Per Core roto].value,[Order Complete].value,[Collator Speed].value,[Offline Speed].value,[No Wide Thru Collator].value,[Calc Length Thru Collator].value,[No Wide Thru Roto].value,[Calc Length Thru roto].value," & _
    "[Completed roto Hrs].value,[Remaining roto Hrs].value,[Offline Delivery roto].value,[Exact Repeat].value,[Cores Due].value,[Cartons Due].value,[Ribbons Due].value,[Laminate Due].value,[Cleaning Cards Due].value,[Chip Due].value,[Tamarack].value," & _
    "[Backer Die Due].value,[Quadrel].value,[Quadrel No].value,[Quadrel Date].value,[completed quad hrs].value,[Remaining Hrs- Quadrel].value,[Calc Length Thru quardel].value,[Tamarack Machine No].value,[Tamarack date].value," & _
    "[Remaining Hr-Tamarack].value,[Calc Length Thru tamarack].value,[completed tam hrs].value,[Roto Machine No].value,[Quad speed].value,[tam speed].value,[roto speed].value,[Feet Per Core tam].value,[Feet Per Core quad].value,[Offline Delivery tam].value,[Offline Delivery quad].value," & _
    "[No Wide Thru quad].value,[No Wide Thru Tam].value,[Special Pallets Due].value,[Security Tape Due].value,[Content Number].value,[Plate Length].value,[Number Wide Plates].value)"
    CurrentDb.Execute strSQL
   
End Sub
 
The_Doc_Man

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:26
Joined
Feb 28, 2001
Messages
27,183
OK, this is not useful. You say you cannot get it to work. So, what does it do that tells you it didn't work? Do you get an error message? No insertions? System crash? How do you know it doesn't work. Give us some symptoms, please.

By the way, I noted a few times where you referenced xxxx.Value - but normally you do not need that because .Value is the default for any of your controls on a form or fields in a recordset. If nothing else, that will shorten your statement by what appears to be at least a couple of hundred characters. Might be easier to read that way.

One other note: You force yourself to do a LOT of typing by having field names with embedded spaces. Not saying you can't do this - but it often makes what you are trying to do a LOT more confusing. With an SQL string that long, I would be surprised to NOT get a little confused. To be honest, it is bewildering to look at it prima facie.
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:26
Joined
Aug 30, 2003
Messages
36,125
Ack! Slow fingers. Sorry Doc.
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:26
Joined
Feb 19, 2002
Messages
43,274
You make a lot of extra work by embedding SQL this way.

Use the QBE to build the query Select the data from the table or query your form is bound to and append it to whereever you want it to go. All you need to provide at run time is one or two arguments. You need to provide the "FROM ID" so Access knows what record you want to copy. If you are appending data to a child table, you also need to provide the foreign key for the "TO ID" so Access knows what the parent record is.

It was too hard to read the SQL because it is all mushed. at a glance, I would say the problem is the way you are refering to the form fields. The query engine (which is what will run this query) has no idea what form you are referring to and in fact, the ".value" suffixes would simply be errors. If you want to do this with embedded SQL, you have two options.
1. use the external format for referencing form fields -
Forms!yourformname!yourcontrol name - Access will interpret this syntax BEFORE sending the query to the query engine and substitute all these form references to literal values.
2. Convert the field references to literal values yourself (best technique). Starting from a random point in your long string, do it this way.

"(''" & onn & "'', " & Me.Customer & "', #" * Me.Received & "#, #" & Me.[Need By] & "#, " & ...

Notice how each form field is referenced and its value concatenated with the rest of the string. Using this technique:
1. text values must be enclosed by single or double quotes
2. dates are enclosed in #
3. Numeric values are not enclosed.

Creating the string using the second method gives you an opportunity to print the sql to the immediate window and then paste it into the QBE if you still have syntax errors.
 
The_Doc_Man

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:26
Joined
Feb 28, 2001
Messages
27,183
Whoops! Pat is right... using .Value in THAT context is wrong. You MIGHT be able to do it in the context of a DoCmd.RunSQL, but not in the context of the .Execute that was actually used, due to the different ways they pass their SQL to the DB engine. That sequence was so distracting to read that I let that one slip. Good catch, Pat!

And if those really are form controls, you won't be able to do that with .Execute anyway, since that requires the SQL string and all of its values to be predefined. DoCmd.RunSQL can look in the Access extended environment, but <DAO Database>.Execute cannot see anything in that environment.

Maybe if you step away from the curb and give us the 5000-foot altitude overview of what you are doing and why, using simple English as opposed to techie jargon, we might be able to offer a better solution.
 
Last edited:
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:26
Joined
May 7, 2009
Messages
19,242
or use fnAnySQL
https://access-programmers.co.uk/forums/showthread.php?t=297170

to use:
Code: 
Dim strSQL As String

strSQL="Insert Into [Order Master] ([Order Master].[Order Number],[Order Master].[Customer],[Order Master].[Received],[Order Master].[Need By],[Order Master].[Sales Rep], [Order Master].[Parts]," & _
    "[Order Master].[Scheduled Ship],[Order Master].[Width],[Order Master].[Length],[Order Master].[Quantity],[Order Master].[Delivery Method],[Order Master].[Midax],[Order Master].[WCSS]," & _
    "[Order Master].[Job Type],[Order Master].[Why],[Order Master].[Service Rep], [Order Master].[Why Reop], [Order Master].[Plate Date],[Order Master].[Press Date], [Order Master].[Press Sequence]," & _
    "[Order Master].[Collator Date],[Order Master].[Offline Date], [Order Master].[Paper Due], [Order Master].[Carbon Due],[Order Master].[Die Due],[Order Master].[Ink Due]," & _
    "[Order Master].[Repeat Order Numbers],[Order Master].[Misc Due],[Order Master].[Companion Order], [Order Master].[Companion Order Number], [Order Master].[Press],[Order Master].[No Parts]," & _
    "[Order Master].[No Wide],[Order Master].[Calc Length],[Order Master].[Speed],[Order Master].[Calc Hrs], [Order Master].[Completed],[Order Master].[Remaining],[Order Master].[Additional Hrs]," & _
    "[Order Master].[No of Inks Face],[Order Master].[No of Inks Back] ,[Order Master].[Die Number(s) Face], [Order Master].[Die Number(s) Liner], [Order Master].[Collator No:]," & _
    "[Order Master].[Calc Collator Hours] , [Order Master].[CompletedC],[Order Master].[RemainingC],[Order Master].[Collator Delivery],[Order Master].[Offline Machine No]," & _
    "[Order Master].[Calc roto Hrs],[Order Master].[Feet Per Core roto], [Order Master].[Order Complete],[Order Master].[Collator Speed],[Order Master].[Offline Speed]," & _
    "[Order Master].[No Wide Thru Collator],[Order Master].[Calc Length Thru Collator],[Order Master].[No Wide Thru Roto],[Order Master].[Calc Length Thru roto]," & _
    "[Order Master].[Completed roto Hrs],[Order Master].[Remaining roto Hrs],[Order Master].[Offline Delivery roto],[Order Master].[Exact Repeat],[Order Master].[Cores Due],[Order Master].[Cartons Due], [Order Master].[Ribbons Due], [Order Master].[Laminate Due],[Order Master].[Cleaning Cards Due],[Order Master].[Chip Due],[Order Master].[Tamarack]," & _
    "[Order Master].[Backer Die Due] , [Order Master].[Quadrel], [Order Master].[Quadrel No], [Order Master].[Quadrel Date],[Order Master].[completed quad hrs],[Order Master].[Remaining Hrs- Quadrel], [Order Master].[Calc Length Thru quardel], [Order Master].[Tamarack Machine No], [Order Master].[Tamarack date]," & _
    "[Order Master].[Remaining Hr-Tamarack],[Order Master].[Calc Length Thru tamarack],[Order Master].[completed tam hrs], [Order Master].[Roto Machine No],[Order Master].[Quad speed],[Order Master].[tam speed], [Order Master].[roto speed],[Order Master].[Feet Per Core tam],[Order Master].[Feet Per Core quad], [Order Master].[Offline Delivery tam],[Order Master].[Offline Delivery quad]," & _
    "[Order Master].[No Wide Thru quad],[Order Master].[No Wide Thru Tam],[Order Master].[Special Pallets Due],[Order Master].[Security Tape Due],[Order Master].[Content Number],[Order Master].[Plate Length],[Order Master].[Number Wide Plates]) "
strSQL = strSQL & _
    "SELECT @1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17,@18,@19,@20,@21,@22,@23,@24,@25,"
strSql = strSQL & _
    "@26,@27,@28,@29,@30,@31,@32,@33,@34,@35,@36,@37,@38,@39,@40,@41,@42,@43,@44,@45,@46,@17,@48,@49,@50,"
strSql = strSQL & _
    "@51,@52,@53,@54,@55,@56,@57,@58,@59,@60,@61,@62,@63,@64,@65,@66,@67,@68,@69,@70,@71,@72,@73,@74,@75,"
strSql = strSQL & _
    "@76,@77,@78,@79,@80,@81,@82,@83,@84,@85,@86,@87,@88,@89,@901,@91,@92,@93,@94,@95"



Call fnAnySQL(strSQL, _  
    onn, &  _
    [Customer].value,[Received].value,[Need By].value,[Sales Rep].value,[Parts].value,[Scheduled Ship].value, _
    [Width].value,[Length].value,[Quantity].value,[Delivery Method].value,[Midax].value,[WCSS].value,  _
    [Job Type].value,[Why].value,[Service Rep].value,[Why Reop].value,[Plate Date].value,[Press Date].value, _
    [Press Sequence].value,[Collator Date].value,[Offline Date].value,[Paper Due].value,[Carbon Due].value,[Die Due].value,[Ink Due].value,  _
    [Repeat Order Numbers].value,[Misc Due].value,[Companion Order].value,[Companion Order Number].value,[Press].value, _
    [No Parts].value,[No Wide].value,[Calc Length].value,[Speed].value,[Calc Hrs].value,[Completed].value,[Remaining].value,[Additional Hrs].value, _
    [No of Inks Face].value,[No of Inks Back].value,[Die Number(s) Face].value,[Die Number(s) Liner].value, _
    [Collator No:].value,[Calc Collator Hours].value,[CompletedC].value,[RemainingC].value,[Collator Delivery].value,[Offline Machine No].value, _
    [Calc roto Hrs].value,[Feet Per Core roto].value,[Order Complete].value,[Collator Speed].value,[Offline Speed].value, _
    [No Wide Thru Collator].value,[Calc Length Thru Collator].value,[No Wide Thru Roto].value,[Calc Length Thru roto].value, _
    [Completed roto Hrs].value,[Remaining roto Hrs].value,[Offline Delivery roto].value,[Exact Repeat].value,[Cores Due].value, _
    [Cartons Due].value,[Ribbons Due].value,[Laminate Due].value,[Cleaning Cards Due].value,[Chip Due].value,[Tamarack].value, _
    [Backer Die Due].value,[Quadrel].value,[Quadrel No].value,[Quadrel Date].value,[completed quad hrs].value, _
    [Remaining Hrs- Quadrel].value,[Calc Length Thru quardel].value,[Tamarack Machine No].value,[Tamarack date].value, _
    [Remaining Hr-Tamarack].value,[Calc Length Thru tamarack].value,[completed tam hrs].value,[Roto Machine No].value, _
    [Quad speed].value,[tam speed].value,[roto speed].value,[Feet Per Core tam].value,[Feet Per Core quad].value, _
    [Offline Delivery tam].value,[Offline Delivery quad].value, _
    [No Wide Thru quad].value,[No Wide Thru Tam].value,[Special Pallets Due].value,[Security Tape Due].value, _
    [Content Number].value,[Plate Length].value,[Number Wide Plates].value)
 
C

Cubsm22p

Registered User.
Local time
Today, 12:26
Joined
Feb 14, 2018
Messages
37
Pat Hartman said:
You make a lot of extra work by embedding SQL this way.

Use the QBE to build the query Select the data from the table or query your form is bound to and append it to whereever you want it to go. All you need to provide at run time is one or two arguments. You need to provide the "FROM ID" so Access knows what record you want to copy. If you are appending data to a child table, you also need to provide the foreign key for the "TO ID" so Access knows what the parent record is.

It was too hard to read the SQL because it is all mushed. at a glance, I would say the problem is the way you are refering to the form fields. The query engine (which is what will run this query) has no idea what form you are referring to and in fact, the ".value" suffixes would simply be errors. If you want to do this with embedded SQL, you have two options.
1. use the external format for referencing form fields -
Forms!yourformname!yourcontrol name - Access will interpret this syntax BEFORE sending the query to the query engine and substitute all these form references to literal values.
2. Convert the field references to literal values yourself (best technique). Starting from a random point in your long string, do it this way.

"(''" & onn & "'', " & Me.Customer & "', #" * Me.Received & "#, #" & Me.[Need By] & "#, " & ...

Notice how each form field is referenced and its value concatenated with the rest of the string. Using this technique:
1. text values must be enclosed by single or double quotes
2. dates are enclosed in #
3. Numeric values are not enclosed.

Creating the string using the second method gives you an opportunity to print the sql to the immediate window and then paste it into the QBE if you still have syntax errors.
Click to expand...
so when i have a numeric value i would put just me.XXXXX

Code: 
Private Sub Duplicate__Record_Click()

Dim onn As String
Dim strSQL As String

onn = [order number].Value
onn = Format(onn + 100000000, String(12, "0"))

MsgBox onn
DoCmd.RunSQL "Insert Into [Order Master] ([Order Number],[Customer],[Received],[Need By],[Sales Rep],[Parts]," & _
    "[Scheduled Ship],[Width],[Length],[Quantity],[Delivery Method],[Midax],[WCSS]," & _
    "[Job Type],[Why],[Service Rep], [Why Reop],[Plate Date],[Press Date],[Press Sequence]," & _
    "[Collator Date],[Offline Date],[Paper Due],[Carbon Due],[Die Due],[Ink Due]," & _
    "[Repeat Order Numbers],[Misc Due],[Companion Order], [Companion Order Number], [Press],[No Parts]," & _
    "[No Wide],[Calc Length],[Speed],[Calc Hrs],[Completed],[Remaining],[Additional Hrs]," & _
    "[No of Inks Face],[No of Inks Back] ,[Die Number(s) Face],[Die Number(s) Liner],[Collator No:]," & _
    "[Calc Collator Hours] , [CompletedC],[RemainingC],[Collator Delivery],[Offline Machine No]," & _
    "[Calc roto Hrs],[Feet Per Core roto],[Order Complete],[Collator Speed],[Offline Speed]," & _
    "[No Wide Thru Collator],[Calc Length Thru Collator],[No Wide Thru Roto],[Calc Length Thru roto]," & _
    "[Completed roto Hrs],[Remaining roto Hrs],[Offline Delivery roto],[Exact Repeat],[Cores Due],[Cartons Due], [Ribbons Due], [Laminate Due],[Cleaning Cards Due],[Chip Due],[Tamarack]," & _
    "[Backer Die Due] , [Quadrel], [Quadrel No], [Quadrel Date],[completed quad hrs],[Remaining Hrs- Quadrel], [Calc Length Thru quardel], [Tamarack Machine No], [Tamarack date]," & _
    "[Remaining Hr-Tamarack],[Calc Length Thru tamarack],[completed tam hrs], [Roto Machine No],[Quad speed],[tam speed],[roto speed],[Feet Per Core tam],[Feet Per Core quad], [Offline Delivery tam],[Offline Delivery quad]," & _
    "[No Wide Thru quad],[No Wide Thru Tam],[Special Pallets Due],[Security Tape Due],[Content Number],[Plate Length],[Number Wide Plates]) Values " & _
    "('" & onn & "'," & Me.Customer & " ', # " * Me.Received & " #,# " * Me.Need_By & " #," & Me.Sales_Rep & " '," & Me.Parts & " ' ,# " * Me.Scheduled_Ship & " #,me.width,me.length,me.Quantity,me.Delivery_Method," & Me.Midax & " '," & Me.WCSS & " '," & Me.Job_Type & " '," & Me.Why & " '," & Me.Service_Rep & " '," & Me.[Why Reop] & " ',# " * Me.Plate_Date & " #,# " * Me.Press_Date & " #,me.[press sequence],# " * Me.Collator_Date & " #,);" & _
    "(# " * Me.Offline_Date & " #,# " * Me.Paper_Due & " #,# " * Me.Carbon_Due & " #,# " * Me.Die_Due & " #,# " * Me.Ink_Due & " #," & Me.[Repeat Order Numbers] & " ',# " * Me.Misc_Due & " #," & Me.Companion_Order & " '," & Me.Companion_Order_Number & " ',me.press," & Me.No_Parts & " ',me.[no wide],me.[calc length],me.speed,me.[calc hours],me.completed,me.remaining,me.[additional hrs],me.[no of inks face],me.[no of inks back]," & Me.[Die Number(s) Face] & " '," & Me.[Die Number(s) Liner] & " ',)" & _
    "(me.[collator no:],me.[Calc Collator Hours],me.completedc,me.remainingC," & Me.[Collator Delivery] & " ',me.[offline machine no],me.[calc roto Hrs]," & Me.Feet_Per_Core_roto & " '," & Me.Order_Complete & " ',me.[collator speed],,me.[Offline speed],me.[No Wide Thru Collator],me.[Calc Length Thru Collator]," & Me.No_Wide_Thru_Roto & " ',me.[Calc Length Thru roto],me.[Completed roto Hrs],me.[remaining roto Hrs]," & Me.[Offline Delivery roto] & " '," & Me.Exact_Repeat & " ',# " * Me.Cores_Due & " #,)" & _
    "(# " * Me.Cartons_Due & " #,# " * Me.Ribbons_Due & " #,# " * Me.Laminate_Due & " #,# " * Me.Cleaning_Cards_Due & " #,# " * Me.Chip_Due & " #," & Me.Tamarack & " ',# " * Me.Backer_Die_Due & " #," & Me.Quadrel & " ',me.[Quadrel No],# " * Me.Quadrel_Date & " #,me[completed quad hrs],me.[Remaining Hrs- Quadrel],me.[Calc Length Thru quardel],me.[Tamarack Machine No],# " * Me.Tamarack_date & " #,me.[Remaining Hr-Tamarack],me.[Calc Length Thru tamarack],me.[completed tam hrs],me.[Roto Machine No],)" & _
    "(me.[Quad speed],me.[tam speed],me.[roto speed]," & Me.Feet_Per_Core_Tam & " '," & Me.[Feet Per Core quad] & " '," & Me.[Offline Delivery tam] & " '," & Me.[Offline Delivery quad] & " '," & Me.[No Wide Thru quad] & " '," & Me.[No Wide Thru Tam] & " ',# " * Me.Special_Pallets_Due & " #,# " * Me.Security_Tape_Due & " #," & Me.Content_Number & " '," & Me.Plate_Length & " '," & Me.[Number Wide Plates] & " ')"
 
Last edited:
M

Mark_

Longboard on the internet
Local time
Today, 12:26
Joined
Sep 12, 2017
Messages
2,111
I would recommend looking into data normalization. There seem to be calculated values being saved and groups of data that look like they should be in child records (or calculated from child records) with in this master record.

From a guess, you could reduce the number of fields quickly by having a child record for all of your "Items Due" where you would have one record for each "Item" as well as the date or quantity (unsure which it is). Since there are 14 "Due" items in your table this could greatly ease reporting when you are looking for any order with items that are due.
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:26
Joined
Feb 19, 2002
Messages
43,274
Was this not clear enough?
Notice how each form field is referenced and its value concatenated with the rest of the string. Using this technique:
1. text values must be enclosed by single or double quotes
2. dates are enclosed in #
3. Numeric values are not enclosed.
Click to expand...
 
M

Mark_

Longboard on the internet
Local time
Today, 12:26
Joined
Sep 12, 2017
Messages
2,111
Pat,

I'd GUESS they are dates, but the OP isn't being consistent with what they wrap as text and what they don't, so I'm not entirely sure.

The issue starts at the beginning, immediately after filling in onn
Code: 
"Values " & _
    "('" & onn & "',[B][COLOR="Blue"][SIZE="5"]"[/SIZE][/COLOR][/B] & Me.Customer & " [B][COLOR="Red"][SIZE="5"]'[/SIZE][/COLOR][/B], # " * Me.Received & " #,# "
onn is wrapped in single quotes, but Me.Customer is not preceeded by a single quote but is ended with a single quote.

This will lead to issues. I also think a child table for all of their "due" dates would be in order with entries only for applicable due dates.
 
You must log in or register to reply here.

Users who are viewing this thread

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