Textbox1 displays records from Table1 in a large textbox on a form, separated by vbnewline for clarity.
The textbox of course has a vertical scrollbar, but I want to be able to use a button click to move to
each record in the textbox. Textbox1 only displays the text of the record, not the index. Image:
So far so good. I tried this from chatGPT but can't figure out how ot do this.
Code:
Private Sub NextVerse_Click()
Dim arrRecords() As String
Dim currentIndex As Integer
currentIndex = Me.txtMatchedVerses.Value
MsgBox currentIndex
currentIndex = currentIndex + 1
If currentIndex >= UBound(arrRecords) Then
MsgBox "End of records reached!"
Else
Me.txtCurrentIndex.Value = currentIndex
Me.txtCurrentRecord.Value = arrRecords(currentIndex)
End If
End Sub
I tried to be as complete as possible describing this.
Any help and guidance greatly appreciated.
Thans, cr.
Attachments
Textbox1 displaying records from Table1.png
88 KB · Views: 25
maktxtbx2tbl (named as Table1 in code as example .png
add the ID to your form (set its Visible property to "No" to hide it and Name the textbox as txtID).
change the code to this:
Code:
Private Sub NextVerse_Click()
Dim id As Long
id = Me!txtID
With Me.RecordsetClone
.FindFirst "ID = " & id
.MoveNext
If Not .EOF Then
Me.Bookmark = .Bookmark
End If
End With
End Sub
Thanks for quick reply arnelgp. This may be a stupid question but I'll ask it anyway - 'add the ID to your form' - where
do I add the ID to the form? Not to overkill this, but the code that populates this textbox, named txtMatchedVerses is
Code:
Private Sub Form_Load()
Me.txtSearchCriteria = gSavedValue
Dim cnt As Long
If DCount("*", "tblSearchResults") > 0 Then
strSQL = "SELECT * FROM tblSearchResults;"
Set db = CurrentDb: Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst
Do Until rs.EOF
Me.Controls("txtMatchedVerses").Value = IIf(Len(Me.Controls("txtMatchedVerses").Value) = 0, rs.Fields(0).Value, Me.Controls("txtMatchedVerses").Value & vbNewLine & vbNewLine & rs.Fields(0).Value)
rs.MoveNext
Loop
Set rs = Nothing: Set db = Nothing
Else
End If
cnt = DCount("*", "tblSearchResults")
Me.Totrows.Value = cnt
Dim cnt2 As Long
If DCount("*", "maktxtbx2tbl") > 0 Then
strSQL = "SELECT * FROM maktxtbx2tbl;"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst
Do Until rs.EOF
Me.Controls("Textbox2").Value = IIf(Len(Me.Controls("Textbox2").Value) = 0, rs.Fields(1).Value, Me.Controls("Textbox2").Value & vbNewLine & vbNewLine & rs.Fields(1).Value)
rs.MoveNext
Loop
Set rs = Nothing: Set db = Nothing
Else
End If
cnt2 = DCount("*", "tblSearchResults")
Me.totrows2.Value = cnt2
DoCmd.MoveSize 0, 0
End Sub
I'm new to Access VBA. I was helped with getting this code above to work right to populate the textbox when the form loads.
Thanks again for helping.
cr
the code is already filling your textbox upto the End Record.
If you can upload your db (anyway it is a Bible, every home needs it), I want to see the logic behind the form.
are you filling the textbox "per book", "per book per chapter"?
the code is already filling your textbox upto the End Record.
If you can upload your db (anyway it is a Bible, every home needs it), I want to see the logic behind the form.
are you filling the textbox "per book", "per book per chapter"?
I saved the file to the desktop and attached it below. After thinking about your first reply, I went back and looked at the
table structure. There was only 1 field. So I added an ID field thinking about the logic behind your first reply. (Access has to
have a way to identify each record before you can tell it to move up or down).
This is the code that does the searching for any record, verse or phrase - if this helps:
Code:
Private Sub cmdTestCode_Click()
Dim x As String
Dim rs As Recordset
Dim db As Database
Dim strSQL As String
Dim srchval As String
x = Me.txtSearchCriteria.Value
srchval = Me.txtSearchCriteria.Value
With DoCmd
.SetWarnings False
.OpenQuery "qmakSearch" 'qmakSearch is a make table query that must exist on the Db
.SetWarnings True
End With
If DCount("*", "tblSearchResults") > 0 Then 'tblSearchResults is the MakeTable created table from qmkSearch query
Me.Controls("txtMatchedVerses").Value = "" 'Clear - Note txtMatchedVerses is the name of the text box thta must exist on the form to receive the results of the SQL query.
strSQL = "SELECT * FROM tblSearchResults;"
Set db = CurrentDb: Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst
Do Until rs.EOF
Me.Controls("txtMatchedVerses").Value = IIf(Len(Me.Controls("txtMatchedVerses").Value) = 0, rs.Fields(0).Value, Me.Controls("txtMatchedVerses").Value & vbNewLine & vbNewLine & rs.Fields(0).Value)
rs.MoveNext
Loop
Set rs = Nothing: Set db = Nothing
MsgBox "There were " & DCount("*", "tblSearchResults") & " records found.", vbInformation
Else
MsgBox "There were no records found.", vbExclamation
End If
Me.Totrows = DCount("*", "tblSearchResults")
End Sub
I had help with this too.
Hopefully the entire db came through. In times past, because the source table structure is the entire Bible from Gen to Rev,
which has 31,103 verses occupying 31,103 rows in the db, the file was too large to send. I have it in Dropbox but couldn't upload it
because because Access kept telling me file the file was in use when I was trying to Save As for the latest version ???
Briefly -if possible - I developed this entire Bible application in Excel, using Excel's VBA which I'm very familiar with. As the app grew to what I wanted in a serious BIble Study full featured application, it put a strain, it seemed on Excel and I got too much 'not responding' errors - I had to Task Manger End Task out too many times to get back into the application - too much fear and worry. It took me 2 full years - from 2021 through 2023 to develop this the way I wanted. One MrExcel experienced member suggested porting over to Access - that a file that size is much better suited to a database rather than many multiple sheets in Excel manipulating 31, 103 rows of verses.
Am at that point now, and painfully learned that Access's VBA is NOT the same as Excel's VBA. Much much easier in Excel - but much less efficient. I said I'd try to keep this brief, didn't - sorry. Last things - I was able to import the entire Bible source sheet from Excel to Access. But it is 31,103 rows across 4 columns - 1 column for each of the 4 most popular and widely used translations -the KJV, NIV, NASB and RSV.
Multiple translations are extremely useful for verse to verse comparisons. Vast differences exist in specific verses with some even omitted in the more modern versions. Anyway, too much rambling from me.
I did not arrange the table structure as Book - chapter - verse because I didn't see the need for form Normalization or establishing referential integrity at the beginning point of this porting project.
You seem interested in wanting to help from the comment on your reply post. So I thought I'd give a little b/g on the where and why of this effort for me. Please let me know if you were able to get the entire db intact and if you have what you need to help with the Next button.
I hope the attachments will give you more information an understanding of where I am in Access now, as you requeted.
Thanks a mil for your help and interest in this project.
cr
Kingwood, Tx
Attachments
NEW TABLE STRCTURE WITH ID FIELD ADDED..png
16.7 KB · Views: 15
ADDED ID FIELD. HOPEFULLY THIS WAS THE RIGHT THING TO DO. .png
......If you click on the SEARCHF form at the bottom of the Forms section, you'll see the form I've developed so far.
This is the main form a user sees when the app is opened. I deigned it to fit my entire laptop screen - it should for 'any
users machine : (0,0) setting in Form Load. The image shows the form + the Next and Prev buttons I'm(we're) working on
When a user searches for Matthew 24, 51 results are loaded ihe underlying source table. When he/she reads to a verse, say, Matthew 24:15, and decides to close the app and return later, when the form opens, it should open to that exact verse location, in this case, Matthew 24:15, and not at the beginning of the verses t the top. Designed this way in Excel(very easy to do in Excel)
Once again, arnelgp, many thanks for all your help.
cr
Attachments
SEARCHFFORM Main development form with Next and Prev buttons.png
i find it hard to comprehend your bible app.
if you like the "bookmark" feature i can add that to mine.
actually there is no search on mine, only comboboxes (dependent to each other).
If the problem you are trying to solve is the variable length of each verse which doesn't render nicely in a form with fixed height records, then use a report to display the data instead of a form. You can embed the report on a form if you want to. You will have hidden data that you can use to control scrolling. You do not need to concatenate the verses. They can grow/can shrink properties handle the variability without code. Make sure to set the detail section to allow can grow/can shrink as well as the individual control that displays the text.
i find it hard to comprehend your bible app.
if you like the "bookmark" feature i can add that to mine.
actually there is no search on mine, only comboboxes (dependent to each other).
Sorry for the confusion. All this form does, is allow for two searches to be done using two textboxes at the top right next to
the Go buttons, clicking the Go buttons and the two larges textboxes are populated with the results respectively. The verse+ button
allows entering any verse in the form of Matthew 24:15 i.e, wirh a colon and 1 space for the verse. This will give Matthew 24:15 or any verse
plus the 10 verse below it. Coming back to the Next button, your initial code solution seems to be the simplest and most correct way to move from
record to record(verse to verse) in the textbox. As mentioned previously, I went back and added an ID field to the record source table structure.
Hopefully this code should work with the ID field in the source table now:
Code:
"add the ID to your form (set its Visible property to "No" to hide it and Name the textbox as txtID).
change the code to this:"
Code:Copy to clipboard
Private Sub NextVerse_Click()
Dim id As Long
id = Me!txtID
With Me.RecordsetClone
.FindFirst "ID = " & id
.MoveNext
If Not .EOF Then
Me.Bookmark = .Bookmark
End If
End With
End Sub
...forgot to ask, what did you mean by the bookmark feature ? - are you saying there is a way to return to the same verse
when the focus is removed from the textbox that the cursor was on in that verse ? - that would fix a major problem. I just moved from
one textbox to the other when I went back to the original textbox, the focus reset the results back to the beginning top verse.
...forgot to ask, what did you mean by the bookmark feature ? - are you saying there is a way to return to the same verse
when the focus is removed from the textbox that the cursor was on in that verse ? - that would fix a major problem. I just moved from
one textbox to the other when I went back to the original textbox, the focus reset the results back to the beginning top verse.
If the problem you are trying to solve is the variable length of each verse which doesn't render nicely in a form with fixed height records, then use a report to display the data instead of a form. You can embed the report on a form if you want to. You will have hidden data that you can use to control scrolling. You do not need to concatenate the verses. They can grow/can shrink properties handle the variability without code. Make sure to set the detail section to allow can grow/can shrink as well as the individual control that displays the text.
Hi Pat - thanks for replying - I'm unsure about displaying a report in an Access form - all I'm trying to do is develop a Next button - easy in Excel VBA - easier said than done in Access VBA - due to me being in an infancy stage with learning Access This code block arnelgp sent earlier seems to be workable once I can figure it out and/or get help doing that.
Code:
Private Sub NextVerse_Click()
'add the ID to your form (set its Visible property to "No" to hide it and Name the textbox as txtID).
'change the code to this:
'Code:Copy to clipboard
Dim id As Variant
id = Me!txtMatchedVerses
With Me.RecordsetClone
.FindFirst "ID = " & id
.MoveNext
If Not .EOF Then
Me.Bookmark = .Bookmark
End If
End With
End Sub
a comment: It the AWF is only for collaboration between experienced users and Access developers, my apologies but I'm on the wrong forum.
That said, on my own efforts as contributing to help solve this Next button issue - with the way this application is being designed in Access
- to display record search results in a single large textbox - I have to rely on my experience with Excel VBA - 15+ years. All that experience
is great but is of no help to me in Access - as I've learned this is a different world.
To me,, to move from one record(verse) down to the net one in a textbox display of the records, Access has to be told to go back to the table
records, identify the current record the blinking cursor is on in the textbox, move down 1 in the table, pick up that ID and "send" it back to the
Access code on the form's button which tells it to move down 1 record(verse). I separated the verses with vbnewline & vbnewline spacing
for easier reading. I did all this and much more in the Excel app but am at a point where the Excel app has become so full fetured that
it seems to be putting a strain on a spreadsheet of 31.103 rows across 4 columns. Several in the Excel camp suggested I try moving this to Access, which is why I'm here.
Sorry for the long rant. Just my thoughts.
cr
Kingwood, Tx
I already mentioned one way I thought it could be done in your crosspost on MrExcel?
However @arnelgpis far more experienced than I, and likely has a better way.
If you had left them as records in a subform, probably how it is in Excel, then this would not even be an issue.
I already mentioned one way I thought it could be done in your crosspost on MrExcel?
However @arnelgpis far more experienced than I, and likely has a better way.
If you had left them as records in a subform, probably how it is in Excel, then this would not even be an issue.
Hi Gasman - thanks. At this point, I have no idea of how to 'have left them as records in a subform'
and how to do this. I've not worked with subforms - yet. A Maketable query on the main
source sheet that the criteria being searched produces a new table from tha query - which to me is just a filter.
Then that table becomes the source table from which the records(verses) in the large textbox are displayed.
As mentioned, am just starting to feel my way through this learning curve and scratching the Access surface
of development using this "new" VBA.
I know you've been following this thread, and I very much appreciate your help and suggestions.
I may get laughed at with this next question, referring to the yellow error line in the image - and that's OK with me - but what's a Recordset.Clone, and what role does it play in making this next button work. ? Sounds like its just making a copy of the table... -
The only other help I've been able to get is good ole chatGPT's Copilot - and all this person - or thing - does is just searches and copies questions from StackOverflow, etc.
Seems like I can't say anything these days without writing a book. Anyway, thanks again for helping me out.
@ChazRab If you want us to look at your database, please upload it using the forum tools.
Access is a RAD (Rapid Application Development) tool. It does most of the work for you. The majority of the code you write in Access is to prevent bad data from being saved. So, it is validation code. Using bound forms and reports, Access takes care of all the code required to fill the form and move from record to record as well as the add/change/delete code.
Form and control events, are "hooks" These are places designed to hang your own custom code. Do you want to do something once when the form loads? Do you want to do something each time the user scrolls to a new record? Do you want to validate data the user is trying to update? Once you understand what the events are for, you have a better chance of getting your code into the correct event for best effect.
@arnelgp is very generous with his time and created a simple form that seems to work the way you want it to work. I would have created something that looked similar but with the "box" as a report to avoid all the concatenation code.
Hi arnelgp. You had the getverses code to move down 10 verses, not 1 to the next verse. I just changed the
10 to a 1 and it works great. See in your code below.
Clicking the Next button moves it down exactly to the next verse. My thinking is to copy this code to a
Prev button to go back 1. I can copy this button code to do the same thing on Textbox2 of the form.
The table structure looks great also. I know this probably has something to do with making this Next code work right.
The other thing I noticed is that the File opens to the exact same location the verse was on when I closed the app - i.e., like you
said - a bookmark to return to where a user left off great!
Code:
Private Sub getVerses(ByVal ID As Variant)
Dim bm As Variant
Dim sKjv As String, sNASB As String
If Not IsNull(ID) Then
Me.txtID = ID
Me![txtSearchCriteria] = DLookup("verse", "tblBibles", "ID=" & ID)
With rs
.Seek "=", ID
bm = .Bookmark
.Move -1 [B]' Changed 10 to 1 to move 1 verse only not 10[/B]
Me.txtPrevID = !ID
.Seek "=", ID
.Move 1 [B]' Changed 10 to 1 to move 1 verse only not 10[/B]
Me.txtNextID = !ID
.Bookmark = bm
i = 1
Do While Not .EOF
sKjv = sKjv & !KJV & vbNewLine & vbNewLine
sNASB = sNASB & !NASB & vbNewLine & vbNewLine
i = i + 1
If i > 10 Then
sKjv = Left$(sKjv, Len(sKjv) - 2)
sNASB = Left$(sNASB, Len(sNASB) - 2)
Exit Do
End If
.MoveNext
Loop
End With
Me.txtMatchedVerses = sKjv
Me.Textbox2 = sNASB
End If
End Sub
I'm now just in the process of copying code and making the correct changes. Please let me know if it was OK
to change the number to move down from 10 to 1 in getverses.
Thanks so much for all your help.
cr
Textbox1 displaying records from Table1.png
maktxtbx2tbl (named as Table1 in code as example .png
Table structure if it helps.png
Type mismatch error at currenindex = line.png
