Results 1 to 15 of 15
  1. #1
    lumpa1000 is offline Novice
    Windows 7 32bit Access 2010 64bit
    Join Date
    Feb 2017
    Posts
    10

    Tracing field edits and efficiency adding data

    Hi ladies and gents,



    I've just recently set out to create a CRM system for a small business, something similar to a marketing call center.

    Im currently creating the form for adding client interactions and i have two questions on functionality is would like to add to improve efficiency.

    1. When logging the interactions with our clients in would like to trace which employee that contacted that client. I have a field in my table set up for this where I aim to add the employee ID. This is could of course do manually but would be very repetitive if you do 100+ calls per day. I first considered using the employee I'D field with a default value but apparently that doesn't work? I have created a functioning log in form already so I was contemplating setting a global variables to the employee I'D and then use an event to populate the field? I will use the same solution in other forms later when employees register other data.

    2. In order to make the client interaction logging more efficient when registring the same data for say 10 clients in a row. Is there any way I can make a combo box keep the same value it had when I logged my previous interaction?


    OK that was a pretty longoing text to type on my phone... hope it make sense. I'm fairly new to access and this is my first more ambitious project but I know the basics of both access, sql and VBA.

    Any input on any of the above would be very much appreciated.

    Kind regards
    Magnus

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    1. with regards user - have you considered using the environ function? environ("username") will return the current (windows) logged in user

    2. really depends on how your forms work, but in the before update event, you can set the default value for a control to be the current value

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    as for #2, I'd add that it depends on other factors too - is the db split? How many consecutive records - 3? 33? How many fields - 2? 22?
    The effort to replicate data may not be worth the effort from a practical standpoint if those numbers are low. It could be done safely with a set of global variables if the db is split. Or maybe a temp table with all of those fields holding the last values used by a particular user and the table is flushed (or not) after logging off. As for that one, I think it would be easier than coding BeforeUpdate events for say, 20 or more controls. A query and temp table would be more efficient IMHO.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    lumpa1000 is offline Novice
    Windows 7 32bit Access 2010 64bit
    Join Date
    Feb 2017
    Posts
    10
    Hi ajax thanks a lot for your input very kind!

    1. I wasent aware of its existence. The primary key for my user table will be the companys employee number. I guess I could a separate table matching the windows login I'd with the company user I'd?

    2. Oh ok that could potentially be very useful. So essential when I click my button to register new interaction (adding new data into the database) the say 10 or so controls I will use on this for will keep the same value? I'll have to explore this one.

    Thanks a lot ajax I'll have to try this out and revert!

    Cheers

  5. #5
    lumpa1000 is offline Novice
    Windows 7 32bit Access 2010 64bit
    Join Date
    Feb 2017
    Posts
    10
    Hi micron thanks for taking the time very much appreciated!
    It will ultimately be a split database with some 10+ users I estimate. Number of records would vary but easily 10-20 and occasionally up to 50 or more. Number of fields would be between 1 and 10. Ok excellent, I aim to make the DB very robust and scalable. I should probably try both global variables (need it for other functions later anyway) and the temp tables + queries solution to get a better command of access. I have use some VBA before with excel so think I can follow up on that lead myself.

    As for the table solution, when yu say flushed do you mean dropped? And sorry for this question but how should i go about implementing this?

    I guess I need to create this table somehow when the user logs in? Then have one field for each variable I want to save? Not sure how I would go about designing this? Any input very much appreciated.

    Thanks a lot for your assistance thus far Micron!

    Cheers
    Magnus

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    lumpa,

    You could post a jpg of your current tables and relationships. That would give readers some idea of the scope of your proposed application and how you have identified relationships between the data subjects. The structure will facilitate/hinder your intended
    I aim to make the DB very robust and scalable
    Good luck

  7. #7
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    by flushed I meant the record(s) deleted. I've never used the other idea and with some afterthought I have to admit that I think the other idea would be easier as long as it works for you. The permanent table would be in the split FE with a field for each control. There is a downside to my idea:

    - if you add controls to the 'list', you have to modify the table design too, which is something we try to avoid (not a huge deal for a table with only one record). To be more normalized, the control names would have to be field values themselves, thus complicating the issue. For me, that's the point where normalization begins to hurt.
    - I'd use the OpenArgs to open the form in a "mode" ('primary' or whatever) and assign that to a variable. For the first record save event, testing the variable tells you to append the form record, whereupon the variable is changed to something else ('secondary'). Subsequent saves then tell you to run an Update query instead.
    - if you try this without the split, I think you can appreciate how this gets more complicated. Whatever is going on has to happen against the record for the particular user, so now you need another field. A single shared FE is not what most people will do.

    I only elaborated on this so that you might pick up useful info on the OpenArgs parameter of the DoCmd.OpenForm method and how temp tables can be used. IIRC, the times that I've used temp tables was for reasons other than yours. It also might point out how a form can have different modes of operating. For example, using the same form is preferred over having multiple copies of the same form for different purposes. The OpenArgs can control the purpose, be it locking certain fields based on user profile, opening to update a single record as opposed to opening to append, pass a control value or label caption from one form to another, etc.
    Last edited by Micron; 02-19-2017 at 08:17 AM. Reason: clarification

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    lumpa,

    I don't know if you are familiar with Barry Williams' site but he offers a number of free data models.
    Here is one that may be helpful.
    There are several models listed under the CRM heading.

  9. #9
    lumpa1000 is offline Novice
    Windows 7 32bit Access 2010 64bit
    Join Date
    Feb 2017
    Posts
    10

    Form background and what I want to achieve

    Hi orange,

    Excellent idea, I should have started in that end. I do appologize!
    First I can mention that the project thus far has 25 tables, 1 query (which i base this form on), 3 forms
    Form 1: LoginForm
    Form 2: AddClientInteraction
    Form 3: Navigation

    I would have posted a picture of the whole database but as the content and names of certain fields in the project is sensitive to my company i have only selected to show the two most important tables and relationships. However there are a number of additional relevant tables of which the most important use the foreign keys XXXContactID and AnalystID. The first hold contact information for the company clients, the second hold information related to the company employees (who will actually use the database for tasks including for example the adding the data i mention in this post).

    The form- AddClientInteraction
    Essentially what I have so far is a form with a combo box where I can search my client list for the relevant client. Having selected the client the form populates with a number of fields, including the name and contact detail of the relevant client. I can then fill in all the data necessary in the "ContactInteraction" table (pictured below).

    Question 1
    Background:
    Some of the data in the query underlying the form is dependent on the AnalystID.
    What I want to achieve:
    I want a way to automatically populate the AnalystID field in the ContactInteraction table every time a new record is created. I was considering using a default value but that doesnt work from what I understand, as the value must be different depending on the user logged on.

    Question 2

    Background:
    What I need now is a way to add the data in the "ContactInterets" table (pictured below), and I would like to be able to "in one go" add several of those. I was considering adding some 10 combo boxes for that and making a VBA script run each time I complete filling out the form (for example clicking a submit button or run it of some event). If there is any better way of doing it I'm interested. (Also note that I will only add one of CompanyID/PublicationID/EndMarket for each record in that table.)
    What I want to achieve:
    Essentially when the company employees interact with clients it may happen that they interact on the exact same topic with up 20 clients in a row, sometimes up to 100 in a day.
    Therefore it is crucial that the form is easy to update and fill in. Therefore I was considering how to make the comboboxes related to the Fields in the ContactInterests table keep the same values as each new client is called. Again if there is a better way of doing this i'm all ears, I'm essentially taking my first steps here...


    I hope that makes more sense and I do apologize to Ajax and Micro for making you guess what I want to achieve.

    Kind regards,
    Magnus

    PS. Is there any source I could refer to for a list of naming conventions for different objects? This turned out to be a rapidly expanding project and I guess I might do it correct from the start...
    Click image for larger version. 

Name:	20170219_182127.jpg 
Views:	20 
Size:	266.2 KB 
ID:	27558

  10. #10
    lumpa1000 is offline Novice
    Windows 7 32bit Access 2010 64bit
    Join Date
    Feb 2017
    Posts
    10
    Excellent I'll have a look at these next week when i have more time. If anything i could probably use the inspiration. CHeers!

  11. #11
    lumpa1000 is offline Novice
    Windows 7 32bit Access 2010 64bit
    Join Date
    Feb 2017
    Posts
    10
    As usual when starting out I can tell the more I learn the more I understand that I dont know anything

    I'll have to read up on the different syntax you just mentioned and see what I can make of it, notably the .OpenForm method. But as I read it in this post you seem to recommend using global variables instead?

    I did add a new post clarifying some background and how my table structure associated with this question looks like. I do apologize as I should of course have started in that end...

    Kind regards
    Magnus

  12. #12
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'll have to read up on the different syntax you just mentioned and see what I can make of it, notably the .OpenForm method.
    Are you addressing me? I liked the idea in point 2 of post 2, and tried to implement it but it seems a bit cumbersome. After playing around, I see that it will make a difference as to how your form is working. If you can cycle through records where any of these values are Null, you will need to handle that with the Nz function. I think the property to use for the default with the BeforeUpdate event is .Text, since that reflects what is visible in the control at the moment before the update, which might be different from the actual control value. Moving from record to record changes the control value but not the default, even on a new record, so that seems OK. However, if the form can cycle on to a new record, the .Text value becomes invalid and generates the #Name? error, so that has to be handled too.

    What I tried was
    - in design view of the form select a control you whose values you want to retain
    - invoke the code builder, BeforeUpdate event and type or paste
    Code:
    Me.txtControlName.DefaultValue = Nz(Me.txtControlName.Text, "")
    where txtControlName is the name of your control.
    To handle the move to a blank record, I see the OnCurrent event for the form as the place to go, but trying to work with a control that has an invalid value generates a strange error (2247). The solution seems to be, handle the error and substitute a value like so:
    Code:
    Private Sub Form_Current()
    
    On Error GoTo errHandler
    If Me.PRIMARY = "#Name?" Then
    'the above will generate error 2247 if the control contains an invalid value
        With Me.txtControlName
            .SetFocus
            .DefaultValue = Me.txtControlName.Text
        End With
    End If
    Exit Sub
    
    errHandler:
    If Err.Number = 2447 Then
        Me.txtControlName = Null 'or whatever default is desired. 'This will allow a
        'record if the value passes table validation, which may not be desired
        Resume Next
    End If
    End Sub
    Ajax - feel free to correct me as this was your idea; I just had a bit of free time and an intrusive nature At this point, globals or temp tables are looking more attractive because of the number of controls involved. I know, I can be fickle.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Micron, yes it is my idea and as you have pointed out it needs additional work to protect against errors. But as I said, it depends on how the form actually works and what the required effect is.

    For example, what happens when a form is loaded, what should the default value be? null until the current user has entered a new record? based on the last time the form was opened by the user? or any user? based on the last record entered into the table? based on an existing record the user has selected but not updated?

    The before update event I was referring was the form before update, not the control (probably didn't make this clear). The other event is the form before insert event - so only new records get to change the default.

    All input is always gratefully received - been doing this a long time now but still learning new tricks. Plus sometimes my old tricks need updating

  14. #14
    lumpa1000 is offline Novice
    Windows 7 32bit Access 2010 64bit
    Join Date
    Feb 2017
    Posts
    10
    Hi guys thanks a lot for your input that you have kindly given so far. I have managed to solve the issue i listed under bullet 1 and will in the next couple of days, when i habe time, explore your input on question two to find out what works best.

    Again thanks a lot for sharing your experience, very much appreciated!

    Kind regards

  15. #15
    lumpa1000 is offline Novice
    Windows 7 32bit Access 2010 64bit
    Join Date
    Feb 2017
    Posts
    10
    Hi Guys,

    I have yet to try your solutions above as i run into an unexpected complication relating to another table in the query underlying the same form.

    In addition to the tables in the picture above i have the "Contacts" table, with primary key "ContactID", which is a secondary key in the "ContactInteraction" table (above). However it is also a secondary key in the "ContacExtra" table, where the relationship between "Contacts" and "ContactsExtra" is a 1 to 1. The "ContactsExtra" table includes unique contacts data which i want ro keep separate from the "contacts" table.

    When i use the form to add records into the "ContactsExtra" table that works ok but for some reason i dont seem able to add the secondary key "ContactID" to the record until after the record has been added. Then i can go to the table and add it...

    Essentially i would like to add the seconday key automatically in "ContactsExtra" at the same time as i create a "ContactInteraction" with a Customer.

    I just typed this on my phone on the fly so let me know if it doesn make sense and ill provide a picture of the tables again. Cheers

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 07-01-2016, 08:11 AM
  2. Replies: 3
    Last Post: 04-23-2016, 08:37 PM
  3. Replies: 3
    Last Post: 04-14-2013, 12:38 PM
  4. Tracing
    By Craby in forum Access
    Replies: 1
    Last Post: 08-15-2011, 11:55 AM
  5. Back tracing in an existing database
    By meridithdawn in forum Access
    Replies: 3
    Last Post: 06-27-2011, 11:10 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums