Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 189
  • Last Modified:

Pulling Data Value from one form into another form

I have a two forms with a Date Completed Field and Date Received fields in each of the forms, I would like to do the possibility:

Table_1 for process 1                        Table_2 for process 2
Date Received                                   Date Received
Date Completed                                Date Completed

So, when we finish process 1, we put in the date completed lets say 08/24/2012 so when go to the next form which is for process 2, I want to have date received field filled in with the same date in the Date completed field from process 1.

I have tried different codes or expressions but it just does not work. Any suggestions?
0
lauriecking0623
Asked:
lauriecking0623
  • 6
  • 5
1 Solution
 
als315Commented:
You can create query (update or append, it depends on your data structure), where Date received in second table will be updated (or record added) with Date compled from table1.
You can also use dlookup for filling Date Received. Someting like this (as a default value for field Date Received on a form):
=dlookup("[Date Completed]", "Table_1","[Process]="  & Me.Process1)
if Process is numeric or
=dlookup("[Date Completed]", "Table_1","[Process]="  & chr(34) & Me.Process1 & chr(34))
if Process is string
0
 
lauriecking0623Author Commented:
I am incorporating your suggestion then mark it as a Accept As solution. Just trying to get to this part.
0
 
lauriecking0623Author Commented:
Ok, I thought about doing an update query; however, what if I have nothing entered in the complete date for the first process, could I setup the query to run only when the first table has the date filled in?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
als315Commented:
You can add some logic for Null values. Something like:
=IIF(isnull(dlookup("[Date Completed]", "Table_1","[Process]="  & Me.Process1), Date(),dlookup("[Date Completed]", "Table_1","[Process]="  & Me.Process1))
but in this case I prefer to use functions (if you have big tables, you can save a lot of time removing second dlookup):
Function d_compl(Process as variant) as Date
d_compl = dlookup("[Date Completed]", "Table_1","[Process]="  & Process)
if isnull(d_compl) then d_comp = Date ' Here you can place any date value
end Function
0
 
lauriecking0623Author Commented:
This is how I wrote my function with your guidance below:

It does not work.

I called it basLookup.

Function dateCompleted (Process As Variant) As Date
dataCompleted = DLookup ("[HDR_Run_Date]", "TBL_Normalization","[Ingestion_Date_Received=" & Ingestion_Date_Received)
If IsNull (dateCompleted) Then
dateCompleted = Date
Me.Refresh
I put the refresh so I can see the data once it has been entered. I used =basLookup() on the AfterUpdate property on my form for the Ingestion_Date_Completed since these are subforms.

It won't work. Did I do something wrong?
0
 
als315Commented:
Parameter for function should be Parameter for dlookup.
You don't need function if you like to do anything in event. I see some errors in dlookup (no closing square bracket). Dlookup seems strange: you compare only date, but I think date could be same in different processed. May be you have some ID? Will be good if you can upload sample DB with involved tables and forms.
If you have single forms and both forms are opened you can set value on other form in afterupdate event of Ingestion_Date_Completed:
Forms![Process 2 form]![Process 2 Date Received] = Me.Ingestion_Date_Completed
0
 
lauriecking0623Author Commented:
I tried your suggestions not working. I will get a sample of my database together for you to review.
0
 
lauriecking0623Author Commented:
I have stripped out all of the data in my application.

I have attached my stripped down database. The form that you need to open is FRM_DataObjects.

CLick on the first tab on my form called HDR Normalization, there is a HDR RUN Date that I am trying to have put into the next tab's form called Ingestion so the field to get the HDR Run Date is Ingestion Date Received. Once the Ingestion Date Completed is filled then it is also in the Publish Date Received. That is it.
Database3.zip
0
 
als315Commented:
Sorry,  lauriecking0623, I've missed your comment. Is your question actual?
Nevertheless uploaded sample can't be used. I can't add any record and test it. You can have many records in table TBL_HDRNormalization. You should start from query where you will get proper record with nesessary date. Then you can use dlookup with this query.
0
 
lauriecking0623Author Commented:
I have tried the dlookup but I could not get it work so many times. Did you put the dlookup in the query?
0
 
als315Commented:
I think you don't need dlookup in query. May be this is possible:
INSERT INTO Tbl_Data_Load ( Data_Load_ID, Ingestion_Date_Received )
SELECT TBL_HDRNormalization.Data_Load_ID, TBL_HDRNormalization.HDR_Date_Run
FROM TBL_HDRNormalization LEFT JOIN Tbl_Data_Load ON TBL_HDRNormalization.Data_Load_ID = Tbl_Data_Load.Data_Load_ID
WHERE (((TBL_HDRNormalization.HDR_Date_Run) Is Not Null) AND ((Tbl_Data_Load.Data_Load_ID) Is Null));

Open in new window

This query will add new records to table Tbl_Data_Load if Data_Load_ID is missing and HDR_Date_Run is not Null. You can run it is some event or (Tab Ingestion got focus, for example). You can also modify this query to limit it to only current Data_Load_ID, but I'm not sure is it necessary or not.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now