473,320 Members | 1,846 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How do I move input data from one place in a form to a specified field in a table.

I am trying to design a form in which a category (field in a table) is selected from a combo box, then a value for that category is entered in a form field. I want that value to be placed in the field selected from the drop down list in the combo box. It would be like what happens when a bookkeeper selects an account field from a list and then enters a value for it. The value is then appended to the specified account field. I think this should be something easy, but I can't figure it out. I have made forms where an input field matches a table column, but I can't figure out how to specify the column (field) and then have the value go there. I would like a user to have just a couple of places to deal with in data entry instead of a massive form. I am pretty inexperienced with Access. Maybe I know only enough to be dangerous. :-/ Thanks.

I have Access 2003 running on XP Pro
Nov 8 '06 #1
14 1904
MMcCarthy
14,534 Expert Mod 8TB
I am trying to design a form in which a category (field in a table) is selected from a combo box, then a value for that category is entered in a form field. I want that value to be placed in the field selected from the drop down list in the combo box. It would be like what happens when a bookkeeper selects an account field from a list and then enters a value for it. The value is then appended to the specified account field. I think this should be something easy, but I can't figure it out. I have made forms where an input field matches a table column, but I can't figure out how to specify the column (field) and then have the value go there. I would like a user to have just a couple of places to deal with in data entry instead of a massive form. I am pretty inexperienced with Access. Maybe I know only enough to be dangerous. :-/ Thanks.

I have Access 2003 running on XP Pro
You will need to create an command button and in the On Click event put something like the following:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub commandBtnName_OnClick()
  3.  
  4.   DoCmd.RunSQL "UPDATE TableName SET " & Me.ComboboxName & _
  5.       "=" & Me.textboxName & WHERE <you will have to identify the record here that you want to be updated> ;"
  6.  
  7. End Sub
  8.  
  9.  
Nov 8 '06 #2
Thank you. I'll try that, although I know next to nothing about VB.
Nov 8 '06 #3
MMcCarthy
14,534 Expert Mod 8TB
Thank you. I'll try that, although I know next to nothing about VB.
Make an attempt and if it doesn't work post the code here and we will try to correct it for you.
Nov 9 '06 #4
OK . . . I created a command button on my form named Go. The idea is to select an acct from a combobox named Account Name and then enter an amount in a textbox named Amount which is to be transferred to an account field (matching Account Name) in a table named TransDistribution. Here's the code I wrote following your example as best I could:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Go_OnClick()
  2.  
  3.     DoCmd.RunSQL "UPDATE Transdistribution SET" & Me.Account_Name & "=" & Me.Amount & Me.TransDistribution
  4.  
  5. End Sub
  6.  


