Solved bible database

[The_Doc_Man]

I reviewed the thread and see that you are getting some specific help, so I'll take a different direction and give you a bit of generic help. You need to look up the topic "Cascading Combo Boxes" to see how to make combo box B sensitive to the selection of Combo box A.

The technical comments you are getting regarding things like "Option Explicit" must not be ignored because until you resolve issues caused by "phantom variable creation" you won't be ABLE to get to the point of using a cascaded combo sequence.

 

[MajP]

See fix.

 

[bob fitz]

there it is

As far as I can see, none of your tables have a Primary Key and you have no relationships defined

 

[WaldinTheAnalyst]

You need
OPTION EXPLICIT
at the top of every module then try to compile your code

Come on now. You ever seen code like this? Not going to work.

Private Sub SelectedBook_Click()
Private Sub btnOpenVerses_Click()

    ' Ensure the combo box is not empty
    If Not IsNull(Me.cmbBookName) Then
        ' Use Nz() to avoid errors with null values and pass the second column (book name)
        Dim strBookName As String
        strBookName = Nz(Me.cmbBookName.Column(1), "")

        ' Open the Verses form and pass the selected book name in OpenArgs
        If strBookName <> "" Then
            DoCmd.OpenForm "VersesForm", , , , , , strBookName
        Else
            MsgBox "No book name found."
        End If
    Else
        MsgBox "Please select a book."
    End If

End Sub
End Sub

I will read up on option explicit, thank you

 

[Steve R.]

I believe your database structure has not been fully developed. Please be aware that I cannot provide an MS Access specific solution. My advice is generic. Additionally, I have not attempted to deconstruct the code what you have already developed.

1. Based on the image you provided you need (a minimum) of three tables. One for Chapter, one for Verse, and one for Scripture.
2. The Chapter table would have a minim of two fields: Primary Key field and a number (integer) field identifying the chapter. I usually have an "extra" field (in all tables) to allow notes or other miscellaneous stuff.
3. The Verse table would be similar to the Chapter table. It would have a minimum of three fields. One, its own Primary Key field, a foreign key field that points back to the Chapter table, and then its own field for the Verse number.
4. The Scripture table would also have a minimum of three fields. Its own Primary Key, a foreign key that points to the Verse table Primary Key, and a text field containing the actual scripture.

----------------------------------------------------------------------------------
Based on your image, when you click on the Chapter combobox (listbox), an SQL statement should be issued to populate the Verse combobox (listbox) with all verses that meet the criteria.
Then, when you select the Verse field, an SQL statement would be issued to show the Scripture selected based on the Verse field.

 

[WaldinTheAnalyst]

See fix.

Thank you
When I login it gives me
Run time error 3044
It says my path is not a valid path I should make sure it's spelled correctly and that I'm connected to the server which the file resides.
What am I missing

 

[MajP]

I will read up on option explicit, thank you

VBA Tip: Variable Declaration | EnvisionCAD

Written by Rod Wing. Declare all your variables VBA does not require you to declare any variables, but it is highly recommended that you do so. Variables that are not… Read more »

envisioncad.com

However that only fixes things going forward. You need to add it manually to existing code.

 

[MajP]

Thank you
When I login it gives me
Run time error 3044
It says my path is not a valid path I should make sure it's spelled correctly and that I'm connected to the server which the file resides.
What am I missing

I linked to the tables on my computer. You will have to relink using the link table manager.

 

[MajP]

 

[WaldinTheAnalyst]

I linked to the tables on my computer. You will have to relink using the link table manager.

Thank it's done, I appreciate the effort

 

[WaldinTheAnalyst]

Thank you to everyone

 

[WaldinTheAnalyst]

As far as I can see, none of your tables have a Primary Key and you have no relationships defined

Hi bob just wanted to show you a diagram of my relationship structure as I cannot understand how is it that you don't see any defined relationships nor primary keys, I really value your insight, Thank You

 

[bob fitz]

