need help passing form data between forms

Question

I have one form that I use a pull down to get a list of numbers. When a number is selected and the Go button is clicked. The remainder of the fields are filled in.  On another form I have a subform that list some of the numbers from the other form. What I would like to do is when the user clicks the number in the subform the first form opens up with all of the fields filled in with the appropriate data. I was able to get the first form to open up with the click using

stDocName = "View_PECN_Data"

stLinkCriteria = "[PECN]=" & Me![PECN]

DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria

but none of the data is shown. Now on the first form I use a similar commmand to populate the fields.

    stDocName = "View_P-ECN_Data"

    stLinkCriteria = "[PECN]=" & "'" & Me.[PECN] & "'"

    DoCmd.OpenForm stDocName, , , stLinkCriteria

Could the problem be the difference between Me. and Me!

Thanks

Bob

Answer 1

Well, the same kind of code should work just fine. It doesn't make any difference that it's on a subform!

Again: what is the datatype of PECN - in table design view is it a Number or is it Text? If it is Number then you want code like

stLinkCriteria = "[PECN]=" & Me![PECN]

but if it is text you need the quotes:

stLinkCriteria = "[PECN]='" & Me![PECN] & "'"

Answer 2

You are correct the data is not stored in the forms. I mis spoke. I'll try to be more clear. On form 1 I have a pull down that gets records (CRS) from a table. Once one of the numbers are selected and a button is pressed the rest of the fields on the form are populated.  On that form is a subform that has associated PECN listed. I would like to be able to select a PECN and then another form open up showing the data for that PECN.

Also The other form that will open also has a pull down where PECNs can be selected then a button is pressed to show the data.

I was looking to be able to open the second form filled in from a link on the subform.

Answer 3

Well, first off: data is NOT stored in forms, and your second form must get its data from a Table (its recordsource), not from the first form. Secondly, if you're trying to copy data from the table bound to the first form into a different table via the second form, you're very likely making a mistake: storing the same data redundantly in two different tables is a bad idea. Could you explain the context? What are the Recordsources of these two tables, and why do you need to do this?

That said... your code as written will not transfer any data into the new form, period. The stLinkCriteria simply limits which records in the second form's recordsource are displayed. If the second form is based on a table with (say) 21,524 different PECN records, and you open it from your first form with PECN 123 displayed, then the second form will show the data for PECN 123; if there are no records in the second form's recordsource for 123, then you'll see a blank form.

The other difference between your two snippets of code is that you have no delimiters for PECN in the first instance, and you're delimiting it with ' characters in the second. Numeric fields should have no delimiter; Text datatype fields should be delimited with either ' or " marks. What is the data type of PECN? Is it the same in the two instances?