How to bookmark a record (1 Viewer)

sbrown106

Member
Local time
Today, 22:37
Joined
Feb 6, 2021
Messages
77
Hi, I have this code that works and tells me lets me know if a number exists in a field. So txtFindNumber say 1234.001
exists in the field 'fldNumber' - but I need to bookmark the once its found so the user can edit it - how would I do this please?

Ive had a look at the doc.microsoft page and it suggests I need a 'with rs... end with' - but a bit confused how to use this.
This is my code below

Private Sub cmdFindNumber_Click()

Dim myval As Double
myval = Me!txtFindNumber
Dim rs As DAO.Recordset
Dim my_value As Variant
Dim SQL As String


Set rs = CurrentDb.OpenRecordset( _
"SELECT fldNumber FROM tbl_Data_LOI_Instructions WHERE fldNumber = " & myval & "", dbOpenSnapshot, dbReadOnly)

With Me.RecordsetClone
Do Until rs.EOF
If myval = rs![fldNumber] Then
MsgBox "Number Found", vbOKOnly
Exit Sub
End If
rs.MoveNext
Loop
MsgBox "Number Not Found", vbOKOnly
rs.Close
Set rs = Nothing
End With

End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:37
Joined
Oct 29, 2018
Messages
21,358
Hi. Try to add an unbound Combobox using the Wizard and select the third option. Then, examine the code it produces as an example. If it creates a macro, you can convert it to VBA.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:37
Joined
Feb 28, 2001
Messages
27,001
The simplest answer is shown in this link.


EDIT: theDBguy also gave you good advice. Two different ways to get to the right answer.
 

sbrown106

Member
Local time
Today, 22:37
Joined
Feb 6, 2021
Messages
77
Hi. Try to add an unbound Combobox using the Wizard and select the third option. Then, examine the code it produces as an example. If it creates a macro, you can convert it to VBA.
Sorry I'm a bit confused with the syntax the macro gives me
="[fldCaseNumber] = " & Str(Nz([Screen].[ActiveControl],0))

so this line becomes

"SELECT fldCaseNumber FROM tbl_Data_LOI_Instructions WHERE fldCaseNumber = & Str(Nz([Screen].[ActiveControl],0))", dbOpenSnapshot, dbReadOnly)

but I'm getting syntax error when it tries to run
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:37
Joined
Feb 28, 2001
Messages
27,001
Wow! Just for clarification, which version of Access are you running? Because I have NEVER seen an Access wizard return ANYTHING based on [Screen].[ActiveControl] before. I'm not going to swear that SQL can even SEE that class of GUI properties and objects. The wizard, at worst, SHOULD have returned something based on Me!controlname (and a bit more syntax after that).
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:37
Joined
Oct 29, 2018
Messages
21,358
Sorry I'm a bit confused with the syntax the macro gives me
="[fldCaseNumber] = " & Str(Nz([Screen].[ActiveControl],0))

so this line becomes

"SELECT fldCaseNumber FROM tbl_Data_LOI_Instructions WHERE fldCaseNumber = & Str(Nz([Screen].[ActiveControl],0))", dbOpenSnapshot, dbReadOnly)

but I'm getting syntax error when it tries to run
No, that macro syntax would probably have become like this in SQL (if, for example, the user selected the value 12345 from the Combobox):

Code:
"SELECT fldCaseNumber FROM tbl_Data_LOI_Instructions WHERE fldCaseNumber = 12345", dbOpenSnapshot, dbReadOnly
 

sbrown106

Member
Local time
Today, 22:37
Joined
Feb 6, 2021
Messages
77
Wow! Just for clarification, which version of Access are you running? Because I have NEVER seen an Access wizard return ANYTHING based on [Screen].[ActiveControl] before. I'm not going to swear that SQL can even SEE that class of GUI properties and objects. The wizard, at worst, SHOULD have returned something based on Me!controlname (and a bit more syntax after that).
Hi The_Doc_Man - its the Access version with Office365
 

