After the LastAddedYear = Year (Now())???
After the LastAddedYear = Year (Now())???
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
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'.
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.
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]
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]
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
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]
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.
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.
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
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.
Does it tell you the error and Highlight a line
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.
Sorry, change "Form.AllowAdditions = False" to "Form.AllowAdditions = True"
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.
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.
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.