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@yahoogroups.com] On Behalf Of David Tolson
Sent: Wednesday, August 24, 2011 8:53 PM
To: MS_Access_Professionals@yahoogroups.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@yahoogroups.com] On Behalf Of John Viescas
Sent: Wednesday, August 24, 2011 2:36 PM
To: MS_Access_Professionals@yahoogroups.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@yahoogroups.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>
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
Tidak ada komentar:
Posting Komentar