Results 1 to 13 of 13
  1. #1
    snhnic is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2017
    Posts
    8

    Question Update filed automaticly based on other fields


    Hi,

    I have a form in where people can select values in fields: discipline, design area, main group and format. Is it possible to auto fill a field with the values from the mentioned fields? let us say discipline= 2, design area = garage, main group= electric and format= A4.

    I like the filed to be filled as the other fields are selected: 2-garage-electric-4-xxxx where xxx should be a number not yet used. Main goal is to make a document number based on the selection.

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Assuming that each selection has some value associated with it, you should be able to use a DLOOKUP to look up that associated value based off of the selection.

    See: https://www.techonthenet.com/access/...in/dlookup.php

    Note: In Access, anything that can be calculated (or "looked up") easily should NOT be stored in a Table field. That kind of violates the rules of data normalization (and why do you need to store it anyway if it can easily be calculated at any time?).

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with JoeM --don't store what can be calculated easily when needed.
    It seems you are creating/using some sort of classification system. You might want to describe what you are doing in business terms. Once readers understand your business requirement, they may offer focused solutions.

    You appear to be working with --this is How I'm doing things, but we're not clear of the What you are trying to accomplish.

  4. #4
    snhnic is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2017
    Posts
    8
    Quote Originally Posted by orange View Post
    I agree with JoeM --don't store what can be calculated easily when needed.
    It seems you are creating/using some sort of classification system. You might want to describe what you are doing in business terms. Once readers understand your business requirement, they may offer focused solutions.

    You appear to be working with --this is How I'm doing things, but we're not clear of the What you are trying to accomplish.
    I'm not sure what you mean.

    There are 4 dropdown fields with values that are stored in tables.
    As you choice the 4 fileds another field Named Documentnumber has also to be populated with the field values from the other fields binded together as one number.

    Field one dropdown say: 100
    Field two dropdown say: 150
    Field tree dropdown say: 050
    Field four dropdown say: 400

    The values field 1 to 4 get there dropdown from different tables - not associated to any other table.

    Field Five should than be populated: 100-150-050-400-xxxx (where xxx should be a number not yet used in table: final metadata column docnumber).
    Hope this clarified things. Nothing to calculate or am I wrong?

  5. #5
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    So are you storing these 4 drop-down selections in each record?
    If so, then wouldn't this "Field Five" just be a calculated field, based on those selections and your other number, i.e.
    Code:
    [Field1] & "-" & [Field2] & "-" & [Field3] & "-" & [Field4] & "-" & [OtherNumber]

  6. #6
    snhnic is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2017
    Posts
    8
    Quote Originally Posted by JoeM View Post
    So are you storing these 4 drop-down selections in each record?
    If so, then wouldn't this "Field Five" just be a calculated field, based on those selections and your other number, i.e.
    Code:
    [Field1] & "-" & [Field2] & "-" & [Field3] & "-" & [Field4] & "-" & [OtherNumber]
    Yep, this is what I'm trying to do. Now how do I make this happen as it is inserted in the 4 fields. and see the results as I fill in the fields?

  7. #7
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    If you make it a calculated field in your query, and then add the calculated field to your Form, it will update automatically as you make selections in the other fields.

  8. #8
    snhnic is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2017
    Posts
    8
    Quote Originally Posted by JoeM View Post
    If you make it a calculated field in your query, and then add the calculated field to your Form, it will update automatically as you make selections in the other fields.
    Thanks got this working now

  9. #9
    snhnic is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2017
    Posts
    8
    Well after every question is e next one.

    Now that I have manage this I got a final doc number [Field1] & "-" & [Field2] & "-" & [Field3] & "-" & [Field4] & "-" & [OtherNumber]
    Like this 100-15-250-21-xxxx Where xxxx supposed to be a new number. So to get a number is there a way to check this with values in the table column docnumber that might contain all sorts of combination in de 4 fields so 100-15-250-21-110 might exist or 200-13-250-20-110 So the last number has to be unique for the total number. I have 10.000 numbers so it is hard to say what number is used. Theree are many missingnumbers aswell. How do I check if a number exist en generate a new one based on the filed selection where the last 4 digits need to be unique within that sequens.

  10. #10
    Join Date
    Apr 2017
    Posts
    1,673
    Have an invisible textbox (field5) on your form, where current MAX value for final part of doc number with all 4 other parts matching selected ones is calculated as number. When creating a new doc number, use field5 + 1 for last part.

    Possible calculations may be based on DMax() function, or on SELECT TOP 1 ... query

  11. #11
    snhnic is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2017
    Posts
    8
    Quote Originally Posted by ArviLaanemets View Post
    Have an invisible textbox (field5) on your form, where current MAX value for final part of doc number with all 4 other parts matching selected ones is calculated as number. When creating a new doc number, use field5 + 1 for last part.

    Possible calculations may be based on DMax() function, or on SELECT TOP 1 ... query
    I feel stupid, but I don't get it to work.

    I now have a number as I mentioned before say: 60122-590-1. And need to see what the highest value is in the column say there is 60122-590-1.001 till 60122-590-1.010 then the new number needs to become 601122-590-1.011

    There can be values like 60122-390-2.001 ---- Where the last 3 digits are the numbers taht matches the string. So for all the combinations there can be 999 files. Hope you see waht I try to do here?

  12. #12
    Join Date
    Apr 2017
    Posts
    1,673
    I use an ID like this in my IT-Devices database, where DeviceID is in format "CCyyyymmdd000", where:
    "CC" determines device group (p.e. "CD" is for desktop computers, "CS" for servers, "DM" for monitors, "NM" for manageable switches, etc.);
    "yyyymmdd" determines registering/purchase/lease contract date;
    "000" is unique number for device in given group on given day. I.e. I can register p.e. up to 999 desktop computers on same date.

    The Device Registry subform (sfDevices with fmDevices as source) is bound to parent form, where device group is selected. On fmDevices is an unbound invisible text box which reads the current device group from parent form.
    Code:
    txtParentDevGroup = [Forms]![fmMain]![sfDeviceGrouping]!txtDevGroup
    The OnCurrent event controls, is the current row a new one, or an existing one. When the record is a new one, a text box is enabled with current date as default value for registering date (user can edit this date), and a button is made visible for registering the device. and all other controls on form are disabled until user registers the device or abandons registering pressing Esc or moving to another record without registering. When the device is registered, then registering date text box is disabled, device registering button is made not visible, and enabled statuses for controls the user is allowed to edit are set True (By default all controls are not editable - the user checks a control to allow edits for active row, and when finishing clicks a button visible in editing mode to save changes he did made.

    To calculate the new DeviceID, a couple of additional invisible text boxes are used.

    Code:
    txtCalcDeviceID.ControlSource = [txtParentDevGroup] & Format([txtRegDate];"yyyy") & Format([txtRegDate];"mm") & Format([txtRegDate];"dd")
    which calculates left 10 characters of new DeviceID

    Code:
    txtCalcDeviceID2.ControlSource = CInt(Nz(Right(DMax("DeviceID";"tblDevices";"LEFT(DeviceID,10)='" & [txtCalcDeviceID] & "'");3);0))
    which calculates current MAX value of last 3 right characters for DeviceID's where left 10 characters equal with txtCalcDeviceID

    When the button for registering the new device is cliked, a new DeviceID is calculated
    Code:
    Private Sub comRegisterDevice_Click()
        Me.txtDeviceID = Me.txtCalcDeviceID & Format((Me.txtCalcDeviceID2 + 1), "000")
        ...
    End Sub

  13. #13
    snhnic is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2017
    Posts
    8
    Thanks guys, got this working now

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

Similar Threads

  1. Replies: 5
    Last Post: 02-12-2016, 06:46 PM
  2. Replies: 2
    Last Post: 12-04-2015, 05:51 AM
  3. Sum numerical filed based on other field
    By BorisGomel in forum Access
    Replies: 0
    Last Post: 07-11-2012, 09:54 AM
  4. Sum one filed based on its contsest
    By BorisGomel in forum Access
    Replies: 2
    Last Post: 03-29-2012, 03:01 PM
  5. Replies: 1
    Last Post: 11-05-2010, 05:49 PM

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