Results 1 to 13 of 13
  1. #1
    hnde is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jan 2019
    Posts
    6

    How can I create "Add a field" option for the user in the form?

    Hello,

    In Microsoft Access, I have cost parameters table and after an user enters the value of the parameters, it calculates total cost that is the sum of the values of all cost parameters in the table. I converted the table to the form also for user interface. In order to make the form more flexible, I want to create "Add parameter and its value" option for the user on the form so that the user can add a new cost parameter and value of the new parameter can be added to total cost.



    How can I create this option? Could you help me?

    Thank you.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you should already have cost param in a lookup table.
    if you want users to add them, on the form, add a tiny button ('add' caption) user clicks,
    add the new param,
    close table/form

    then user must click REFRESH ALL button (on toolbar) to see the new entry.

    If you are talking about a user running a query on a table and wants specific params to search on,
    do the same thing but with a tParam table. User adds the params wanted in the query, the query is joined to the tParam table and only those items are pulled.
    I use a 'pick' list so users can dbl-click the item, it adds it to the search param table.

    Click image for larger version. 

Name:	Charge fees.jpg 
Views:	31 
Size:	24.5 KB 
ID:	36931

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Why do you say 'add a field'? Should not be a requirement to 'add a field'. Adding field is a design change. User should only be entering records.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    hnde is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jan 2019
    Posts
    6
    Thank you for the reply Ranman256. I was asking the code under “ add” and “ refresh all” buttons. How can I engage the new parameter that the user added to the cost model (“add” button) and how can I add the value of the new parameter to the total cost (“refresh all” button)? Should I do it with macros or Visual Basic codes?

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    VBA for the ribbon RefreshAll button:

    CommandBars.ExecuteMso "DataRefreshAll"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    hnde is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jan 2019
    Posts
    6
    Thank you June7. I will try it. What about “add” button? Do you know VBA codes for it?

  7. #7
    hnde is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jan 2019
    Posts
    6
    Dear June7,
    In my forms there are cost parameters and, with these cost parameters, form calculates total cost. I want my forms will be more flexible. Therefore, I want the user add more cost parameters if he / she requires it. Because of it, I want to create an “add a parameter” button for adding parameters by an user and “ refresh all” button so that the new parameter can be added to total cost.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Sorry, don't understand. Is adding a cost parameter adding a new record?

    As I said earlier, adding field is a design change. This is not a practical approach.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    hnde is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jan 2019
    Posts
    6
    Dear June7,

    I want the user add a cost parameter that is be the field of the "cost table" if he\she thinks that existing parameters are not enough for him\her. Is it possible? If is it not possible, how can I Make my cost parameters form more flexible so that the user can change it according to his\her needs?

    Thank you

  10. #10
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Can you post up a picture of the table design for the "cost table" please?
    I'm not sure we are fully understanding your question , and sometime a picture can say a thousand words...
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  11. #11
    hnde is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jan 2019
    Posts
    6
    Hello Minty,

    I attached my cost table and my cost form. I want to create "add a parameter" option for the user at the end of the form. Also, I want the value of the new parameter will be added to total cost. How can I do this?

    Thank you.
    Attached Thumbnails Attached Thumbnails cost table.png   cost form.png  

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    By 'add a parameter' do you mean a new field in table? This is allowing users to modify design and is a very bad idea.

    Data structure is not normalized and makes totaling cost complicated.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    As June has pointed out you are not storing this data correctly. You have simply moved a spreadsheet into Access, trust us all, this is not the way forward.

    You should have a header record for the each job.
    A separate related table should record the various possible process options for the job with the cost.
    I would also create a table for the Job Options, perhaps with a default price.

    When you need a new process you add a record to the process table, NOT a new field.

    This then makes you system flexible for 1, 2 , 10 or 100 processes per job.
    And adding up the cost per job up becomes super simple.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

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

Similar Threads

  1. Replies: 12
    Last Post: 10-01-2018, 02:40 PM
  2. Replies: 1
    Last Post: 02-28-2017, 12:29 PM
  3. Replies: 3
    Last Post: 08-05-2016, 12:59 PM
  4. Replies: 4
    Last Post: 12-20-2015, 12:16 PM
  5. Replies: 1
    Last Post: 09-07-2015, 08:00 AM

Tags for this Thread

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