Results 1 to 6 of 6
  1. #1
    Join Date
    May 2013
    Posts
    1

    Unanswered: Help with creating a customer database please

    Hi guys,

    I am pretty new to using Access. I had taken a class on it a while back, but I would in no way call myself anything beyond a novice when it comes to databases. When it comes to things other than databases, I am a power user.

    what I am trying to accomplish is this, I am trying to create a customer database for my wife so she can more easily keep track of her customers. What I would like the database to do is not allow duplicate phone number entries (possibly saying something like "there is already an entry with this phone number, would you like to view it?" or something along those lines). The reasoning for not wanting duplicate phone number entires is that her customers return infrequently. Because of this there is a concern that multiple entries would occur. I understand that setting the phone number as the primary key is a bad idea, and this is where I think I am getting stuck about how to approach this. Obviously if the phone number is set as the primary key, there would be no duplicates allowed. Is there a way to link a primary key to a number and have this queried each time a new customer is added?

    I understand that a workaround for this would be to search for the phone number first and then enter the data afterwards, but I am trying to remove as many steps as possible from this system for her as she isn't very technically savvy.

    I would also like to setup a cascading combo box so when a State is selected, only cities within that State are returned.

    From what I could see there is no template that does this already. Is there any way to do this without writing code?


  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    As to your first question, I'd force Focus to the Textbox for the phone number, when a New Record is started, and pop a message telling the user to enter the phone number before entering other data.

    Code:
    Private Sub Form_Current()
     
     If Me.NewRecord Then
      MsgBox "Please Enter Phone Number Before Any Other Data!", , "Please Enter Phone Number"
      PhoneNumber.SetFocus
     End If
    
    End Sub


    Then, after the phone number is entered, search for it in existing Records, and if found, ask the user if they want to go to that Record. If they respond with a 'yes,' navigate to that Record, if 'no,' stay on the current, New Record.

    Code:
    Private Sub PhoneNumber_AfterUpdate()
     
     Dim Resp As Integer
     Dim CurrentPN As String
     
     CurrentPN = Me.PhoneNumber
     
     If Nz(Me.PhoneNumber, "") <> "" Then
     
     If DCount("*", "CustomerTable", "[phonenumber]= '" & Me.PhoneNumber & "'") > 0 Then
      
      Resp = MsgBox("This Phone Number Already Exists! Would You Like to See This Record?", vbYesNo + vbDefaultButton1)
       If Resp = vbYes Then
        Me.Undo
        PhoneNumber.SetFocus
        DoCmd.FindRecord CurrentPN
       Else
        'Do nothing; stay on the New Record
       End If
     
     End If
    
    End If
    
    End Sub

    You'll nee to replace all instances of PhoneNumber, in the code, with the actual name of the Textbox containing your phone numbers.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    As to your second question, the concept you're speaking of is, indeed, called 'Cascading Comboboxes.'
    • Create a Table with Fields for State and City and populate it. There are commercials apps available, often at nominal fees, that provide an Access Table with this data, usually coupled with ZipCodes, or you can create your own.
    • Place a Combobox on your Form
    • When the Combobox Wizard comes up hit Cancel
    • With the Combobox selected, go to Properties - Other
    • In the Name Property enter cboStates
    • Click on the Data Tab
    • In the Row Source Property enter
    • SELECT DISTINCT [tblCities].[StateFieldName] FROM tblCities ORDER BY [StateFieldName];

    Now use this code in the AfterUpdate event for your cboStates Combobox:

    Code:
    Private Sub cboStates_AfterUpdate()
    cboCities.RowSource = _
    "Select Distinct tblCities.CityFieldName " & _
        "FROM tblCities " & _
        "WHERE tblCities.StateFieldName = '" & cboStates.Value & "' " & _
        "ORDER BY tblCities.CityFieldName;"
    
    cboCities = ""
    End Sub
    • Now, place a second Combobox on your Form
    • When the Combobox Wizard comes up hit Cancel
    • With the Combobox selected, go to Properties - Other
    • In the Name Property enter cboCities
    Replace CityFieldName, StateFieldName and tblCities with your actual names for these objects.

    You should be set!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You can simply set an index on the phone number field that doesn't allow duplicates. It doesn't have to be the primary key to accomplish this.

    If you adopt Missinglinq's suggestion for your first query, you'll need to wrap to the DCount function in Nz, returning 0 for nulls, or you'll get a type mismatch error on new records.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by weejas View Post

    ...If you adopt Missinglinq's suggestion for your first query, you'll need to wrap to the DCount function in Nz, returning 0 for nulls, or you'll get a type mismatch error on new records.
    Why? That exact code worked fine on new Records, when I tested the above code before posting!

    DLookup
    returns Nulls, I believe, but the only time DCount returns Nulls, per Access Help, is if you include a Field in the Where clause of the function that doesn't exist in the Table or Query.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Oh, fair enough. I've got so used to anticipating and dealing with null returns that I'm seeing them where they don't exist!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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