Option Explicit
Dim db As Database
Dim rs As Recordset
Dim ws As Workspace
Dim max As Long
Dim i As Long
Dim errormsg
Dim dbadd As Boolean
Dim dbedit As Boolean

Private Sub Form_Load()
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(App.Path & "\xorfLib2.mdb")
Set rs = db.OpenRecordset("Language", dbOpenTable)
langList
End Sub

Private Function langList()
If rs.RecordCount = 0 Then
  errormsg = MsgBox("No records fount in xorfLib2.mdb", , "Fatal Error")
  Exit Function
End If
rs.MoveLast
rs.MoveFirst
max = rs.RecordCount
rs.MoveFirst
Language.Clear
For i = 1 To max
  Language.AddItem rs("Language")
  rs.MoveNext
Next i
End Function

Option Explicit
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''                         A Simple Database Example                       '''
'''                          Written By Darren Kurn                         '''
'''                                 28/09/01                                '''
'''                                   xdaz                                  '''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'Firstly, For those of you who have never used Databases with your applications
'before, please make a point of going to the project menu, and clicking on
'references. Here you will notice that one of the references is "Microsoft
'DAO Object Library". This is the component which is required to access the
'Database.

'Right,, Lets Start The Example.

'First, we need to dim some variables
'We will start with the DB Variable. This Variable simply tells VB Which Database
'we intend to work from
Dim db As Database
'Next, we have the rs variable. this tells VB which recordset, or table within
'the database that we intend to work from
Dim rs As Recordset
'The ws variable tells VB the workspace. Sorry, but im not entirely sure what this
'actually does, but i do know that it is essential for the project to work
Dim ws As Workspace
'The max variable will eventually hold the number of records in the database
'table, so we can use the variable as a loop control variable
Dim max As Long
'The i variable is just another loop control variable
Dim i As Long
'This variable is used to store any answers from message boxes
Dim errormsg
'these two variables are here so that we know whether we want to add new data to the
'database, or edit existing data
Dim dbadd As Boolean
Dim dbedit As Boolean






'Right, now that we have declared our variables, we can start the actual
'coding of the project
Private Sub Form_Load()
'The first thing that we will need to do when the form loads
'is tell VB which table we are going to work from
Set ws = DBEngine.Workspaces(0)

'Here we set the database. This will tell VB to use the database called
'"Database" which can be found in the same directory as this project.
'Obviously, you do not always have to keep the database in the same directory,
'but i find that it helps!
Set db = ws.OpenDatabase(App.Path & "\Database.mdb")

'Here we set the table within the database which we intend to use.
'As we only have one table in our database, this is easy enough.
Set rs = db.OpenRecordset("tbldata", dbOpenTable)

'Next, we will call the List function which will get some of the
'Information out of the database, and place it into the list box
list

End Sub


Private Function list()
'This Function will extract Surnames from the database, and
'Place them into the list box for selection

'If there are no records in the database table, then we cannot extract
'any data, so we have to exit the function. If we do not do this, then
'The program will throw up an error, and crash
If rs.RecordCount = 0 Then
    errormsg = MsgBox("No Records Found", , "Error")
    'If no records have been found, then it is very likely that the user
    'is using the search field, so we will set the text box back to what
    'it was before the error came up
    If Len(txtSearch.Text) > 0 Then
        txtSearch.Text = Mid(txtSearch.Text, 1, Len(txtSearch.Text) - 1)
    Else
        Exit Function
    End If
End If
'Move to the first record in the database
rs.MoveLast
'Move to the last record in the database
rs.MoveFirst
'You're probably wondering why i have just moved to the first, and then
'the last record in the database. Well, I find that Access does not report
'the number of records in the table accurately. I have no idea Why. It's a
'microsoft thing. however, going to the last, and then the first record usually
'helps Access report the accurate number of records
'Next, we need to set variable "max" to the number of records in the database
max = rs.RecordCount
'Now we move back to the first record in the database
rs.MoveFirst
'Now we need to clear our list box, so that we do not have repeating data
lstdata.Clear
'Now we can start a loop, which will in turn extract data from each of the
'records in the database
For i = 1 To max
    'For each Entry, we want to put the surname into the list box
    'rs("Surname") simply tells VB to get the data from the surname field
    lstdata.AddItem rs("surname")
    'Then we need to move to the next record in the table
    rs.MoveNext
'repeat the loop
Next i

End Function

Private Sub lstdata_Click()
'Once all our surnames are into the list box, we need to control what happens
'when the user click on one of the surnames
'The line below will send an SQL command to the database which tells access
'to create a new theoretical table which only holds the information on
'the selected surname. this works exactly the same way as making a query in the
'database to only show data on a person with a certain surname
Set rs = db.OpenRecordset("Select * from tbldata where surname = '" & Trim(lstdata.list(lstdata.ListIndex)) & "'")
'move to the first record in the table
rs.MoveFirst
'Next, we need to extract the information out of the database, and put
'it in the relevent text boxes
txtForename.Text = rs("forename")
txtSurname.Text = rs("Surname")
txtPhone.Text = rs("phone_no")
'Now we need to enable some of the command buttons so that we can work on the
'data
cmdEdit.Enabled = True
cmdDelete.Enabled = True

End Sub

Private Sub cmdDelete_Click()
'Now we will look at one of the easiest database functions, deletion of data
'First, we will throw up an error message to make sure the user wishes to
'delete the data, because, once you have deleted the data, there is no
'going back
errormsg = MsgBox("Are You Sure You Want To Delete This Record", vbYesNo, "Delete Record")
'if the user has answered yes, we can delete the data
If errormsg = vbYes Then
'Delete the data
rs.Delete
'now we have to set the recordset back to all records in the table, rather than the one
'we just deleted
Set rs = db.OpenRecordset("tbldata", dbOpenTable)
'Then we need to recall the list function to relist all the data, not including the
'record we just deleted
list
'now we need to set the command buttons and text boxes back to their original status
txtSurname.Text = vbNullString
txtSurname.Enabled = False
txtForename.Text = vbNullString
txtForename.Enabled = False
txtPhone.Text = vbNullString
txtPhone.Enabled = False
txtSearch.Text = vbNullString
'then we need to set the command buttons back to their original status
cmdsave.Enabled = False
cmdcancel.Enabled = False
cmdAdd.Enabled = True

Else
'If the user pressed no then we just exit the sub-routine
Exit Sub
End If

End Sub


Private Sub cmdAdd_Click()
'When the add button is pressed, we will first need to clear all the
'text boxes
txtSurname.Text = vbNullString
txtForename.Text = vbNullString
txtPhone.Text = vbNullString
'Now we need to enable the textboxes so that the user can enter the data
txtSurname.Enabled = True
txtForename.Enabled = True
txtPhone.Enabled = True
'We also need to disable all the command buttons, so that the user cannot
'click on one of these and confuse the program
cmdAdd.Enabled = False
cmdDelete.Enabled = False
cmdEdit.Enabled = False
cmdend.Enabled = False
'And enable the save and cancel buttons so that the user can save the
'new data
cmdsave.Enabled = True
cmdcancel.Enabled = True
'Now we set dbadd = true so that VB Knows what we are trying to do
dbadd = True
End Sub

Private Sub cmdsave_Click()
'When the user has finished entering the new data, they will click the
'save button to save the data to the database
'We need to decide at this point whether the user is entering new data
'or editing existing data, already held within the database
'This is what we have used the dbadd and dbedit variables for.
If dbadd = True Then
    Call add
ElseIf dbedit = True Then
    Call edit
End If
End Sub

Private Function languageList()
If rs.RecordCount = 0 Then
  errormsg = MsgBox("No records fount in xorfLib2.mdb", , "Fatal Error")
  Exit Function
End If
rs.MoveLast
rs.MoveFirst
max = rs.RecordCount
rs.MoveFirst
langList.Clear
For i = 1 To max
  langList.AddItem rs("Language")
  rs.MoveNext
Next i
End Function

rs.MoveFirst
langList.Clear
Do Until rs.EOF
  langList.AddItem rs!Language
  rs.MoveNext
Loop

rs!FieldName

rs.fields("FieldName")

rs![Field Name]

...
langList.Clear
For i = 1 To max
  langList.AddItem rs("Language")
  rs.MoveNext
Next i
...