Question Copy data from one form to another

[txmmoore]

Okay folks. I know this maybe the wrong place to ask, but I have to start somewhere. I have a table entitled customer info. I have developed a form "Customer Info" in which I enter into this table the customer name, PO number, SO Number, and a bunch of other stuff. When I close the form, and check the table, all the data is there exactly how I entered into the form. No problem at this point. Now, here is the problem I need help with -

I have a menu button, which opens up a separate form. This form is linked to a different table called "custdata", and the form is "Prerelease Info". I want to transfer the following items from my first form/table into the 2nd form/table. Here is my VB code that I am using:

DoCmd.OpenForm "Customer Info", , , , acFormReadOnly, acHidden
DoCmd.GoToRecord acDataForm, "Customer Info", acLast
DoCmd.OpenForm "Prerelease info", , , , acFormAdd
Forms![Prerelease info]![ID Select] = Forms![Customer Info]![ID]
Forms![Prerelease info]![PO Num] = Forms![Customer Info]![PO number]
Forms![Prerelease info]![SO Num] = Forms![Customer Info]![SO number]

When the form "Prerelease Info" opens, I can see all the information that transferred over from the first form/table. HOWEVER, the data is not being copied into the new table.

What am I doing wrong here?

 

[pbaldy]

Not how I'd go about it, but is the second form bound to the table (record source property)? Are the 3 controls bound to fields in that table (control source property)?

 

[txmmoore]

Hey, I'd take any suggestions at this point. Yes, all the fields are bound by the table. Just not working, and I can't figure why. Help!!!:banghead:

 

[pbaldy]

Can you attach the db here? The data wouldn't be saved until you either closed the second form or moved off the new record.

 

[txmmoore]

Sorry. Can't post. It's a mess right now. I thought the same thing, as soon as I close the 2nd form, the table would be updated. Not the case. So, how would you do this? I just want to add these 2 or 3 fields, and I'm good to go. The field ID Select is nothing more than a pointer for my search that I will perform later on. That's because the customers name, po, and so may be the same. So, I need to add a unique identifier to the 2nd table. I just figure the auto number would be perfect. Thanks for the input.
:banghead::banghead:

 

[pbaldy]

Well, using forms like that is sort of a rookie workaround. I know because I used to do the same thing.

For starters, using acLast isn't necessarily reliable, particularly in a multi-user environment. If the form where you originally entered that stuff is still open to that record (or could be), I'd just do it from there. If you don't need the second form left open, I'd just append the data to the table, either with a recordset or an append query.

 

[txmmoore]

I tried the append route, but there is a chance the 2nd form may have multiple records. This is why I want to do it one at a time. Here is an example of what I want to do.
Enter a name, address and say birthday into one table using Form #1. Then close Form 1, and open Form 2. Form 2, will need the name only, and then I want to add say phone numbers associated with this name. Each phone number needs to be a separate record that will be used in a report later. So, here is what I want:

Table 1
ID Name Address Bday
1. Fred. 1234. 3/12/57
2. Sam. Go street. 4/12/54

Table 2
ID Select. name. Phone
2. Sam. 212-345-5678
2. Sam. 564-123-4567
2. Sam. 121-345-3344

Etc...:banghead::banghead::banghead:

 

[pbaldy]

Normally you would only put the ID (2) in the second table, not the name. This sounds like the kind of data I might have as a subform on the first form. The subform would be bound to table 2, and master/child links would keep the subform in sync with the main form. Does that sound like it might work? That keeps your presentation of the data in one place, and locks the data together (you know the phone numbers will get related to the correct person).

 

[txmmoore]

The problem is these will be 2 separate tables, performing 2 separate functions. I only used the sample above as an example. The 2nd table in reality will used about 6 things from the first table/form. The reason why I don't want to use a sub form is that the data for table 2 may be inputed at a different time or date, but needs to be linked to the record in table 1

 

[txmmoore]

Well, I tried several different methods last night. Geez this is frustrating! I have no problem using an Append Query, but I want it to only append 1 record (the last record of Table 1). I tried the Insert Command, no luck. Kept getting errors, either format, or syntax errors. So, this has become my challenge and anyone else who wants to help. Here is what I want to do; the fields listed are my actual field and their respective function / meaning:

Table 1:
ID - Autonumber
Customer - Customer Name
PO Num - PO number
SO Num - Sales Number
Value - Total value of PO

Table 2:
ID Select - Needs to be the same as ID from Table 1
PO Num - Needs to be the same as PO num from Table 1
Item Num - Item number they are ordering (Inputted by user)
Item Descrp - Description of Item (Inputted by user)

Okay, that is the table description. I have developed a form to enter the data into Table 1 and it works perfect. This is what I am looking for:

When I open "Form 2", I want the ID & PO Num from table 1 to be copied over to the ID Select & PO Num in Table 2. I will then use a form to enter the Item Num and Item description. This may be multiple records.

Can someone please help! The ID and PO num DO NOT get copied over into the new form / table. This is killing me!
:banghead:

 

[Minty]

