Results 1 to 8 of 8

Thread: Tables question

  1. #1
    Join Date
    May 2006
    Posts
    16

    Unanswered: Tables question

    I am creating a database for a non profit organization that receives vouchers for services performed such as bus passes and referrals to other organizations. Anyway, I am designing it in access 2000 format and my question is: is it better to use one large table or a lot of smaller tables for the data and how do i create the form so that it does not overwrite the old data with new data?

    Thanks,

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    is it better to use one large table or a lot of smaller tables for the data
    In general, it's better to use several smaller tables with each table designed to carry specific sets of data. This way the same data can be easily used in other areas of your Database and it can be much easier to manage.

    how do i create the form so that it does not overwrite the old data with new data?
    You'll have to design your forms so that it doesn't. There are many ways to do this. I would suggest you get a book on the subject. There are hundreds out there.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    May 2006
    Posts
    16
    should i use bound or unbound boxes? what will the difference in how the data is stored?

    Thanks

  4. #4
    Join Date
    Mar 2006
    Posts
    163
    You should use bound controls.

    If you used unbound you would probably need to use VBA to enter/update/delete the data in the tables.

  5. #5
    Join Date
    May 2006
    Posts
    16
    I ma using bound controls and also using vba scripts to add, delete, clear, and save on my forms. i am getting some scripting errors though. on the delete control button i keep getting an error for the varible x in my vba could this be due to the boxes being bound? How do i keep from overwriting my data for the same clients. all the books tell me is how to enter the data, i have yet to find anything about not overwriting data in my tables.
    Thanks

  6. #6
    Join Date
    Nov 2003
    Posts
    1,487
    Display the code you are using for your Delete.

    Lock current table records so that they can not be changed.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  7. #7
    Join Date
    Mar 2006
    Posts
    163
    If you are using bound controls you really shouldn't need to use code to add/update/delete records.

  8. #8
    Join Date
    May 2006
    Posts
    16
    here is the code i am using for my frmAdd_Delete. i am getting the error message "item not found in this collection" when i go to add a client. when i go to delete it debugs at the x = MsgBox("Do you really want to delete " & Chr(10) & Chr(13) & rsCust!Client_Name, 36)

    Option Compare Database
    Option Explicit
    Private Sub BtnCancel_Click()

    Dim response As Integer

    response = MsgBox("Clear all of the fields?", 1, "Start Over?")

    If response = 1 Then 'yes
    Call ClearControls
    End If

    End Sub

    Private Sub btnExit_Click()
    On Error GoTo Err_btnExit_Click


    DoCmd.Close

    Exit_btnExit_Click:
    Exit Sub

    Err_btnExit_Click:
    MsgBox Error$
    Resume Exit_btnExit_Click

    End Sub
    Private Sub btnADD_Click()

    If IsNull(Me!Chart_Number) Then
    MsgBox "Chart Number is a Required Entry.", 48
    Me!Chart_Number.SetFocus
    ElseIf IsNull(Me!DMH) Then
    MsgBox "DMH Number is a Required Entry.", 48
    Me!DMH.SetFocus
    Else

    '**** add a new record ****
    Dim db As Database, rsCust As Recordset, Criteria As String

    On Error GoTo ErrorHandler
    Set db = CurrentDb
    Set rsCust = db.OpenRecordset("tblClients", DB_OPEN_DYNASET)

    Criteria = "Chart_Number=" & Val(Me!Chart_Number)
    rsCust.FindFirst Criteria
    If rsCust.NoMatch Then

    rsCust.AddNew
    rsCust("Chart_Number") = (Me!Chart_Number)
    rsCust("DMH") = Int(Me!DMH)
    rsCust("Client_Name") = UCase(Me!Client_Name)
    rsCust("SSN") = UCase(Me!SSN)
    rsCust("DOB") = UCase(Me!DOB)
    rsCust("Age") = UCase(Me!Age)
    rsCust("Gender") = UCase(Me!Gender)
    rsCust("Active_Inactive") = UCase(Me!Active_Inactive)
    rsCust("Address") = UCase(Me!Address)
    rsCust("City") = UCase(Me!City)
    rsCust("cboState") = UCase(Me!cboState)
    rsCust("PostalCode") = UCase(Me!PostalCode)
    rsCust("Treatment") = UCase(Me!Treatment)
    rsCust("LastTreatmentCenter") = UCase(Me!LastTreatmentCenter)
    rsCust("ReferredBy") = UCase(Me!ReferredBy)
    rsCust("Notes") = UCase(Me!Notes)
    rsCust("PhoneNumber") = UCase(Me!PhoneNumber)
    rsCust("DrugProblem") = UCase(Me!DrugProblem)
    rsCust("LastDrugUsage") = UCase(Me!LastDrugUsage)
    rsCust("Alcohol") = UCase(Me!Alcohol)
    rsCust("LastAlcoholUsage") = UCase(Me!LastAlcoholUsage)
    rsCust("HowManyTreatments") = UCase(Me!HowManyTreatments)
    rsCust("CurrentTreatmentCenter") = UCase(Me!CurrentTreatmentCenter)
    rsCust("Homosexual") = UCase(Me!Homosexual)
    rsCust("EmergencyPhone") = UCase(Me!EmergencyPhone)
    rsCust("EmergencyContact") = UCase(Me!EmergencyContact)
    rsCust("Vehicle") = UCase(Me!Vehicle)
    rsCust("Model") = UCase(Me!Model)
    rsCust("Year") = UCase(Me!Year)
    rsCust("SexuallyAbused") = UCase(Me!SexuallyAbused)
    rsCust("PhysicallyAbused") = UCase(Me!PhysicallyAbused)
    rsCust("EmotionallyAbused") = UCase(Me!EmotionallyAbused)
    rsCust("cboEmployee") = UCase(Me!cboEmployee)
    rsCust.Update

    MsgBox Me!Client_Name & " has been added to the Client table."

    'clear the controls to add more customers
    Call ClearControls

    Else
    MsgBox "Chart Number: " & Me!Chart_Number & " already exists.", 48, "ERROR!"
    Me!ACCOUNT.SetFocus
    End If

    rsCust.Close
    db.Close
    Me!Client_Name.Requery

    End If

    Exit_btnADD_Click:

    Exit Sub


    ErrorHandler:
    MsgBox Error$
    rsCust.Close
    db.Close
    GoTo Exit_btnADD_Click:
    End Sub
    Private Sub CUSTOMER_NAME_BeforeUpdate(Cancel As Integer)

    End Sub


    Private Sub cboChart_Number_AfterUpdate()
    Dim D As Database
    Dim rsCust As Recordset
    Dim Criteria As String

    Set D = CurrentDb
    Set rsCust = D.OpenRecordset("tblClients", DB_OPEN_DYNASET)

    Criteria = "[Chart_Number] = " & [Client_Name]

    rsCust.FindFirst Criteria

    Me!Chart_Number = rsCust("Chart_Number")

    Me!DMH = rsCust("DMH")

    Me!Client_Name = rsCust("Client_Name")
    Me!Client_Name = UCase(Me!Client_Name)

    Me!SSN = rsCust("SSN")
    Me!SSN = UCase(Me!SSN)

    Me!DOB = rsCust("DOB")
    Me!DOB = UCase(Me!DOB)

    Me!Age = rsCust("Age")
    Me!Age = UCase(Me!Age)

    Me!Gender = rsCust("Gender")
    Me!Gender = UCase(Me!Gender)

    Me!Active_Inactive = rsCust("Active_Inactive")
    Me!Active_Inactive = UCase(Me!Active_Inactive)

    Me!Notes = rsCust("Notes")
    Me!Notes = UCase(Me!Notes)

    Me!PhoneNumber = rsCust("PhoneNumber")
    Me!PhoneNumber = UCase(Me!PhoneNumber)

    Me!DrugProblem = rsCust("DrugProblem")
    Me!DrugProblem = UCase(Me!DrugProblem)

    Me!LastDrugUsage = rsCust("LastDrugUsage")
    Me!LastDrugUsage = UCase(Me!LastDrugUsage)

    Me!Alcohol = rsCust("Alcohol")
    Me!Alcohol = UCase(Me!Alcohol)

    Me!LastAlcoholUsage = rsCust("LastAlcoholUsage")
    Me!LastAlcoholUsage = UCase(Me!LastAlcoholUsage)

    Me!HowManyTreatments = rsCust("HowManyTreatments")
    Me!HowManyTreatments = UCase(Me!HowManyTreatments)

    Me!CurrentTreatmentCenter = rsCust("CurrentTreatmentCenter")
    Me!CurrentTreatmentCenter = UCase(Me!CurrentTreatmentCenter)

    Me!LastTreatmentCenter = rsCust("LastTreatmentCenter")
    Me!LastTreatmentCenter = UCase(Me!LastTreatmentCenter)

    Me!EmergencyContact = rsCust("EmergencyContact")
    Me!EmergencyContact = UCase(Me!EmergencyContact)

    Me!EmergencyPhone = rsCust("EmergencyPhone")
    Me!EmergencyPhone = UCase(Me!EmergencyPhone)

    Me!Vehicle = rsCust("Vehicle")
    Me!Vehicle = UCase(Me!Vehicle)

    Me!Model = rsCust("Model")
    Me!Model = UCase(Me!Model)

    Me!Year = rsCust("Year")
    Me!Year = UCase(Me!Year)

    Me!SexuallyAbused = rsCust("SexuallyAbused")
    Me!SexuallyAbused = UCase(Me!SexuallyAbused)

    Me!PhysicallyAbused = rsCust("PhysicallyAbused")
    Me!PhysicallyAbused = UCase(Me!PhysicallyAbused)

    Me!EmotionallyAbused = rsCust("EmotionallyAbused")
    Me!EmotionallyAbused = UCase(Me!EmotionallyAbused)

    Me!cboEmployee = rsCust("cboEmployee")
    Me!cboEmployee = UCase(Me!cboEmployee)


    rsCust.Close

    End Sub

    Private Sub ClearControls()
    'clear the controls on the form

    Me!Chart_Number = Null
    Me!DMH = Null
    Me!Client_Name = ""
    Me!SSN = ""
    Me!DOB = ""
    Me!MaritalStatus = ""
    Me!Age = ""
    Me!Gender = ""
    Me!Active_Inactive = ""
    Me!Notes = ""
    Me!Address = ""
    Me!City = ""
    Me!PostalCode = ""
    Me!cboState = ""
    Me!PhoneNumber = ""
    Me!DrugProblem = ""
    Me!LastDrugUsage = ""
    Me!Alcohol = ""
    Me!LastAlcoholUsage = ""
    Me!HowManyTreatments = ""
    Me!CurrentTreatmentCenter = ""
    Me!LastTreatmentCenter = ""
    Me!Homosexual = ""
    Me!EmergencyPhone = ""
    Me!EmergencyContact = ""
    Me!Vehicle = ""
    Me!Model = ""
    Me!Year = ""
    Me!SexuallyAbused = ""
    Me!PhysicallyAbused = ""
    Me!EmotionallyAbused = ""
    Me!cboEmployee = ""
    Me!Chart_Number.SetFocus
    Me!Client_Name = Null
    Me!Chart_Number.SetFocus



    End Sub


    Private Sub cmdDelete_Click()

    If IsNull(Me!Chart_Number) Then
    MsgBox "Choose a Client to Delete.", 48
    Me!Client_Name.SetFocus
    Else

    '**** add a new record ****
    Dim db As Database, rsCust As Recordset, Criteria As String

    On Error GoTo Error_cmdDelete_Click
    Set db = CurrentDb
    Set rsCust = db.OpenRecordset("tblClients", DB_OPEN_DYNASET)

    Criteria = "Chart_Number=" & Val(Me!Chart_Number)
    rsCust.FindFirst Criteria
    If Not rsCust.NoMatch Then

    x = MsgBox("Do you really want to delete this client?")
    If x = 6 Then
    rsCust.Delete
    Call ClearControls
    End If

    End If

    rsCust.Close
    db.Close
    Me!Client_Name.Requery

    End If

    Exit_cmdDelete_Click:

    Exit Sub


    Error_cmdDelete_Click:
    ' MsgBox "Error number is: " & ErrNum & "; message is: " & Error(ErrNum)
    MsgBox Error$
    rsCust.Close
    db.Close
    GoTo Exit_cmdDelete_Click:

    End Sub




    thank you,

Posting Permissions

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