Results 1 to 12 of 12
  1. #1
    gutenberg is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2016
    Posts
    31

    Expanding a cell in a form (MS Acess 2010)

    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?



    Click image for larger version. 

Name:	Per Student Quote.JPG 
Views:	19 
Size:	175.1 KB 
ID:	24930

  2. #2
    Micron is offline Very Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    13,369
    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.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    gutenberg is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2016
    Posts
    31
    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

  4. #4
    gutenberg is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2016
    Posts
    31

    Expanding a cell in a form (MS Acess 2010)

    Here's what the form looks like:

    Click image for larger version. 

Name:	Property Sheet - Form.jpg 
Views:	13 
Size:	175.3 KB 
ID:	24955

  5. #5
    gutenberg is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2016
    Posts
    31
    How do you add an add a "form open event"?

  6. #6
    Micron is offline Very Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    13,369
    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.

  7. #7
    gutenberg is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2016
    Posts
    31
    Can the "Use Autocorrect Name" feature be changed and solve the issue?

  8. #8
    Micron is offline Very Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    13,369
    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.

  9. #9
    gutenberg is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2016
    Posts
    31
    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:

    Click image for larger version. 

Name:	On Load Event.jpg 
Views:	8 
Size:	83.6 KB 
ID:	25003

    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?

  10. #10
    Micron is offline Very Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    13,369
    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.

  11. #11
    gutenberg is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2016
    Posts
    31
    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



  12. #12
    Micron is offline Very Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    13,369
    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
    Code:
    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
    Code:
    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
    Code:
    Private Sub Form_Load()
       DoCmd.SetWarnings True
       Me.OrderBy = ""
       Me.OrderByOn = True
       Me.Filter = ""
       Me.FilterOn = False
    End Sub
    NAVIGATION FORMS

    Code:
    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.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. form with expanding/shrinking sub-forms?
    By benjammin in forum Forms
    Replies: 19
    Last Post: 02-02-2015, 02:52 PM
  2. Replies: 1
    Last Post: 03-09-2014, 05:21 AM
  3. Expanding a form's data fields
    By hertfordkc in forum Forms
    Replies: 6
    Last Post: 07-18-2011, 12:33 PM
  4. Expanding Memo Field in Form
    By AKQTS in forum Forms
    Replies: 3
    Last Post: 07-26-2010, 08:51 AM
  5. Can we post Access cell data to Excel cell properties?
    By Zethro in forum Import/Export Data
    Replies: 1
    Last Post: 12-13-2005, 08:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums