Bookmarks

  • Thread starter Thread starter BS
  • Start date Start date
B

BS

I want to use a textbox on a form(source = table1) that
when the user enters a value, searches the underlying
table to see if the value exists, if it does then brings
up the record in the form so the user can see it and if
the records does not exist, then enters it into the
table. It uses bookmarks, this code used to work till
now, and has stopped working. I am using Access2000.
Even though a record exists it comes up with the
message "not a valid bookmark". The only thing that has
changed is the number of records in the table has
increased from 100 to 2500.

'FOUND is a global variable that stores the bookmark
'USING THIS CODE ON BEFORE UPDATE of the textbox to check
for the value
Public Function SerialUpdate()

Dim Objrs As ADODB.Recordset
Dim objrs1 As ADODB.Recordset
Dim frm As Form_frmGeneralInfo_New
Dim Serial As ADODB.Field
Dim Serial1 As ADODB.Field
Dim txtSerial As TextBox
Dim txt As String
Dim a

Set objrs1 = New ADODB.Recordset
objrs1.Open "tblGeneralInfo", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic

Set Serial1 = objrs1!SerialNo
Set frm = Forms![frmgeneralinfo_new]
Set txtSerial = frm.SerialNo
txt = txtSerial.Value
Debug.Print "Txt = "; txt


If objrs1.RecordCount <> 0 Then

'MsgBox "The number of records in the current table
are: " & objrs1.RecordCount
objrs1.MoveFirst

Do While Not objrs1.EOF

If (Serial1 = txtSerial.Value) Then

MsgBox "The serial Number you have entered
already exists in the database", vbOKOnly

If Serial1 = txtSerial Then
'MsgBox "TblGeneral", vbOKOnly
Found = objrs1.Bookmark

'MsgBox "Found in before update is: " &
Found
'MsgBox "Found is : " & Found
End If

If Not IsNull(Found) Then
DoCmd.CancelEvent
SendKeys "{ESC 2}{TAB}", False
Exit Function
End If
Else
objrs1.MoveNext
End If

Loop

Else
' MsgBox "There are currently no existing records in
the table", vbOKOnly
'do nothing, do not need to compare because there are
no existing records.
End If


End Function


'USING THIS CODE ON EXIT of the textbox
Public Function Find_OnExit1()


Dim frm1 As Form_frmGeneralInfo_New
Set frm1 = Forms![frmgeneralinfo_new]
If Not IsNull(Found) And Len(Found) <> 0 Then
'frm1.Bookmark = Found
'DoCmd.CancelEvent
'frm1.Bookmark = Found
'MsgBox "Active screen : " &
Screen.ActiveForm.Name

frm1.Refresh
MsgBox "Found is : " & Found
Screen.ActiveForm.Bookmark = Found
Found = Null
End If

End Function
 
BS said:
I want to use a textbox on a form(source = table1) that
when the user enters a value, searches the underlying
table to see if the value exists, if it does then brings
up the record in the form so the user can see it and if
the records does not exist, then enters it into the
table. It uses bookmarks, this code used to work till
now, and has stopped working. I am using Access2000.
Even though a record exists it comes up with the
message "not a valid bookmark". The only thing that has
changed is the number of records in the table has
increased from 100 to 2500.
Click to expand...
Build a combobox and select "find a record on my form.."
This may do all that you need.
If not the code from that combo
Set rs = Me.Recordset.Clone
rs.FindFirst "[Client Name] = '" & Me![Combo3] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

will give you what you need.
The table should be indexed.
There is no need to loop through the records.
You can use the On not in list event to pop messages, etc.
 
Back
Top