Expanding a cell in a form (MS Acess 2010)

[gutenberg]

I'm trying to expand the cells in the far right column of this form more to the right in both layout view and design view. However, when I try to save and close out that screen it keeps on asking me if I want to save in which I click yes and the message box keeps coming up over and over again and it will not save at all. Does anyone know how to solve this issue?

[Micron]

Try opening the form in design view, then go to the vb window for that form. If it has no code, you won't see it there, so I'd just add a form open event, even if you will never use it. Save it in the vb window, then go back to access and save in design view, then try to close the form.

[gutenberg]

I did find code there:

Option Compare Database
Option Explicit

Private Sub CmdClosePrint_Enter()
'Check to make sure they selected a Graduation Date , Program type and Drop date are entered
Dim PT As Integer ' program type
'Dim DD As Boolean
Dim TC As Integer ' total count of start dates
Dim CT As Integer ' current invoice timing
Dim EC As Integer ' ecommerse
Dim St As String ' school type
Dim STL As Integer ' length of school type
Dim MDR As Boolean
Dim D1 As Integer ' start date 1
Dim D2 As Integer
Dim D3 As Integer
Dim D4 As Integer
Dim D5 As Integer
Dim D6 As Integer
Dim D7 As Integer
Dim D8 As Integer
Dim GD As Integer
PT = Len(Me!CmbGetPrintType) ' Get the length of the program type (PrintType)
'DD = IsDate(Me![1Drop]) ' Make sure there is a Date in Drop1 field
St = Me!CSchType ' Get the School Type
STL = Len(Me!CSchType) ' Get length of School Type
D1 = Me!SC1 ' check Start 1
D2 = Me!SC2 ' check Start 2
D3 = Me!SC3 ' check Start 3
D4 = Me!SC4 ' check Start 4
D5 = Me!SC5 ' check Start 5
D6 = Me!SC6 ' check Start 6
D7 = Me!SC7 ' check Start 7
D8 = Me!SC8 ' check Start 8
EC = Me!eCommerce
CT = Me!InvoiceTiming ' check the number of invoices to be done
GD = Me!GD1

MDR = True

If St = "Trn" Then ' Set the number of invoices to be issued for TRN it must be 5
CT = 5
Else
CT = Me!InvoiceTiming
End If
Select Case CT 'Check all dates needed for are there
Case 1
If D1 = 1 Then
TC = 1
Else
TC = 0
End If
Case 2
If D1 = 1 And D2 = 1 Then
TC = 2
Else
TC = 0
End If
Case 3
If D1 = 1 And D2 = 1 And D3 = 1 Then
TC = 3
Else
TC = 0
End If
Case 4
If D1 = 1 And D2 = 1 And D3 = 1 And D4 = 1 Then
TC = 4
Else
TC = 0
End If
Case 5
If D1 = 1 And D2 = 1 And D3 = 1 And D4 = 1 And D5 = 1 Then
TC = 5
Else
TC = 0
End If
Case 6
If D1 = 1 And D2 = 1 And D3 = 1 And D4 = 1 And D5 = 1 And D6 = 1 Then
TC = 6
Else
TC = 0
End If
Case 7
If D1 = 1 And D2 = 1 And D3 = 1 And D4 = 1 And D5 = 1 And D6 = 1 And D7 = 1 Then
TC = 7
Else
TC = 0
End If
Case 8
If D1 = 1 And D2 = 1 And D3 = 1 And D4 = 1 And D5 = 1 And D6 = 1 And D7 = 1 And D8 = 1 Then
TC = 8
Else
TC = 0
End If
End Select
If PT = 0 Then
MsgBox "You forgot to enter the Program Type" 'check for print type
Me!CmbGetPrintType.SetFocus
'Cancel = True
ElseIf GD = 0 Then
MsgBox "You need to enter the Graduation date" 'check for graduation date
Me![GraduationDate].SetFocus
'Cancel = True
ElseIf TC <> CT Then
MsgBox "You are missing a Start Date somewhere" 'check for missing start date
Me![1Start].SetFocus
'Cancel = True
End If
End Sub
Private Sub Ctl1Start_AfterUpdate()
Dim Byr As Integer
Byr = Year(Me![1Start])
Me!BeginningClassYear = Byr
End Sub

Private Sub GraduationDate_AfterUpdate()
Dim Gyr As Integer
Gyr = Year(Me![GraduationDate])
Me!GradClassYear = Gyr
End Sub

[gutenberg]

Here's what the form looks like:

[gutenberg]