I assume that in the OnClick property for Go, I am to choose [Event Procedure] or am I to name it? I tried "Private Sub Go_OnClick()" but Access thought I was trying to run a macro. Is there some way to do this with a macro?
Nov 10 '06 #5
NeoPa
32,556 Expert Mod 16PB
The code looks mainly good.
However :
There should be a space after SET
There needs to be single-quotes around the new data
The name of the field (if there's any possibility of it containing spaces or clashing with reserved words) should have [] around it.
Try this :-
Expand|Select|Wrap|Line Numbers
  1. Private Sub Go_OnClick()
  2.     DoCmd.RunSQL "UPDATE Transdistribution SET [" & Me.Account_Name & "]='" & Me.Amount & Me.TransDistribution & "'"
  3. End Sub
Nov 10 '06 #6
NeoPa
32,556 Expert Mod 16PB
Just a note.
I don't know what you were trying to do with the
Expand|Select|Wrap|Line Numbers
  1. "=" & Me.Amount & Me.TransDistribution
bit so I just copied it straight across.
In my version I added quotes as I saw you had the '&' bit in there for string concatenation.
If this was not intended and you wanted just the value of the amount, then lose the quotes AND the ' & Me.TransDistribution' bits.
Nov 10 '06 #7
MMcCarthy
14,534 Expert Mod 8TB
OK . . . I created a command button on my form named Go. The idea is to select an acct from a combobox named Account Name and then enter an amount in a textbox named Amount which is to be transferred to an account field (matching Account Name) in a table named TransDistribution. Here's the code I wrote following your example as best I could:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Go_OnClick()
  2.  
  3. DoCmd.RunSQL "UPDATE Transdistribution SET Account=" &  Me.Amount & " WHERE Account_Name=" & Me.Account_Name ";"
  4.  
  5. End Sub
  6.  


This code assumes that the table name is Transdistribution and it contains two fields named Account which will be set with the Amount value and Account_Name which will equal the combo box value in the record being updated.

I assume that in the OnClick property for Go, I am to choose [Event Procedure]
yes
Nov 10 '06 #8
OK.... the code now looks like this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Go_OnClick()
  2.     DoCmd.RunSQL "UPDATE Transdistribution SET [" & Me.Account_Name & "]= & Me.Amount"
  3. End Sub
Did I do that right? Maybe I'm just way in over my head. I have some JPEG's of the table, form, and properties box for the go button. I will try to send them in a subsequent post, but have not quite figured out the "insert image" procedure the button above not withstanding. I wiped out this whole message once trying to insert the images, so I will send this separately. Thanks.
Nov 10 '06 #9
Trying to insert the images. Copy and Paste doesn't seem to work.
Nov 10 '06 #10
MMcCarthy
14,534 Expert Mod 8TB
OK.... the code now looks like this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Go_OnClick()
  2. DoCmd.RunSQL "UPDATE Transdistribution SET [" & Me.Account_Name & "]= & Me.Amount"
  3. End Sub
This says make account_Name field in Transdistribution = Amount

Because you haven't specified which record you want to do it in with a where statement it will change every record to that amount.
Nov 10 '06 #11
Hmmmm . . . I better go back to the first code. Can't do it this afternoon though. Thanks for all you help to this point. Sorry I'm sort of thick about this.
Nov 10 '06 #12
MMcCarthy
14,534 Expert Mod 8TB
Hmmmm . . . I better go back to the first code. Can't do it this afternoon though. Thanks for all you help to this point. Sorry I'm sort of thick about this.
Another possibility to try is something like

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Account_Name_AfterUpdate()
  3.  
  4.    Me.Amount.ControlSource = Me.Account_Name
  5.  
  6. End Sub
  7.  
  8.  
This will only work if the form is bound to table Transdistribution and the current record being viewed is the one you want to update.
Nov 10 '06 #13
NeoPa
32,556 Expert Mod 16PB
OK.... the code now looks like this:

Code:
Private Sub Go_OnClick()
DoCmd.RunSQL "UPDATE Transdistribution SET [" & Me.Account_Name & "]= & Me.Amount"
End Sub


Did I do that right?
I know this is going a way back in the 'conversation' but this looks like you're trying to set the name of the account to an ammount or numeric value.
Is this really what you want?
If you specify your table layouts (list relevant fields) and form to be used (list relevant controls) then we can put something together for you. It's much easier to work with named specifics.
Nov 11 '06 #14
MMcCarthy
14,534 Expert Mod 8TB
I know this is going a way back in the 'conversation' but this looks like you're trying to set the name of the account to an ammount or numeric value.
Is this really what you want?
If you specify your table layouts (list relevant fields) and form to be used (list relevant controls) then we can put something together for you. It's much easier to work with named specifics.
Hi Adrian

I understood that the combo box had a list of field names relating to certain type of accounts and the figure could be assigned to different fields depending on requirements. Therefore, the combo box has a list of the relevant fields.

I may be wrong??

Mary
Nov 12 '06 #15

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Willoughby Bridge | last post by:
Hi - Having trouble getting a multipart series of forms to move to the next form. The problem line is: <form method="post" enctype="multipart/form-data" action="Data_Form1.php"> If the...
4
by: Justin Lebar | last post by:
Sorry about the huge post, but I think this is the amount of information necessary for someone to help me with a good answer. I'm writing a statistical analysis program in ASP.net and MSSQL7 that...
6
by: Robin S. | last post by:
**Eric and Salad - thank you both for the polite kick in the butt. I hope I've done a better job of explaining myself below. I am trying to produce a form to add products to a table (new...
3
by: Eric | last post by:
When i run my query it transfer last 4 digits of account number from one table to another and its wrong. There are two tables one i use for parsing. Second thru query i use to move data from temp...
1
by: AMDRIT | last post by:
Occasionally I try my hand at a simple data storage engine. Today I ran across an article on the web http://msdn2.microsoft.com/en-us/library/aa289151(vs.71).aspx, and it got me thinking again. ...
0
by: prasenjit2007 | last post by:
I have a main form for inputing the (to/from/mesg/file) with the following code:- <html> <body> <table> <tr> <td>To:</td> <td><input type="text" name="to" size="50" ...
1
by: SkipNRun | last post by:
I am a novice when comes to JavaScript, AJAX. I am working on a form, which will allow users to update their contact information. In order to make the form flexible, I need to use pull down list. ...
3
by: Guig | last post by:
Hi, I have a problem with one of my form. I want to record some data in a table by using a form. One of those data it is the time and I want to use an input mask to be sure that everyone will...
21
by: DanicaDear | last post by:
I've been trying to figure this out for three weeks. I've been sleeping on it. Not getting answers, just losing sleep. ;-) In a table, I have a field COMPANY_EMAIL. In this field I want to...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.