Retrieve Data on 2nd Form Based on 1st Forms Entry

[legalhustler]

I think this is simple, I'm just not getting any results.

How do I retreive data automatically for specific fields for my 2nd Form which is based on the entry of the 1st Form? I assume it has to do with Control Source in the properties? Or AfterUpdate event?

Thanks!

 

[ranman256]

Your query control source would not change. The critieria IN the query would point to the fields in the 1st form, so whatever you pick in form 1, the query uses it as critiera.

qry2.sql = "select * from tbl where [CustName] = ' " & forms!FirstForm!cboCust & " ' "

form2.datasource = qry2

 

[legalhustler]

Your query control source would not change. The critieria IN the query would point to the fields in the 1st form, so whatever you pick in form 1, the query uses it as critiera.

qry2.sql = "select * from tbl where [CustName] = ' " & forms!FirstForm!cboCust & " ' "

form2.datasource = qry2

I didn't create a query. I created a blank Form (Form2) with text boxes that has the same text box names as Form1. The text boxes in Form2 is Unbound, so it needs to point to Form1 and retrieve the data for the specific text box. How would I do that?

Form1 has alot of data that a manager doesn't need to see, so Form2 will be used by a manager for a quick review and a sign off/approval.

 

[legalhustler]

Anyone?

 

[legalhustler]

I think this is simple, I'm just not getting any results.

How do I retreive data automatically for specific fields for my 2nd Form which is based on the entry of the 1st Form? I assume it has to do with Control Source in the properties? Or AfterUpdate event?

Thanks!

Is this possible?

 

[Joe4]

I think you need to explain the relationship between these two forms.
How/why do they interact?
Is one a Subform on the other?
Are the both open at the same time?
Is one called by the other (if so, how)?
What is the purpose of each Form?

The more details you can provide, the better the chance someone will be able to help you.

 

[legalhustler]

I think you need to explain the relationship between these two forms.
How/why do they interact?
Is one a Subform on the other?
Are the both open at the same time?
Is one called by the other (if so, how)?
What is the purpose of each Form?

The more details you can provide, the better the chance someone will be able to help you.

Sorry, about the duplicate question. I was trying to give a better title to the thread and didn't realize there was rules on the forum.

The Mainform is a data entry sheet with many fields. The Mainform has an icon so when a user clicks it it opens the second form, which I will refer to as the SubForm. The SubForm only has some of the fields of the Mainform, and it should automatically be filled when I open it based on the entry on the Mainform. The Subform will be printed and given to management for signature/approval. Once the Subform is approved, then Mainform data should submitted/recorded to the Table. The Subform should not record any data to the Table, only the MainForm record data to the Table after approval of the SubForm.

Approvals of records can vary, maybe 1 day, or 1 week. How can I make this happen so that the user can still continue recording new data to the MainForm while waiting for approval of the Subform? I need to have something that also indicates pending approval? There is never an instance of a record not being approved.

TIA!

 

[Joe4]

First, let's get some nomenclature out of the way. A Subform is a Form that is imbedded into another Form. Usually the data is related. So, for example, you may have a Form that returns a person, and then there is a Subform on that Form that returns the person's transaction history. So what you have is actually not a Form/Subform relationship. It sounds like you have two separate Forms.

Secondly, I recommend approaching this differently. If the intention with the Second Form is really to print a scaled down report to give to someone, do not use a Form at all, but a Report. Here is an article from Access-guru Allen Browne on how to do that: Microsoft Access tips: Print the record in the form

Thirdly, data cannot be stored in Forms or Subforms, only in tables. So any data entered in will need to be stored in a table, unless you plan to re-enter the whole thing again manually at some point in the future. I would recommend adding another field to your table, something like "Pending Approval" check-box, that gets populated when the record is first added, and updated once approved. You can then use this check box in any query where you only want to see approved or unapproved records.

Hope that helps!

 

[legalhustler]

First, let's get some nomenclature out of the way. A Subform is a Form that is imbedded into another Form. Usually the data is related. So, for example, you may have a Form that returns a person, and then there is a Subform on that Form that returns the person's transaction history. So what you have is actually not a Form/Subform relationship. It sounds like you have two separate Forms.

Secondly, I recommend approaching this differently. If the intention with the Second Form is really to print a scaled down report to give to someone, do not use a Form at all, but a Report. Here is an article from Access-guru Allen Browne on how to do that: Microsoft Access tips: Print the record in the form

Thirdly, data cannot be stored in Forms or Subforms, only in tables. So any data entered in will need to be stored in a table, unless you plan to re-enter the whole thing again manually at some point in the future. I would recommend adding another field to your table, something like "Pending Approval" check-box, that gets populated when the record is first added, and updated once approved. You can then use this check box in any query where you only want to see approved or unapproved records.

Hope that helps!

That is correct, I don't have Form/Subform relationship.

I followed your suggestion and made it a report and it worked out beautiful. Thanks!

I would recommend adding another field to your table, something like "Pending Approval" check-box, that gets populated when the record is first added, and updated once approved.

I will create a new field in my Table called Pending Approval. So in my data entry Form I should also create a Pending Approval radio button that automatically selects "Yes" and once the hardcopy gets approved the user will then go to the Table and update it to "No."? I assume they cannot go back to the Form since it's a data entry Form and displays only new records to be entered.

 

[legalhustler]

I am hesitant if the person touches the source data (i.e. Table). I created a search form that looks into the Table and they can enter the data using that but again they would touch the source data (i.e. Table). Is there a way you can search for a data entry form record and update it without having to go to the Table and updating the status? If possible, I do have a primary key called ID (set to Autonumber) in my data entry form which I would like the user to search by when they need to go back and update the approval status.