UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Go To Certain Record On Opening Form, Access 2010    
 
   
MikeWaring1
post Dec 23 2017, 06:17 AM
Post#1



Posts: 93
Joined: 7-November 12



Hi, I hope someone can help me with my lack of knowledge of coding dunce.gif

I have a form based on a table for entering records of parcels sent by a Tracked postal service.

The underlying table has 3 fields.

The field "Tracking_Number" contains the sequential tracking number, which I batch upload with 500 records at a time.

The next field is "Sales_Record", in which we manually enter the order number of the parcel against the relevan tracking number. Typically we enter 20 to 30 records per day, all sequentially ie using each sequential tracking number.

The 3rd Field is a date field which has an update query attached to a control button to enter today's date to all Sales Records entered on that day.

As you can imagine, the form soon gets a lot of data in it, so what I need is that when I open the form, the cursor is placed in the first empty row in the "Sales records" field. I thought by using GotoNew (record) on the form's OnOpen property would work, but that takes me to the first empty row of the 1st field, which is right at the bottom of the table and nowhere near where we are going to enter data on that day. likewise if I just open the form with no control events being triggered it shows the first record ever entered so we then have to manually scroll down to find the row of the last entered "Sales Record"

I don't think it is relevant, but I have set the tab stops to "No" in all fields except "Sales Record"


We have to keep records for over a year so there are a lot of rows that have data in.


Could someone assist with building code to enter into the OnOpen property to enable me to resolve this? apologies if I haven't used naming conventions..... blush.gif

Kind regards
Mike

Go to the top of the page
 
DanielPineault
post Dec 23 2017, 06:40 AM
Post#2


UtterAccess VIP
Posts: 6,276
Joined: 30-June 11



How about something along the lines of:

CODE
Private Sub Form_Open(Cancel As Integer)
10        On Error GoTo Error_Handler
          Dim db                    As DAO.Database
          Dim rs                    As DAO.Recordset

20        Set db = CurrentDb
30        Set rs = Me.RecordsetClone

40        rs.FindFirst "[Sales_Record] Is Null"
50        If rs.NoMatch = False Then
60            Me.Bookmark = rs.Bookmark
70        End If

Error_Handler_Exit:
80        On Error Resume Next
90        If Not rs Is Nothing Then
100           rs.Close
110           Set rs = Nothing
120       End If
130       If Not db Is Nothing Then Set db = Nothing
140       Exit Sub

Error_Handler:
150       MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
                 "Error Number: " & Err.Number & vbCrLf & _
                 "Error Source: Form_Open" & vbCrLf & _
                 "Error Description: " & Err.Description & _
                 Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
                 , vbOKOnly + vbCritical, "An Error has Occured!"
160       Resume Error_Handler_Exit
End Sub
Go to the top of the page
 
MikeWaring1
post Dec 23 2017, 09:58 AM
Post#3



Posts: 93
Joined: 7-November 12



Hi Daniel, Thanks for the quick response. I copied this into the "On Open" property but when i go to open the form in Form View, i get an error " Compile Error - User Defined Type Not Defined, and the code "Private Sub Form_Open(Cancel As Integer)" is highlighetd in yellow.

Any ideas how to resolve this?

Kind regards
Mike
Go to the top of the page
 
projecttoday
post Dec 23 2017, 11:15 AM
Post#4


UtterAccess VIP
Posts: 10,368
Joined: 10-February 04
From: South Charleston, WV


Not sure about that message because
Dim db As DAO.Database
Dim rs As DAO.Recordset
should be okay. But you can try
Dim db As Object
Dim rs As Object

But I don't think this statement will work for you:
rs.FindFirst "[Sales_Record] Is Null"
You need to search your column with your input value.


Go to the top of the page
 
DanielPineault
post Dec 23 2017, 11:36 AM
Post#5


UtterAccess VIP
Posts: 6,276
Joined: 30-June 11



You created an Event Procedure for your form's On Open event and then pasted the supplied code?

You could always post a sample of your db, removing any sensitive information first, for us to review.
Go to the top of the page
 
MikeWaring1
post Jan 2 2018, 03:21 AM
Post#6



Posts: 93
Joined: 7-November 12



