End Select without Select Case, but Select Case exists...

[hothead]

Private Sub RecordControls_Click
    Select Case Index
            Case 0
                On Error GoTo ErrorHandler
                If MasterControl.Text = "" Then
                    MsgBox "You must specify a header in which to put the data.", vbInformation, App.Title
                    Exit Sub
                End If
                For i = 0 To Data.Count - 1
                   If Data(i).Text = "" Or DataType.Text = "" Then
                        MsgBox "One or more of the required fields have not been filled in.", vbInformation, "Cannot continue"
                        Exit Sub
                    End If
                Next i
                RecordSource = "SELECT * FROM " & MasterControl.Text & " ORDER BY Name"
                ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Databases\" & MasterControl.Text & ".mdb;Persist Security Info=False"
                rs.Open RecordSource, ConnectionString, adOpenKeyset, adLockOptimistic
                For i = 1 To rs.RecordCount
                If rs.BOF = False Then
                    rs.MoveFirst
                End If
                If rs.EOF = True Then Exit For
                If Data(0).Text = rs!Name Then
                    MsgBox "Duplicate items not allowed", vbCritical, "Can't create record"
                    Exit Sub
                Else
                    rs.MoveNext
                End If
                rs.AddNew
                rs!Name = Data(0).Text
                rs!DataType = DataType.Text
                rs!Data = Data(1).Text
                rs.Update
                rs.Close
                Set WkSpc = Nothing
                Set rs = Nothing
                OrganizerDatabase.ListItems.Add , , Data(0).Text
    End Select
End Sub

As you can see, Select Case is the very first line in my code, yet it gives me the aforementioned error. Why is this?

Also, I need to know how to catch cancel button clicks when creating a new datalink dynamically.

This code opens a box that creates a new dynamic datalink to the database you choose. It's just like right-clicking a udl file and selecting properties.

Set conn = dl.PromptNew

I want to know how to catch cancel button clicks from this box.

[MartinLiss]

Because you don't have a Next for the For i = 1 To rs.RecordCount line.

[si_the_geek]

As you can see, Select Case is the very first line in my code, yet it gives me the aforementioned error. Why is this?

That is a very complex error for a compiler to get exactly right.. check for any missing statements that end a block, such as "End Select", "End If", "Loop", or in this case: "Next".

Also, I need to know how to catch cancel button clicks when creating a new datalink dynamically.

I've written hundreds of database programs, and never one used DataLinks (or heard of them till you posted about them recently).

What are they, and what is the point of using them?
(the chances are, there are better ways of doing what you want)

[hothead]

Because you don't have a Next for the For i = 1 To rs.RecordCount line.

Yeah, you're right, sometimes my code gets so complex it's difficult to keep track of all the block statements. Sorry to waste your time.

I've written hundreds of database programs, and never one used DataLinks (or heard of them till you posted about them recently).

What are they, and what is the point of using them?
(the chances are, there are better ways of doing what you want)

A datalink is a small UDL file (UDL, I believe, stands for Universal Data Link) used for the purpose of maintaining an open connection to a Microsoft Access database. There are two ways to create UDL files.

1. The easy way is to create a datalink variable referrence, as I stated in the above code. There's no physical UDL file on your hard drive, but a temporary datalink is created in memory, and then destroyed when you're done using it.

2. The other way is to create a text file, and rename it so it uses a UDL extension rather than a TXT extension. These are more permanent datalinks that reside on your hard drive as files. This method is recommended if you're constantly accessing the same database in your programs, because you don't have to create the datalink on every connection.

Oh, and btw, the easier way to handle cancel button clicks is to use an error handling routine. (When you click the cancel button, an error pops up, which is kind of a good thing.)

[si_the_geek]

Ah yes, error handling is a good solution there.


Instead of DataLinks I use a connection string, which of course you can simply append a file name to, eg:

  cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & filename

..when mixed with a Common Dialog (or a control such as a listbox, etc), this may be a better solution for you.

[Al42]

It's a lot simpler, if you want to avoid duplicating a name, to open a recordset "... Where table.UserName = '" & TheNameYouDon'tWantToDuplicate & "'" Then if EOF there's no record with that name - there's no need to loop through the recordset looking for it.

(Name is a reserved word in almost every database, so don't use it as a field name.)

As far as the cancel button in the UDL dialog goes (it doesn't throw an error):

Dim conn As ADODB.Connection
'...
  Set conn = dl.PromptNew
  If conn Is Nothing Then
    MsgBox "cancelled"
  Else
    MsgBox "not cancelled"
  End If

@Si:
A UDL file is a cheap and easy way to create the connection string. Open it (as a UDL file - double-click on it), fill out the information it needs, close it, open it in Notepad and copy the connection string.

[hothead]

Ah yes, error handling is a good solution there.


Instead of DataLinks I use a connection string, which of course you can simply append a file name to, eg:

  cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & filename

..when mixed with a Common Dialog (or a control such as a listbox, etc), this may be a better solution for you.

I never would've thought of this, thank you very much. That makes this project a whole lot easier. I was tinkering with it when I first saw your post, but got errors. Long story short, I did some digging through the code and my databases, and it turned out to be a misspelled table name causing the issue. I had forgotten about changing the field names.

It's a lot simpler, if you want to avoid duplicating a name, to open a recordset "... Where table.UserName = '" & TheNameYouDon'tWantToDuplicate & "'" Then if EOF there's no record with that name - there's no need to loop through the recordset looking for it.

(Name is a reserved word in almost every database, so don't use it as a field name.)

I shall remember that for future referrence.