Results 1 to 5 of 5

Thread: Contact Help

  1. #1
    Join Date
    May 2007
    Posts
    3

    Unanswered: Contact Help

    Good afternoon All,
    I know there is a way to do what I need, but I am a bit rusty with my Access skills. Any help is greatly appreciated!

    Here is what I would like to do as an end result...
    When my users enter a name on my form, I would like for the address and company information for that person automatically fill in. If they are not already in the contacts list, I would like for the record to be added. Any idea how I accomplish this?

    So far I have the tblAccts (which would be the main information entered on the form), tblContacts (where the names, companies,and contact information is stored), and frmAcctOpen (the user's form for opening accounts).

    It has been about 6+ years since I have really messed around with access, so please speak basic language in your answers.

    Thank you!

  2. #2
    Join Date
    Sep 2006
    Posts
    265
    The problem is matching as any small typo or different spelling will result in a mismatch. I would put an intermediate Dialog Box to allow users to search on Name or Company etc. Wildcards minimise input.

    If they can't find a record then they add it!

    Simon

  3. #3
    Join Date
    May 2007
    Posts
    3

    Still not clear

    That is a good point. Could you point me in the right direction on how I would go about this? If I do a find from a form on a separate table, can I have the found record populate the record on the form I am working on? If so how would I do it?

    Thank you for your quick response

  4. #4
    Join Date
    Sep 2006
    Posts
    265
    This are the basics call I will use a new Form Client Dialogue Search with two Fields a some type of Switchboard or Menu. To launch the Search:

    Function ClientsOpenName()

    DoCmd.OpenForm "Clients Dialogue Search"
    Forms![Clients Dialogue Search].[Search] = "N"
    Forms![Clients Dialogue Search].[Field1].Visible = True
    DoCmd.GoToControl "Field1"
    Forms![Clients Dialogue Search].[LabelF2].Caption = "Surname"
    Forms![Clients Dialogue Search].[LabelF2].Caption = "First Name"

    End Function

    Seocnd function:

    Function ClientsOpenCompany()

    DoCmd.OpenForm "Clients Dialogue Search"
    DoCmd.GoToControl "Field2"
    Forms![Clients Dialogue Search].[Search] = "C"
    Forms![Clients Dialogue Search].[LabelF2].Caption = "Company"
    Forms![Clients Dialogue Search].[Field1].Visible = False

    End Function

    Create the Criteria (abbreviated version):

    Private Function ClientsCriteria() As String

    With CodeContextObject
    If .[Search] = "N" Then
    ClientsCriteria = "[Surname] like '" & .[Field1] & "*" & "' and [First Name] like '" & .[Field2] & "*" & "'"
    ElseIf .[Search] = "C" Then
    ClientsCriteria = "[Client Co Name] like '" & .[Field2] & "*" & "'"
    End If
    End With

    End Function

    Then on the Search Form a Command Button:

    Function ClientsEntry() As String

    DoCmd.OpenForm "Clients", , , ClientsCriteria

    End Function

    I have found that creating your own Criteria is best approach as it is declared once. I also search by Email address and Code.


    Simon
    Simon

  5. #5
    Join Date
    May 2007
    Posts
    3

    Very Confused

    Simon,

    I really appreciate the effort you put forth in answering my question, but I am I think a bit intimidated by it all. Due to my lack of messing around with Access in so long I am like a newbie all over again. To make matters worse, my books from class are in storage since I am in the transition of moving. Would you mind terribly breaking this down to the novice level? I am just not sure what is code and what is given values. Also, would I be putting this into the code builder for a form or a command button? Sorry to seem so ignorant, but after reading your post I realize just how ignorant and forgetful I am now!

Posting Permissions

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