Pushing form fields to table

[mbatestblrock]

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!

[PianoMan64]

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.

 
       Dim MyDB As DAO.Database
       Dim MyRS As DAO.Recordset
 
       Set MyDB = CurrentDb()
       Set MyRS = MyDB.OpenRecordset("SELECT * FROM [TABLENAME]", dbOpenDynaset)
 
        With MyRS
 
               .AddNew
               !FieldNameInTable = Me.ControlNameOnForm.Value
               .Update
        End With
        MyRS.Close
        MyDB.Close
        Set MyRS = Nothing
        Set MyDB = Nothing
 
 

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@joepottschmi dt.com

Hope that helps,

Joe P.

[mbatestblrock]

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.

 
       Dim MyDB As DAO.Database
       Dim MyRS As DAO.Recordset
 
       Set MyDB = CurrentDb()
       Set MyRS = MyDB.OpenRecordset("SELECT * FROM [TABLENAME]", dbOpenDynaset)
 
        With MyRS
 
               .AddNew
               !FieldNameInTable = Me.ControlNameOnForm.Value
               .Update
        End With
        MyRS.Close
        MyDB.Close
        Set MyRS = Nothing
        Set MyDB = Nothing
 
 

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@joepottschmi dt.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!

[mbatestblrock]

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!