Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-05-2019, 08:07 PM   #1
joshirohany
Newly Registered User
 
Join Date: Apr 2019
Posts: 16
Thanks: 3
Thanked 0 Times in 0 Posts
joshirohany is on a distinguished road
Update Query Via FORM Input. Please advice

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

joshirohany is offline   Reply With Quote
Old 11-05-2019, 08:14 PM   #2
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,468
Thanks: 0
Thanked 575 Times in 571 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Update Query Via FORM Input. Please advice

Do you want to use VBA? Something like:

CurrentDb.Execute "UPDATE EmployeeDetails Set [" & Me.combobox & "] = '" & Me.textbox & "'"
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
joshirohany (11-05-2019)
Old 11-05-2019, 08:17 PM   #3
joshirohany
Newly Registered User
 
Join Date: Apr 2019
Posts: 16
Thanks: 3
Thanked 0 Times in 0 Posts
joshirohany is on a distinguished road
Re: Update Query Via FORM Input. Please advice

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

joshirohany is offline   Reply With Quote
Old 11-05-2019, 08:18 PM   #4
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,468
Thanks: 0
Thanked 575 Times in 571 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Update Query Via FORM Input. Please advice

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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 11-05-2019, 08:29 PM   #5
joshirohany
Newly Registered User
 
Join Date: Apr 2019
Posts: 16
Thanks: 3
Thanked 0 Times in 0 Posts
joshirohany is on a distinguished road
Re: Update Query Via FORM Input. Please advice

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.
Attached Images
File Type: jpg Sheet1.JPG (57.8 KB, 5 views)
File Type: jpg EmployeeDetails.JPG (53.8 KB, 5 views)
File Type: jpg TEST QUERY.JPG (45.3 KB, 6 views)
File Type: jpg Query Form.JPG (15.0 KB, 2 views)
joshirohany is offline   Reply With Quote
Old 11-05-2019, 08:46 PM   #6
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,468
Thanks: 0
Thanked 575 Times in 571 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Update Query Via FORM Input. Please advice

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?
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 11-05-2019, 08:50 PM   #7
joshirohany
Newly Registered User
 
Join Date: Apr 2019
Posts: 16
Thanks: 3
Thanked 0 Times in 0 Posts
joshirohany is on a distinguished road
Re: Update Query Via FORM Input. Please advice

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.

joshirohany is offline   Reply With Quote
Old 11-05-2019, 09:01 PM   #8
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,468
Thanks: 0
Thanked 575 Times in 571 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Update Query Via FORM Input. Please advice

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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 11-05-2019, 09:03 PM   #9
joshirohany
Newly Registered User
 
Join Date: Apr 2019
Posts: 16
Thanks: 3
Thanked 0 Times in 0 Posts
joshirohany is on a distinguished road
Re: Update Query Via FORM Input. Please advice

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 & "'"
joshirohany is offline   Reply With Quote
Old 11-05-2019, 09:20 PM   #10
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,468
Thanks: 0
Thanked 575 Times in 571 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Update Query Via FORM Input. Please advice

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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 11-05-2019, 10:27 PM   #11
joshirohany
Newly Registered User
 
Join Date: Apr 2019
Posts: 16
Thanks: 3
Thanked 0 Times in 0 Posts
joshirohany is on a distinguished road
Re: Update Query Via FORM Input. Please advice

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.
joshirohany is offline   Reply With Quote
Old 11-05-2019, 10:36 PM   #12
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,468
Thanks: 0
Thanked 575 Times in 571 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Update Query Via FORM Input. Please advice

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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 11-06-2019 at 11:56 AM.
June7 is offline   Reply With Quote
Old 11-06-2019, 08:30 AM   #13
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,316
Thanks: 15
Thanked 1,601 Times in 1,520 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Update Query Via FORM Input. Please advice

I'm going to ask a silly question.

Why are you using an update query instead of a bound form?
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 11-06-2019, 10:17 PM   #14
joshirohany
Newly Registered User
 
Join Date: Apr 2019
Posts: 16
Thanks: 3
Thanked 0 Times in 0 Posts
joshirohany is on a distinguished road
Re: Update Query Via FORM Input. Please advice

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.
joshirohany is offline   Reply With Quote
Old 11-06-2019, 10:22 PM   #15
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,468
Thanks: 0
Thanked 575 Times in 571 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Update Query Via FORM Input. Please advice

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?

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Update Query Question/Advice MattioMatt Queries 4 06-04-2018 06:49 AM
Need advice on an Update Query (Data cleansing) jonathanchye General 4 11-04-2011 08:19 AM
Use form input in an Update Query mark627 Queries 2 10-20-2011 05:10 AM
[SOLVED] update query with criteria from form user input kernelsan Queries 2 04-23-2005 08:49 AM
need db advice, input, ideas lipin General 2 10-09-2002 09:33 AM




All times are GMT -8. The time now is 09:13 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World