John, When I don't know what I'm doing, I don't always explain myself
properly. I think my earlier question wanted to know how to bring up a form
with certain default values, not explaining that I wanted it to be a new
record. You advised me to use the DoCmd.OpenForm "frmRepairOrder" '
WhereCondition:="tblRepairOrder_CustomerID = " &
Forms!frmCustomerContactData!txtCustomerID, which loads the form with a
record that meets the WhereCondition (I think). All I needed to do was open
the form and then set the record to acNewRec with the following code:
DoCmd.GoToRecord acDataForm, "frmRepairOrder", acNewRec and it works, but
now I am having trouble with my combo boxes that I will post under a new
question.
John I really appreciate your assistance. Thanks again.
David
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Friday, August 26, 2011 1:37 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Passing Default Values to a Form
David-
Did you not understand what I said?
"If you have a relationship between two tables and you build a query on
those
two tables, both fields will contain the same value because the JOIN will
match
them. If you want the query to be updatable, you need to include the
field(s)
from the "many" side of the join (in your case, tblRepairOrder) and NOT
include
the the field(s) from the "one" side."
What does the Record Source of frmRepairOrder look like now? The query
should
include the CustomerID from tblRepairOrder. It should NOT include the
CustomerID from tblMasterCustomer. It should include ALL the fields from
tblRepairOrder and any fields from tblMasterCustomer that you want to
display
but not update on the form.
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of David
Tolson
Sent: Friday, August 26, 2011 4:59 AM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] Passing Default Values to a Form
Thanks again John, What you are advising seems like a simple solution,
however, I must be doing something incorrect, because the fields are not
automatically loading, and I have a number of resource manuals that do not
give much/any information on auto-lookup. Consequently, I have resorted to
filling the fields by giving them default values, but my non-number fields
are filing with" #Name?." I do not know what that is.
I have a general question about related tables that are used in forms, which
might help me in this instance: As an example, if I relate
tblMasterCustomer_CustomerID (the one side of the join) with
tblRepairOrder_CustomerID (the many side of the join), which field do I
place in the form, and whichever it is, should autolookup automatically fill
the related fields? In this case, the customer contact data - name, address,
phone, etc?
Thanks,
David
From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of John
Viescas
Sent: Thursday, August 25, 2011 7:42 AM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] Passing Default Values to a Form
David-
If you base frmRepairOrder on a query that includes both customers and
repair
orders, then set the Default Value of CustomerID from the form that opens
frmRepairOrder, Access should "auto-lookup" the related customer name,
address,
etc. as soon as you "dirty" any field on frmRepairOrder. It would be a good
idea to lock those controls on frmRepairOrder. You don't want a user
accidentally changing the customer data while entering a repair order. Edits
to
customer info should be done on a separate customer edit form.
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of David
Tolson
Sent: Thursday, August 25, 2011 1:26 PM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] Passing Default Values to a Form
Thank you John! That is a very valuable lesson for me to learn. It leads to
a follow-up question: Since I've joined CustomerID in tblMasterCustomer
and tblRepairOrder, how do I load default values into frmRepairOrder that
has the Customer's name and contact information? I should not have to place
fields in tblRepairOrder that have this information; should I since it
appears that it would be superfluous, given tblMasterCustomer and
tblRepairOrder are joined by CustomerID?
r/David
From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of John
Viescas
Sent: Thursday, August 25, 2011 2:09 AM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] Passing Default Values to a Form
David-
If you have a relationship between two tables and you build a query on those
two
tables, both fields will contain the same value because the JOIN will match
them. If you want the query to be updatable, you need to include the
field(s)
from the "many" side of the join (in your case, tblRepairOrder) and NOT
include
the the field(s) from the "one" side. Do NOT rename the field. The code
opening the form is looking for CustomerID, but if there is no CustomerID in
the
Record Source, it'll error out.
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of David
Tolson
Sent: Wednesday, August 24, 2011 8:53 PM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] Passing Default Values to a Form
Thanks John, Yes, I have a CustomerID field in the form that is running the
code(tblCustomerVehicles_CustomerID). I placed a CustomerID field in
tblCustomerRepair as you see, but my thoughts at the time were to put it in
there and related that field to tblMasterCustomer_CustomerID, and that would
pickup the contact data - name, address, etc.. when frmRepairOrder was
opened. But evidentially that that is not the case.
I am not sure which CustomerID needs to go in this form/table.
r/David
From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of John
Viescas
Sent: Wednesday, August 24, 2011 2:36 PM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] Passing Default Values to a Form
David-
Is there a CustomerID field in the form that's running this code? Probably.
But there's no CustomerID in the target form because you've renamed the
field
for some strange reason:
tblRepairOrder.CustomerID AS tblRepairOrder_CustomerID
Why did you do that?
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of
djsdaddy531
Sent: Wednesday, August 24, 2011 8:16 PM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: [MS_AccessPros] Passing Default Values to a Form
Great Day, I am attempting to open a repair order form for an automobile
service
center. The click event that opens frmRepairOrder comes from the subform
(frmCustomerVehicles subform) attached to the customer's contact information
form (frmCustomerContactData)
I am trying to pass CustomerID from frmCustomerContactData and
CustomerVehicleID
from subform frmCustomerVehiclesSub (subform within
frmCustomerContactData)and
pass it to related fields in frmRepairOrder.
When I click btnRepairOrder, the program opens a dialogue box prompting me
for a
CustomerID and when I enter the appropriate customer number I get a message
that
says:
Run-time error '2465':
"Microsoft Office Access can't find the field 'CustomerID' referred to in
your
expression."
The form does open, but it is empty.
Here is the code behind btnRepairOrder, which is in the header of the above
subform:
Private Sub btnRepairOrder_Click()
Dim lCnt As Long
lCnt = DCount("[CusVehicleID]", "tblCustomerVehicles", "[CustomerID]= " &
Me.CustomerID)
Select Case lCnt
Case 0
MsgBox "Please enter a vehicle for this customer before proceeding",
vbInformation
Exit Sub
Case 1
' Open the form filtered for the current customer
DoCmd.OpenForm "frmRepairOrder", WhereCondition:="CustomerID = " &
Me.CustomerID
' Make sure the form is there
Forms!frmRepairOrder.SetFocus
' Set the defaults
Forms!frmRepairOrder!CustomerID.DefaultValue = Me.CustomerID
Forms!frmRepairOrder!CusVehicleID.DefaultValue =
Me.CustomerVehicleID
Exit Sub
Case Else
MsgBox "Message about more than 1 vehicle.", vbInformation
Exit Sub
End Select
End Sub
It is my hope that after the values of CustomerID and CustomerVehicleID are
appropriately placed in frmRepairOrder, the related fields concerning the
vehicle make, model and year and the contact information for the customer
will
automatically populate the form.
I am including the SQL for those who may be interested in it.
Thanks in advance,
David
SELECT tblRepairOrder.RepairOrderID, tblRepairOrder.CustomerID AS
tblRepairOrder_CustomerID, tblRepairOrder.CusVehicleID AS
tblRepairOrder_CusVehicleID, tblRepairOrder.RepairTypeID AS
tblRepairOrder_RepairTypeID, tblRepairOrder.RepairOrderPartsID,
tblRepairOrder.PartCost, tblRepairOrder.QtyParts, tblRepairOrder.LaborCost,
tblRepairOrder.DescribeRepair, tblRepairOrder.DateIn,
tblRepairOrder.DateOut,
tblRepairOrder.DateOfWarrantyWork, tblRepairOrder.MilageIn,
tblRepairOrder.MileageOut, tblRepairOrder.EmployeeID AS
tblRepairOrder_EmployeeID, tblMasterCustomer.CustomerID AS
tblMasterCustomer_CustomerID, tblMasterCustomer.FirstName,
tblMasterCustomer.LastName, tblMasterCustomer.[Street Address-Line 1],
tblMasterCustomer.[Street Address-Line 2], tblMasterCustomer.City,
tblMasterCustomer.State, tblMasterCustomer.ZipCode,
tblMasterCustomer.HomePhone,
tblMasterCustomer.BusinessPhone, tblMasterCustomer.MobilePhone,
tblMasterCustomer.EmailAdress, tblCustomerVehicles.CusVehicleID AS
tblCustomerVehicles_CusVehicleID, tblCustomerVehicles.Make,
tblCustomerVehicles.Model, tblCustomerVehicles.ModYear,
tblCustomerVehicles.Color, tblCustomerVehicles.VIN,
tblCustomerVehicles.[Tag-State], tblRepairType.RepairTypeID AS
tblRepairType_RepairTypeID, tblMasterEmployee.EmployeeID AS
tblMasterEmployee_EmployeeID, tblRepairType.RepairType
FROM tblRepairType INNER JOIN (tblMasterEmployee INNER JOIN
(tblMasterCustomer
INNER JOIN (tblCustomerVehicles INNER JOIN tblRepairOrder ON
tblCustomerVehicles.CusVehicleID = tblRepairOrder.CusVehicleID) ON
tblMasterCustomer.CustomerID = tblCustomerVehicles.CustomerID) ON
tblMasterEmployee.EmployeeID = tblRepairOrder.EmployeeID) ON
tblRepairType.RepairTypeID = tblRepairOrder.RepairTypeID;
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar