How to link a value from one form to another form?

[Steven Seagal]

I have a form, lets call this A, whose data source is a file I import from Excel...One of the fields is a target date. Now I have another form, lets call this B, which is used to organize and compile the data onto a separate table, where, whatever you put in here goes on this new table. However, I frequently have to manually put in the target date onto this new form and I was wondering if there was a way I can automate this so that whenever I import the original excel file, it goes right on this new form. Every attempt so far results in a #Name? error.

Here's what I want to do on form B.

Control Source.................FormA.[Target Date]

Thanks for any help

[John_G]

First, FormA must be open (it can be hidden) in order to reference controls in it. The correct syntax for that is forms!FormA![Target Date]. But - how do you know which record in formA to get the data from?

Did you create a link to the Excel file? If so, it can be used is many cases the same way as you would a table. This includes the DLookup function, which might be an easier approach, since you woulld not need the two forms open.

HTH

John

[Steven Seagal]

Still seem to getting errors. On the original approach where you gave me the syntax, I got an error that the field name was't on where I was getting the data from, which it is indeed on the table that is the source for Form A.


Then I tried doing a dlookup where the Target Date is located on the table P_FPSX. This is imported directly from an Excel file. I now get a #Error.
Control Source on Form B field = DLookUp("Target Date","P_FPSX")

Any hints on how to proceed? Thanks

[John_G]

Try putting the DLookup in one of the event procedires of your form, instead of the control source:

Me![controlname] = DLookUp("[Target Date]","P_FPSX")

Note that I added the square brackets - you need them because "Target Date " has a space in it. Note that this will return the value from the first record of the "P_FPSX" table, since you did not include any criteria in the DLookup.


John

[Steven Seagal]

Sweet! Got that working! Now you were correct that it only returns the value from the first record. Now I have a list of about 300 projects, each having a 6 digit serial number. On Form B, the project number is available. How can I make the dlookup know how to choose the Target Date based on what project # my screen is on? What would my criteria look like?

[John_G]

You DLookup would look something like this:

DLookUp("[Target Date]","P_FPSX","Project_ID = " & Me!Proj_ID)

Replace Project_ID with the name of the Project ID field in the table, and Proj_ID with the name of the control that contains the Project ID on your form, and it should work file.

The criteria in the DLookup look just like a WHERE clause, but without the "where". If the criteria identify more than one record, DLookup will return the value for the first one it finds that matches.

John