Update Query Via FORM Input. Please advice

[joshirohany]

Dear Experts,
First Thanking you in Advance for the help. I am new to Ms Access. Need your guidance in "Update Query".

I am trying to update table (EmployeeDetails)from another table(Sheet1). I have made a FORM(QueryForm) where there is Combobox listing down what to Update. For Example "FIRST NAME" but how to add this Combobox input to "UPDATE QUERY".

Note :- I know the query should be [Sheet1].[FIRST NAME] in "Update to).

Need you help in how should i get First Name via FORM to "Update to". Please advice

 

[June7]

Do you want to use VBA? Something like:

CurrentDb.Execute "UPDATE EmployeeDetails Set [" & Me.combobox & "] = '" & Me.textbox & "'"

 

[joshirohany]

I can use it , but you need to guide me on that. Also I have many fields to update like Father's name, DOB, DOj, etc. do i have to create vba for each of this?

need you advice. thanks for your response

 

[June7]

Happy to give advice but don't have enough info.

How should Sheet1 data be utilized? Are there unique identifier fields that link these tables? Post some sample data to show table structures.

 

[joshirohany]

Sheet1 is the Table Where All Data has to be imported to EmployeeDetails.

I am using Update Query to Import Data for above. Where i have to type manually all fields i.e. where to update "Update To".

TEST Query :- its the query created for update via Form

Query Form :- Its the Form where There is Drop Down items from EmployeeDetails.

 

[June7]

Yes, in UPDATE query you have to specify each field you want to update and the field to pull value from in the design grid. Include as many fields as you want in the UPDATE design. So why would user be selecting a field for update?

 

[joshirohany]

I want that field "Update to" to pull data from FORM's combobox selection as INPUT to "Update to". So instead of typing Sheet1.FIRST NAME, i want Sheet1 and FIRST NAME SHOULD be selected from Combobox and then run the query.

 

[June7]

Sorry, but that isn't making sense. Since Sheet1.FirstName can only be under EmployeeDetails FirstName field and names on Field row in a query object cannot be dynamic, making criteria dynamic by selection in combobox serves no purpose. However, VBA can dynamically build SQL statement.

 

[joshirohany]

Ok,
can this help, if yes, let me know to use it,

Do you want to use VBA? Something like:

CurrentDb.Execute "UPDATE EmployeeDetails Set [" & Me.combobox & "] = '" & Me.textbox & "'"

 

[June7]

Use query designer to build the SQL that joins tables so you can get correct syntax. Then copy/paste and make adjustments in VBA. Result should be something like:

CurrentDb.Execute "UPDATE EmployeeDetails INNER JOIN Sheet1 ON Sheet1.Code = EmployeeDetails.Code SET EmployeeDetails.[" & Me.combobox & "] = Sheet1.[" & Me.combobox & "]"

This assumes Sheet1 and EmployeeDetails field names are identical.

 

[joshirohany]

yes the Field names are same. i did some what like this. please guide me on VBA part.

UPDATE EmployeeDetails INNER JOIN Sheet1 ON EmployeeDetails.CODE = Sheet1.CODE SET;


in me.combobox do i have to write lable name.

 

[June7]

Combobox would be a list of field names. Set combobox RowSourceType to Field List and RowSource to EmployeeDetails.

Your SQL statement is incomplete. Need to finish the SET clause as shown in my example. Use your combobox name.

 

[Pat Hartman]

I'm going to ask a silly question.

Why are you using an update query instead of a bound form?

 

[joshirohany]

Hello Pat,
I am new to access. I have to update record based on Employee Code. like vlookup in excel.

currently i have the update query which is working. but i need that query should look up on form for input and then run.


your advice will help if i am going wrong anywhere.

 

[June7]

If you are importing data from Excel to edit existing records in Access, then an UPDATE query is needed.

I still don't understand why you want user to select field.

I have to ask - why is Excel even involved?

 

[Pat Hartman]

currently i have the update query which is working. but i need that query should look up on form for input and then run.

If you are copying data from one table to another, don't. It is poor design strategy to duplicate data. When the source data changes, what prompts the copies to be updated? -- that's why we don't do it. Always do the calculations in a query at the point in time where you need them.