Consulting

Page 2 of 2 FirstFirst 1 2
Results 21 to 38 of 38

Thread: Form opening with data instead of blank

  1. #21
    VBAX Regular
    Joined
    Mar 2009
    Posts
    29
    Location
    After the LastAddedYear = Year (Now())???

  2. #22
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Yes, that section appears to be where "Default Field Values" are entered.

    this section

    If Not (IsNull(Me.OpenArgs)) Or Me.RecordsetClone.EOF Then
    ' use defaults
    LastAddedYear = Year(Now())
    LastAddedPage = 0
    LastAddedLine = 0
    LastAddedCity = ""
    LastAddedSourceDocumentType = "S"
    End If

  3. #23
    VBAX Regular
    Joined
    Mar 2009
    Posts
    29
    Location
    That still hasn't taken care of it. It is still opening the forms with data thats already in the database. However, it always opens at a record that has a blank 'Source Document Year'.

  4. #24
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Then the VBA code that you already have is not working.

    Let's go back to the beginning, you said "I select the county I want, then click on add/edit. That then opens a form which puts that data into that county database"
    What is the VBA code that the "add/edit" button has to open the form?
    Are you sure that it should open on a Blank Record, as the "Edit" implies that if the county is already in the table you will open to that Record for editting.

  5. #25
    VBAX Regular
    Joined
    Mar 2009
    Posts
    29
    Location
    Here's the code for the add/edit button for the on click event procedure from the main menu.

    [VBA]Private Sub Res_AddEdit_Button_Click()
    On Error GoTo Err_Details_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "Sales Residential"
    DoCmd.OpenForm stDocName
    Exit_Details_Click:
    Exit Sub
    Err_Details_Click:
    If Err.Number = 2501 Then Resume Next
    MsgBox Err.Description
    Resume Exit_Details_Click
    End Sub[/VBA]

  6. #26
    VBAX Regular
    Joined
    Mar 2009
    Posts
    29
    Location
    Here's the code for the 'OnOpen' event procedure of the Sales Residential form.

    [VBA]Private Sub Form_Open(Cancel As Integer)
    Dim MainMenuOpen As Boolean
    Dim StPath As String
    Dim DB As Database
    Dim RS As Recordset
    ' Determine the path that this application is running in, and reset default to that path
    StPath = WhatisDBPath()
    ChDir StPath
    ChDrive StPath
    MainMenuOpen = IsLoaded("Main Menu")
    If MainMenuOpen = False Then
    ' This program is being run from the Forms tab of the Database Properties
    MsgBox ("Please run this program from the main menu")
    Me.County_Mask = "CCCCCCCCCCCCCCCCCCCCCCCCCCCCCC"
    Me.Label_Header_2.Caption = "For Development Purposes Only"
    Me.County_ID = "000"
    Me.Target_County = "000"
    Me.RecordSource = "Select * From [Residential Sale] Order By [Source Document Year], [Source Document Page], [Source Document Line]"
    Else
    ' This program is being run from [Main Menu] or from a program opened by [Main Menu]
    Me.Label_Header_2.Caption = Forms![Main Menu]![County Name] & " County"
    Me.County_Mask = Forms![Main Menu]![Parcel Number Mask]
    Me.County_ID = Forms![Main Menu]![County ID]
    Me.RecordSource = "Select * From [Residential Sale] In 'Sales" & Forms![Main Menu]![County Abbreviation] & ".Mdb' Order By [Source Document Year], [Source Document Page], [Source Document Line]"
    'Me.RecordSource = "Select * From [Residential Sale " & Forms![Main Menu]![County Abbreviation] & "] Order By [Source Document Year], [Source Document Page], [Source Document Line]"
    End If
    ' Start with a record displayed on the screen
    If IsNull(Me.OpenArgs) Then
    If Not (Me.RecordsetClone.EOF) Then
    ' Find the last record in order of DateTimeEntered
    If MainMenuOpen Then
    Set DB = OpenDatabase("Sales" & Forms![Main Menu]![County Abbreviation])
    Else
    Set DB = CurrentDb
    End If
    Set RS = DB.OpenRecordset("Residential Sale", dbOpenTable)
    RS.Index = "DateTime Entered"
    RS.MoveLast
    Me.RecordsetClone.FindLast ("[Sale ID] = " & RS![Sale ID])
    Me.Bookmark = Me.RecordsetClone.Bookmark
    RS.Close
    DB.Close
    ' Set the variables that will be used when a new record is added
    LastAddedYear = 0 + Me![Source Document Year]
    LastAddedPage = 0 + Me![Source Document Page]
    LastAddedLine = 0 + Me![Source Document Line]
    LastAddedCity = Nz(Me![City])
    LastAddedSourceDocumentType = FindSourceDocumentType()
    End If
    Else
    ' Opened by a program that wants a specific record found
    Me.AllowAdditions = False
    Me.Filter = "[Sale ID] = " & Me.OpenArgs
    Me.FilterOn = True
    Me.NavigationButtons = False
    Me.Add_Sale_Button.Visible = False
    Me![Add Sale Button].Visible = False
    Me![Find Button].Visible = False
    End If
    Me.Site_Type_Combo.SetFocus
    Form.AllowAdditions = False
    End Sub[/VBA]

  7. #27
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    No wonder you are struggling, the Creator of this database is using VBA to control everything on the Form even the Recordset.
    The code that you have displayed is the VBA for when the Form is opened, what I was looking for is the VBA from the Main Menu? where you click Add/Edit

  8. #28
    VBAX Regular
    Joined
    Mar 2009
    Posts
    29
    Location
    This should be what you're looking for.

    [VBA]Private Sub Res_AddEdit_Button_Click()
    On Error GoTo Err_Details_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "Sales Residential"
    DoCmd.OpenForm stDocName
    Exit_Details_Click:
    Exit Sub
    Err_Details_Click:
    If Err.Number = 2501 Then Resume Next
    MsgBox Err.Description
    Resume Exit_Details_Click
    End Sub[/VBA]

  9. #29
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Yes, that is it, it just opens the Form, no Filter Record, no OpenArgs. So everything is controlled by the On Open event code that you posted before in #26.

    In this section
    If MainMenuOpen = False Then
    ' This program is being run from the Forms tab of the Database Properties MsgBox ("Please run this program from the main menu") Me.County_Mask = "CCCCCCCCCCCCCCCCCCCCCCCCCCCCCC" Me.Label_Header_2.Caption = "For Development Purposes Only"
    Me.County_ID = "000"
    Me.Target_County = "000"
    Me.RecordSource = "Select * From [Residential Sale] Order By [Source Document Year], [Source Document Page], [Source Document Line]"
    Else
    ' This program is being run from [Main Menu] or from a program opened by [Main Menu]
    Me.Label_Header_2.Caption = Forms![Main Menu]![County Name] & " County"
    Me.County_Mask = Forms![Main Menu]![Parcel Number Mask] Me.County_ID = Forms![Main Menu]![County ID]
    Me.RecordSource = "Select * From [Residential Sale] In 'Sales" & Forms![Main Menu]![County Abbreviation] & ".Mdb' Order By [Source Document Year], [Source Document Page], [Source Document Line]" 'Me.RecordSource = "Select * From [Residential Sale " & Forms![Main Menu]![County Abbreviation] & "] Order By [Source Document Year], [Source Document Page], [Source Document Line]"
    End If

    Try putting a ' before this line of code
    Me.RecordSource = "Select * From [Residential Sale] In 'Sales" & Forms![Main Menu]![County Abbreviation] & ".Mdb' Order By [Source Document Year], [Source Document Page], [Source Document Line]"

    To see if it gives you a blank record, it might even give you a blank form.

  10. #30
    VBAX Regular
    Joined
    Mar 2009
    Posts
    29
    Location
    I think we're on the right track, but now the county databases are opening at record 1 of 1. I still need all the records that are currently in the database.

  11. #31
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    That is OK, I just wanted to make sure that it was that line of code that was controlling the recordset.
    So un rem the line so that you get data and put this between "Form.AllowAdditions = False" and "End Sub"

    DoCmd.GoToRecord , , acNewRec

  12. #32
    VBAX Regular
    Joined
    Mar 2009
    Posts
    29
    Location
    I tried that and it doesn't run at all. It doesn't like it. Sorry, I shouldn't say it does't run at all, I mean it's getting a debug error.
    Last edited by emk; 03-18-2009 at 09:19 AM.

  13. #33
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Does it tell you the error and Highlight a line

  14. #34
    VBAX Regular
    Joined
    Mar 2009
    Posts
    29
    Location
    Runtime error 2105. You can't go to the specified record. When I click on debug, it takes me to the code I just inserted.

  15. #35
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Sorry, change "Form.AllowAdditions = False" to "Form.AllowAdditions = True"

  16. #36
    VBAX Regular
    Joined
    Mar 2009
    Posts
    29
    Location
    Okay, that works now. It is opening a blank record and is showing the other records. Now, the Source Document Year is still blank. I need it to display 2009. Then, the Source Document Line needs to open with 1 and then that needs to count up by one for each new record added.

    Thank you so much for what you have already done. I really appreciate your help.

  17. #37
    VBAX Regular
    Joined
    Mar 2009
    Posts
    29
    Location
    I just tried adding a new record and once I clicked on 'Add Sale' it didn't show that it added the record. The record count didn't increase and the form didn't empty out. The information I entered just stayed there. However, when I open up that specific county database it has that new record that I added.

  18. #38
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    emk, the problem is that the "Add Sale" Button has VBA code and we don't know what it is doing, it may not move to another new record, just save the data.
    The fact that everything is being controlled by VBA is going to make changes very hard to impliment, because we do not know what affect they will have on the VBA code eslewhere.
    But at least we made a little progress.
    After DoCmd.GoToRecord , , acNewRec copy in
    me.[Source Document Year] = Year(Now())
    Me.[Source Document Line] = 1
    and see if it puts in 2009 and 1.

    I suppose that you do realise that it will do this every time that you open the form?
    The creator was controlling the events by looking at the last record stored in the table, which is what this should be doing. If the last record already has 2009 in the Source Document Year then Increment the Source Document Line. So at the moment if you just want to Edit or View an Old Record, it will still open on a new one and create a new record.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •