Import Data and assign value through form

[Iamrickdeans]

Hi Everyone,

I am back with a problem that has stumped me and I don't seem to be getting close so here is my problem, I wonder if any of you has the answer or indeed knows if this is possible?

I have a form which I'll call 'import'
and a table which is called 'trialbalanceimport' (on which there are many different ledgers) this gets populated with the information from an import...

I have a saved import called 'tbimport' which pulls an excel file and imports it into the table 'trialbalanceimport'

What I am wanting to achieve is... On the 'import' form I have a date field which I want to write to populate against every record that I import.


To give a bit of further information the import pulls the information from the trial balance excel file into the tbimport table, the excel file contains balances for;
Bank Account 1
Bank Account 2
etc

So what I want to do is when the import occurs have the date within my form that I have set populate next to the account.

Field 1 Field 2
Bank Account 1 Date From Form
Bank Account 2 Date From Form
etc

My issues is that it has to be the dates on the form otherwise I know that I could use Date() for example in the tables default setting but as sometimes these balances that I am importing could go back a month that wouldn't work.

Please ask if you need clarification and I know that I could do this in excel before importing the data but ideally I do not want to have to do this.

Thank you in advance for your help with this!

 

[jdraw]

Quite often people import "raw data" into a Temporary Table. They design a table eg RealTable with all the fields required for their processing.

In vba a query (sql string) is created to append (INSERT) records from the temporary table to the Real table. The raw data and criteria are based on the temporary table, and the "Date you need" is from the Form parameter/value, you create.

Suppose your imported data has 5 fields --fld1, fld2, fld3... fld5. Also suppose you enter the date you need in a control MyDate on your form called MyForm.

You could have something like this as your vba to set up the query - (untested but for concept only).

Probably on a button click event

Dim mSQL as string 
Dim YourConstraint as string

YourConstraint = [COLOR="Purple"]whatever your where clause is/should be[/COLOR]

... logic to ensure fields contained data/validation goes here

mSQL = "Insert into RealTable ( F1,F2,F3,F4,F5, NeededDate) " _
         & " SELECT fld1,fld2,fl3,fld4,fld5, " &  Me.MyDate & "  From TemporaryTable " _
         &  YourConstraint

Debug.print "Rendered SQL is : " & mSQL   'for debugging --see the SQL to be executed

Currentdb.Execute MSQL, dbFailonError

... Error handler code would go here

This is to show the concept involved and in this case would be run when a button was clicked.
You should have some validation and error handling logic in all your vba.
The debug.print is a means to see what "Access " thinks the actual query SQL looks like.
You comment the Currentdb.Execute line, and you will get a print out of the SQL to be processed.

Good luck.

 

[Iamrickdeans]

I will have a play about with this as a concept and see what I can come up with...

Thank you for the help!

Regards

R