Application Problem

E

edonahue

Registered User.
Local time
Today, 00:01
Joined
Apr 6, 2000
Messages
15
I have been asked to make changes to an app that is WAY more complicated than anything I've ever done. The changes themselves are no problem (add 3 new fields to each of two tables and modify 2 forms and a report). The problem is after I make the changes the app doesn't work. The database (even stripped of all but 10 records) is too big to upload here. (2.5M)

I need to add 2 text fields (Route and Region) each 10 chars. and a yes/no field (Early Delivery) to the Order Head table and the Location table. I need to add those three fields to the Order Head and Location forms and get the info to copy over from the Location table if they select a reception location from the drop down on the Order Head form.

I have at least two possible problems. The first is as soon as I add the fields to the table and the Order Head form the form doesn't open in Data Entry mode. If I change the Data Entry property on the form to Yes, it works (although I think from looking at the code that it should have been set automatically when the New Order button was clicked on the switchboard). The second problem is that once I change the Data Entry property to Yes I can't add records to the subforms (the message is that a record must be added to Order Head first).

So....something I'm doing appears to be circumventing the code that commits the record.

I'd really appreciate any help.

OrderHead Code (I cut a lot out so the message wasn't too big)

Option Compare Database ' Use database order for string comparisons.
Option Explicit ' Requires variables to be declared before they are used.

Private Sub cmdAdd_Click()
On Error GoTo Err_cmdAdd_Click
Me.cmdAdditionalPayment.Visible = False
DoCmd.GoToRecord , , acNewRec
Me.frmBridalConsultant.SetFocus

Exit_cmdAdd_Click:
Exit Sub

Err_cmdAdd_Click:
MsgBox Err.Description
Resume Exit_cmdAdd_Click

End Sub

Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click
Dim myVar As Variant
Me.cmdAdditionalPayment.Visible = False
If IsNull(Me.frmRecordStatus) _
Or IsEmpty(Me.frmRecordStatus) _
Or Me.frmRecordStatus = " " Then
myVar = OrdersCleanUp(Me.frmOrder)
Me.frmRecordStatus = "P"
Me.dspRecordStatus = "DELETED"
Me.dspRecordStatus.Visible = True
'Me.cmdDelete.Visible = False
'Me.cmdUndelete.Visible = True
Me.Refresh
Form.Repaint
MsgBox ("Order flagged as deleted . . .")
Else
If Me.frmRecordStatus = "P" _
Or Me.frmRecordStatus = "D" Then
MsgBox "Order is already flagged as deleted"
Else
End If
End If

Exit_cmdDelete_Click:
Exit Sub

Err_cmdDelete_Click:
MsgBox Err.Description
Resume Exit_cmdDelete_Click

End Sub



Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click

If IsNull(Me.frmReceptionDate) _
Or IsEmpty(Me.frmReceptionDate) _
Or Me.frmReceptionDate = " " Then
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.Refresh
MsgBox ("Order saved . . .")
End If

Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click

End Sub

Private Sub Form_Activate()
If gTrace Then
gSequence = gSequence + 1
MsgBox Me.Name & " " & gSequence & " Form_Activate" _
& vbCr & vbLf & "Order=" & Me![frmOrder]
Else
End If
If IsNull(Me![frmOrder]) Then
intOrder = 0
Else
intOrder = Me![frmOrder]
End If
If blnPackage Then
If Me.cmdAdditionalPayment.Visible = True Then
Me![frmTotalAdditionalPayments] = rtnTotalAdditionalPayments(intOrder)
Me![dspBalanceDue] = Me![dspTotalCharges] - Me![frmTotalAdditionalPayments]
Me.cmdMoney.Visible = True
Else
Me![frmTotalPayments] = rtnTotalPayments(intOrder)
Me![dspBalanceDue] = Me![dspTotalCharges] - Me![frmTotalPayments]
Me.cmdMoney.Visible = False
End If
Else
Me![frmTotalPayments] = rtnTotalPayments(intOrder)
Me![dspBalanceDue] = Me![dspTotalCharges] - Me![frmTotalPayments]
End If
Form.Repaint
End Sub

Private Sub Form_AfterInsert()
If IsNull(Me![frmReceptionGuests]) Then
intGuests = 0
Else
intGuests = Me![frmReceptionGuests]
End If
blnCoveringCharge = False
If IsNull(Me![frmLayers]) Then
intLayers = 0
Else
intLayers = Me![frmLayers]
End If
If IsNull(Me![frmTierSets]) _
Or Me![frmTierSets] < 1 Then
blnRaised = False
Else
blnRaised = True
End If
intLayer = 1
If gTrace Then
gSequence = gSequence + 1
MsgBox Me.Name & " " & gSequence & " Form_AfterInsert" _
& vbCr & vbLf & "Order=" & Me![frmOrder] _
& vbCr & vbLf & "Guests=" & Me![frmReceptionGuests] _
& vbCr & vbLf & "intLayers=" & intLayers _
& vbCr & vbLf & "blnRaised=" & blnRaised _
& vbCr & vbLf & "intLayer=" & intLayer
Else
End If
End Sub


Private Sub Form_BeforeInsert(Cancel As Integer)
If gTrace Then
gSequence = gSequence + 1
MsgBox Me.Name & " " & gSequence & " Form_BeforeInsert" _
& vbCr & vbLf & "Order=" & Me![Order]
Else
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If gTrace Then
gSequence = gSequence + 1
MsgBox Me.Name & " " & gSequence & " Form_BeforeUpdate"
Else
End If
End Sub

Private Sub Form_CommandExecute(ByVal Command As Variant)
If gTrace Then
gSequence = gSequence + 1
MsgBox Me.Name & " " & gSequence & " Form_CommandExecute"
Else
End If
End Sub

Private Sub Form_Current()
' MsgBox Me.Name & "Form_Current Order=" & Me.frmOrder
If gTrace Then
gSequence = gSequence + 1
MsgBox Me.Name & " " & gSequence & " Form_Current" _
& vbCr & vbLf & "Order=" & Me![Order] _
& vbCr & vbLf & "NewRecord=" & Me.NewRecord _
& vbCr & vbLf & "DataEntry=" & Me.DataEntry _
& vbCr & vbLf & "AllowAdditions=" & Me.AllowAdditions
Else
End If
If Form.DataEntry Then ' New Order
Me![dspMode] = "New Order"
'Me.dspFilter.Visible = False
Me.cmdAdd.Visible = True
Me.cmdDelete.Visible = True
Me.cmdUndelete.Visible = False
Me.cmdFilter.Visible = False
Me.cmdMoney.Visible = False
Me.cmdAdditionalPayment.Visible = False
Else ' Order Inquiry
Me![dspMode] = "Order Inquiry/Edit"
Me.AllowAdditions = False
Me.cmdAdd.Visible = False
'Me![dspFilter] = Me.Filter
'Me.dspFilter.Visible = True
Me.cmdFilter.Visible = True
If IsNull(Me.frmRecordStatus) _
Or IsEmpty(Me.frmRecordStatus) _
Or Me.frmRecordStatus = " " Then
Me.cmdDelete.Visible = True
Me.cmdUndelete.Visible = False
Me.dspRecordStatus = " "
Me.dspRecordStatus.Visible = False
Else
Me.cmdDelete.Visible = False
Me.cmdUndelete.Visible = True
Me.dspRecordStatus = "DELETED"
Me.dspRecordStatus.Visible = True
End If
End If
Me![frmHotelPackage] = Me![dspHotelPackage]
If IsNull(Me![frmHotelPackage]) Then
Me.cmdMoney.Visible = False
Me.cmdAdditionalPayment.Visible = False
Else
Me.cmdMoney.Caption = "Show Upgrades"
Me.cmdMoney.Visible = True
End If
Me![frmReceptionLocation] = Me![dspReceptionLocation]
Me![frmCakeType] = Me![dspCakeType]
If IsNull(Me![frmReceptionGuests]) Then
intGuests = 0
Else
intGuests = Me![frmReceptionGuests]
End If
Me![dspTotalCharges] = Me![frmCakePrice] _
+ Me![frmCoveringCharge] _
+ Me![frmFlowersCharge] _
+ Me![frmTieringCharge] _
+ Me![frmFlatCharge] _
+ Me![frmDeliveryCharge]
Me![dspBalanceDue] = Me![dspTotalCharges] - Me![frmTotalPayments]
If Me![frmCoveringCharge] > 0 Then
blnCoveringCharge = True
Else
blnCoveringCharge = False
End If
If IsNull(Me.TierSets) Or Me.TierSets < 1 Then
blnRaised = False
Else
blnRaised = True
End If
'MsgBox "Trap 3 blnRaised=" & blnRaised, vbInformation
intLayer = 0
End Sub

Private Sub Form_DataChange(ByVal Reason As Long)
If gTrace Then
gSequence = gSequence + 1
MsgBox Me.Name & " " & gSequence & " Form_DataChange" _
& vbCr & vbLf & "Reason=" & Reason
Else
End If
End Sub

Private Sub Form_Deactivate()
If gTrace Then
'gSequence = gSequence + 1
'MsgBox Me.Name & " " & gSequence & " Form_Deactivate"
Else
End If
End Sub

Private Sub Form_Delete(Cancel As Integer)
If gTrace Then
gSequence = gSequence + 1
MsgBox Me.Name & " " & gSequence & " Form_Delete"
Else
End If
End Sub


Private Sub Form_Filter(Cancel As Integer, FilterType As Integer)
If gTrace Then
gSequence = gSequence + 1
MsgBox Me.Name & " " & gSequence & " Form_Filter" _
& vbCr & vbLf & "FilterType=" & FilterType
Else
End If
End Sub

Private Sub Form_GotFocus()
If gTrace Then
gSequence = gSequence + 1
MsgBox Me.Name & " " & gSequence & " Form_GotFocus"
Else
End If
End Sub

Private Sub Form_Load()
If gStartup Then
Else
gTrace = True
gSequence = gSequence + 1
MsgBox Me.Name & " " & gSequence & " Form_Load"
End If
End Sub

Private Sub Form_LostFocus()
If gTrace Then
gSequence = gSequence + 1
MsgBox Me.Name & " " & gSequence & " Form_LostFocus"
Else
End If
End Sub

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open
Dim myFilter As String
Dim myBride As String, myCakeType As String, myPackage As String

' MsgBox Me.Name & "Form_Open Current Order=" & Me.frmOrder

If Form.DataEntry Then ' New Order
Else ' Order Inquiry
If gTrace Then
MsgBox ">>>Filter Values" _
& vbCr & vbLf & "Order=" & Forms![OrderFilter]![frmOrder] _
& vbCr & vbLf & "OrderDate=" & Forms![OrderFilter]![frmOrderDate] _
& vbCr & vbLf & "Bride=" & Forms![OrderFilter]![frmBridesLastName] _
& vbCr & vbLf & "Package=" & Forms![OrderFilter]![frmPackage] _
& vbCr & vbLf & "CakeType=" & Forms![OrderFilter]![frmCakeType] _
& vbCr & vbLf & "ReceptionDates=" & Forms![OrderFilter]![rptReceptionDateBegin] _
& "-" & Forms![OrderFilter]![rptReceptionDateEnd]
Else
End If
If Not IsNull(Forms![OrderFilter]![rptReceptionDateBegin]) Then
myFilter = "OrderHead.ReceptionDate >= #" _
& Forms!OrderFilter!rptReceptionDateBegin _
& "# And OrderHead.ReceptionDate <= #" _
& Forms!OrderFilter!rptReceptionDateEnd & "#"
Else
End If
If Not IsNull(Forms![OrderFilter]![frmOrder]) Then
If IsNumeric(Forms![OrderFilter]![frmOrder]) Then
'Me.Filter = "Order = " & Forms![OrderFilter]![frmOrder]
myFilter = "Order = " & Forms![OrderFilter]![frmOrder]
Else
'Me.Filter = "Order > 0"
myFilter = "Order > 0"
End If
Else
If IsNull(Forms![OrderFilter]![frmBridesLastName]) Then
myBride = "*"
Else
myBride = Forms![OrderFilter]![frmBridesLastName] & "*"
End If
If myFilter = "" Then
If myBride = "*" Then
myFilter = "(IsEmpty(BridesLastName) or IsNull(BridesLastName) or BridesLastName like '" & myBride & "')"
Else
myFilter = "BridesLastName like '" & myBride & "'"
End If
Else
If myBride = "*" Then
myFilter = myFilter & " and (IsEmpty(BridesLastName) or IsNull(BridesLastName) or BridesLastName like '" & myBride & "')"
Else
myFilter = myFilter & " and BridesLastName like '" & myBride & "'"
End If
End If
If IsNull(Forms![OrderFilter]![frmPackage]) Then
myPackage = "*"
Else
myPackage = Forms![OrderFilter]![frmPackage] & "*"
End If
If myFilter = "" Then
If myPackage = "*" Then
myFilter = "(IsEmpty(HotelPackage) or IsNull(HotelPackage) or HotelPackage like '" & myPackage & "')"
Else
myFilter = "HotelPackage like '" & myPackage & "'"
End If
Else
If myPackage = "*" Then
myFilter = myFilter & " and (IsEmpty(HotelPackage) or IsNull(HotelPackage) or HotelPackage like '" & myPackage & "')"
Else
myFilter = myFilter & " and HotelPackage like '" & myPackage & "'"
End If
End If
If IsNull(Forms![OrderFilter]![frmCakeType]) Then
myCakeType = "*"
Else
myCakeType = Forms![OrderFilter]![frmCakeType] & "*"
End If
If myFilter = "" Then
If myCakeType = "*" Then
myFilter = "(IsEmpty(CakeType) or IsNull(CakeType) or CakeType like '" & myCakeType & "')"
Else
myFilter = "CakeType like '" & myCakeType & "'"
End If
Else
If myCakeType = "*" Then
myFilter = myFilter & " and (IsEmpty(CakeType) or IsNull(CakeType) or CakeType like '" & myCakeType & "')"
Else
myFilter = myFilter & " and CakeType like '" & myCakeType & "'"
End If
End If
End If
Me.Filter = myFilter
Me.FilterOn = True
'Me![dspFilter] = Me.Filter
Me.OrderBy = "Order DESC"
Me.OrderByOn = True
Form.Repaint
End If

Exit_Form_Open:
Exit Sub

Err_Form_Open:
MsgBox Err.Description
Resume Exit_Form_Open

End Sub

Private Sub Form_Undo(Cancel As Integer)
If gTrace Then
gSequence = gSequence + 1
MsgBox Me.Name & " " & gSequence & " Form_Undo"
Else
End If
End Sub

Private Sub Form_Unload(Cancel As Integer)
If gTrace Then
'gSequence = gSequence + 1
'MsgBox Me.Name & " " & gSequence & " Form_Unload"
Else
End If
End Sub


Private Sub frmReceptionLocation_AfterUpdate()
Dim CurConn As New ADODB.Connection, objRSLocation As New ADODB.Recordset
Dim myReceptionLocation As String
'MsgBox Me.Name &" frmReceptionLocation_AfterUpdate", vbInformation
If IsNull(Me![frmReceptionLocation]) Then
myReceptionLocation = " "
Else
myReceptionLocation = Me![frmReceptionLocation]
End If
Me![dspReceptionLocation] = myReceptionLocation
CurConn.Open CurrentProject.Connection
objRSLocation.Open "Select * from [Location] where [Location] = '" & myReceptionLocation & "'", CurConn, adOpenKeyset, adLockOptimistic
If objRSLocation.RecordCount = 1 Then
Me![dspReceptionLocationID] = objRSLocation("LocationID")
Me![frmReceptionContact] = objRSLocation("Contact")
Me![frmReceptionContactTelephone] = objRSLocation("Telephone")
Me![frmReceptionStreetAddr1] = objRSLocation("StreetAddr1")
Me![frmReceptionStreetAddr2] = objRSLocation("StreetAddr2")
Me![frmReceptionTown] = objRSLocation("Town")
Me![frmReceptionState] = objRSLocation("State")
Me![frmReceptionZipcode] = objRSLocation("Zipcode")
Me![frmDeliveryContact] = objRSLocation("Contact")
Me![frmDeliveryContactTelephone] = objRSLocation("Telephone")
If blnPackage Then
Me![frmDeliveryCharge] = 0
Else
Me![frmDeliveryCharge] = objRSLocation("DeliveryCharge")
End If
Else
Me![frmReceptionContact] = " "
Me![frmReceptionContactTelephone] = " "
Me![frmReceptionStreetAddr1] = " "
Me![frmReceptionStreetAddr2] = " "
Me![frmReceptionTown] = " "
Me![frmReceptionState] = " "
Me![frmReceptionZipcode] = " "
Me![frmDeliveryContact] = " "
Me![frmDeliveryContactTelephone] = " "
Me![frmDeliveryCharge] = 0
End If
objRSLocation.Close
Set objRSLocation = Nothing
CurConn.Close
Set CurConn = Nothing
Me![dspTotalCharges] = Me![frmCakePrice] _
+ Me![frmCoveringCharge] _
+ Me![frmFlowersCharge] _
+ Me![frmTieringCharge] _
+ Me![frmFlatCharge] _
+ Me![frmDeliveryCharge]
Me![dspBalanceDue] = Me![dspTotalCharges] - Me![frmTotalPayments]
Form.Repaint
End Sub

Private Sub frmReceptionLocation_BeforeUpdate(Cancel As Integer)
Dim CurConn As New ADODB.Connection, objRSLocation As New ADODB.Recordset
Dim myReceptionLocation As String
'MsgBox Me.Name &" frmReceptionLocation_BeforeUpdate", vbInformation
If IsNull(Me![frmReceptionLocation]) Then
myReceptionLocation = " "
Else
myReceptionLocation = Me![frmReceptionLocation]
End If
Me![dspReceptionLocation] = myReceptionLocation
CurConn.Open CurrentProject.Connection
objRSLocation.Open "Select * from [Location] where [Location] = '" & myReceptionLocation & "'", CurConn, adOpenKeyset, adLockOptimistic
If objRSLocation.RecordCount = 1 Then
Me![dspReceptionLocationID] = objRSLocation("LocationID")
Me![frmReceptionContact] = objRSLocation("Contact")
Me![frmReceptionContactTelephone] = objRSLocation("Telephone")
Me![frmReceptionStreetAddr1] = objRSLocation("StreetAddr1")
Me![frmReceptionStreetAddr2] = objRSLocation("StreetAddr2")
Me![frmReceptionTown] = objRSLocation("Town")
Me![frmReceptionState] = objRSLocation("State")
Me![frmReceptionZipcode] = objRSLocation("Zipcode")
Me![frmDeliveryContact] = objRSLocation("Contact")
Me![frmDeliveryContactTelephone] = objRSLocation("Telephone")
If blnPackage Then
Me![frmDeliveryCharge] = 0
Else
Me![frmDeliveryCharge] = objRSLocation("DeliveryCharge")
End If
Else
Me![frmReceptionContact] = " "
Me![frmReceptionContactTelephone] = " "
Me![frmReceptionStreetAddr1] = " "
Me![frmReceptionStreetAddr2] = " "
Me![frmReceptionTown] = " "
Me![frmReceptionState] = " "
Me![frmReceptionZipcode] = " "
Me![frmDeliveryContact] = " "
Me![frmDeliveryContactTelephone] = " "
Me![frmDeliveryCharge] = 0
End If
objRSLocation.Close
Set objRSLocation = Nothing
CurConn.Close
Set CurConn = Nothing
Me![dspTotalCharges] = Me![frmCakePrice] _
+ Me![frmCoveringCharge] _
+ Me![frmFlowersCharge] _
+ Me![frmTieringCharge] _
+ Me![frmFlatCharge] _
+ Me![frmDeliveryCharge]
Me![dspBalanceDue] = Me![dspTotalCharges] - Me![frmTotalPayments]
Form.Repaint
End Sub


Private Sub frmStacked_AfterUpdate()
If IsNull(Me![frmTierSets]) _
Or Me![frmTierSets] < 1 Then
blnRaised = False
Else
blnRaised = True
End If
End Sub

Private Sub frmStacked_BeforeUpdate(Cancel As Integer)
If IsNull(Me![frmTierSets]) _
Or Me![frmTierSets] < 1 Then
blnRaised = False
Else
blnRaised = True
End If
End Sub


Private Sub cmdLocations_Click()
On Error GoTo Err_cmdLocations_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Location"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Me.Refresh

Exit_cmdLocations_Click:
Exit Sub

Err_cmdLocations_Click:
MsgBox Err.Description
Resume Exit_cmdLocations_Click

End Sub



Switchboard Code

ption Compare Database ' Use database order for string comparisons.
Option Explicit ' Requires variables to be declared before they are used.

Function OpenForms(strFormName As String) As Integer
' This function is used in the onClick event of command buttons that
' open forms on the Main Switchboard. Using a function is more efficient
' than repeating the same code in multiple event procedures.
On Error GoTo Err_OpenForms

' Open specified form.
DoCmd.OpenForm strFormName

Exit_OpenForms:
Exit Function

Err_OpenForms:
MsgBox Err.Description
Resume Exit_OpenForms

End Function


Private Sub cmdNewOrder_Click()
DoCmd.OpenForm "OrderHead", , , , acFormAdd
End Sub

Private Sub cmdOrderInquiry_Click()
DoCmd.OpenForm "OrderFilter"
End Sub

Private Sub cmdReports_Click()
DoCmd.OpenForm "ReportFilter"
End Sub

Sub DisplayDatabaseWindow_Click()
On Error GoTo Err_DisplayDatabaseWindow_Click
Dim strDocName As String

' Close Main Switchboard form.
DoCmd.Close
gStartup = False
gTrace = False
gSequence = 0

' Give focus to Database window.
strDocName = "OrderHead"
DoCmd.SelectObject acTable, strDocName, True

Exit_DisplayDatabaseWindow_Click:
Exit Sub

Err_DisplayDatabaseWindow_Click:
MsgBox Err.Description
Resume Exit_DisplayDatabaseWindow_Click

End Sub




Erika
 
First of all, when you post code, please, PLEASE, PLEASE use code tags so that it is more readable. To do it you put the word code within square brackets [] and at the end you add a forward slash on the word code - /code within square brackets. That way it shows up like:

Code: 
Dim strTest As String
   Set x = etc.
   If this = that then
       do this
   Else
       do that
   End If

and second, when you say you make changes to two forms, what kind of changes?
 
Simple Software Solutions

Witout plowing though all the code, let me ask you one thing. You added the new fields to the tables, right? now the form that you are opening is the underlying recordsource the actual table or is it a query containing the amended table? If it is, then you will need to drop then new fields into the query so that they are visible to the form.

CodeMaster::cool:
 
SO sorry Bob! and Thanks DCrake

Hi Bob and D,

Thanks for your replies. I'm SO sorry I didn't post the code correctly. It was my first time posting code and I didn't know how to do it properly.

The changes I made to the forms were to add the 2 fields. I've done simple apps before and had no problem modifying the tables or the forms. The problem was that once the forms were modified the dataentry property (which sets differently depending on which button is selected on the switchboard form) wasn't set properly when coming from the switchboard and if I manually changed the setting in the table, I couldn't commit a record to the Order Head table. Once I got to the subform I got the message that a record couldn't be added to the table referenced in the subform because it hadn't been added to the order head table.

I'm assuming that something happened so that sections of the code were skipped as a result of my changes, but since I only modified the form using the graphic interface I'm not sure why that would have happened.

Hope you can help!

Erika
 
You must log in or register to reply here.

Similar threads

J
Debugger complains about [apparently] non-existant characters
Replies
9
Views
442
The_Doc_Man
The_Doc_Man
D
Clearing data in a form after clicking print report
Replies
10
Views
529
Ken Sheridan
Ken Sheridan
Y
New with a problem
Replies
4
Views
415
Pat Hartman
P
R
How do I save the layout of a subform after modifying it on the main form's on open event?
Replies
12
Views
366
RickSanchez
R
Mike Krailo
Solved Need help with before update code logic?
Replies
12
Views
597
Mike Krailo
Mike Krailo

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom