Populating an Access Table 2

[sherree]

Hi!<br><br>I need help populating an access table using VBA code only.<br>Also, how do you concatenate two fields in an access table using vba code?<br><br>Thanks,<br>Sherrie

 

[famousb]

first off, how do you want to populate the table, what is your source data.<br><br>second, to bring two fields together in one field, it depends on where you want to do it and why.<br>if you're just looking to do it to avoid having an extra query floating around, then it would just be the<br><br>docmd.runSQL &quot; &quot;<br><br>followed by the appropriate SQL code.<br>something like:<br><br>SELECT <font color=red>[field1]</font> & &quot; &quot; & <font color=red>[field2]</font> AS <font color=red>NewFieldName</font> FROM <font color=red>tablename</font>;<br><br>this even puts a space in the middle.&nbsp;&nbsp;all you have to do is put it in the quotes above, and change the red words to fit your app. <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[Phooey]

Would it not be easier just to use:<br><br>Myfield = field1 & &quot; &quot; & field2<br><br>

 

[famousb]

i'd say yes, i've used that before as well.&nbsp;&nbsp;i think i'm just on my SQL kick right now. <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[sherree]

I want to populate an access table used the excel spreadsheet I imported using VBA.<br>Here's an example of excel spreadsheet table and the table I want it to populate.<br><br>F1&nbsp;&nbsp;&nbsp;F2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;F3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;F4&nbsp;&nbsp;&nbsp;F5&nbsp;&nbsp;F6&nbsp;&nbsp;&nbsp;F7<br><br>1&nbsp;&nbsp;&nbsp;Diagnosis Anxiety&nbsp;&nbsp;300.00 xx&nbsp;&nbsp;930&nbsp;&nbsp;49006<br><br>Form Element Table:<br><br>FormID Order ElemGroup ElemName ElemVal ElemType CodeMeth ChargeCode&nbsp;&nbsp;<br><br>0001&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;Diagnosis Anxiety&nbsp;&nbsp;300.00&nbsp;&nbsp;&nbsp;&nbsp;xx&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;930<br>49006<br><br>I want to add F6+F7 before importing to the form element table, output should be:&nbsp;&nbsp;93049006 (chargecode)<br>&nbsp;<br>Thanks,<br>Sherrie<br>

 

[famousb]

personally, i don't know if there is a way to import it directly into a table and combine those two fields from Excel (i'm sure there has to be a way though).&nbsp;&nbsp;You could just import the table as is, and then run a Make Table query that concatenates the two fields using any of the code above. <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[JimConrad]

Assuming you have already imported the Excel data and that F1-F7 are the fieldnames in the imported table... then a &quot;append&quot; query is all you need.<br><br>You probably will want to make a copy of your &quot;Form Element&quot; table before you start, in case something doesn't work while testing. <br><br>Start a new query and add your imported table to the upper section of the query designer.&nbsp;&nbsp;Change the query type to Append.&nbsp;&nbsp;This will ask you for the name of the table to append the new records to.&nbsp;&nbsp;Enter the name of your &quot;Form Element&quot; table.<br><br>Then select all the columns from your imported table and drop them on the grid in the lower half.<br><br>Select the column names to receive the data into the &quot;append to&quot; row of the grid.<br><br>Delete F6 and F7 columns.&nbsp;&nbsp;Type a formula into an empty space... &quot;[F6] & [F7]&quot;&nbsp;&nbsp;(do not type the quotes).<br><br>Select &quot;ChargeCode&quot; for the &quot;AppendTo&quot; row of the grid.<br><br>Running the query will append the rows.&nbsp;&nbsp; <p>Jim Conrad<br><a href=mailto:JimConrad@Consultant.com>JimConrad@Consultant.com</a><br><a href= > </a><br>

 

[sherree]

Thanks Guys,<br><br>It works using the query.&nbsp;&nbsp;But I have to use VBA code only.<br>But I figured it out.<br><br>Here's the code for future references:<br><br>'This module selects all records in the Excel Import File table and copies them into the<br>'form elements table.<br><br>Sub SelectIntoX()<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Dim db As Database<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim qdf As QueryDef<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Set db = CurrentDb<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>'Selects all records in the Import table and adds F6 & F7 from the excel file called &quot;spreadsheet&quot; and copies the fields into a new table, &quot;FormElemen_Backup&quot;.<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>db.Execute &quot;SELECT Spreadsheet.F1, Spreadsheet.F2, Spreadsheet.F3, Spreadsheet.F4,spreadsheet.F5, [F6] & [F7] AS ChargeCode INTO &quot; _<br>& &quot;[Formelem_back] FROM Spreadsheet;&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;' Delete the old table<br>&nbsp;&nbsp;&nbsp;&nbsp;'db.Execute &quot;DROP TABLE [spreadsheet];&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>db.Close<br><br>End Sub<br><br><br>Sherree