Update Query Via FORM Input. Please advice (1 Viewer)

joshirohany

Registered User.
Local time
Today, 07:16
Joined
Apr 3, 2019
Messages
33
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

AWF VIP
Local time
Today, 06:16
Joined
Mar 9, 2014
Messages
5,425
Do you want to use VBA? Something like:

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

joshirohany

Registered User.
Local time
Today, 07:16
Joined
Apr 3, 2019
Messages
33
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

AWF VIP
Local time
Today, 06:16
Joined
Mar 9, 2014
Messages
5,425
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

Registered User.
Local time
Today, 07:16
Joined
Apr 3, 2019
Messages
33
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.
 

Attachments

  • Sheet1.JPG
    Sheet1.JPG
    57.8 KB · Views: 103
  • EmployeeDetails.JPG
    EmployeeDetails.JPG
    53.8 KB · Views: 103
  • TEST QUERY.JPG
    TEST QUERY.JPG
    45.3 KB · Views: 115
  • Query Form.JPG
    Query Form.JPG
    15 KB · Views: 113

June7

AWF VIP
Local time
Today, 06:16
Joined
Mar 9, 2014
Messages
5,425
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

Registered User.
Local time
Today, 07:16
Joined
Apr 3, 2019
Messages
33
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

AWF VIP
Local time
Today, 06:16
Joined
Mar 9, 2014
Messages
5,425
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

Registered User.
Local time
Today, 07:16
Joined
Apr 3, 2019
Messages
33
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

AWF VIP
Local time
Today, 06:16
Joined
Mar 9, 2014
Messages
5,425
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

Registered User.
Local time
Today, 07:16
Joined
Apr 3, 2019
Messages
33
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

AWF VIP
Local time
Today, 06:16
Joined
Mar 9, 2014
Messages
5,425
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.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:16
Joined
Feb 19, 2002
Messages
42,983
I'm going to ask a silly question.

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

joshirohany

Registered User.
Local time
Today, 07:16
Joined
Apr 3, 2019
Messages
33
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

AWF VIP
Local time
Today, 06:16
Joined
Mar 9, 2014
Messages
5,425
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

Super Moderator
Staff member
Local time
Today, 10:16
Joined
Feb 19, 2002
Messages
42,983
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.
 

Users who are viewing this thread

Top Bottom