The problem is these will be 2 separate tables, performing 2 separate functions. I only used the sample above as an example. The 2nd table in reality will used about 6 things from the first table/form. The reason why I don't want to use a sub form is that the data for table 2 may be inputed at a different time or date, but needs to be linked to the record in table 1

A sub form will deal with this perfectly, just use both fields you need as the child / master join.

So in your case the PONum And the ID field.I would add a new field to Table 2 called OrderLineID and make it the PK autonumber.

To be honest you really don't need the PO in the second table as it will always be linked with the ID from the order header table. Also before you get too stuck in change the ID field name in the Table one to a more meaningful OrdHeadID or similar - it will save you a lot of confusion later.

 

[txmmoore]

Thank you. But, I do not want to use a sub form. This is because the second form may not be used for several days, until some of the information is known. So, I want to create the Master record in Table 1, then several hours or days later input the information in Table 2. What I do is open Form 1 (hidden) go to last record, then open Form 2 and extract information from Form 1 / table 1 for use in Form 2 / table 2.

Any help / clues here why my Information is not getting stored in Table 2?

 

[Minty]

How do you know the last record is the one you want to use ? A sub form would solve all your issues in one fell swoop. Can you post up your tables and relationships - it seems you are missing a table in your set up. It should look something like this;

 

[txmmoore]

When the master record is created, then the program is typically closed until the additional information is known. This is "confirmed" as no additional information will be added into table 1 until table 2 information is entered and confirmed. This is why I do not want to use a subform.

 

[Minty]

I think you are making this very hard work for yourself.
I'll post up a sample with two or three simple forms that will do what you want almost with NO coding required. It won't matter if you come back to that record tomorrow or next year the data won't have "moved" and you'll still get the same control over it.

 

[txmmoore]

Minty - Thank you. I am only trying to make this straight forward and easy, as the next person that comes along may need to make changes. I just want to do this WITHOUT a subform. I want two separate forms. I know the subform is the way to go, but I have my reasons behind this.

 

[Minty]

I'm going to keep plugging away here...
Attached is the worlds simplest customer / order / order line structure. If this doesn't head towards what you are after, I would suggest in simple terms describe what you are are trying to process and the main information you are storing. Maybe show us your tables.

 

[txmmoore]

Thank you Minty. I thought I described what I was looking for in an earlier post. Simply put, I have a Master table (Table 1) that contains ID,Name, and birthday. I have created a form to enter data into Table 1. ID is the auto number field, and does not require anything. Once the name and birthday is entered. I want to close the form and go back to a menu. In this case "main menu". Then I want to click on a buttin in the main menu that will open Form#2. Form 2 will get from Table 1/ form 1 the ID and person's name. Within Form 2 I will add something, lets say date and address. Since these are always being added, I want to place this data into another table (Table 2). The ID from Table 1 / Form 1 will be the linked to Table 2 / Form 2 in the term of ID Select (or something). So, what you will have is this:

Table 1:
ID Name Birthday
1 Fred 3/13/58
2 Sam 4/18/61
3 Ann 1/15/67

Table 2 (Example):
ID Select Name Phone Address
1 Fred 123-123-4567 45 which way
1 Fred 212-444-1234 45 which way
1 Fred 212-337-4444 67 Otto Street
2 Sam 409-455-1818 415 There street

Etc.

I want 2 separate forms. I know that copying over the name is not necessary, but I want to do this.

In my "on click" button on my main menu I have the following commands which I thought would do the trick:

Docmd.open Form "Nameinput", achidden,acreadonly
docmd.goto record last
docmd.open form "phoneinput",acadd

(please don't quote me on above format of commands. I am doing this from memory)

Forms![phoneinput]![ID Select]=Forms![Nameinput]![ID]
Forms![phoneinput]![Name]=Forms![Nameinput]![Name]

The information is getting copied over and is viewable on the form. I see it with my own eyes. However, the data IS NOT getting copied into the table. Only data that I physically type in on Form 2.

Please, can someone help?:banghead::banghead::banghead:

 

[Minty]

I'm hoping you would describe what your data is and what you are trying to achieve with it, as I can guarantee that your current process will fail, as it relies on your "Last" record premise.
Unless you have a timestamp or ID field that you can reference you cannot be absolutely certain that the "Last" record Access returns is the one you want.

How are you going to go back to a main record later and perform the same task if your button only opens the "Last" record?

Can you explain your reluctance to use a subform? They are almost an inherent part of any half well thought out database project.

 

[txmmoore]

Minty / all -
Thank you for your support and suggestions. The definition of the database is simple; to reduce the amount of time necessary to submit two critical forms. The forms uses repeated information obtained from clients, but this information must be saved in the event of an audit. Due to the secrecy of the information, I could not post my database. This is why I was using generic stuff, aka to get an idea.

I burned mid-night oil over the past 2 days, and have figured out to do this and my issue is solved. I had several command lines in my code where I used the Me![stuff] command. When I change the ! to the dot, aka Me.[stuff], my data was being copied into the secondary table. All is good.

I know there are a lot of short cuts in Access. I am one who uses the simple things as it is so much easier to explain. I know its not efficient, but someone can look at it and know EXACTLY what is going on.

Once again, I am thankful to everyone who chimed in. Case closed!