Hi Both, thanks for your responses.

I cahnged the "Dim db" and "Dim rs" lines which has removed the original error message, but now I get the following critical error

QUOTE
An Error has Occured!
---------------------------
The following error has occured

Error Number: 3070

Error Source: Form_Open

Error Description: The Microsoft Access database engine does not recognize 'Sales_Record' as a valid field name or expression.

Line No: 40
---------------------------
OK


Any Ideas how I get over this one?
Go to the top of the page
 
MikeWaring1
post Jan 2 2018, 03:37 AM
Post#7



Posts: 93
Joined: 7-November 12



Hi both, ignore my last message re that Error 3070 - I fixed the issue.

I think I typed the control name incorrectly in my original message blush.gif - when I overtyped the correct name into the code it seemed to work OK......

Thanks again both of you.

Kind regards
Mike
uarulez2.gif
Go to the top of the page
 
projecttoday
post Jan 2 2018, 06:45 AM
Post#8


UtterAccess VIP
Posts: 10,368
Joined: 10-February 04
From: South Charleston, WV


You're welcome. Will you post the corrected code?
Go to the top of the page
 
MikeWaring1
post Jan 6 2018, 08:18 AM
Post#9



Posts: 93
Joined: 7-November 12



Hi, Yes I'll be glad to smile.gif

As a matter of interest that might help me on the road to start learning, understanding and writing code properly, what's the significance of the line numbers to the left? Is it just a means of identifying each part of the procedure? and if an error occurs, do these line numbers appear in the error to make it easy to identify where the error is? I know normally that errors are highlighted in yellow.


CODE
Private Sub Form_Open(Cancel As Integer)
10        On Error GoTo Error_Handler
          Dim db As Object
          Dim rs As Object

20        Set db = CurrentDb
30        Set rs = Me.RecordsetClone

40        rs.FindFirst "[Sales Record 1] Is Null"
50        If rs.NoMatch = False Then
60            Me.Bookmark = rs.Bookmark
70        End If

Error_Handler_Exit:
80        On Error Resume Next
90        If Not rs Is Nothing Then
100           rs.Close
110           Set rs = Nothing
120       End If
130       If Not db Is Nothing Then Set db = Nothing
140       Exit Sub

Error_Handler:
150       MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
                 "Error Number: " & Err.Number & vbCrLf & _
                 "Error Source: Form_Open" & vbCrLf & _
                 "Error Description: " & Err.Description & _
                 Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
                 , vbOKOnly + vbCritical, "An Error has Occured!"
160       Resume Error_Handler_Exit
End Sub


Kindest regards
Mike
Go to the top of the page
 
projecttoday
post Jan 6 2018, 10:43 AM
Post#10


UtterAccess VIP
Posts: 10,368
Joined: 10-February 04
From: South Charleston, WV


I don't use line numbers in Visual Basic for Applications. In the original Basic, line numbers were required. I guess they give it to you now as an option. For example, you can say Goto 100. The modern way is to use descriptive label names instead of numbers (and to avoid Goto's where you can).
Daniel posted this code so maybe he has something to add.
Glad you got it working. I guess I was wrong about the Findfirst statement.
Go to the top of the page
 
DanielPineault
post Jan 6 2018, 11:08 AM
Post#11


UtterAccess VIP
Posts: 6,276
Joined: 30-June 11



The line numbers are completely optional, BUT, and it is a very useful but, by adding them as I did in my example, I can then get my error handler to return the actual line number that raised the error! So no more an error occurred somewhere in procedure xyz. Now you can get an error occurred on line 235 of procedure xyz. For simpler procedure there is little value, but in more complex procedures, this can become invaluable! Add to that the fact that an add-in such as Mz-tools can add/remove line numbers at the click of a button and you'd need to be mad not to employ them.

That's what the line Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) in my error handler is all about. If there are line numbers present then it will report it back, otherwise it returns "". So my error handler can be used in any case without modification.
Go to the top of the page
 
projecttoday
post Jan 6 2018, 12:34 PM
Post#12


UtterAccess VIP
Posts: 10,368
Joined: 10-February 04
From: South Charleston, WV


Useful to know.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th December 2018 - 08:06 PM