Update Table

[tobybremner]

Hi,

I am importing data from excel, to a table called "tblimport", then I have a append query set up so that each time data is imported it appends the data to "tblesourcedata" and creates new fields by adding, say for example, "client ref" & "inceptiondate" to give a unique identifier for that client and called this "clientmonth".
I have a form set up so that any user can add a new record, as much or as little detail as they wish.

My question is how do I get the table to update the blank "clientmonth" field as this is created on import? Do I use and update query? If so, how do I do this as I have never used these before.

Thanks in advance

 

[Ziggy1]

In your Append query put this in a field

Expr1: Month(Now())

On the Append to line put the field you want to add the Month to

clientmonth

when the append query is run it will populate the field

 

[tobybremner]

Sorry, I probably havent explained myself very clearly.

I create the "clientmonth" field and populate these in the append query itself, that isnt a problem.

Its when a user tries to add a record via the form to "tblsourcedata", after append query has been run, then this is the point I need to update the table "tblesourcedata", based on the data added by the user, to fill in the blank "clientmonth" fields.

Does this make thing clearer?

Thanks again

 

[dcobau]

why can't the user supply that value?

Dave

 

[Ziggy1]

I think I am following you now. You combined these fields "client ref" & "inceptiondate" on the append to create a unique key. Is this also to avoid duplicates? Is the field "clientmonth" set to not allow duplicates?


You will need an Update query
* Form is bound to table


Field:clientmonth
Table: tblesourcedata
UpdateTo: [client ref] & [inceptiondate]


a second field to control the update based on the selected record on your form

Field:ID
Table: tblesourcedata
UpdateTo:
Criteria:[Forms]![Form1]![txtID]

You might have an add record button on the form, use this to trigger the update query. But tell me how you might want it to work if you are not sure. The best bet might be the "On Current" event.

 

[tobybremner]

yes, spot on, thanks for that.