ACCESS FEED: [MS_AccessPros] Passing Default Values to a Form

Rabu, 24 Agustus 2011

[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;