473,326 Members | 2,124 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,326 software developers and data experts.

Pushing form fields to table

164 100+
Perhaps this is a no brainer...I have a database that is comprised of just one form and one table. This was made quite a while ago for someone, and now they want to do some more with it.

Now they want to have a separate table that holds some of the same information as the original. There are a ton of records in here, and what I was wondering is if there was a button I could put on the form for, whichever record they were on while browsing the form they could hit the button and would send certain (pre defined fields) from that record over to another table.

Reason being: the form right now has all their current customers.. the new form they want is to add new customers in a different way as well as adding SOME of the customers from the original form.. So I thought I would give them the control of who they want to add to the new table by adding this button.

So what I am wondering is how to put the button on there to pass certain fields to a given table.

I do hope this makes sense. I am trying my hardest to get the words to come
out correctly. I would be happy to elaborate!



Thanks in advance!
Jan 22 '08 #1
3 2981
PianoMan64
374 Expert 256MB
yes, that is very easy and it is very possible.

the way that you would do it, is simply open the other table up using VBA code and then when you push the button on the current record, it will copy those select fields over to the new table.

Step 1.

Create a button with the label "Copy Customer" with the controlName called CopyCustomer

Step 2.

scroll down the properties list of the button control until you see the On Click event.

click on the ... and select Code Builder.

Step 3.

Copy and paste the following code for the button.

Expand|Select|Wrap|Line Numbers
  1.  
  2.        Dim MyDB As DAO.Database
  3.        Dim MyRS As DAO.Recordset
  4.  
  5.        Set MyDB = CurrentDb()
  6.        Set MyRS = MyDB.OpenRecordset("SELECT * FROM [TABLENAME]", dbOpenDynaset)
  7.  
  8.         With MyRS
  9.  
  10.                .AddNew
  11.                !FieldNameInTable = Me.ControlNameOnForm.Value
  12.                .Update
  13.         End With
  14.         MyRS.Close
  15.         MyDB.Close
  16.         Set MyRS = Nothing
  17.         Set MyDB = Nothing
  18.  
  19.  
Please NOTE that [TABLENAME] needs to be replaced with the accual table name that the data will be pushed too. also you will need to add the names of the fields in the table and reference the controls on the form. please note the syntax of !fieldname ----- and the control name of me.ControlName.value this will read the value of the controlname on the form.

If you have any questions, please feel free to email me at me@joepottschmidt.com

Hope that helps,

Joe P.
Jan 22 '08 #2
mbatestblrock
164 100+
yes, that is very easy and it is very possible.

the way that you would do it, is simply open the other table up using VBA code and then when you push the button on the current record, it will copy those select fields over to the new table.

Step 1.

Create a button with the label "Copy Customer" with the controlName called CopyCustomer

Step 2.

scroll down the properties list of the button control until you see the On Click event.

click on the ... and select Code Builder.

Step 3.

Copy and paste the following code for the button.

Expand|Select|Wrap|Line Numbers
  1.  
  2.        Dim MyDB As DAO.Database
  3.        Dim MyRS As DAO.Recordset
  4.  
  5.        Set MyDB = CurrentDb()
  6.        Set MyRS = MyDB.OpenRecordset("SELECT * FROM [TABLENAME]", dbOpenDynaset)
  7.  
  8.         With MyRS
  9.  
  10.                .AddNew
  11.                !FieldNameInTable = Me.ControlNameOnForm.Value
  12.                .Update
  13.         End With
  14.         MyRS.Close
  15.         MyDB.Close
  16.         Set MyRS = Nothing
  17.         Set MyDB = Nothing
  18.  
  19.  
Please NOTE that [TABLENAME] needs to be replaced with the accual table name that the data will be pushed too. also you will need to add the names of the fields in the table and reference the controls on the form. please note the syntax of !fieldname ----- and the control name of me.ControlName.value this will read the value of the controlname on the form.

If you have any questions, please feel free to email me at me@joepottschmidt.com

Hope that helps,

Joe P.

Thank you so much! I will be giving this the ole' college try this evening. Again, thanks a ton!
Jan 22 '08 #3
mbatestblrock
164 100+
Thank you so much! I will be giving this the ole' college try this evening. Again, thanks a ton!
Okay, so I got it working.. and I know this may be pushing it but here is my dilemma.. I was hoping someone may have a solution for me. the form that this button is on was and still is being used to filling in information and it has a button on it to export everything to a word tmeplate to print out.. Now they want to he database to be used for more stuff,.

the button works fine but here is my problem. the form i have created has a sub form in it.

so main part of form consists of

CustID (auto num) Name, Address.

and the sub form consists of
CustID (pulled from above) Name, (also pulled from above), Date of order, Amount paid, Amount Due.


so when I hit the copy button what i want it to do is push data from that form into

Main form: Name, Address
Sub form: * all fields

So I am running into the problem of only being able to push fields to one table, which I am sure there is a workaround. but the bigger problem I see is this:

In the form with the copy customer button, If the same customer is in there twice I would like the copy procedure to not make a new record but to insert a new record into the sub form. and on the same note, I dont know what to do as far as a situation where there would happen to two customers with the same name??

I think I may be hosed...? and I hope this all made sense to everyone, I am finding it hard to explain.

If it helps at all, the person I am doing this for starts with the form with the copy button on it for entering in any new customers, this is why I am so concerned about getting the copy button to work, just to make it easier for them so they dont have to open up a new form to enter some of the same information they just did (also possibly entering a wrong number here or there)

I dont know if it possible to have something like this happen when the button is clicked:

a window pop up and have the option to select from a list of customers in the new form to append the results to, or to make a new customer?
-that might be a bit complex, and I don't even know if it can be done, but I thought it was worth a shot.


SO, with that being said, if ANYONE has any advice for me... that'd be great!
Jan 27 '08 #4

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

Similar topics

2
by: Nick | last post by:
Loop to create an array from a dynamic form. I'm having trouble with an application, and I'll try to explain it as clearly as possible: 1. I have a form with two fields, say Apples and...
3
by: Mike Whittemore | last post by:
I am trying to convert an HTML table into a list of name-value pairs, one pair per field in the table. I believe my XSLT is correct, but I've tried both Xalan and Saxon, which both fail with...
7
by: Aravind | last post by:
Hi folks. I have 2 forms, frmBorrow and frmHistory frmBorrow has an unbound multi-column combo box (cboMember) and 7 unbound text boxes (MemNo, MemName, MemIC, MemType, CourseFaculty, Borrow,...
0
by: Jason | last post by:
I have a primary form which is used to enter/edit data in a table named Test_Results. On this primary form there is a subform which displays site addresses. This subform is linked to the primary...
1
by: meganrobertson22 | last post by:
hi everybody- what is the best way to add data from one form to another? i have 2 tables: person and contract. here are some of the fields. table: person personid (autonumber and primary...
3
by: Bill | last post by:
I'm using the POST method to submit a simple form html page with yes/no and checkbox fields to an asp response page which stores the values in a new dim string, then uses it to build a new table...
3
by: bronen | last post by:
I need a step by step instruction to do the following (i'm a beginner): 1) I have a form with two unbound text boxes. ProjectNumber, ProjectName 2) I have a form called "GoTeam" with two...
2
by: adwest | last post by:
Forgive me if this is a foolish question. I "play" in Access and have only created a few rather simple relational databases. My knowledge and experience is limited. I have no formal training, just...
3
by: ibeehbk | last post by:
Hi. I have a form made in xhtml. I test via vbscript to make sure none of the fields are empty and properly formatted (ie email). All the regular fields work. However, I have two drop down menus...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
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: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
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)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
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
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.