moke123

AWF VIP
Local time
Today, 18:37
Joined
Jan 11, 2013
Messages
3,852
Wow! Just for clarification, which version of Access are you running? Because I have NEVER seen an Access wizard return ANYTHING based on [Screen].[ActiveControl] before. I'm not going to swear that SQL can even SEE that class of GUI properties and objects. The wizard, at worst, SHOULD have returned something based on Me!controlname (and a bit more syntax after that).
i've got 2010 and here's what I got

Code:
'------------------------------------------------------------
' Combo4_AfterUpdate
'
'------------------------------------------------------------
Private Sub Combo4_AfterUpdate()
On Error GoTo Combo4_AfterUpdate_Err

    DoCmd.SearchForRecord , "", acFirst, "[InvoiceID] = " & Str(Nz(Screen.ActiveControl, 0))


Combo4_AfterUpdate_Exit:
    Exit Sub

Combo4_AfterUpdate_Err:
    MsgBox Error$
    Resume Combo4_AfterUpdate_Exit

End Sub
 

CodeByJim

New member
Local time
Today, 16:37
Joined
Apr 18, 2015
Messages
9
I don't know what Me.Recordsetclone refers to, but I would
Dim rsc as Recordsetclone

Then dimension a variable to hold a bookmark from the recordsetclone.
Dim varBookmark As Variant

After the following line,
MsgBox "Number Found", vbOKOnly
add this:
varBookmark = rsc.Bookmark

When you want to edit the bookmarked record, find the bookmark from the clone in the recordset.
rs.FindNext varBookmark

Hope this helps.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:37
Joined
Oct 29, 2018
Messages
21,358
I don't know what Me.Recordsetclone refers to, but I would
Dim rsc as Recordsetclone

Then dimension a variable to hold a bookmark from the recordsetclone.
Dim varBookmark As Variant

After the following line,
MsgBox "Number Found", vbOKOnly
add this:
varBookmark = rsc.Bookmark

When you want to edit the bookmarked record, find the bookmark from the clone in the recordset.
rs.FindNext varBookmark

Hope this helps.
Hi @CodeByJim

Welcome to AWF!

I think you would get a "User-defined type not defined" compile error at:

Code:
Dim rsc as Recordsetclone
 

CodeByJim

New member
Local time
Today, 16:37
Joined
Apr 18, 2015
Messages
9
theDBguy is right. Change the dim to:
Dim rsc as DAO.Recordsetclone

That catches me up all the time since moving to 365. In 2000, I never had to worry about that. Old habits...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:37
Joined
Oct 29, 2018
Messages
21,358
theDBguy is right. Change the dim to:
Dim rsc as DAO.Recordsetclone

That catches me up all the time since moving to 365. In 2000, I never had to worry about that. Old habits...
Hmm, I don't have A365, but I'm thinking that still won't work (same error). Can you (or anyone with A365) please confirm that it works? Thanks!
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:37
Joined
Sep 21, 2011
Messages
14,048
I do not even have a RecordsetClone?
I do have a Recordset2 ?

I have always used just a recordset for the clone?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:37
Joined
Feb 19, 2002
Messages
42,976
but I need to bookmark the once its found so the user can edit it - how would I do this please?
I think you are making this too complicated. Once the user selects a record from a list form, use the double click event on some control to open the edit form to the record he wants to edit.

Opening the form the way you are, you should already be positioned on the record the user wants to edit so you don't need any of the other code.

For most of my forms, I have an unbound combo or textbox that the user uses for searching. The RecordSource of the form uses that control as criteria.

Select ..
From ...
Where SomeField = Forms!MyForm!cboSomeField;

Then when the form opens initially, it opens to a "new" record. In the AfterUpdate event of the combo I have:
Me.Requery
So, if he chooses something from the combo, the form is requeried and immediately moves to the record he selected. No bookmarking, no recordsetclone processing.
 

Users who are viewing this thread

Top Bottom