Launching a Form to Another Form to Create a New Record

[nytwodees]

I use MS Access 2000.

I want to link 2 forms: Customer Info Form to Customer Work Order Form, The
Customer Work Order form has 1 Main Form and 2 sub-forms within it.

I want to create a command button from the Customer Info Form that will
launch the Customer Work Order Form and populate the main part of this form
with some identifying data such as ID, First Name, Last Name, etc. from the
Customer Info Form. This data will not be editable here. The remaining 2
sub-forms will be available for data entry and / or editing. Below is my
Command button code:

Private Sub CreateWOCommand_Click()
On Error GoTo Err_CreateWOCommand_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "CustomerWorkOrderForm"
stLinkCriteria = "[ID]=" & Me![ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CreateWOCommand_Click:
Exit Sub

Err_CreateWOCommand_Click:
MsgBox Err.Description
Resume Exit_CreateWOCommand_Click

End Sub

The 2 IDs are linked in a 1 to many relationship. 1 unique customer can have
many Work Orders.

I get to the new Customer Work Order Form OK, but it does not populate the
main part of the form as I desired. Would someone help me resolve this issue?

Thanks

 

[Jeanette Cunningham]

That's because the table for the customer work order form doesn't have the
first name and last name in it.
You can create a small subform for the customer's name and put the subform
in (say) the header of the work order form.
Use the link master and child fields so that the subform will show the
correct details for the current customer.

Another alternative is to pass the customer's name in the open args when
opening the work order form.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[nytwodees]

Hi Pieter:

Thanks for your reply!

I tried what you suggested but received an error message "Field cannot be
updated."

First it is not clear to me in your instructions whether you mean Control
Source Name or the Name of its Label. In any case, I tried the various
combinations and did not get the results I wanted.

Perhaps my design is fundamentally flawed!

My Customer Work Order Form gets inf from 3 tables:

tbl1CustInfo tbl2CustWO
tbl3CustFUP

ID CustomerWorkID
CustomerFollowUpID
(1 to Many Relationship) (1 to Many Relationship)

Other Fields Other Fields
Other Fields
LastName WorkOrderID
FollowUp Date
FirstName JobLocation
FollowUpRemark
etc. etc.
etc.

I have created a query that includes fields from each of the 3 tables
indicated above. The source for Work Order Form's Main form plus 2 sub-forms
use this query as its recordsource.

A I indicated in my original post, I have a control button on my Customer
Info Form that launches the Customer Work Order Form. The Main part of the
form should be populated with the ID, LastName, FirstName, etc. from the
Customer Order Form automatically. The remaining 2 subforms are for user
input. I hope I've made myself more clear.

I use MS Access 2000.

I want to link 2 forms: Customer Info Form to Customer Work Order Form, The
Customer Work Order form has 1 Main Form and 2 sub-forms within it.

I want to create a command button from the Customer Info Form that will
launch the Customer Work Order Form and populate the main part of this form
with some identifying data such as ID, First Name, Last Name, etc. from the
Customer Info Form. This data will not be editable here. The remaining 2
sub-forms will be available for data entry and / or editing. Below is my
Command button code:

Private Sub CreateWOCommand_Click()
On Error GoTo Err_CreateWOCommand_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "CustomerWorkOrderForm"
stLinkCriteria = "[ID]=" & Me![ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CreateWOCommand_Click:
Exit Sub

Err_CreateWOCommand_Click:
MsgBox Err.Description
Resume Exit_CreateWOCommand_Click

End Sub

The 2 IDs are linked in a 1 to many relationship. 1 unique customer can have
many Work Orders.

I get to the new Customer Work Order Form OK, but it does not populate the
main part of the form as I desired. Would someone help me resolve this issue?

Thanks

You have to get it to pull the values from the other form...
In Form2
Me.Controls("ControlName") = Forms!Form1.Controls("TextBoxNameInForm1")
etc in the Form's open event.

--
Message posted via AccessMonster.com


.

 

[nytwodees]

Hi Pieter:

My last post did not format properly and made it impossible to decipher. So
I am attempting to list it again.

Thanks for your reply!

I tried what you suggested but received an error message "Field cannot be
updated."

First it is not clear to me in your instructions whether you mean Control
Source Name or the Name of its Label. In any case, I tried the various
combinations and did not get the results I wanted.

Perhaps my design is fundamentally flawed!

My Customer Work Order Form gets inf from 3 tables:

Table---------tbl1CustInfo------------tbl2CustWO---------- tbl3CustFUP

Linked Fields----- ID----------- CustomerWorkID------ CustomerFollowUpID

Relationship--(1 to Many Relationship)-------- (1 to Many Relationship)

Other Fields------ LastName-----WorkOrderID -----------FollowUpDate
Other Fields------ FirstName----JobLocation----------- FollowUpRemark
Other Fields------ etc.-------------- etc.------------------- etc.

I have created a query that includes fields from each of the 3 tables
indicated above. The source for Work Order Form's Main form plus 2 sub-forms
use this query as its recordsource.

A I indicated in my original post, I have a control button on my Customer
Info Form that launches the Customer Work Order Form. The Main part of the
form should be populated with the ID, LastName, FirstName, etc. from the
Customer Order Form automatically. The remaining 2 subforms are for user
input. I hope I've made myself more clear.

 

[nytwodees]

Hi Jeanette:

Thanks for your reply!

I've copied my post to Pieter for you to please comment on.

My last post did not format properly and made it impossible to decipher. So
I am attempting to list it again.

Thanks for your reply!

I tried what you suggested but received an error message "Field cannot be
updated."

First it is not clear to me in your instructions whether you mean Control
Source Name or the Name of its Label. In any case, I tried the various
combinations and did not get the results I wanted.

Perhaps my design is fundamentally flawed!

My Customer Work Order Form gets inf from 3 tables:

Table---------tbl1CustInfo------------tbl2CustWO---------- tbl3CustFUP

Linked Fields----- ID----------- CustomerWorkID------ CustomerFollowUpID

Relationship--(1 to Many Relationship)-------- (1 to Many Relationship)

Other Fields------ LastName-----WorkOrderID -----------FollowUpDate
Other Fields------ FirstName----JobLocation----------- FollowUpRemark
Other Fields------ etc.-------------- etc.------------------- etc.

I have created a query that includes fields from each of the 3 tables
indicated above. The source for Work Order Form's Main form plus 2 sub-forms
use this query as its recordsource.

A I indicated in my original post, I have a control button on my Customer
Info Form that launches the Customer Work Order Form. The Main part of the
form should be populated with the ID, LastName, FirstName, etc. from the
Customer Order Form automatically. The remaining 2 subforms are for user
input. I hope I've made myself more clear.

 

[Jeanette Cunningham]

The tables look OK.
I think that creating a query based on the 3 tables and using that for all 3
forms is the problem with this.
I would expect that you could have a main form based on the a query using
the cust table.
It would have a drop down for choosing the customer.
On it put a subform to show and enter work orders for the customer selected
in the main form.
The work orders subform would be based on a query using the work orders
table.

You could set up a button on the subform that would open the follow ups
form to show followups for each work order.
The follow ups form would be based on a query using the followups table.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[nytwodees]

Hi Jeanette:

Thanks again for your reply and help!

For reasons of clarity and accuracy I want to create the Work Order from the
Customer's Info form. I know I can create a drop down list for the customer
but I prefer not to go that route.

My basic question remains:

Given a record in 1 Form how can I populate a new 2nd Form that
automatically fills in equivalent fields (of my choosing) using a Command
Button?

Please let me know, if you know specifically how to do that.

 

[Jeanette Cunningham]

If you will explain how
<reasons of clarity and accuracy I want to create the Work Order from the

Customer's Info form>

makes things more clear and accurate, we may be able to suggest a way to do
it.

Also please explain a bit more of the meaning of <create the Work Order from
the Customer's Info form>.
Do you mean that after user opens the customer form at a particular
customer, then click a button to create a work order for that customer?

For ease of building and maintaining this database, it is best to base each
form on just a single table, sometimes 2 tables.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[nytwodees]

Hi Jeanette:

""Do you mean that after user opens the customer form at a particular
customer, then click a button to create a work order for that customer? ""

The answer is YES.

For clarity, I'm particularly concerned with clients that have the same name
but have properties at different locations. The Customer Info form contains
that distinction. Also I want to create a work order if and only if that
customer already exists in my database.

 

[Jeanette Cunningham]

Sounds as if you have a form where you choose the customer then show all the
properties for that customer.
I have questions for you.
Does a work order apply to a customer or does it apply to a property?
Can more that one customer have the same property?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[nytwodees]

Hi Jeanette:

Since my Customers are sorted by alphabetic order followed by location
order, I can easily scroll through all that customer's property locations by
the customer name. (multiple records if necessary).

A work order is created for a distinct customer name plus property location.
They are not mutually exclusive.

Only 1 customer can have a distinct property location.

 

[Jeanette Cunningham]

I just went back to one or your earlier posts to get your table structure,
shown below.

My Customer Work Order Form gets inf from 3 tables:

tbl1CustInfo tbl2CustWO
tbl3CustFUP

ID CustomerWorkID
CustomerFollowUpID
(1 to Many Relationship) (1 to Many Relationship)

Other Fields Other Fields
Other Fields
LastName WorkOrderID
FollowUp Date
FirstName JobLocation
FollowUpRemark
etc. etc.
etc.

---------suggested tables--------
Customer
Building
WorkOrder
Followup

tblCustomer - CustomerID = primary key
LastName
FirstName
other Customer details like address, phone, email

tblBuilding - BuildingID - Primary key
CustomerID - foreign key from tblCustomer
BuildingAddress
Other Building details


tblWorkOrder WorkOrderID - Primary key
tblBuilding .BuildingID - foreign key from tblBuilding
WorkOrderName
WorkOrderDueDate
WorkOrderEnteredDate
other WorkOrder details

tblFollowup - FollowupID - Primary key
CustomerID - foreign key from tblCustomer
other followup details

Relationships
tblCustomer --1 to Many -- tblBuilding

tblBuilding -- 1 to Many -- tblWorkOrder

tblCustomer -- 1 to Many -- tblFollowup
------------end suggested tables-----------

To open your customer work order form from the customer form, put code like
this on a button.

Private Sub WOBtn_Click()
Dim strWhere As String

If Not IsNull(Me.BuildingID) Then
strWhere = "[BuildingID = " & Me.BuildingID
Debug.Print strWhere

DoCmd.OpenForm "WorkOrderForm" , , , strWhere
End If
End Sub

The above code will open the work order form to show all work orders for the
chosen building on the customer form.
If it is a continuous form, users can add a new work order at the bottom of
the list of work orders.




Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia