Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2014
    Posts
    10

    Unanswered: Need some help with populating a Form

    I have a small problem with a Form I have created.

    I created a db to keep track of specific deatils when visting with clients. These details are mostly questions and other details that should be asked everything we meet and were not set up to be tracked in my primary management system.

    I am running Access 2010

    Tabels:
    AllAgentInfo
    - This table is a linked table to a SQL db table that I created that holds all the client info such as Agent Code, Agent Name, Address etc.

    Visit_Data
    - This is a blank table that has fields for all the info I want to track. I have created fields for the fields that I want to auto populate from the AllAgentInfo table mentioned above.

    Both tables mentioned above are linked by AllAgentInfo!AgentCode and Visit_Data!Agent_Code. The other tables in the db contain sales statistice about each client.

    Forms:
    I have created a form based on the Visit_Data table. I have added all the fields I want to use to auto populate the Visit_Data table from the AllAgentInfo table. I have also added free form, and other combo box fields that would populate the Visit_Data table that would be competed during each visit.


    What I have been trying to with little success is "on load" of the form I would like to be able to cycle and view all of the auto populated data from the established table, at the same time the fields that were not populated but will be during each visit would stay blank while cycling through.

    I have been using the following code to populate the form with the data from the AllAgentInfo table.
    Code:
    Private Sub Form_Load()
    Me!Agent_Code = DLookup("AgentCode", "dbo_KSMALLAgentInfo")
    Me!Agency_Name = DLookup("ProducerName", "dbo_KSMALLAgentInfo")
    Me!Branch_Name = DLookup("BranchName", "dbo_KSMALLAgentInfo")
    Me!Address1 = DLookup("Address1", "dbo_KSMALLAgentInfo")
    Me!Address2 = DLookup("Address2", "dbo_KSMALLAgentInfo")
    Me!City = DLookup("City", "dbo_KSMALLAgentInfo")
    Me!State = DLookup("State", "dbo_KSMALLAgentInfo")
    Me!Zip = DLookup("Zip", "dbo_KSMALLAgentInfo")
    Me!County = DLookup("County", "dbo_KSMALLAgentInfo")
    Me!Contact_Title = DLookup("ContactTitle", "dbo_KSMALLAgentInfo")
    Me!Contact_Name = DLookup("ContactName", "dbo_KSMALLAgentInfo")
    Me!Phone = DLookup("Phone", "dbo_KSMALLAgentInfo")
    Me!Fax = DLookup("Fax", "dbo_KSMALLAgentInfo")
    Me!Email = DLookup("Email", "dbo_KSMALLAgentInfo")
    End Sub
    Now this works as far as showing the first record in the AllAgentInfo table. However I cannot cycle through any other records that are in the table and I cannot paste an AgentCode into the respective field to search for another agent. I also get error messages requiring me to complete the other form fields that are not auto populated.

    I have also tried to use the following code which has worked in other forms but this method populates nothing in my form.
    Code:
    Private Sub Agent_Code_AfterUpdate()
    Me!Agency_Name = DLookup("ProducerName", "dbo_KSMALLAgentInfo", "AgentCode = Forms!Visit_Data_Form!Agent_Code")
    Me!Branch_Name = DLookup("BranchName", "dbo_KSMALLAgentInfo", "AgentCode = Forms!Visit_Data_Form!Agent_Code")
    Me!Address1 = DLookup("Address1", "dbo_KSMALLAgentInfo", "AgentCode = Forms!Visit_Data_Form!Agent_Code")
    Me!Address2 = DLookup("Address2", "dbo_KSMALLAgentInfo", "AgentCode = Forms!Visit_Data_Form!Agent_Code")
    Me!City = DLookup("City", "dbo_KSMALLAgentInfo", "AgentCode = Forms!Visit_Data_Form!Agent_Code")
    Me!State = DLookup("State", "dbo_KSMALLAgentInfo", "AgentCode = Forms!Visit_Data_Form!Agent_Code")
    Me!Zip = DLookup("Zip", "dbo_KSMALLAgentInfo", "AgentCode = Forms!Visit_Data_Form!Agent_Code")
    Me!County = DLookup("County", "dbo_KSMALLAgentInfo", "AgentCode = Forms!Visit_Data_Form!Agent_Code")
    Me!Contact_Title = DLookup("ContactTitle", "dbo_KSMALLAgentInfo", "AgentCode = Forms!Visit_Data_Form!Agent_Code")
    Me!Contact_Name = DLookup("ContactName", "dbo_KSMALLAgentInfo", "AgentCode = Forms!Visit_Data_Form!Agent_Code")
    Me!Phone = DLookup("Phone", "dbo_KSMALLAgentInfo", "AgentCode = Forms!Visit_Data_Form!Agent_Code")
    Me!Fax = DLookup("Fax", "dbo_KSMALLAgentInfo", "AgentCode = Forms!Visit_Data_Form!Agent_Code")
    Me!Email = DLookup("Email", "dbo_KSMALLAgentInfo", "AgentCode = Forms!Visit_Data_Form!Agent_Code")
    End Sub

    If someone could help me I would really appreciate it. If I forgot to mention a detail then please ask me and I will respond with whatever you need.


    Thank you in advance

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    496
    Provided Answers: 24
    Stop using DLOOKUPs (slow)
    connect the form to a query.
    All fields should connect to each form box. (instant)

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    either use a bound form (plain vanilla access) or use an inbound form based on a recordset your control

    bound form is the easiest, and requires little if any programming, unbound does the same thing but you have to do the spadework
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jun 2014
    Posts
    10
    Quote Originally Posted by ranman256 View Post
    Stop using DLOOKUPs (slow)
    connect the form to a query.
    All fields should connect to each form box. (instant)


    Thank you for the advice, I have done this. Now getting the form to save the data to the other table is now the problem.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the other table is the problem?

    what other table
    what problem?
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2014
    Posts
    10
    Quote Originally Posted by healdem View Post
    the other table is the problem?

    what other table
    what problem?
    My control source is the AllAgentInfo table, I am populating a number of fields in the form with data from this table. As well as having a number of free form fields for the rep to fill out while meeting with the client. I would like to save all data fields on the form to the Visit_data table as a new record.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so your form wants to be bound to the AllAgentInfo table
    pulling in values as required (using combo or list boxes) from other tables
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jun 2014
    Posts
    10
    Quote Originally Posted by healdem View Post
    so your form wants to be bound to the AllAgentInfo table
    pulling in values as required (using combo or list boxes) from other tables
    Yes, the form is bound to the AllAgentInfo table pulling in required data from that table and populating the respective form fields. But I want to place unbound controls on the form for free form data entry. In the end I would like to save all of the data fields (Bound and Unbound) to a new table "Visit_Data".

  9. #9
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    496
    Provided Answers: 24
    Execute an append query

  10. #10
    Join Date
    Jun 2014
    Posts
    10
    Quote Originally Posted by ranman256 View Post
    Execute an append query
    I can understand using the append query to put the new records in to the Visit db, however you have to attach the query to a table to get the records from and a table to put the records in. And doing it this way will only send the bound control's data on the form to the new db, all of the unbound controls will not move with bound data.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    there's two appraoched to an Access form
    either:-
    use plain vanilla access, suing bound controls and let Access handle the interaction with the db
    OR
    use unbound controls, where you do the donkey work, the interaction with the DB through code.

    the smaller the system, the fewer the number fo users palin vanilla Access works well. the more users, the more complex the system, if you are using a server back end, then you probably want to go the unbound route.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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