Goto Record based on text box entry

[Fonzie]

On my main form, I have a textbox which shows the workorder number for that record. The workorder number is an autonumber and the primary key in the underlying table. This number is generated every time a new workorder is created. Is there any way that if the user enters a different number in the workordernumber textbox, it will go to that corresponding record, and not change the value of the workorder number for the current record. Hope this makes some sense. I thought I could put something in the afterupdate, but can't figure out what.

 

[Maquis]

Not sure if this is the most efficient method or not, but here is how I do it. I put this code in the lost focus event of a Social Security field on a form. When entering new people into the database it first checks for duplicate SSN's already entered. If they exist then the form goes to the entered record. You can tweak the code for your purposes.

Dim TableName As String
Dim rs As Recordset
Set rs = Me.RecordsetClone

'First check to make sure the Social isn't already in table
TableName = &quot;<your table>&quot;
Criteria = &quot;[SSN] = &quot; & dQuote & Me![Members SSN] & dQuote
NumRecords = DCount(&quot;*&quot;, TableName, Criteria)
If NumRecords > 0 Then
Message = &quot;Error! That SSN already exists in the table, Cannot Add!&quot;
MsgBox Message, vbCritical
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
rs.FindFirst Criteria
Me.Bookmark = rs.Bookmark
rs.Close
If Not (rs) Is Nothing Then Set rs = Nothing
Exit Sub
End If Maq B-)
<insert witty signature here>

 

[Fonzie]

Thanks for the reply.

I was thinking more along the lines of using the gotorecord command in the code. Where I am running into trouble is if records are deleted, I am not sure how to point to the correct record using the workorder number. For example, if I create records 1,2,3,4, and 5, and then delete the first one, I only have 4 records in my table, but they have corresponding workorder numbers of 2,3,4, and five. If I use the gotorecord command, how could I enter the number 2 as the workorder I want to goto now, and have it goto the correct record, which would be the first record (since I deleted workorder number 1)?

 

[Maquis]

Hmmm, I have never used the gotorecord command myself, so I'm not too familiar with it, but I don't believe you will be able to use it for the reasons that you mentioned above. Recordsets aren't always sorted by their primary keys, so you can't use that as the parameter in your gotorecord command.

Somehow you are going to have to determine where in the recordset the record you want is. The above code will do that with the rs.findfirst statement.

Like I said, I'm not sure that the above code is the best way to accomplish this task, but it does work. If someone else has a better way, please jump in!

Maq B-)
<insert witty signature here>

 

[Fonzie]

I tried using your code, and for the most part it works fine. But every now and then (can't see any pattern yet), I get an error message: &quot;Update or CancelUpdate without AddNew or Edit&quot;. I don't have a clue what this means. When I choose the debug option, the line me.bookmark = rs.bookmark is highlighted. Did you run accross this, or does anyone have any idea what this is?

 

[Maquis]

That means you tried to undo a change without making any changes. This probably happens when you go into your text box and then try to exit the box without changing any text.

A possible solution would be to set a variable equal to the text box's value in the on enter event and then compare it for changes before running this code.

You could look at the documentation for the on_change event as well, but I've had problems in the past with that event firing too often, so it may or may not be a solution.

Good Luck Maq B-)
<insert witty signature here>

 

[Sameal]

As per you wanting to use the GOTORECORD method of the DoCmd object. I had this same problem with a form of mine where you have a subform that lists all the tooling from a query. You then can click on an item in the list and it loads it into the main form. I accomplished this by having both the main form and the subform working off the same query. This way you can use the AbsolutePosition of the recordset to point to the same record your looking for. I'm not sure your particular situation but maybe this will give you a place to start.