Results 1 to 13 of 13

Thread: Database Help

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Jul 2004
    Posts
    1,202

    Database Help

    Can anyone help me please.

    I'm trying to make a database program, in the program i have a button called New and this will take you to another form that needs to be filled out and then added to the database. Once it has been added its then been put on the main forms listbox but it only shows the name in the listbox until you have closed the form down once you start the program up again it will then show all that members details that you have added.

    Take a look and let me know if you can help me.
    Last edited by Jamie_Garland; Sep 22nd, 2006 at 06:56 PM.

  2. #2
    WiggleWiggle dclamp's Avatar
    Join Date
    Aug 2006
    Posts
    3,527

    Re: Help

    Please use a more informative Title next time, and most people will not download code
    My usual boring signature: Something

  3. #3

    Thread Starter
    Frenzied Member
    Join Date
    Jul 2004
    Posts
    1,202

    Re: Database Help

    Yeah i updated it to give you more information on my program.

  4. #4
    Fanatic Member BillBoeBaggins's Avatar
    Join Date
    Jan 2003
    Location
    in your database, dropping your tables.
    Posts
    628

    Re: Database Help

    Well lets look at the problem at hand.

    Your newly added data does not show up on your drop down list, after entry. But it does after you re-open the program. Consider these...

    a)Your keeping an active static data connection that does not see updates/or additions.
    b)Your not triggering code to update your list appropriately after the user is done entering and committing the new record.
    c)If your listbox can see the new record of information, but whatever screen that shows the rest of the information does not then you have a connection state and or type issue. Meaning the code that is accessing the information to fill the listbox is doing it right by either allowing itself to see updates and additions or it is creating a new connection at the correct interval which will see whatever already exists.

  5. #5

    Thread Starter
    Frenzied Member
    Join Date
    Jul 2004
    Posts
    1,202

    Re: Database Help

    can you help me sort this out please?

  6. #6
    Fanatic Member BillBoeBaggins's Avatar
    Join Date
    Jan 2003
    Location
    in your database, dropping your tables.
    Posts
    628

    Re: Database Help

    Show the code that updates your listbox, and loads the detail information page, and the creation code of your connection.

  7. #7

    Thread Starter
    Frenzied Member
    Join Date
    Jul 2004
    Posts
    1,202

    Re: Database Help

    The code below is for the main form.

    VB Code:
    1. Option Explicit
    2.  
    3.  
    4. Dim VolRecCnt As Integer
    5. Public Gilroy As adodb.Connection
    6. Public Volunteers As adodb.Recordset
    7. Public rstTeachers As adodb.Recordset
    8. Public AdvSearchRS As adodb.Recordset
    9. Dim NameQuery As String
    10. Dim Search As String
    11. Public keydrop As Integer
    12. Dim CheckCaption As String
    13. Dim i As Integer
    14. Dim OpIndex As Integer
    15. Dim varReturn As String * 50
    16. Dim varReturn2 As String * 50
    17. Dim varConvert As Double
    18. Dim varConvert2 As Double
    19. Dim CheckValue As Integer
    20. Dim varKey As Integer
    21.  
    22. Private Sub Command1_Click()
    23. End
    24. End Sub
    25.  
    26. Private Sub cmdDelete_Click()
    27.     If Admin = False Or User = True Then
    28.     MsgBox "Sorry, unfortunately only someone with Administrator Access can perform that operation. Please contact your administrator for further queries."
    29.     Else
    30.     Text1.Visible = True
    31.     If MsgBox("Are you sure you want to delete this volunteer's record?", vbYesNo, "Volunteers") = vbYes Then
    32.     Volunteers.Delete
    33.     If lstNames.ListIndex = 0 Then
    34.         lstNames.ListIndex = 1
    35.     End If
    36.     ListNamesUpdate
    37.     Else
    38.             Exit Sub
    39.         End If
    40.     End If
    41. End Sub
    42.  
    43. Private Sub cmdExit_Click()
    44.     End
    45. End Sub
    46.  
    47. Private Sub cmdNew_Click()
    48.     If Admin = False Or User = True Then
    49.     MsgBox "Sorry, unfortunately only someone with Administrator Access can perform that operation. Please contact your administrator for further queries."
    50.     Else
    51.     frmNew.Show
    52.     End If
    53. End Sub
    54.  
    55. Private Sub Form_Load()
    56.  
    57.     Dim ListNamesID As Integer
    58.     ListNamesID = 0
    59.     On Error GoTo Form_Load_Err
    60.          
    61.     i = 1
    62.     'Below code connects prgram to database
    63.     Set Gilroy = New adodb.Connection
    64.     Gilroy.Open "Provider=MSDASQL.1;Persist Security Info=False;Data Source=MS Access Database;Initial Catalog=gilroy4.mdb"
    65.    
    66.     'below code sets recordet (where to get info from)
    67.     Set Volunteers = New adodb.Recordset
    68.     Volunteers.Open "SELECT * FROM Volunteers", Gilroy, adOpenDynamic, adLockOptimistic
    69.     Set AdvSearchRS = New adodb.Recordset
    70.     AdvSearchRS.Open "SELECT * FROM AdvSearch", frmMain.Gilroy, adOpenDynamic, adLockOptimistic
    71.    
    72.     If Volunteers.EOF Then
    73.         MsgBox "Please Add Someone To The Database"
    74.     Else
    75.         Volunteers.MoveFirst
    76.     End If
    77.    
    78.     Form1Display 'displays informtaion of first record
    79.     lstNames.Clear
    80.    
    81.     'below code adds all volunteers to list box on main form
    82.     If Not Volunteers.BOF Then
    83.         Volunteers.MoveFirst
    84.         Do Until Volunteers.EOF
    85.             lstNames.AddItem Volunteers("FullName")
    86.             lstID.AddItem Volunteers("ID")
    87.             Volunteers.MoveNext
    88.         Loop
    89.         Else
    90.             MsgBox "Please add someone to the database."
    91.     End If
    92.     lstNames.ListIndex = 0
    93.     Volunteers.MoveFirst
    94.     User = True
    95.     'the below code sets another recordset that is used to store the encrypted passwords
    96.     Set rstTeachers = New adodb.Recordset
    97.     rstTeachers.Open "SELECT * FROM Teachers", Gilroy, adOpenDynamic, adLockOptimistic
    98.     rstTeachers.MoveFirst
    99.  
    100.            'The following section of code adds the names to the list box
    101.    
    102.    
    103.  Call Sort_it 'sorts list
    104.  
    105.  
    106. Form_Load_Err:
    107.     Select Case Err.Number
    108.     Case 94
    109.         Exit Sub
    110.     End Select
    111. End Sub
    112.  
    113.  
    114. Private Sub cmdPrev_Click()
    115.     If lstNames.ListIndex > 0 Then
    116.         lstNames.ListIndex = lstNames.ListIndex - 1
    117.     End If
    118. End Sub
    119. Private Sub cmdNext_Click()
    120.     If lstNames.ListIndex < lstNames.ListCount - 1 Then 'Because listindex is 0 based, if there is 165 entries, then the last will be 164.
    121.         lstNames.ListIndex = lstNames.ListIndex + 1
    122.     End If
    123. End Sub
    124.  
    125.  
    126. Public Sub Form1Display()
    127.     On Error Resume Next
    128.     Dim intValue As Integer
    129.     Dim intValue2 As Integer
    130.     Dim intValue3 As Integer '
    131.  
    132.     Dim i As Integer
    133.    
    134.     'Below lines put the Database field information
    135.     'into text boxes.
    136.    
    137.     txtFirstName.Text = ""
    138.     txtSurName.Text = ""
    139.    
    140.     On Error Resume Next
    141.     txtFirstName.Text = Volunteers.Fields("Name") & ""
    142.     txtSurName.Text = Volunteers.Fields("LastName") & ""
    143.     txtAddress.Text = Volunteers.Fields("Address") & ""
    144.     txtPhone.Text = Volunteers.Fields("Phone") & ""
    145.    
    146.     If User = True Then
    147.         User = False    'This code temporarily turns User mode off if it is on so that when it displays the checkboxes you don't get a message box each time saying that you are a user and you can't change them.
    148.         WasUser = True
    149.     Else
    150.         WasUser = False
    151.     End If
    152.     'The below code decodes the binary from the Access DB
    153.     'and displays it in the correct checkboxes.
    154.     intValue = Volunteers.Fields("PandF")
    155.    
    156. End Sub
    157.  
    158. Private Sub Frame2_DragDrop(Source As Control, X As Single, Y As Single)
    159. lstNames.Refresh
    160. End Sub
    161.  
    162.  
    163.  
    164. Private Sub txtSurName_lostfocus()
    165.     'The code in this sub saves information
    166.     'if it is edited in the textboxes
    167.     'by an admin.
    168.    
    169.     Dim ListIndexTemp As Integer
    170.     If Admin = True Then
    171.         ListIndexTemp = lstNames.ListIndex
    172.         'The below code saves the changed information in to the database.
    173.         Volunteers.Fields("Lastname") = txtSurName.Text & ""
    174.         Volunteers.Fields("Name") = txtFirstName.Text & ""
    175.         Volunteers.Fields("Address") = txtAddress.Text & ""
    176.         Volunteers.Fields("Phone") = txtPhone.Text & ""
    177.        
    178.         Volunteers.Update
    179.         Volunteers.Fields("FullName") = Volunteers.Fields("Name") & " " & Volunteers.Fields("Lastname") 'Sets the FullName field in the database
    180.         Volunteers.Update
    181.         Call ListNamesUpdate 'Updates the listbox on the main form
    182.        
    183.         lstNames.ListIndex = ListIndexTemp
    184.         lstNames.Text = Volunteers.Fields("Name") & " " & Volunteers.Fields("Lastname") 'Changes the text that appears on the main listbox, this is so that if the admin changes the first or last name, this change will appear in the main listbox.
    185.         lstNames.ListIndex = ListIndexTemp
    186.     Else
    187.         Exit Sub
    188.     End If
    189.    
    190. End Sub
    191.  
    192.  
    193. Private Sub Sort_it()
    194.     ' sort them alphabetically
    195.     ' uses a bubble sort
    196.     Dim a As Integer, b As Integer
    197.     For a = 0 To lstNames.ListCount - 2
    198.         For b = a + 1 To lstNames.ListCount - 1
    199.             ' compare and swap if necessary
    200.             If lstNames.List(b) < lstNames.List(a) Then
    201.                 Call SwapPeople(a, b)
    202.             End If
    203.         Next b
    204.     Next a
    205.     Call lstNames_Click ' show it all up now again
    206.     Text1.Visible = False
    207. End Sub
    208.  
    209. Private Sub SwapList(lst As ListBox, a As Integer, b As Integer)
    210.     'This code swaps the listbox information if needed.
    211.     Dim temp As String
    212.     temp = lst.List(a)
    213.     lst.List(a) = lst.List(b)
    214.     lst.List(b) = temp
    215. End Sub
    216.  
    217. Private Sub SwapPeople(a As Integer, b As Integer)
    218. ' used by the sort to swap two values
    219.     Call SwapList(lstNames, a, b)
    220.     Call SwapList(lstID, a, b)
    221. End Sub
    222.  
    223. Private Sub lstNames_Click()
    224.     'Displays the name when you click on it.
    225.     lstID.ListIndex = lstNames.ListIndex
    226.     Volunteers.MoveFirst
    227.     Volunteers.Find "ID like '" & lstID.Text & "'"
    228.     Form1Display
    229. End Sub
    230.  
    231. Public Sub ListNamesUpdate()
    232.      lstNames.Clear
    233.      Text1.Visible = True
    234.      If Not Volunteers.BOF Then
    235.         Volunteers.MoveFirst 'Moves to first record
    236.         Do Until Volunteers.EOF
    237.         lstNames.AddItem Volunteers.Fields("FullName") 'Changes the text that appears on the main listbox, this is so that if the admin changes the first or last name, this change will appear in the main listbox.
    238.             lstID.AddItem Volunteers("ID") 'Adds record ID's to the ID list box
    239.             Volunteers.MoveNext 'Moves to the next record
    240.         Loop
    241.         Else
    242.             MsgBox "Please add someone to the database." 'If there is no one in the database.
    243.     End If
    244.     lstNames.ListIndex = 0 'goes to first name in list box
    245.     Call Sort_it 'Sorts in alphabetical order
    246. End Sub

    The code below is for the New client page

    VB Code:
    1. Option Explicit
    2.     Dim AddintValue As Integer
    3.     Dim AddintValue2 As Integer
    4.     Dim AddintValue3 As Integer
    5.     Dim ChkValue1 As Integer
    6.     Dim ChkValue2 As Integer
    7.     Dim ChkValue3 As Integer
    8.     Dim i As Integer
    9.     Dim n As Integer
    10.  
    11.  
    12. Private Sub cmdAdd_Click()
    13.     If txtSurName.Text = "" Then 'If there is nothing in the surname box
    14.         MsgBox "Please enter a Surname", vbInformation, "Volunteers" 'tell them to pu tone in
    15.     Else
    16.         frmMain.Volunteers.AddNew 'Add new record into DB
    17.         frmMain.Volunteers!Name = txtFirstName.Text    'V
    18.         frmMain.Volunteers!Lastname = txtSurName.Text        'The following code
    19.         frmMain.Volunteers!Address = txtAddress.Text
    20.         frmMain.Volunteers!Phone = txtPhone.Text
    21.        
    22.         frmMain.Volunteers.Fields("Fullname") = frmMain.Volunteers.Fields("Name") & " " & frmMain.Volunteers.Fields("Lastname") 'This fills in the Fullname field by adding the first and last names together, the fullname field is used for displaying purposes.
    23.         frmMain.Volunteers.Update 'Updates the database to incorporate new changes.
    24.     'End If
    25.     MsgBox "Your entry has been added."
    26.     frmMain.ListNamesUpdate 'This calls a sub that re-fills-in the listbox on the main form so that the new entry is there
    27.     End If
    28. End Sub
    29.  
    30. Private Sub Form_LostFocus()
    31.     Unload Me
    32. End Sub
    33.  
    34. Private Sub txtname_KeyPress(index As Integer, KeyAscii As Integer)
    35.     If KeyAscii = 13 Then 'If they hit enter, it will add the entry to the database
    36.         Call cmdAdd_Click 'rather than them having to click the Add button
    37.     End If
    38. End Sub
    39.  
    40. Private Sub cmdExit_Click()
    41.     Unload Me 'Unloads the Add new vounteer form. Ie closes it and deletes information that may be left there in textboxes.
    42. End Sub

  8. #8
    Fanatic Member BillBoeBaggins's Avatar
    Join Date
    Jan 2003
    Location
    in your database, dropping your tables.
    Posts
    628

    Re: Database Help

    I am leaving work so I have little time to look this over at the present.

    VB Code:
    1. Private Sub cmdAdd_Click()
    2.     If txtSurName.Text = "" Then 'If there is nothing in the surname box
    3.         MsgBox "Please enter a Surname", vbInformation, "Volunteers" 'tell them to pu tone in
    4.     Else
    5.         frmMain.Volunteers.AddNew 'Add new record into DB
    6.         frmMain.Volunteers!Name = txtFirstName.Text    'V
    7.         frmMain.Volunteers!Lastname = txtSurName.Text        'The following code
    8.         frmMain.Volunteers!Address = txtAddress.Text
    9.         frmMain.Volunteers!Phone = txtPhone.Text
    10.        
    11.         frmMain.Volunteers.Fields("Fullname") = frmMain.Volunteers.Fields("Name") & " " & frmMain.Volunteers.Fields("Lastname") 'This fills in the Fullname field by adding the first and last names together, the fullname field is used for displaying purposes.
    12.         frmMain.Volunteers.Update 'Updates the database to incorporate new changes.
    13.     'End If
    14.         frmMain.Volunteers.Requery    '<--- The only thing I can think of right now try adding
    15.     MsgBox "Your entry has been added."
    16.     frmMain.ListNamesUpdate 'This calls a sub that re-fills-in the listbox on the main form so that the new entry is there
    17.     End If
    18. End Sub

    Your connection is dynamic and your using it around your application which is good and saves time opening and killing connections. So try the requery thing.

  9. #9

    Thread Starter
    Frenzied Member
    Join Date
    Jul 2004
    Posts
    1,202

    Re: Database Help

    All im trying to do is geting the clients information to go in to the list box and the text boxes without needing to close the project down then reopening for it to work properly.

  10. #10

    Thread Starter
    Frenzied Member
    Join Date
    Jul 2004
    Posts
    1,202

    Re: Database Help

    Can anyone help me please?

  11. #11
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Database Help

    I would requery the recordset the reload the listbox once the new addition is completed.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  12. #12

    Thread Starter
    Frenzied Member
    Join Date
    Jul 2004
    Posts
    1,202

    Re: Database Help

    How would i do that?

  13. #13
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Database Help

    if the record set is still open then rs.requery or you can close the recordset and re-generate the SQL statement and reopen the recordset (which is how I normally do it since I never leave a record set open after getting the data from it.)
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width