Hi bob just wanted to show you a diagram of my relationship structure as I cannot understand how is it that you don't see any defined relationships nor primary keys, I really value your insight, Thank You

If you look at the backend db you posted, you will see that none of the tables has a PK. Anyway, since you have marked the thread as solved it's a moot point.

 

[MajP]

If you look at the backend db you posted, you will see that none of the tables has a PK. Anyway, since you have marked the thread as solved it's a moot point.

But that is not the real backend, that is only an import into access from the MySql database. I assume the PKs and relations did not port over. These seem to exist on the real BE.

 

[WaldinTheAnalyst]

If you look at the backend db you posted, you will see that none of the tables has a PK. Anyway, since you have marked the thread as solved it's a moot point.

Thank you, I will look into this

 

[WaldinTheAnalyst]

See fix.

Hi Majp...if it's possible I just wanted to find out what you done to make the chapter selection on my "books" form function and filter it only to display the selected book and chapter in my "verses" form.

 

[MajP]

In the original code you did nothing with the chapter combo box. I added that in to the docmd.openform

Private Sub btnOpenBooksVerses_Click()
    ' Ensure the combo box is not empty
    If Not IsNull(Me.comboSelectBook) Then
        ' Use Nz() to avoid errors with null values and pass the second column (book name)
        Dim strBookName As String
        strBookName = Nz(Me.comboSelectBook.Column(1), "")

        ' Open the Verses form and pass the selected book name in OpenArgs
        If strBookName <> "" And Not IsNull(Me.cmboChapter) Then
            DoCmd.OpenForm "frmVerses", , , "book_ID = " & Me.comboSelectBook & " AND Chapter_Number = " & Me.cmboChapter, , , strBookName
        ElseIf strBookName <> "" And IsNull(Me.cmboChapter) Then
            DoCmd.OpenForm "frmVerses", , , "book_ID = " & Me.comboSelectBook, , , strBookName
        Else
            MsgBox "No book name found."
        End If
    Else
        MsgBox "Please select a book."
    End If
End Sub

if it has a chapter selected then include the chapter in the WHERE argument

DoCmd.OpenForm "frmVerses", , , "book_ID = " & Me.comboSelectBook & " AND Chapter_Number = " & Me.cmboChapter, , , strBookName

if the chapter is not selected then opens to just the book

DoCmd.OpenForm "frmVerses", , , "book_ID = " & Me.comboSelectBook, , , strBookName

 

[WaldinTheAnalyst]

In the original code you did nothing with the chapter combo box. I added that in to the docmd.openform

Private Sub btnOpenBooksVerses_Click()
    ' Ensure the combo box is not empty
    If Not IsNull(Me.comboSelectBook) Then
        ' Use Nz() to avoid errors with null values and pass the second column (book name)
        Dim strBookName As String
        strBookName = Nz(Me.comboSelectBook.Column(1), "")

        ' Open the Verses form and pass the selected book name in OpenArgs
        If strBookName <> "" And Not IsNull(Me.cmboChapter) Then
            DoCmd.OpenForm "frmVerses", , , "book_ID = " & Me.comboSelectBook & " AND Chapter_Number = " & Me.cmboChapter, , , strBookName
        ElseIf strBookName <> "" And IsNull(Me.cmboChapter) Then
            DoCmd.OpenForm "frmVerses", , , "book_ID = " & Me.comboSelectBook, , , strBookName
        Else
            MsgBox "No book name found."
        End If
    Else
        MsgBox "Please select a book."
    End If
End Sub

if it has a chapter selected then include the chapter in the WHERE argument

DoCmd.OpenForm "frmVerses", , , "book_ID = " & Me.comboSelectBook & " AND Chapter_Number = " & Me.cmboChapter, , , strBookName

if the chapter is not selected then opens to just the book

DoCmd.OpenForm "frmVerses", , , "book_ID = " & Me.comboSelectBook, , , strBookName

Thanks a million