Form opening with data instead of blank

[emk]

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.

[CreganTur]

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:thumb

[emk]

Thanks for your help CreganTur, however, I need to be able to review existing records.:dunno

[CreganTur]

Then use some VBA. For your second Form's On Load event use:
DoCmd.GoToRecord,,acLast

I may be off in my placement of commas... if the code doesn't work then lookup the GoToRecord method in Help.

[emk]

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?

[OBP]

You can use
DoCmd.GoToRecord,,acNewRec

[igotgame]

nice

[emk]

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.

[CreganTur]

Hmmm...:think:

What even is kicking off the DoCmd.GoToRecord?

[emk]

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

[emk]

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

[CreganTur]

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:
Private Sub Add_Sale_Button_Click()
DoCmd.GoToRecord , , acNewRec
End Sub

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.

[emk]

'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?

[emk]

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.

[emk]

I think that's where all this is takes place, but I don't understand it at all.

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

[OBP]

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.

[emk]

Here is the code that is in the On Current event procedure for the form.

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

[OBP]

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]".

[emk]

No, this is not my code. I inherited it and its all very confusing to me.

[OBP]

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?

[emk]

After the LastAddedYear = Year (Now())???

[OBP]

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

[emk]

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'.:dunno

[OBP]

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.

[emk]

Here's the code for the add/edit button for the on click event procedure from the main menu.

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

[emk]

Here's the code for the 'OnOpen' event procedure of the Sales Residential form.

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

[OBP]

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

[emk]

This should be what you're looking for.

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

[OBP]

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.

[emk]

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.

[OBP]

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

[emk]

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.

[OBP]

Does it tell you the error and Highlight a line

[emk]

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.

[OBP]

Sorry, change "Form.AllowAdditions = False" to "Form.AllowAdditions = True"

[emk]

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.

[emk]

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.

[OBP]

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.