Multiple Lines in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everyone,

I have the below code, and I am having a serious blonde moment. I can't
remember how to convert it so that I can have it transfer stuff from a
specific range in Excel...currently the macro would have to have the info
being transferred to Access in the actual ( ) of the VALUES. How can I word
this, so it will take for example,
thisworkbook.sheets("Raw_Data").range("A2").value in for the PIP_ID field??
Any ideas?

sSQL = "INSERT INTO User (PIP_ID)" & _
"VALUES ('PIP46')"
 
as far as i understand you:

sSQL = "INSERT INTO User (PIP_ID) VALUES ('" &
thisworkbook.sheets("Raw_Data").range("A2").value & "')"
 
Well, my main concern with that, I will have multiple lines of text for the
coding. I am trying to simplify it. I saw somewhere, but I can't remember
where for the life of me, someone using a .field(example) = ....... But I
cannot remember where. Below is the copy of me doing it the way you
suggested, which is extremely long. Basically, I have a form, that has 10
questions with 5 possible answers, and each one is assigned a value, and it
is dumping those values into the Table 'Results'.

sSQL = "INSERT INTO Results (Date, Name, Shop_ID, Claim_Number, Q1A1,
Q1A2, Q1A3, Q1A4, Q1A5, Q2A1, Q2A2, Q2A3, Q2A4, Q2A5, Q3A1, Q3A2, Q3A3, Q3A4,
Q3A5, Q4A1, Q4A2, Q4A3, Q4A4, Q4A5, Q5A1, Q5A2, Q5A3, Q5A4, Q5A5, Q6A1, Q6A2,
Q6A3, Q6A4, Q6A5, Q7A1, Q7A2, Q7A3, Q7A4, Q7A5, Q8A1, Q8A2, Q8A3, Q8A4, Q8A5,
Q9A1, Q9A2, Q9A3, Q9A4, Q9A5, Q10A1, Q10A2, Q10A3, Q10A4, Q10A5, comments)" &
_
"VALUES ('" & ThisWorkbook.Sheets("Raw_Data").Range("N8").Value &
"', '" & ThisWorkbook.Sheets("Raw_Data").Range("A2").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("B2").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("C2").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("D2").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("E2").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("F2").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("G2").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("H2").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("I2").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("J2").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("K2").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("L2").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("M2").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("N2").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("A4").Value & "', )" & _
"('" & ThisWorkbook.Sheets("Raw_Data").Range("B4").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("C4").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("D4").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("E4").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("F4").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("G4").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("H4").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("I4").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("J4").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("K4").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("L4").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("M4").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("N4").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("A6").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("B6").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("C6").Value & "', )" & _
"('" & ThisWorkbook.Sheets("Raw_Data").Range("D6").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("E6").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("F6").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("G6").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("H6").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("I6").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("J6").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("K6").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("L6").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("M6").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("N6").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("A8").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("B8").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("C8").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("D8").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("E8").Value & "', )" & _
"('" & ThisWorkbook.Sheets("Raw_Data").Range("F8").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("G8").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("H8").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("I8").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("J8").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("K8").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("L8").Value & "', '" &
ThisWorkbook.Sheets("Raw_Data").Range("M8").Value & "')"
 
OUCH! No offence, but your table looks severely denormalized!

You should be storing each answer as a separate row in a related table, not
as a field in a single row.
 
Ok...well...i'm new to Access...and this is what my manager has handed me. I
am better with Excel & VB...but, basically, the form is keeping track of a
survey, so each line is an individual survey with the corresponding results.
I am needing to find a way to add the data into the db to keep track of these
survey results.
 
Back
Top