How do you add an add a "form open event"?

[Micron]

I suggested that if you have no form code, which you just posted. I don't see anything there that should cause your issue.
If you want to try it anyway, click on the event row of the property sheet (I'd use On Open) and click on the button with the elipses (...).
If that doesn't work, an alternative is to create a fresh db and import all objects into it. Perhaps at one time, you changed the name of your form or one or more of its objects. Many developers will not use the 'Use Autocorrect Name" feature in Access because it can cause problems like this. There is a hidden system table that tracks when you change object names and tries to propagate those changes. Doesn't always work as intended.

[gutenberg]

Can the "Use Autocorrect Name" feature be changed and solve the issue?

[Micron]

If that is the issue, then that won't help. I would take you 2 or 3 minutes to import as suggested
I wouldn't fool around with trying to fix it if the code suggestion didn't work.

[gutenberg]

I didn't create this database so I do not want to delete forms/tables/queries and mess with it to great extent.

I think this might be a solution to my problem.

From what I have been able to read the "Do you want to save" message is a bug with the Navigation Forms that have datasheets as subforms. I've tried numerous attempts at a solution over the past week with turning on an off warnings and different times and trying to modify the filters with vba. I ended up realizing today that I needed to modify the filter and order by on the on load event of the datasheet subform. This is what I've done using what saberman had posted with a few additions. All of my form On Load events have the warnings set to True

Code:
Private Sub Form_Load()
DoCmd.SetWarnings True
End SubIn addition to setting warnings to true on the On Load events on the datasheet subforms I've also set the default filter and order by parameters via VBA so my On Load events for these forms would look like this.

Code:
Private Sub Form_Load()
DoCmd.SetWarnings True
Me.OrderBy = ""
Me.OrderByOn = True
Me.Filter = ""
Me.FilterOn = False
End SubAll of the On Enter events of the Navigation Form tabs have the warnings set to False

Code:
Private Sub Navigation_Service_Main_Enter()
DoCmd.SetWarnings False
End SubMy idea with this is that since I've turned off the warnings when a tab is clicked any changes that a user has made via a filter or sort will be saved without the "Do you want to save" prompt. I need to set the filter and order by parameters back to what I want basically undoing what I couldn't prevent being saved. Since my application is designed to be used at runtime by multiple people I needed to have it open consistently every time. So far it seems to work!

However can someone please provide me with the steps of how to do this and where to go to accomplish this?

So far I think you go to:



Do you choose the code builder option?

Does anyone know where to go concerning the navigation forms they are talking about? Are they talking about the navigation pane?

[Micron]

I didn't create this database so I do not want to delete forms/tables/queries and mess with it to great extent.

I didn't suggest you delete anything. Did someone else suggest that?

So this is a navigation form? Looking at your posted picture, I concluded it was 3 forms using tabbed views.
I don't understand what you're asking for now. You say you've implemented code for these events,

All of my form On Load events have the warnings set to True

but you're showing the event creator dialog and asking how to set the filters in case someone applies a filter and saves the form that way? If I understand you, it doesn't matter what filter anyone applies to a form by utilizing the property sheet or by using the form normally if you're going to set those attributes in a form open or load event. The event code will override the manual manipulations.

Or have you just posted an entire clip from some web page that wrote all that as a solution and now you're asking how to implement it? If so, you really should make that clear by including the link to that page as well as either using quote tags or expressly saying so. Just coloring the text doesn't tell us much.
In that case, you'd use the code builder and past everything in between the Sub/End Sub (or Function/End Function for functions) so that those lines are not duplicated. Obviously, the names of things on or about your form would have to be the same as what you're pasting.

[gutenberg]

Sorry about the confusion. Yes, I copied and pasted the blue part from another website. I have not done anything with it yet. So would it be possible to go to visual basic and copy and paste DoCmd.SetWarnings False? In order to do that would I go insert procedure copy an paste it like this Private Sub Form_Load()
DoCmd.SetWarnings True
Me.OrderBy = ""
Me.OrderByOn = True
Me.Filter = ""
Me.FilterOn = False
End Sub

Here is my vba code:

Option Compare Database

Private Sub CmdRptPdf_Click()
Dim StDocName As String
Dim StOutputName As String
'Dim StFaxDoc As String
'Dim StFaxOutputName As String
Dim StDelDoc As String
Dim StDelOutputName As String
Dim SteCommerce As String
Dim StMacroName As String
Dim StSaved As Boolean
StSaved = Me.DidISave
If StSaved = False Then ' If Order was not previously saved then print and save
SteCommerce = [Forms]![Frm_ProgramSelect]![MyeCommerce]
StDocName = "Purchase Quote/Order"
StOutputName = [Forms]![Frm_ProgramSelect]![MyRptFileName]
'StFaxDoc = "FaxOrder"
'StFaxOutputName = [Forms]![Frm_ProgramSelect]![MyFaxFileName]
StDelDoc = "DeliveryOrder"
StDelOutputName = [Forms]![Frm_ProgramSelect]![MyDelFileName]
StMacroName = "Mcr_SaveData"

If SteCommerce = 0 Then
DoCmd.OutputTo acOutputReport, StDocName, acFormatPDF, StOutputName, True
'DoCmd.OutputTo acOutputReport, StFaxDoc, acFormatPDF, StFaxOutputName, True
DoCmd.OutputTo acOutputReport, StDelDoc, acFormatPDF, StDelOutputName, True
DoCmd.RunMacro StMacroName
Else

DoCmd.OutputTo acOutputReport, StDocName, acFormatPDF, StOutputName, True

DoCmd.OutputTo acOutputReport, StDelDoc, acFormatPDF, StDelOutputName, True

DoCmd.RunMacro StMacroName

End If
Else 'if the Order was already saved to the archive warn the user and end without printing or saving again.
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "This Order has Already been saved. Reload from archive or reuse as new" ' Define message.
Style = vbOK + vbCritical + vbDefaultButton1 ' Define buttons.
Title = "Already Saved Message" ' Define title.
Help = "" ' Define Help file.
Ctxt = 0 ' Define topic

' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbOK Then ' User chose OK.
End If
End If

Exit_CmdRptPdf_Click:
Exit Sub
Err_CmdRptPdf_Click:
MsgBox Err.Description
Resume Exit_CmdRptPdf_Click
End Sub
Private Sub CmdSumToPdf_Click()
Dim StDocName As String
Dim StOutputName As String
Dim StMcrName As String
StDocName = "Client Purchase Quote Summary1E"
StOutputName = [Forms]![Frm_ProgramSelect]![MySumFileName]
StMcrName = "Mcr_StartSummaryPdf"

DoCmd.RunMacro StMcrName, 0
DoCmd.OutputTo acOutputReport, StDocName, acFormatPDF, StOutputName, True

End Sub

[Micron]

First, don't just repeat a whole block of text from a post if it doesn't add any clarification.
Second, when someone suggests you provide a link to the quoted code, please do so. I think I found where you got this from and will comment on it at the end.
Third, use code tags, at least when there is a significant amount of code involved.
Fourth, separate your comments from code or quotes so we can tell where each begins and ends.

In that case, you'd use the code builder and paste everything in between the Sub/End Sub or Function/End Function for functions) so that those lines are not duplicated. Obviously, the names of things on or about your form would have to be the same as what you're pasting.

For this object and this event you'd choose the code builder from that object's property sheet line (as per your picture) and paste in these green lines only

Private Sub Form_Load() 
DoCmd.SetWarnings True
Me.OrderBy = ""
Me.OrderByOn = True
Me.Filter = ""
Me.FilterOn = False
End Sub

otherwise you will end up with this

Private Sub Form_Load() 
Private Sub Form_Load()
DoCmd.SetWarnings True
Me.OrderBy = ""
Me.OrderByOn = True
Me.Filter = ""
Me.FilterOn = False
End Sub
End Sub

and will cause an error. So now the aforementioned comment:

If you already have code in these events or if the matching event you want to create already has "embedded macro" listed there be careful or you will mess it up. Not to be condescending, but I would definitely monkey around with a copy of your database, for I suspect you know nothing of what you are about to alter. Reason is, it appears you copied code which applies to someone else's report when you are trying to affect a form, and are asking if you should just paste it in somewhere. The thread I looked at also states that the modifications you seem to need where applied to subform data sheets as well as regular forms thusly:
SUBFORMS

Private Sub Form_Load()
   DoCmd.SetWarnings True
   Me.OrderBy = ""
   Me.OrderByOn = True
   Me.Filter = ""
   Me.FilterOn = False
End Sub

NAVIGATION FORMS

Private Sub Navigation_Service_Main_Enter()
    DoCmd.SetWarnings False
End Sub

This can be found here: https://social.msdn.microsoft.com/Fo...orum=accessdev

In my maybe not so valuable opinion, my solution would be to NOT use navigation forms. Really, the more MS seems to make database creation require less and less knowledge about it, the worse the confusion seem to be getting.