Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 38

Thread: Form opening with data instead of blank

  1. #1
    VBAX Regular
    Joined
    Mar 2009
    Posts
    29
    Location

    Form opening with data instead of blank

    I am trying to make some changes to a database I inherited. On the main form, I select the county I want, then click on add/edit. That then opens a form which puts that data into that county database. When I open the form, it doesn't open to a blank form, it opens to a record thats already in the database. For instance in this specfic database, there are 10,430 records. I would like it to open a blank form when add/edit is selected and have that black form be 10,431. I also want to be able to set the year and not let it be changed. Then, if it is the first entry for that year, the Document Line needs to start at one and increase by one each time a new entry is made for that year. I've attached a copy of the code behind the form. Any help is greatly appreciated.

  2. #2
    VBAX Master CreganTur's Avatar
    Joined
    Jan 2008
    Location
    Greensboro, NC
    Posts
    1,676
    Location
    Welcome to the forum- it's always good to see new members.

    Go to the properties for your second form, under the Data Tab, and set Data Entry to True, This will make your Form into a data entry Form. Basically, this means it is always set to New Record mode. All you can do is add records with it. You can't review existing records.

    HTH
    -Randy Shea
    I'm a programmer, but I'm also pro-grammar!
    If your issue is resolved, please use Thread Tools to mark your thread as Solved!

    PODA (Professional Office Developers Association) | Certifiable | MOS: Access 2003


  3. #3
    VBAX Regular
    Joined
    Mar 2009
    Posts
    29
    Location
    Thanks for your help CreganTur, however, I need to be able to review existing records.

  4. #4
    VBAX Master CreganTur's Avatar
    Joined
    Jan 2008
    Location
    Greensboro, NC
    Posts
    1,676
    Location
    Then use some VBA. For your second Form's On Load event use:
    [VBA]DoCmd.GoToRecord,,acLast[/VBA]

    I may be off in my placement of commas... if the code doesn't work then lookup the GoToRecord method in Help.
    -Randy Shea
    I'm a programmer, but I'm also pro-grammar!
    If your issue is resolved, please use Thread Tools to mark your thread as Solved!

    PODA (Professional Office Developers Association) | Certifiable | MOS: Access 2003


  5. #5
    VBAX Regular
    Joined
    Mar 2009
    Posts
    29
    Location

    Getting closer

    Okay, I added the command and it now opens with the data from the very last record (10,430). How do I get it to open with a blank form and the record number being 10,431?

  6. #6
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,090
    Location
    You can use
    DoCmd.GoToRecord,,acNewRec

  7. #7

  8. #8
    VBAX Regular
    Joined
    Mar 2009
    Posts
    29
    Location

    Still struggling

    Thanks for the suggestion. I tried adding 'DoCmd.GoToRecord,,acNewRec' to the code. Now, I get a message saying "Cannot go to specified record". Then when I click on OK, it opens a blank form, but its showing its record 2569 of 10430??? Sorry for my stupidity in this matter, but I have very little knowledge in VBA. Thanks for any help.

  9. #9
    VBAX Master CreganTur's Avatar
    Joined
    Jan 2008
    Location
    Greensboro, NC
    Posts
    1,676
    Location
    Hmmm...

    What even is kicking off the DoCmd.GoToRecord?
    -Randy Shea
    I'm a programmer, but I'm also pro-grammar!
    If your issue is resolved, please use Thread Tools to mark your thread as Solved!

    PODA (Professional Office Developers Association) | Certifiable | MOS: Access 2003


  10. #10
    VBAX Regular
    Joined
    Mar 2009
    Posts
    29
    Location
    Here's the snippet of code where I added the DoCmd. I'm at a lose. I added it here and the database for the county I was testing with worked fine, but now when I open another county database, it opens the form at the last record entered.

    Private Sub Add_Sale_Button_Click()
    On Error GoTo Err_Add_Sale_Button_Click
    Form.AllowAdditions = True
    DoCmd.GoToRecord , , acNewRec
    Form.AllowAdditions = False
    Exit_Add_Sale_Button_Click:
    Exit Sub
    Err_Add_Sale_Button_Click:
    MsgBox Err.Description
    Resume Exit_Add_Sale_Button_Click
    End Sub

  11. #11
    VBAX Regular
    Joined
    Mar 2009
    Posts
    29
    Location
    I would like the form to open with a blank form and in the 'Source Document Year' have it populated with 2009 and make that uneditable. I would then like to have a 'Source Document Line start with 1. That also needs to be uneditable. I just can't fully grasp the code to understand how to do that. Following is the code that specifies the year

    Private Sub Source_Document_Year_AfterUpdate()
    ' Run the SaveRecord command, because the database may not yet have saved the new year
    DoCmd.RunCommand acCmdSaveRecord
    LastAddedYear = Me![Source Document Year]
    SourceDocumentChanges
    End Sub
    Sub SourceDocumentChanges()
    ' This is only run when adding a new record because these controls are not accessible otherwise
    Dim Criteria As String
    ' Find the last record added for this document type
    Criteria = "[Source Document Year] = '" & LastAddedYear & "'"
    If Me![Source Document Type Combo] = "N" Then
    Me.RecordsetClone.FindLast (Criteria & " And [Source Document Page] = 0")
    LastAddedPage = 0
    If Me.RecordsetClone.NoMatch Then LastAddedLine = 1 Else LastAddedLine = 1 + Me.RecordsetClone![Source Document Line]
    Else
    Me.RecordsetClone.FindLast (Criteria & " And [Sale ID] <> " & Me![Sale ID])
    If Me.RecordsetClone.NoMatch Then LastAddedPage = 1 Else LastAddedPage = 1 + Me.RecordsetClone![Source Document Page]
    If Me![Source Document Type Combo] = "S" Then LastAddedLine = 0 Else LastAddedLine = 1
    End If
    ' Make sure that information on the form has been saved
    DoCmd.RunCommand acCmdSaveRecord
    ' Update the record
    Me.RecordsetClone.Bookmark = Me.Bookmark
    Me.RecordsetClone.Edit
    Me.RecordsetClone![Source Document Page] = LastAddedPage
    Me.RecordsetClone![Source Document Line] = LastAddedLine
    Me.RecordsetClone.Update
    ' Display the information on screen
    Me.Bookmark = Me.RecordsetClone.Bookmark
    End Sub

  12. #12
    VBAX Master CreganTur's Avatar
    Joined
    Jan 2008
    Location
    Greensboro, NC
    Posts
    1,676
    Location
    Here's the snippet of code where I added the DoCmd.
    The code you're using looks like Access pre-generated bloat code. It has a lot of stuff in it you do not need.

    First, the reason it is not workign is because you turn additions on, go to the next record, and then immediately turn additions off. Well, if you turn additions off, then Access will not allow you to be on the New record, so it takes you to the last record.

    All you need is this, just be sure that your Form properties are set to allow additions:
    [vba]Private Sub Add_Sale_Button_Click()
    DoCmd.GoToRecord , , acNewRec
    End Sub[/vba]

    Also, whenever you post code, please wrap it in VBA tags- click the green VBA button. This will format it according to VBIDE and make it much easier to read.

    I would like the form to open with a blank form and in the 'Source Document Year' have it populated with 2009 and make that uneditable.
    Is 'Source Document Year' bound to a table? If it is, then go to the field in the table (in design view) and set a default value for it.
    -Randy Shea
    I'm a programmer, but I'm also pro-grammar!
    If your issue is resolved, please use Thread Tools to mark your thread as Solved!

    PODA (Professional Office Developers Association) | Certifiable | MOS: Access 2003


  13. #13
    VBAX Regular
    Joined
    Mar 2009
    Posts
    29
    Location
    'Source Document Year' is bound to a table. I set the default value to 2009 however, it still is opening with the wrong year. Is there code that is overriding the default?

  14. #14
    VBAX Regular
    Joined
    Mar 2009
    Posts
    29
    Location
    Source Document Year should be changed to 2009. Source Document Line would then start with 1 and automaticall change to 2 for the next sale entered and so on and so on for the Source Document Year 2009.

  15. #15
    VBAX Regular
    Joined
    Mar 2009
    Posts
    29
    Location
    I think that's where all this is takes place, but I don't understand it at all.

    [VBA]Sub SourceDocumentChanges()
    ' This is only run when adding a new record because these controls are not accessible otherwise
    Dim Criteria As String
    ' Find the last record added for this document type
    Criteria = "[Source Document Year] = '" & LastAddedYear & "'"
    If Me![Source Document Type Combo] = "N" Then
    Me.RecordsetClone.FindLast (Criteria & " And [Source Document Page] = 0")
    LastAddedPage = 0
    If Me.RecordsetClone.NoMatch Then LastAddedLine = 1 Else LastAddedLine = 1 + Me.RecordsetClone![Source Document Line]
    Else
    Me.RecordsetClone.FindLast (Criteria & " And [Sale ID] <> " & Me![Sale ID])
    If Me.RecordsetClone.NoMatch Then LastAddedPage = 1 Else LastAddedPage = 1 + Me.RecordsetClone![Source Document Page]
    If Me![Source Document Type Combo] = "S" Then LastAddedLine = 0 Else LastAddedLine = 1
    End If
    ' Make sure that information on the form has been saved
    DoCmd.RunCommand acCmdSaveRecord
    ' Update the record
    Me.RecordsetClone.Bookmark = Me.Bookmark
    Me.RecordsetClone.Edit
    Me.RecordsetClone![Source Document Page] = LastAddedPage
    Me.RecordsetClone![Source Document Line] = LastAddedLine
    Me.RecordsetClone.Update
    ' Display the information on screen
    Me.Bookmark = Me.RecordsetClone.Bookmark
    End Sub[/VBA]

  16. #16
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,090
    Location
    I am a bit worried about this code as it is in the SourceDocumentChanges() procedure and I would have thought you would want it in the Form's
    "On Current" event procedure or in the First Relevant Field's "After Update" event procedure.
    It should start with

    if me.newrecord then

    to ensure that this is a new record.
    you can then use

    me.[Source Document Year] = DatePart("yyyy", Date())

    that sets the Source Document Year to the Year of the current date.

    But setting "Source Document Line" to 1 should only be done for the First Record of the Year, subsequent records should be incremented which is what the code above appears to be doing.

  17. #17
    VBAX Regular
    Joined
    Mar 2009
    Posts
    29
    Location
    Here is the code that is in the On Current event procedure for the form.

    [VBA]Private Sub Form_Current()
    Dim RS As Recordset
    Dim DB As Database
    Dim TableName As String
    'Dim RecordStatusX As Boolean
    If Nz(Me![Record Status]) = "X" Then
    Me.AllowEdits = False
    Me![Delete Sale Button].ForeColor = 255
    Me![Deleted Sale].Visible = True
    Me![Delete Sale Button].Caption = "Un&delete"
    Else
    Me.AllowEdits = True
    Me![Delete Sale Button].ForeColor = 0
    Me![Deleted Sale].Visible = False
    Me![Delete Sale Button].Caption = "&Delete"
    End If
    'RecordStatusX = (Nz(Me![Record Status]) = "X")
    'Me.AllowEdits = Not RecordStatusX
    'Me![Deleted Sale].Visible = RecordStatusX
    ' If not a new record: set the Source Document Type, flag as old record, disable combo box and exit
    If Not IsNewRecord() Then
    Me![Source Document Type Combo] = FindSourceDocumentType()
    ThisRecordIsNew = False
    SetSourceDocumentAttributes
    Me![Source Document Type Combo].Enabled = False
    Me![Source Document Year].Enabled = False
    Me![Source Document Page].Enabled = False
    Me![Source Document Line].Enabled = False
    Exit Sub
    End If
    ' Flag as a new record and enable the combo box
    ThisRecordIsNew = True
    Me![Source Document Type Combo].Enabled = True
    Me![Source Document Year].Enabled = False
    Me![Source Document Page].Enabled = False
    Me![Source Document Line].Enabled = False

    If Not (LastAddedDoneBefore) Then
    ' This is done here rather than in OnOpen because OnOpen changes the form's data file,
    ' which causes this routine to be run before OnOpen finishes up.
    LastAddedDoneBefore = True
    If Not (IsNull(Me.OpenArgs)) Or Me.RecordsetClone.EOF Then
    ' use defaults
    LastAddedYear = Year(Now())
    LastAddedPage = 0
    LastAddedLine = 0
    LastAddedCity = ""
    LastAddedSourceDocumentType = "S"
    End If
    End If
    ' Increment values of LastAddedPage or LastAddedLine
    If LastAddedLine <= 0 Then
    ' Line counter not being used, so increment the page counter
    LastAddedPage = LastAddedPage + 1
    Else
    ' Line counter is being used, so increment it and don't change the page counter
    LastAddedLine = LastAddedLine + 1
    End If
    ' Create the new record
    Me.RecordsetClone.AddNew
    Me.RecordsetClone![County Link] = Me.County_ID
    Me.RecordsetClone![Record Status] = "N"
    Me.RecordsetClone![Converted from MktStdy] = False
    Me.RecordsetClone![City] = IIf(LastAddedCity = "", " ", LastAddedCity)
    Me.RecordsetClone![Source Document Year] = LastAddedYear
    Me.RecordsetClone![Source Document Page] = LastAddedPage
    Me.RecordsetClone![Source Document Line] = LastAddedLine
    Me.RecordsetClone.Update
    ' Display the new record on the screen
    Me.Bookmark = Me.RecordsetClone.LastModified
    Me![Source Document Type Combo] = LastAddedSourceDocumentType
    SetSourceDocumentAttributes
    Me.Site_Type_Combo.SetFocus
    End Sub
    [/VBA]

  18. #18
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,090
    Location
    I take it that this is not your code, as this line

    LastAddedYear = Year(Now())

    does the same as you want for the "[Source Document Year]".

  19. #19
    VBAX Regular
    Joined
    Mar 2009
    Posts
    29
    Location
    No, this is not my code. I inherited it and its all very confusing to me.

  20. #20
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,090
    Location
    Yes I can see it would be.
    After the line I have highlighted above can you add
    me.[Source Document Year] = Year(Now())

    and see if it does what you want?

Posting Permissions

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